SSRS dynamic group expression needs to be null if a parameter is null

0
votes

I have an SSRS report that I am doing dynamic grouping on. Regular grouping on a field name that is provided through a report parameter is working with no problems.

The problem that I am having is that I want to avoid the grouping if the parameter is null.

I tried what this article suggested to use (checking for null in the IIF statement) but it isn't working for me: http://www.optimusbi.com/2012/10/12/dynamic-grouping-ssrs/

NOT WORKING:

Setting GROUP_3 report parameter to NULL and checking for null in the grouping expression.

=IIF(Parameters!GROUP_3.Value is Nothing,1,Fields(Parameters!GROUP_3.Value).Value)

Result: The IIF expression doesn't seem to be evaluating the null value properly. I get this as the result...

The Group expression for the grouping ‘GROUP_3’ contains an error: The expression references the field '', which does not exist in the Fields collection. Expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case. (rsRuntimeErrorInExpression)

I also tried setting the parameter to 'blank' and this but I get the same error message.

 =IIF(Parameters!GROUP_3.Value = "",1,Fields(Parameters!GROUP_3.Value).Value)

Is there something I am doing wrong here? Any suggestions?

1
dynamicreporting-servicesgrouping

1 Answers

2
votes

The Iif() call evaluates all parameters passed to it. So when GROUP_3 doesn't have a value, you're trying to reference a non-existent member of the Fields collection in the third parameter of Iif().

It's possible (though ugly) to work around this with another embedded IIF() thusly:

IIF(Parameters!GROUP_3.Value is Nothing,1,Fields(IIF(Parameters!GROUP_3.Value is Nothing,"VALID COLUMN NAME",Parameters!GROUP_3.Value)).Value)

In case it isn't obvious, you need to replace "VALID COLUMN NAME" with a column name from your dataset. Doesn't matter which column it is as long as it's always in the dataset. This means if GROUP_3 is nothing it's using a valid column name reference in parameter three just to get around the error. The IIF() call will still default it to '1' and the code should behave as you desire.