CODEWITHSUNDEEP
reporting-services
Vian Ojeda Garcia
Vian Ojeda Garcia

Reputation: 867

SSRS returned data type not valid for the aggregate function

I am making a SSRS, when executing the query

SELECT Brand.[KeyOffer],
   COALESCE(SUM([Key Offer].Revenue),0) as Revenue
FROM [Brand Key Offer] Brand
LEFT JOIN [Key Offer] ON Brand.Keyoffer = [Key Offer].[Key Offer] AND [Key Offer].[Date] = '7/05/2017'
WHERE Brand.[Brand] = 'SMART'
GROUP BY [Brand].[KeyOffer]
ORDER BY [Revenue] DESC

But when I preview the reprot, I get this warning message.

Warning     [rsAggregateOfInvalidExpressionDataType] The Value expression for the textrun ‘Textbox21.Paragraphs[0].TextRuns[0]’ uses an aggregate function with an expression that returned a data type not valid for the aggregate function.       c:\users\t-aordiz\documents\visual studio 2015\Projects\TelemarketingRS\TelemarketingRS\Telemarketing Revenue.rdl   0

Ive been to a lot of topics but cant seem to find a way to solve this problem.

Upvotes: 14

Views: 9478

Answers (3)

Nopadol S.
Nopadol S.

Reputation: 366

The same happened to me when I tried to change an output's datatype from datetime to varchar.

Try to delete the file YourReportFile.rdl.data and preview again. It worked for me in VS2015.

Upvotes: 25

niktrs
niktrs

Reputation: 10056

You can use conversion in your expression to the appopriate type like below

= CDec(Fields!Revenue.value)

Also try the following SQL alternatives

COALESCE(SUM([Key Offer].Revenue),0.00)

or

CAST(COALESCE(SUM([Key Offer].Revenue),0) AS DECIMAL(10, 2))

(it's a little different from what Tim suggested)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522626

It looks like the error is being caused by the call to SUM(), possibly because you are feeding it a non numeric type. To test this, you could try casting [Key Offer].Revenue to decimal:

SELECT
    Brand.[KeyOffer],
    COALESCE(SUM(CAST([Key Offer].Revenue AS DECIMAL(10, 2))),0) AS Revenue
FROM [Brand Key Offer] Brand
...

Upvotes: 0

Related Questions