SQL Table shows up regardless of IIF statement

0
votes

Currently I have a SSRS report that has 3 tables.

I have defined 3 parameter values as such:

  • Table A = 1
  • Table B = 2
  • Table C = 3

I also created 3 Tables:

  • Table A
  • Table B
  • Table C

I also created the following iif statements

  • =IIF(Parameters!Transaction.Value(0)=1, true,false)
  • =IIF(Parameters!Transaction.Value(0)=2, true,false)
  • =IIF(Parameters!Transaction.Value(0)=3, true,false)

My system doesn't error out but this query doesn't work - in the sense that it still shows all 3 tables regardless of the parameters chosen.

What am I doing wrong here?

1
sqlreporting-servicesssrs-2019
Does this answer your question? SSRS - Set default parameters and datasources based on folder it is deployed to – Maciej Los
Unfortunately it does not – lostcoder
I assume your iif statements are in the visibility condition for the tables? That would be worth clarifying and perhaps changing the question title to "... regardless of visibility condition" (if that edit is permitted). – Steve Lovell

1 Answers

0
votes

There quite a bit of detail missing from your question but I'm going to guess that..

  1. "I created 3 tables" means you created 3 table(tablix) controls on the report, not you created 3 database tables.
  2. You want to show/hide these tables based on the parameter selection
  3. Your parameter is multi-value
  4. The IIF statements you show are in the hidden property of each table.

If any of this is wrong, please update you question to clarify.

The problem with your expressions is that they only check the first selected parameter value (as you are referencing index (0)), so the result will be the same for all three, everytime.

To get round this you can do the following...

=("|" & JOIN(Parameters!Transaction.Value,"|") & "|").Contains("|1|") =False

Repeat this for the other two tables replacing "|1|" with "|2|" or "|3|".

All this does is JOIN() all the selected parameter values separated by a pipe | symbol which will give us something like

1|2|3  or 1|2 for exmaple

Next we add a pipe at the start and end to give us

|1|2|3|  or |1|2| for exmaple

this makes if safe to use if you have a parameter value of 10 or more and check for the existence of "1" would return true even if 1 was not selected but 10 was.

Finally we check if the concatenated values contain a string |1|, |2| etc and compare the result to false. This way we don't need to use an IIF statement