3

I'm trying to set up a filter against a "Totals" column in an SSRS report using an expression for the field.

I have a multivalued parameter setup with the values value1, value2, value3, and value4 as options.

Before the filter, this totals column just simply added the integer values from the different columns. I want to add functionality to where you can check which of the values the user has selected.

Similar to below (Pseudo code, because I can't figure out the syntax):

=IIF(Parameters!<Parameter>.Label="value1",Fields!value1.Value,0) +IIF(Parameters!<Parameter>.Label="value2",Fields!value2.Value,0) +IIF(Parameters!<Parameter>.Label="value3",Fields!value3.Value,0) +IIF(Parameters!<Parameter>.Label="value4",Fields!value4.Value,0)

All values in the fields being added are simple integers. I'd appreciate any help anyone can provide. Thanks!

Sev09
  • 883
  • 2
  • 12
  • 27

2 Answers2

3

Assuming a parameter called Include, you can use an expression like this:

=IIf(InStr(Join(Parameters!Include.Value, ","), "value1") > 0, Fields!value1.Value, 0)
    + IIf(InStr(Join(Parameters!Include.Value, ","), "value2") > 0, Fields!value2.Value, 0)
    + IIf(InStr(Join(Parameters!Include.Value, ","), "value3") > 0, Fields!value3.Value, 0)
    + IIf(InStr(Join(Parameters!Include.Value, ","), "value4") > 0, Fields!value4.Value, 0)

This uses the Join function to create a comma-delimited string of the selected parameters, checks for a particular parameter value being selected, then add either the corresponding value or 0 to the total.

Here's a simple report with the expression:

enter image description here

All values selected:

enter image description here

Two values selected:

enter image description here

Ian Preston
  • 38,816
  • 8
  • 95
  • 92
  • This approach is good enough for simple cases but it has the problem that if there are ten fields called `value1` to `value10` then every time `value10` is selected, `value1` will also be included in the total (as well as being boring to type and maintain those field names if used across several reports). – Chris Latta Jun 12 '13 at 02:51
  • 1
    @ChrisLätta, you can deal with the case above by updating the `IIf` expression slightly: `IIf(InStr("," & Join(Parameters!Include.Value, ",") & ",", ",value10,")`. I thought about custom code for this, but for four columns it's probably a step too far. Agree for more columns it would probably tip over to custom code. With custom code it's helpful to have tight coupling between parameter values and field names; hardly inconvenient but just another consideration. All depends on the circumstances at the time. – Ian Preston Jun 12 '13 at 08:43
  • 1
    Agreed - for simple cases in one report with a few fields and non-overlapping field names your method is certainly quick and easy, +1 – Chris Latta Jun 12 '13 at 12:23
3

You can acheive this in a simple way without stringing together a whole bunch of IIF functions and specifying each and every field to be used. What if you want to change the name of a field or add fields?

Using custom code we can have a simple and robust way of performing this calculation. Right-click on the non-design surface of your report, click Report Properties... and then click on the Code tab. Insert the following code:

Function CalcTotal (ByVal param As Parameter, flds AS Fields) AS Integer
  Dim Result As Integer
  Result = 0
  If param.IsMultiValue then
    For i as integer = 0 to param.Count-1 
        Result = Result + flds(param.Value(i)).Value 
    Next 
  End If 

  Return Result
End Function

This takes the parameter object (not value) and iterates through the values selected to perform the calculation using the fields in the Fields collection.

Now for your expression, just use:

=Code.CalcTotal(Parameters!Include, Fields)
Chris Latta
  • 20,316
  • 4
  • 62
  • 70
  • A great option. I decided to go with a quick expression-defined field instead, however. – Sev09 Jun 12 '13 at 15:17