Search code examples
t-sqlreporting-servicesgroup-bysumiif

SSRS Report generating #Error without details

This is a sub section of my tablix.

enter image description here

These are invisible columns that I will pull out into text boxes so that I can reference elsewhere. The data I have to reference has to be in the main group row or else i get en error message.

This is the code in column A

=Sum(iif(Parameters!season.Value = Fields!season.Value, Fields!pkg_due_amt.Value, 0))

This is the code in column B and it generates an error. 

=Sum(iif(Fields!inDateRange.Value = TRUE and Parameters!season.Value = Fields!season.Value, Fields!pkg_due_amt.Value, 0))

If I adjust in column B I had this code:

=Sum(iif(Fields!inDateRange.Value = TRUE and Parameters!season.Value = Fields!season.Value, 1, 0))

The only difference between the two lines above is that I am trying to sum up a field pkg_due_amt rather then 1. The 1 in the true field doesn't generate an error the pkg_due_amt does.

This is the first issue. As you can see to the right of the 701947. is an #Error.

repor

The second issue is that it appears like this when generated/previewed. If you look at the above image the first/general header row is also generating an #Error. That is the top highest most grouping. When I tried replacing the pkg_due_amt with a 1 it once again worked but this is not helpful. What I need is a summation of the field.

Compilation Errors read as follows:

enter image description here

Warning 1   [rsAggregateOfMixedDataTypes] The Value expression for the textrun ‘Textbox794.Paragraphs[0].TextRuns[0]’ 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.  ... 0   0   

Warning 2   [rsAggregateOfMixedDataTypes] The Value expression for the textrun ‘Textbox814.Paragraphs[0].TextRuns[0]’ 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.  ... 0   0   

Warning 3   [rsAggregateOfMixedDataTypes] The Value expression for the textrun ‘Textbox789.Paragraphs[0].TextRuns[0]’ 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.  ... 0   0
Solution

  • It means that pkg_due_amt is not the same data-type as 0 probably as simple as one is an int and one is a decimal - you solve it by using the cast functions e.g. CDec(Fields!pkg_due_amt.Value), CDec(0)