passing null when all items chosen in multi select

[sds814]

I have a dataset (dsCategories) that populates a multiselect drop down list (prmPropCat). If the user selects all the Categories in prmPropCat I want to pass null to the stored procedure instead of all the Categories. prmPropCat is passed as a parameter to another dataset called dsInfo. How do I compare the values in dsCategories and prmPropCat? This is what I have so far:

IIF((Parameters!prmPropCat.Count = COUNT(Fields!CAT_NM.Value, "dsCategoryList"), Nothing, Parameters!prmPropCat.Value)

But I get an error that Fields cannot be used in the parameters tab.

Thanks for the help!

 

[RiverGuy]

Why do you want to pass in NULL? I'm not sure I understand your logic. If someone selects all of your listed categories, wouldn't you want to return them all?

 

[sds814]

Hi RiverGuy,

Sorry for not explaining in more detail.

For each parameter I have, I pass the values of the parameter as xml to the stored procedure. In the stored procedure, my logic is: (MP.CTGY_ID IN (SELECT CAT_ID FROM @CAT_ID_TMP)). So if the user selects all the Categories in the drop down list an xml tree has to be created in Reporting Services for the Categories and then the xml is parsed in the stored procedure. I would rather pass null if all the categories are chosen and have this code in the stored procedure:

(((@CAT_ID IS NULL) OR (MP.AG_PRI_PROP_TYP_CTGY_ID IN (SELECT CAT_ID FROM @CAT_ID_TMP))).

This in return would improve the performance of the stored procedure.

I've read other posts where you can pass parameters as comma delimiters, but even though the performance could be faster than xml it still would be better to pass null.

 

[RiverGuy]

I understand. I'm not sure right off the top of my head how to do it. Normally, I pass in a comma delimited string of values and use a splitting function in SQL to return a table variable of values to join on. This is without using the XML.

Are you using SSRS 2008? If so, you might see if you can set a variable in your report to reference for the value.

 

[sds814]

SSRS 2005. I was thinking of creating datasets with the counts of all the parameters I'm using and then have these as internal and hidden report parameters. That way in the dataset that passes the chosen Categories to the stored procedure I can compare the count of the Categories chosen to the total count of Categories. The code would be like this:

IIF(Parameters!Categories.Count = Parameters!Cat_Count.Value, Nothing, Code.BuildXML(Parameters!Categories.Value))

I wasn't sure what performance impact it would have to the report creating these datasets and comparing the two datasets as noted above.

 

[aila84]

Hi Guys,

I had a similar problem. I have a multi select dropdown list populated from the following dataset(Country):
SELECT txtName FROM TblCountries
ORDER BY intOrder

My Main dataset looked as follow:
SELECT * FROM TblClients WHERE( txtCountry IN (@txtName) OR txtCountry IS NULL)

I needed the Main dataset to return all the records even if the txtCountry IS null when the user has selected ‘Select All’ option, and ignore null values when the user specified the countries (just selected few)

The solution:
I have created a dataset (CountryCount) that counts the possible countries:

Select Count (*) AS MyCount from TblCountries

My Main data set has 2 parameters now: txtName and MyCount
txtName is a multi-value , available and default values come from query; Country dataset
MyCount is a hidden parameter, available and default values come from query; CountryCount dataset

Then I have edited Min dataset and in the Filters section I have used following logic

Expression:
=IIF(Parameters!MyCount.Value=Parameters!txtName.Count,true,isNothing(Fields!txtName.Value))
Operator:
=
Value:
=IIF(Parameters!MyCount.Value=Parameters!txtName.Count,true,false)

And this did the trick.

Hope it helps,
Joanna