January 23, 2015 at 7:42 am
Hi ,
My report is having three fields : NAME || Level || ID .I have a single dataset and a filter to my dataset. The dataset filter should work like , if ID = 101 is selected show only LEVEL=30 students, if ID 102 is selected show students of LEVEL= 1,2,3 . The first scenario works but the second doesnot
🙁
i would like to achieve something like :
Filer on Filed ID
=iif(Parameters!ID.Value=101,"30",
iif(Parameters!ID.Value=102,"1" or "2" or "3",
Parameters!ID.Value))
January 25, 2015 at 12:52 pm
I suggest passing the parameter to SQL and letting SQL handle filters in its WHERE clause (if you can modify the query/stored procedure). I believe using a dataset query will run the query on the server, return all results back to the report, and then filter out the ones that aren't wanted - this is considerably less efficient, especially for larger datasets.
If you can't edit the query to parse the parameter to SQL, I suggest re-writing your dataset to be like this. It may not be standard, but it works for me (the below isn't tested code, so it may contain syntactical errors):
=iif(
Parameters!ID.Value=101 and Fields!ID.Value = 30
,"1"
, iif(
Parameters!ID.Value=102 and Fields!ID.Value = 1
or Parameters!ID.Value=102 and Fields!ID.Value = 1
or Parameters!ID.Value=102 and Fields!ID.Value = 2
or Parameters!ID.Value=102 and Fields!ID.Value = 3
,"1"
, iif(
Parameters!ID.Value = Fields!ID.Value
, "1"
, "0"
)
)
)
the above would give an expression that results to 1 (show) or 0 (don't show), modify the dataset filter value to "1". That way you only have one expression to focus on.
You may not be getting many responses in the SQL Server subforum as there is a dedicated subforum for Reporting Services ( http://www.sqlservercentral.com/Forums/Group416.aspx ).
January 26, 2015 at 11:47 am
keshav_nitrkl (1/23/2015)
Hi ,My report is having three fields : NAME || Level || ID .I have a single dataset and a filter to my dataset. The dataset filter should work like , if ID = 101 is selected show only LEVEL=30 students, if ID 102 is selected show students of LEVEL= 1,2,3 . The first scenario works but the second doesnot
🙁
i would like to achieve something like :
Filer on Filed ID
=iif(Parameters!ID.Value=101,"30",
iif(Parameters!ID.Value=102,"1" or "2" or "3",
Parameters!ID.Value))
As mentioned you should work this logic into your stored procedure or embedded query, rather than in the report itself. It is more efficient that way. If you have test data to play with then I can illustrate better.
----------------------------------------------------
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply