Summing Values in filtered rows only

问题:

I have a Report Builder 3.0 report that uses a group filter tied to a parameter to display data. How do I sum these columns on only the visible rows?

I need to sum Fields!PY_Dollars.Value, Fields!CY_Dollars.Value and also calculate py-cy and the % difference between the two

In case 2 where PY values are zero or null, I don't need to total the py values or do the comparison rows. I'm hiding those if the Parameters!Store.Value=1

Group Filter[bool expression = TRUE]:

=(
  (Parameters!Store.Value=2) 
  OR 
  (Parameters!Store.Value=1 AND (IsNothing(SUM(Fields!PY_Dollars.Value)) OR (SUM(Fields!PY_Dollars.Value)=0))) 
  OR
  (Parameters!Store.Value=0 AND SUM(Fields!PY_Dollars.Value)>0)
)

回答1:

OK I'm pretty sure I figured out how to get this done. Moved the summing of CY dollars value and PY dollars value into the sql query that builds the report. I kept the grouping the same, with the same filter group. The minor changes are shown here:

=(
  (Parameters!Store.Value=2) 
  OR 
  (Parameters!Store.Value=1 AND (IsNothing(Fields!Sum_PY_Dollars.Value) OR (Fields!Sum_PY_Dollars.Value=0))) 
  OR
  (Parameters!Store.Value=0 AND Fields!Sum_PY_Dollars.Value>0)
)

To get the correct CY values to show through the report, the expression requires the Switch() function.

=SUM(
    Switch(
        (Parameters!Store.Value=2), (Fields!Sum_CY_Dollars.Value) ,  
        (Parameters!Store.Value=1 AND (IsNothing(Fields!Sum_PY_Dollars.Value) OR Fields!Sum_PY_Dollars.Value=0)), (Fields!Sum_CY_Dollars.Value) , 
        (Parameters!Store.Value=0 AND Fields!Sum_PY_Dollars.Value>0), (Fields!Sum_CY_Dollars.Value)
        )
    )

This basically tells it to sum only what the filter is showing. Since PY only needs to be summed where it exists,