Hello all,
I had editted this post to ask a better question.
I had noticed that certain expressions won't work in details. Is this true ?
when I tried using an IIF expression, I always get a blank or an empty field even if it was a text.
could someone clarify this ? I don't know whether it is just me or is it a limitation.
Sincerely,
Bernard Ong
Hi
I am using sql reporting service 2005 with .NET 2.0.
I have created a custom dll file for report and put this dll in appropriate folder.
Report is running fine in designer project.
but when I am trying to view this report after uploading to report manager it give me an error like
Failed to load expression host assembly. Details: The type initializer for 'CableReporting.Utilities' threw an exception. (rsErrorLoadingExprHostAssembly)Is there any solution for that?
thanks and Regards
Apurv Shah
IBM India pvt Ltd
I previously had an ASP.NET 1.1 site running on my IIS 6.0 server (not the default website) with Reporting Services running in a subdirectory of that website. I recently upgraded to ASP.NET 2.0 for my website and was greeted with an error when trying to view a report. The error was very non-descript, but when I checked the server logs, it recorded the details as "It is not possible to run two different versions of ASP.NET in the same IIS process. Please use the IIS Administration Tool to reconfigure your server to run the application in a separate process."
First of all, I could not figure out where and how to do this. Secondly, I decided to try to also change the Reporting Services folders to run ASP.NET 2.0 and when I did, I was greeted with the following message when attempting to view a report:
"Failed to load expression host assembly. Details: StrongName cannot have an empty string for the assembly name."
Please help.
HI,
I tried to run the following query.
Code Block
WITH YesterdayCTE AS
(
SELECT type = 'Members Joined Yesterday'
, Borrowers = (select count(*)
from LoanApplication INNER JOIN
Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (LoanApplication.SubmittedOn >= GETDATE()-1) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
, Depositors = (select count(*)
from CDOrder INNER JOIN
Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (CDOrder.SubmittedOn >= GETDATE()-1) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
),
MonthlyCTE AS
(
SELECT type = 'Members Joined Last Month'
, Borrowers = (select count(*)
from LoanApplication INNER JOIN
Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (LoanApplication.SubmittedOn >= GETDATE()-30) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
, Depositors = (select count(*)
from CDOrder INNER JOIN
Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (CDOrder.SubmittedOn >= GETDATE()-30) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
),
YearlyCTE AS
(
SELECT type = 'Members Joined Last year'
, Borrowers = (select count(*)
from LoanApplication INNER JOIN
Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (LoanApplication.SubmittedOn >= GETDATE()-360) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
, Depositors = (select count(*)
from CDOrder INNER JOIN
Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (CDOrder.SubmittedOn >= GETDATE()-360) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
),
combinedCTE AS
(
SELECT * FROM YesterdayCTE
UNION ALL
SELECT * FROM MonthlyCTE
UNION ALL
SELECT * FROM YearlyCTE
)
SELECT *
, Members = Borrowers + Depositors
FROM combinedCTE
But I get the following error message.
An error occurred while reading data from the query result set.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (Microsoft Report Designer)
===================================
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (.Net SqlClient Data Provider)
Please can anyone help me to make this work?
Thanks
Sql date base help 1 do not now where to start I am trying simple to run the sql database I have in expression web which I built within visual web developer 2005. I thought. I would upload it in with expression web in the website using ftp and it would run on the server. No can anybody help the web site uses all ASP.NET pages , I have everything tested and working right on my computer, I have three simple data bases I up date through master details forms pages I run on my computer and not on the server.The data is of names addresses, ex and I display them with a grid view control on the web site pages. I used visual web developer 2005 to do this.And I do not use ASP.NET 2.0 Membership, Role and Profile features. I tested it on a server got a server error connecting to sql server 2005. I think I need sql server 2005 installed on the server and is the sql server 2005 just a program for and to run sql data basesAt this point I’m lost.I do not know how it works Do not know anything about sql data bases other than the above.I need a simple explanation no how to get the sql data base to work on the web site and a quick explanation on how all this works. In the Recipe: Deploying a SQL Database to a Remote Hosting Environment (Part 1) It said unfortunately there hasn't historically been a super-easy way to accomplish this. Copying the .aspx files and compiled assemblies to the remote system is pretty easy (just FTP or copy them up). I coped the data base files by ftp but what are compiled assemblies? The release candidate of a new SQL Server Hosting Toolkit is this, what I need and how do I use it.The toolkit allows you to work with SQL Express, SQL Server 2000, and SQL Server 2005 databases locally and then easily transfer your schema and data and install themWhat is a schema? And how do I use it to setup and recreate the database contents - both schema and data - on the remote hosted site. Bill
Deploying a SQL data base to a server have looked at the sql server videos from Microsoft and now can create a fantastic data base but cannot get a simple signal table data base published and running on a sql server 2005. Why is it so difficult to get help or a video or publication from Microsoft on the most important things? What good is the most fantastic data base if you cannot get it up and running it was the same for a simple piece of script to get an asp web form to post the results of a form as an e-mail took me 3 months to scrap together the script to make it work? How this with the data base. As I have explained do not know the process never down it, first time too build and try to put a data base on a website.
If you publish to sql express all you need to do is ftp it up to the server and it will work I think.
But it’s not so simple with SQL server 2005 it’s much more complicated. So I have installed the new publishing wizard in Microsoft for visual web developer 2005 to help get it running on the server. This was the simplest way I think. Have three data bases the data bases are so simple as they only have one table each.
Does anybody now of a video or article that explains from the start how to get a simple data base up and running no SQL server 2005 for a beginner that explains in detail the possess.
Any idea why this expression would not work in SSRS?
Based on a expression on a group textbox I get 0 records that match:
=Iif(Fields!ACCRUAL_CODE.Value <> "A", CountDistinct(Fields!LOAN_NBR.Value), 0)
I've evaluated in the proc & there should be a count of 29 records.
I found a work around by using this in the stored proc & I get my record count:
ACCRUAL_CODE = CASE WHEN BASE.ACCRUAL_CODE = 'A' THEN 0 ELSE 1 END
and changed the expression in SSRS to this & it works:
=sum(Fields!ACCRUAL_CODE.Value)
Is this a known issue with strings? From searching on this site I see that string evaluations are widely used so I do not see why it wouldn't work. I've also see this when filtering the dataset on anything that is a character. Any suggestions?
Hi,
I am not understanding this part of the problem. I am currently reusing a stored procedure that has a ".." as part of the select statement.
I can't put the select statement up here due to privacy but I have found the error where the error states the following:-
Invalid Column Prefix: AM, invalid table name.
I noticed that part of the select statement was the following:-
AM..Field1
I tried executing this stored procedure in the query analyzer and it works fine, but when I tried executing it in SSRS, it gives me the error. After searching through the internet for possible causes, I found out that it was the ".." was giving the error. Anyone knows why ? I found out that it was supposed to bypass any users and permissions to the table.
Thakns !
Bernard
Greetings,
I have an expression in a precedence constraint that is returning false when it should return true. This is the expression that returns false:
((5500 / 9990) * 100) > 10
The following expression returns true. I did the division (5500 / 9990) myself and substituted the resulting value:
((0.55055055055055055055055055055055) * 100) > 10
Why is the first expression returning false? I'm stuck in the mud up to my axles on this and I know I'll probably feel like a fool when I learn the answer...
Thanks,
BCB
Hello,
I have to build dynamic sql statement in an SQL task.
The SQL statement is way over 4000 char.
The expression builder complains the length of the expression.
Any work around to this limitation?
Thanks a lot!
Hi There,
I am trying to set a variable with this default value using expression. This works in tsql but doesn't in ssis. Can anybody tell me what is wrong with this?
dateadd("dd", -1, datediff("dd", 0, getdate()))
Thanks.
I created am inventory table with few columns say, Servername, version, patching details, etc
I want a tracking of the table.
Let's say people are asked to modify the base table and I want a complete capture of the details modified and the session of the user ( ) who (system_user) is actually modifying the details.
I have the following data
MASTER
id
name
DETAIL
id
master_id
name
I want a perform a query where i can get all the rows of the master table which have no relationed rows in detail table.
How can I do that???
I need to combine the details of two tables and narrow the data included.
in my tutor table i have
tutorLastName, tutorSessions and tutorSkills
in my term table i have
week, date, session_Name, tutorName
i need to combine the tales to show:
(from the term table) week, date,session_Name, tutorName,
(from the tutor table) tutorSkills.
i then need to narrow the table down so it shows only tutors with guitar skills and the term dats when the session_name is empty
i can run the two searches sepereately but cannot combine them i am tryin to use Where date = "whateverdate" AND session_name is null
AND tutorSkills = guitar
please help and tell me where im going wrong
Hi
Can i able to see the all table structure at once throught the Select
statement.
How?
thansk
asm
I Use the below QUERY to fetch the index details of a table from SQL Server 2000 database .
EXEC sp_helpindex 'mytable'
Is there any alternative for the above query .
Because the above query is not fetching some indexes in some version of SQL Server database .
I am trying the above query against SQL Server 2000,2005 and SQL Server 7 versions .
How to query Table and its column details
View 2 Replies View RelatedHello,
I'm French, so sorry for the faults.
I want to make a report with a table who gives some figures of a family and elements of her subfamily. For example, for the Fruit family I have like subfamily the apple, the pear, the banana.
I tried to make a table with a group but to aggregate some figures of the subfamily dataset, calculations are complicated.
So I make two report:
- one for a dataset for the subfamily with a parameter (the family).
- one for the dataset of the family.
In the second, I want to put the under-report (the first) in the details of the table while passing in parameter the family.
But, I have an error when I want to see the result:"
Error: Impossible to post the under-report."
If you can help me that would be sympathetic!
Thanks
Mickaël
I am having problems exporting data into a flat file using specific code page. My application has a variable "User::CodePage" that stores code page value (936, 950, 1252, etc) based on the data source. This variable is assigned to the CodePage property of desitnation file connection using Property expression.
But, when I execute the package, the CodePage property of the Destination file connection defaults to the initial value that was set for "User:CodePage" variable in design mode. I checked the value within the variable during runtime and it changes correctly for each data source. But, the property of the destinatin file connection doesn't change and results in an error.
[Flat File Destination [473]] Error: Data conversion failed. The data conversion for column "Column01" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[DTS.Pipeline] Error: The ProcessInput method on component "Flat File Destination" (473) failed with error code 0xC02020A0. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
If I manually update the variable with correct code page and re-run the ETL, everything works fine. Just that it doesn't work during run-time mode.
Can someone please help me resolve this.
Thanks much.
I want to export the datas of a table into a excel file using an Sql Query.
When I execute that query the Excel file should be created automatically in the given path with all the datas in that table.
Can anybody give me a solution for this problem?
Hi Friends
I faced a problem when using Northwind sample Database. On of the table in the database is 'Order Details'. I am unable to query on the table because of the space in the name ('Order'<space> 'Details') of the table. When I remove the space in the name, the reports associated in Cognos are not working properly. Let me know whether there is any purpose to put the name like that and if I want to see the data in query mode how it is possible.
Thanks in advance
Satish
Hi,
Can anyone tell me is it possible to put static data (a string) inside details section of a table?
I've tried to find some more pieces of information in the Report Definition Language Specification, but to no avail.
When I put some strings as a detail's cells values they are displayed in the preview window in VS.
But thay are invisible when I open my report using ReportViewer in my app.
So does it means that the only accepted value of a cell within the details section of a table is a name of the field from DataSet (something like =Fields!FieldName.Value) ?
Regards,
Stan
I have created a linked server using my local SQL2005. The linked server can be seen as a linked server, but the database can not been expanded to see the tables, stored procedures, views, and other details regarding the linked server. The only method that I have found that will allow me to see all the details is to use XP's Remote Desktop Connection to access the database. Has anyone else experienced this problem, if so, how did you resolve it?
View 1 Replies View RelatedI am using table object to present report.
table row1 has all the column names and table row2 has the data.
How can i put a horizontal line between column names and the data rows.
Thank you very much.
Hi
I have came across a table in SQL server 2000 which named 'Order Details' in the sample 'Northwind' database which is available with the product. Am using the eval version . Generally no table name exist with a 'space' between the words. But the table 'Order Details' exist in the Northwind sample database.
Due to the naming convention i can't run sql queries on that. Is anyone aware of this type of issues.
Pls help
I have a scenario,
We have equipment table which stores Equipment_ID,Code,Parent_Id etc..for each Equipment_ID there is a Parent_Id. The PK is Equipment_ID Now i want to select the Code for the Parent_Id which also sits in the same table. All the Parent_Id's also are Equipment_ID's.
Equipment table looks like :
Equipment_ID Code DescriptionTreeLevelParent_Id
6132 S2611aaa 4 6130
11165 V2546bbb 3 1022
15211 PF_EUccc 5 15192
39539 VP266ddd 4 35200
5696 KA273eee 3 3215
39307 VM2611fff 4 35163
39398 IK264ggg 4 35177
There is another table for Equipment_Tree which has got Equipment_Tree_ID,Parent_Id and Equipment_ID does not has the Code here.
Select query where i need to select the Code for all Parent_Id's.
Hi.
I building some reports with SSRS, and showing data in a table. The table has one Details row that forms at the end multiple rows with the tree view (+ and -).
Is there a way to open the tree view upwards and not downwards so that the children of level 2, 3 etc come over the parent row?
I've goggled it everywhere, but I don't seem to find the answer. If anyone could help me out, I would appreciated.
Thanks
Novi
I am trying to create a matrix report to work similar to a pivot table, where when a user clicks on the count, the details are displayed. When I add the action to open a sub-report, how do I pass in the parameter values for the group that was selected? meaning if in row group- I have company name, and column group I have job title, when the count gets clicked, i need to pass in the company name for the row that was clicked along with the column group. How can I proceed?
View 2 Replies View RelatedUsing SQL against a DB2 table the 'with' key word is used todynamically create a temporary table with an SQL statement that isretained for the duration of that SQL statement.What is the equivalent to the SQL 'with' using TSQL? If there is notone, what is the TSQL solution to creating a temporary table that isassociated with an SQL statement? Examples would be appreciated.Thank you!!
View 11 Replies View RelatedHello,
Probabaly a silly question yet as a DOTNET developer, I'm trying to simulate DTS when for example, I don't have permission to perform DTS on a production server.
In particular and interested regards caching of rows before the service decides to flush the buffer and write to the target table. Safe to assume DTS is cursor based?
Hi
I was studying Common Table expression in Sql server 2005.
I have written the code
Declare @PictureArray as varchar(200)
Set @PictureArray = '';
with UserProfile_CTE(UserPicture)
As(
select @PictureArray = @PictureArray + '~' + PictureName from UserPicture where UserProfileID = 1102
select @PictureArray
)
select * from UserProfile_CTE
But I am getting the error
Incorrect syntax near '='
I am getting the error in the lineselect @PictureArray = @PictureArray + '~' + PictureName from UserPicture where UserProfileID = 1102
But I don't know the reason for this,
Kindly advice
Regards
Karan
Hi all,
My report has a number of tables.
These table design is as [Total(numeric),Year(numeric),Month(varchar(15)].
Each table on report is populated based on individual tables in Mngt Studio.
My problem is.If table is empty,the table on report does not get populated/displayed.It is hidden.How do I return a message,Example {"No Value for table A"} instead and show the table in report only when the table has some value.
I have written an expression like this.But it just hides the table if its empty,and im not able to return a value "No Value"instead.
=IIF(Fields!Month.Value IS Nothing,"No Value for table A",False)
What is the SQL Server equivalent of DB2 common table expressions? Forexample,with gry(year,count) as(select floor(sem/10),count(distinct ssn)from gradesgroup by floor(sem/10))select year,sum(count) Head_Count from grygroup by yearhaving year >= 1980;N. ShamsundarUniversity of Houston
View 2 Replies View RelatedI have inserted a table in the layout. I want to create in the header of this table a field that sums up a set of values from a iif statement. So My expression is =sum(iif(Fields!aloc.value=2, Fields!balc.Value, 0)).
What I'm trying to do is sum all the values where aloc=2.
I Get an error saying The value expression for the textbox €˜textbox134€™ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.
That data type of balc is Float.
What am I doing wrong or what do I need to do to fix this?
I have no schooling on Reporting Services or SQL. I've been learning as I go for the past 6 months.