I am using the Switch function in Reporting Services to determine the visibility of a row. It happens that I am using more that one column or field to test my expression like so:

=Switch(Parameters!View.Value = "Green" AND Fields!Tax.Value = "N",TRUE,Parameters!View.Value = "Current" AND Fields!PastVal.Value = 0 AND Fields!DatePay.Value = 0 AND Fields!Comment.Value = 0,True)

With the expression above, I want that if the first part is true, the row should be hidden likewise for the second part of the expression, I want to hid a row when all the conditions are met. But this not yielding the desired result. I equally tried with another expression like so:

=IIF(Parameters!View.Value = "Green" AND Fields!Tax.Value = N",False, IIF(Parameters!View.Value = "Current" AND Fields!PastVal.Value = 0 AND Fields!DatePay.Value = 0 AND Fields!Comment.Value = 0,True,False))

I anticipate you help.

Hi Zionlite,

I have created sample data as below:-

SELECT  'N'  Tax,0 PastVal  , 0 DatePay, 0  Comment
 UNION
  SELECT  'N'  Tax,0 PastVal  , 1 DatePay, 0  Comment
  union
   SELECT  'Y'  Tax,1 PastVal  , 0 DatePay, 1  Comment
 UNION
  SELECT  'Y'  Tax,1 PastVal  , 1 DatePay, 1  Comment

I applied your expression for hide

=IIF(Parameters!View.Value = "Green" AND Fields!Tax.Value = "N",False, IIF(Parameters!View.Value = "Current" AND Fields!PastVal.Value = 0 AND Fields!DatePay.Value = 0 AND Fields!Comment.Value = 0,True,False))

When I select Green value in parameter

I have output as below:-

When I select Current value in parameter:-

Now my question, what you expecting from this output. I mean if you provide the more cases in my data (inserting more rows in my sample data). then what are the rows you want to display?

I will try again based on your inputs

Thanks

Prasad

Thanks so much Prasad.
looks so much like what I am developing. Except that the "Green" parameter controls a deferent row where I have "Y" for Yes and "N" for No.
I want that when "Green" is selected, all rows with "N" be hidden.

When "Current" is selected and PastVal = 0, and DatePay = 0 and Comment = 0, the row should be hidden.

This will mean that the expression are not depended on another. I can write the express for one at a time but can't get it to work as I try to nest it. I anticipate your response.

Thank you

 

Hi Zionite,

Then you must change the expression to

=IIF(Parameters!View.Value = "Green" AND Fields!Tax.Value = "N",True, IIF(Parameters!View.Value = "Current" AND Fields!PastVal.Value = 0 AND Fields!DatePay.Value = 0 AND Fields!Comment.Value = 0,True,False))

Now after that change , I select Green value in parameter

I have output as below:-

When I select Current value in parameter:-

Hence this modified expression will work.

Kindly let me know its working or not

Thanks

Prasad

• Proposed as answer by Vicky_LiuMicrosoft contingent staff, Moderator 58 minutes ago

The first part will always work but not the second part of the expression. Did you get it to work for you in your test environment?

Looking at using Switch?

Tha

Hi Zionite,

Then you must change the expression to

=IIF(Parameters!View.Value = "Green" AND Fields!Tax.Value = "N",True, IIF(Parameters!View.Value = "Current" AND Fields!PastVal.Value = 0 AND Fields!DatePay.Value = 0 AND Fields!Comment.Value = 0,True,False))

Now after that change , I select Green value in parameter

I have output as below:-

When I select Current value in parameter:-

Hence this modified expression will work.

Kindly let me know its working or not

Thanks

Prasad

• Proposed as answer by Vicky_LiuMicrosoft contingent staff, Moderator Thursday, March 26, 2015 6:06 AM

Hi Zionlite,

Per my understanding that the expression you are using not works fine for the second part "Parameters!View.Value = "Current" AND Fields!PastVal.Value = 0 AND Fields!DatePay.Value = 0 AND Fields!Comment.Value = 0,True".

I have tested on my local environment and if the three fields :PastVal,DatePay,Comment are both string or numeric values, the first expression using the switch function will works fine.

So, In your scenario, Please check the data type of this three field in the DB first and try to modify the query like below to have a test:
=Switch(Parameters!View.Value = "Green" AND Fields!Tax.Value = "N",TRUE,Trim(Parameters!View.Value) = "Current" AND CBool(Fields!PastVal.Value) = 0 AND CBool(Fields!DatePay.Value) = 0 AND CBool(Fields!Comment.Value) = 0,True)

If the above modification didn't work, please also test this one:
=Switch(Parameters!View.Value = "Green" AND Fields!Tax.Value = "N",TRUE,Trim(Parameters!View.Value) = "Current" AND Trim(Fields!PastVal.Value) = 0 AND Trim(Fields!DatePay.Value) = 0 AND Trim(Fields!Comment.Value) = 0,True)

If your problem still exists, please feel free to ask.

Regards,
Vicky Liu

Thanks Vicky for the reply. None of the expressions worked in my environment.

PastVal,DatePay,Comment are all integer data type. Below is an example of what I have:

Users want that when Parameter is set to "Current" and PastVal,DatePay,Comment are all 0, that row should be hidden. Likewise, when IsUpdated - Tax(and Parameter selected is Green) is N, that row should be hidden.

This part of the expression(below) only returned the columns labels (below as well)

IIF(Parameters!View.Value = "Current" AND Fields!PastVal.Value = 0 AND Fields!DatePay.Value = 0 AND Fields!Comment.Value = 0,True,False)

I anticipate your reply.

Thank you

Hi Yookos,

Per my understanding that when you using the expression "IIF(Parameters!View.Value = "Current" AND Fields!PastVal.Value = 0 AND Fields!DatePay.Value = 0 AND Fields!Comment.Value = 0,True,False)" to hide and show the row, you got all the row hidden and only display the table header, but the correct result is only hide the rows when Parameter is set to "Current" and PastVal,DatePay,Comment are all 0, right?

If so, it seems that the condiftion "Fields!PastVal.Value = 0 AND Fields!DatePay.Value = 0 AND Fields!Comment.Value = 0" didn't work in the expression.

I have tested on my local einvronment and can't reproduce the issue, I recommend you to redesign an same rdl report as this one to have a try if your report is simple.

If you still have the problem. Could you please post both the report and the dataset with sample data to us by the following E-mail address?  It is benefit for us to do further analysis.
E-mail: sqltnsp@microsoft.com

Regards,
Vicky Liu