October 31, 2006 at 9:18 am
In my report design I have a main report and 4 sub reports. To generate datasets for all these sub reports and main report , we have a single procedure. In the procedure I am passing a variable S,T,U,R and K. Based on the passed variable this procedure returns the available values from that table. The values are comming exactly the way I wanted.
My problem is : If I use this procedure in SSRS and pass a variable it is getting the fields from the first declared table only. Procedure parameters return exact values but does not generate dataset based on the provided variable.
Please help URGENT!
November 1, 2006 at 10:47 am
You need a case statement in the WHERE clause of your procedure.
My situation is doing a Data Dictionary where I give the user an option to choose a table name or choose "All Tables". I pass that into my Proc which looks for columns for the table(s) chosen (BTW, I HATE the way SSC is formatting my code. I can't get it all together nice and neat. If anyone has suggestions on how to get around this without having it all mushed together on one line, I'd appreciate it):
Declare @tblName varchar(100) Set @tblName = 'All Tables'
Select so.[name] as TableName, sc.[name] as ColumnName
from sys.objects so
join sys.columns sc on so.Object_ID = sc.Object_ID
where so.Type = 'U'
AND so.[name] = CASE WHEN @tblName = 'All Tables' THEN so.[name]
ELSE LTRIM(RTRIM(@tblName)) END
Does that help?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply