Totaling unique values or summing firsts

Question

Totaling unique values or summing firsts

zuma01

SSC Eights!

Points: 943
More actions
January 28, 2010 at 6:35 am

#209543

HI,
This one is baffeling me, please help.
We have a report that brings back orders based on a contract parameter specified. The orders can have several rows but all rows for that particualr order will have the same cost.
I need to total these costs but only cost per order (row). I have tried using =First(Fields!OrderCost.Value) in the cost field and it only displays the one cost but when totaling in the footer it includes and totals all.
So I need a way of totaling just the first cost for each order.
Hope this makes sense, happy to provide more info if required

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

mcoombes SSC Journeyman Points: 90 More actions · Answer 1

Hi there,

The FIRST(...) command only displays the first result, the other results have still been collected by the SQL statement and are held in memory. That's why when you do a SUM (...) it shows all results.

Have you tried SUM(FIRST(...)) to only add up the FIRST(...) values? Otherwise have you tried tweaking the SQL statement directly to only pull out 1 value per record?

Yours,

Mikey

zuma01 SSC Eights! Points: 943 More actions · Answer 2

Thanks for the reply.

I have tried the SUM(first) but this doesn't seem to exist as far as I can see. We need to bring back the other records as some of the data on the line is different.

There must be some way but it is well hidden

Thanks again

wschampheleer SSCertifiable Points: 5504 More actions · Answer 3

Here is one way to solve this.

In your query, add an extra column where the amount is only kept on the first line of each order and made zero for all other lines, similar to

SELECT orderid

,product

,amount

,CASE WHEN ROW_NUMBER() OVER (PARTITION BY orderid ORDER BY orderid) = 1 THEN amount

ELSE 0

END AS amount2

FROM (SELECT 1 AS orderid

,'abc' AS product

,5 AS amount

UNION ALL

SELECT 1 AS orderid

,'def' AS product

,5 AS amount

UNION ALL

SELECT 1 AS orderid

,'ghi' AS product

,5 AS amount

UNION ALL

SELECT 2 AS orderid

,'jkl' AS product

,10 AS amount

UNION ALL

SELECT 2 AS orderid

,'mno' AS product

,10 AS amount) x

Calculate your total using amount2 instead of amount.

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

zuma01 SSC Eights! Points: 943 More actions · Answer 4

Thanks.

I will have a look at that but it seems a lot of work for such a simple request. I was hoping to be able to do it with an expresion within the report design for various reasons.

I appriciate you reply and have learnt a few things that will solve other issues from it but if anyone has a way of doing this withing the report design rather than TSQL that would be great.

Cheers