Search code examples
expressionsql-server-2014ssrs-2008-r2

Use SSRS Expression to Hide Tablix Based on Parameter Selection

I have a report that displays sales by county or by agent - but the user cannot select both. The @AgentOrCounty parameter is hard-coded: Agent = 0 and County = 1. And the @AgentID parameter only accepts one value but the @CountyID accepts multiple values. And my report has two tablixes: one for the dataset and one to display an error message if the user selects both an @AgentID and @CountyID

parameters

tablix

What I hope someone can help me with is how I can write two expressions to hide the tablixes based on the user selection.

One additional issue I am experiencing is that the @CountyID parameter is a multi-value parameter that I have to pass a NULL value to to allow the report to run if the user wishes to display by Agent.

Right now, on my Error Message tablix, I have the following expression:

=IIF(LEN(Parameters!AgentOrCounty.Value)=0,TRUE,FALSE) OR IIF(LEN(Parameters!AgentID.Value)=0,TRUE,FALSE)

But this doesn't hide the Error Message tablix when the user runs the report correctly for Agents.

When I think in logical terms, I would like the Error Message table to be hidden if the @AgentOrCountyID = 0 and @CountyID is NULL or the @AgentOrCountyID = 1 and @AgentID is NULL.

And I would like the report data tablix to be hidden if @CountyID and @AgentID are both not NULL.

Any suggestions?

Solution

  • To hide the error message tablix, I used the following expression:

    =IIF((Parameters!AgentOrCounty.Value=0 And Parameters!CountyID.Value(0)="NULL") 
Or (Parameters!AgentOrCounty.Value=1 And Parameters!AgentID.Value Is Nothing),True,False)

    And to hide my data set tablix when the user selected both an agent and a county, I used the following expression:

    =IIF(Len(Parameters!AgentID.Value)>0 And Len(Parameters!CountyID.Value(0))>0,True,False)