SSRS IIF and OR in same expression

Category: sql server reportingservices


JorgenVH on Mon, 26 Sep 2016 08:46:18

I'm making a report in Reportbuilder 2012 and I'm adding an expression to hide a certain field if it has no input. If I add the following expression it works perfectly


But now I want it to be hidden when it has no input or when it's a certain Report Type, 'Register Date' in this case. I enter the following expression and now it suddenly starts showing up even if it has no input.

Or (Fields!WDS_ReportType.Value = Parameters!ReportType_RegisterDate.Value)

I've also tried it with an IIF statement but this gives me the same problem

=IIF(((CountRows("WorkDataSetsFromSegReqId") = 0) Or
(Fields!WDS_ReportType.Value = Parameters!ReportType_RegisterDate.Value)), True, False)

What is wrong with my expression?


TUSG on Mon, 26 Sep 2016 08:53:24

It looks like you have an extra parenthesis. Try this:

Or (Fields!WDS_ReportType.Value = Parameters!ReportType_RegisterDate.Value))

or try switching True and False positions:

=IIF(((CountRows("WorkDataSetsFromSegReqId") = 0) Or
(Fields!WDS_ReportType.Value = Parameters!ReportType_RegisterDate.Value)), False, True)

JorgenVH on Mon, 26 Sep 2016 09:28:51

Still doesn't work. With both options tested :/

TUSG on Mon, 26 Sep 2016 09:42:08

Shoot. Try displaying both parameter value and field value on your report first and see if you've the correct fields and data types. Or remove countrows condition and evaluate the second condition results.



JorgenVH on Mon, 26 Sep 2016 10:10:37

Still doesn't work... pfff, stuck with this for days now

TUSG on Mon, 26 Sep 2016 10:36:04

could you be confusing parameters label with parameter value?

JorgenVH on Mon, 26 Sep 2016 11:52:48

No, it is exactly how it should be. Asked a co-worker about my expression and he also doesn't see the problem.

Xi Jin on Tue, 27 Sep 2016 02:20:23

Hi JorgenVH,

Your expression is right. Also I have made some test about your expression and it works.

So in your scenario, where did you set the visibility expression? Could you please us more detailed information or screenshots about your report structure or some sample data if possible?

Xi Jin.

JorgenVH on Tue, 27 Sep 2016 06:53:21

I set it on a header row. So this header row should be hidden when there is no data or when the report type = Register Date. 

When I try to execute this expression it returns me True so I don't see what the problem is... 

=IIf(First(Fields!WDS_ReportType.Value, "WorkDataSetsFromSegReqId")=Parameters!ReportType_RegisterDa‌​te.Value, True, False)

Xi Jin on Fri, 30 Sep 2016 07:56:40

Hi JorgenVH,

First for your executed expression, there exists a First() function in it. So the expression only returns the first record of your dataset. As you said the result of this expression is true, please check your dataset records see if the first record is containing the same report type with the parameter.

Then please refer to my following sample, I have also set this visible expression on the header row. And it works successfully.

You can see from the result, when the WDS_ReportType field value is same as the parameter's value. The header row is hidden. So please refer to my sample and share us the difference in your report. Also if you can share us more information like sample data with screenshots, this will help my much on troubleshooting your issue.

If you still have any questions, please feel free to ask.


Xi Jin.