CODEWITHSUNDEEP
sql-serverreporting-servicesssis
Geo
Geo

Reputation: 336

Varying Data Type error SSIS

I am having an issue with a SSRS report cell that is aggregating 2 fields for me, using the same code. There is a mixed data type error here, I believe because of the 0 and use of "-" I have the report to run for different months and oddly enough I am only seeing a #ERROR for two of my months. I've tried adding conversions throughout the code but it doesn't seem to fix it. Can anyone help point out why I am receiving an #ERROR in my aggregates?

=IIF(Sum(Fields!ID30activity.Value + Fields!ID60activity.Value + Fields!ID90activity.Value) = 0, "-", Sum(Fields!ID30activity.Value + Fields!ID60activity.Value + Fields!ID90activity.Value))

The warning I am receiving from SSIS:

Warning 2 [rsAggregateOfMixedDataTypes] The Value expression for the textrun ‘Textbox523.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.

Upvotes: 1

Views: 240

Answers (1)

Geo
Geo

Reputation: 336

Okay well a bit of trial and error solved this for me, I added CDEC( in front of both SUM portions to fix this:

=IIF(Sum(CDEC(Fields!ID30activity.Value + Fields!ID60activity.Value + Fields!ID90activity.Value)) = 0, "-", Sum(CDEC(Fields!ID30activity.Value + Fields!ID60activity.Value + Fields!ID90activity.Value)))

Upvotes: 1

Related Questions