SSRS Switch with an Or?
-
Hello,
I've been searching and can't find any answers to how (or even whether) this can be done so I'm hoping a regular here can help.
In SSRS I'm trying to build a SWITCH statement that has two options on the result. Something like this:
SWTICH(
Parameter = 1,"A" OR "B",
Parameter = 2,"C" OR "B",
Parameter = 3,"D" OR "B"
)Ultimately I'm looking for an IN statement on the result, since the result value could be either of two options. Is this even possible?
Any help would be greatly appreciated.
Thanks!
-
You're trying to do this in a parameter? Or to hide/show a Tablix? or what?
If it's to control the results of the report, can you push it down into the stored procedure that runs the report?
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses -
Unfortunately, I can't push it to the stored proc. I'm using this to filter a table since the proc has to return all results but the table must only show specific results.
-
As a thought on this, as you can't push this to the stored procedure, what about if you push it to the TSQL?
What I mean is in the SSRS package, you set it up to run a TSQL script as your data source. In the TSQL, you create a temporary table (or table variable) and insert the results of the stored procedure into the temp table. Then you select the results back from the temp table with the filter you need?
If that is not an option, then I am not sure how to do it.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
I am not sure I understand the request - what are you trying to accomplish? Can you be more specific?
There is the option of filtering the dataset, which you can then build a filter based on your parameter to include/exclude rows based on which parameter is selected. The dataset will still execute the stored procedure and return all data - but the filter will then be applied to exclude rows that don't meet the filter requirement.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.How to post questions to get better answers faster
Managing Transaction Logs -
Thanks for your input, guys:
Mr. Gale, I'm not sure I'm following. I'm using a stored proc for the dataset. Are you saying to modify the proc to have a table variable for the final data output of the proc? I'm not sure how that helps. (I may be missing something)
Mr. Williams, I'm trying to use the filtering in the table. The dataset returns all data since I'm using all data in other parts of the report; just this one table has to filter to reflect multiple values. I'm trying to apply the table filter with the switch code but I don't see how the switch function allows for multiple options in the result.
If parameter = 1, I just want the table to show data for "A" or "B" (It's this "or" part that I'm stuck on since Switch doesn't do "or" in this part of the function.) I could do "If parameter IN (1, 2) ,...." but that's not what I'm looking for here, and there doesn't seem to be an "IN" option (or an "OR" option) in the second part of the switch statement.
I can't think of any other way to filter the data, just for that table, to be either options when a specific parameter is selected.
Essentially the table needs to always display "B", but also whatever the parameter specifies, A, or C, or D, etc.
Does that clarify anything or did I just muddy the waters further?
-
Wouldn't a simple filter on the tablix suffice?
Use 3 different expressions
IF 1 THEN IN A and B
IF 2 THEN IN C and B
IF 3 THEN IN D and B
-
How exactly would that look? Would it be a nested statement? IIF statements have to have a value for when it's true and a value for when it's not true so not sure how that would work.
In the Filter section I currently have the expression as ProviderName =
=SWITCH(
Parameters!ProviderID.Value = 1,"REG1" ,
Parameters!ProviderID.Value = 4,"REG4" ,
Parameters!ProviderID.Value = 5,"REG5" ,
Parameters!ProviderID.Value = 6,"REG6"
)...which obviously doesn't do what I need. How do I switch this to an IF like you suggest to add "SYS" to each of them as well?
-
I like the approach that everyone else is offering, but thought I'd chime in to describe mine in a bit more detail.
My suggestion was to change the data source from stored procedure to TSQL. Then in the TSQL, you would have a CREATE TABLE #AllData (col1 datatype1, col2 datatype2, ...) statement which would hold all of your data from the stored procedure. Next line, you'd do an INSERT INTO #AllData EXEC <stored procedure>. Then you'd end the TSQL off with a SELECT * FROM #AllData WHERE (@ProviderID = 1 AND ProviderName = 'REG1') OR (@ProviderID = 4 AND ProviderName = 'REG4') OR (@ProviderID = 5 AND ProviderName = 'REG5') OR (@ProviderID = 6 AND ProviderName = 'REG6') OR ProviderName = 'SYS'. Assuming you always want to return ProviderName = 'SYS'. Otherwise adjust the WHERE clause as you see fit. I would probably replace the SELECT * with selecting the column names. To put it all together:
CREATE TABLE #AllData (
<column 1> <datatype 1>,
...
)
INSERT INTO #AllData
EXEC <stored procedure>
SELECT <column 1>,
...
FROM #AllData
WHERE (@ProviderID = 1 AND ProviderName = 'REG1')
OR (@ProviderID = 4 AND ProviderName = 'REG4')
OR (@ProviderID = 5 AND ProviderName = 'REG5')
OR (@ProviderID = 6 AND ProviderName = 'REG6')
OR ProviderName = 'SYS'
DROP TABLE #AllDataThis puts all of the data filtering on the SQL side. Depending on how much data comes back, you may even want to toss an index on the temp table (on ProviderName) which could help with performance. Downside to this is it puts load on tempdb so if the result set is huge and you have a lot of people looking at this report at the same time, you may have to watch tempdb for overgrowing the disk.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
But this will then filter the entire dataset, won't it? I can't do that because I need everything in the dataset for other parts of the report. It's just this one table in the report that needs to be filtered with this logic.
-
Yes, my approach will filter the whole data set. If you need it filtered in just one place, then my suggestion won't help you and will actually make things worse.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
You could use two datasets, and filter the second data set. Data retrieval times would double as your executing the proc twice but that’s how I would implement Brian’s solution.
My solution is on the tablix set the filter to have 3 different filters
=IIf(Parameter.Paramname.Value = 1, Dataset.column, Dataset.Column)
operator IN
filter expression A, B
second filter if 2 then , b c
third filter if 3 then b d
-
Ok, (honestly, I'm not intentionally trying to be obtuse here), but is this what you mean?
And the expression in the "Value" is =IIF(Parameters!ProviderID.Value = 1,"REG1","SYS" ) with one filter for each ProviderID value I have listed (I only showed 2 filters above). Because I tried this and it didn't work right. It didn't show anything in the table when I selected ProviderID = 1.
What did I miss?
-
Yeah thats what I was eluding too.
Something going on with my Visual Studio and not displaying results in the tablix as I have tried to repro this in some way but it's proving difficult if I can't see the data in my tablix.
But yeah a number of filters that are, in theory that should do it, but as I say having a bit of trouble in my sandbox at the moment to test it.
=IIf(Parameter!ProviderID.Value = 1, 'A,B', 'NOVALUE')
=IIf(Parameter!ProviderID.Value = 2, 'B,C', 'NOVALUE')
=IIf(Parameter!ProviderID.Value = 3, 'B,D', 'NOVALUE')
-
Yeah, that's not working either:
=IIF(Parameters!ProviderID.Value = 1,"REG1,SYS", "" )
Wouldn't all of these filters have to be true in order to display anything? They can't all be true at once though.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply