January 28, 2010 at 6:35 am
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
January 28, 2010 at 3:37 pm
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
January 28, 2010 at 11:40 pm
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
January 29, 2010 at 12:16 am
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.
January 29, 2010 at 5:34 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply