SSRS Parameter Display Logic

See more:

SQL

SSRS

Could you please help me out with the below similar requirement for displaying Countries and States in the report

I have two Data sets one for Countries and other for States in the report and below are the details:

The Country Data set returns "USA" if USA is selected else "None" from the query used inside the Country Dataset.

Example :

Country
USA

IF no country is selected from the Application then

Country
None

The State data set returns the below rows if below are the selected States from the Application

state
Alabama
Alaska
Arkansas
California
Delaware
District of Columbia
Georgia

IF no State is selected from the Application then

State
None


Current Code :

=": "& Iif(Parameters!Countries.Count=0 or array.IndexOf(Parameters!Countries.Value,"None")>-1,nothing,Join (Parameters!Countries.Value,", ")) &
Iif(Parameters!Countries.Count=0 or array.IndexOf(Parameters!Countries.Value,"None")>-1,nothing,", ") &
Iif(Parameters!States.Count=0 or array.IndexOf(Parameters!States.Value,"None")>-1,nothing,Join (Parameters!States.Value,", "))

The End user can select an Countries or state from the front end of the Applciation and I need to display these parameters based on the below Business requirement in the report

User can select 1 or more countries or All Countries or subset of states or multiple states or a single state like explained below

Requirement is as below and now the objective is to display like the below
If (All Countries/States) then a text like "Business" to be displayed on the report out put
If (1 or more Countr(ies), all States) then Business: USA, UK
If (1 country, subset of States) then Business: NY, NJ, PA
If (1+ Countries, subset of States) then Business: USA (NY, NJ, PA), UK (London, Rushden)

The Colon and Commas needs to be strictly followed while displaying the report output with a space after comma after Country and state.

Any Solutions in terms of expressions or Custom code in SSRS and a not sure if this code could be handled at SSRS level or Custom Function .

I would like to use the below existing code and make changes on the same code.

=": "& Iif(Parameters!Countries.Count=0 or array.IndexOf(Parameters!Countries.Value,"None")>-1,nothing,Join (Parameters!Countries.Value,", ")) &
Iif(Parameters!Countries.Count=0 or array.IndexOf(Parameters!Countries.Value,"None")>-1,nothing,", ") &
Iif(Parameters!States.Count=0 or array.IndexOf(Parameters!States.Value,"None")>-1,nothing,Join (Parameters!States.Value,", "))

I have used Array of Index to return "None" if nothing is selected based on the dataset query

Could you please share a solution

Thanks in Advance