Ssrs 2012 is having a sum issue

SSAS and SSRS
1

In an SSRS 2012 report, I am getting the error message in a summation area where the data is grouped together:

uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.

For the statement, =Sum(Fields!Usage.Value * Fields!CaseRate.Value).

I changed the statement to the following:

Sum(IIF(IsNothing(Fields!Usage.Value),cdbl(0),cdbl(Fields!Usage.Value)) * IIF(IsNothing(Fields!CaseRate.Value),cdbl(0),cdbl(Fields!CaseRate.Value)),cdbl(0)).

After the above change, I got the error message:

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, the name of a containing data region, or the name of a dataset.
ssrs 2012 is having an issue with sum
I then changed the statement to the following:

Sum(IIF(IsNothing(Fields!Usage.Value),str(0),str(Fields!Usage.Value)) * IIF(IsNothing(Fields!CaseRate.Value),str(0),str(Fields!CaseRate.Value)),str(0))

and I got the error message:

uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.

Thus can you tell me how to change the original =Sum(Fields!Usage.Value * Fields!CaseRate.Value) so that it will work correctly? Also could you explain why you chose that logic so that it will work?

2

I think the problem is the second parameter to the sum:

Sum(IIF(IsNothing(Fields!Usage.Value)
       ,cdbl(0)
       ,cdbl(Fields!Usage.Value)
       )
   *IIF(IsNothing(Fields!CaseRate.Value)
       ,cdbl(0)
       ,cdbl(Fields!CaseRate.Value)
       )
   ,cdbl(0) /* second sum parameter */
   )

Sum(IIF(IsNothing(Fields!Usage.Value)
       ,str(0)
       ,str(Fields!Usage.Value)
       )
   *IIF(IsNothing(Fields!CaseRate.Value)
       ,str(0)
       ,str(Fields!CaseRate.Value)
       )
   ,str(0) /* second sum parameter */
   )
1 Like
3

That solved the issue