How to calculate a sum based of a parameter selection on SSRS Report?

Question

I need some help maybe you can help not sure about the Payroll Actual vs Budget I need to change the calculation to the Total Payroll field since Brookwood wants the PTO field to hide but my calculation doesn't seem to work SSRS hates it, to be honest, can you take a look and see if my theory is wrong. Basically, it says if the parameter Include PTO is set to Yes then add Payroll + OT + PTO other wise add Payroll + OT. 

When I run it I get an error The Value expression for the textrun ‘Textbox126.Paragraphs[0].TextRuns[0]’ has a scope parameter that is not valid for an aggregate function.  The scope parameter must be set to a string constant that is equal to either the name of a containing group, when I add the name to the Dataset at the end of each iif statement get a rendering error.

Any Ideas

=IIF(Parameters!IncludePTO.Value=1,IIF(Sum(Fields!strCategory.Value="Payrole", CInt(Fields!mnyMTDActual.Value),CInt(0))) + IIF(SUM(Fields!strCategory.Value="OT",Cint(Fields!mnyMTDActual.Value),CInt(0))) + IIf(Sum(Fields!strCategory.Value="PTO",CInt(Fields!mnyMTDActual.Value),CInt(0))), IIF(Sum(Fields!strCategory.Value="Payrole", CInt(Fields!mnyMTDActual.Value),CInt(0))) + IIF(SUM(Fields!strCategory.Value="OT",Cint(Fields!mnyMTDActual.Value),CInt(0)))

Answer 1

Hi @Jannette Jones

I understand what you mean, since the PTO is part of the Category in the matrix, it is not convenient to use the Visibility expression.

So what about using filters? When IncludePTO is "No", filter out PTO data, otherwise keep it.

I made a simple test as an example. Here is my raw data.

Then according to your description, create the following matrix.

Then we randomly select a column, open the Filter panel in the Tablix property, and add the following expression.

Preview:

Best regards,

Aniya