totaling a field in a group header (not the detail lines)
-
I've got a report that has a numeric field (formatted as C) in a group header line. The field is NOT a sum in the group header. It's value is =First(Fields!In_Stock_Cost.Value).
I want a table footer to have a sum of this field. I do NOT want to sum the field from all the detail rows as this will be the wrong number. It would be correct to sum the detail rows if I could specify something like sum(in_stock_cost.value) where part_no.value is distinct.
-
You should write an expression using an inline IF
But I don't understand what you mean by where part_No.Value is Distinct.
Not sure exactly what you need to do, but I have had to add a field to my dataset that handles this logic using a case statement, case when whatever then somevalue else 0 end as calcfield. Then sum the calcfield in the report.
-
The data I'm using repeats in_stock_cost for all the detail rows for a part. So a strait sum gives me the wrong value because it adds this value for every detail row. I need it to sum the values in the report field not from the data set.
Can I do that with an if or make it pick and choose which values it chooses from the data set.
How would I code it using an in line if.
I'm not sure my comment about distinct makes any since, I want to sum the field but only for the dataset rows with a distinct part no.
-
This example basically displays your problem Using AdventureWorks2000
Boils down to your going to have to eliminate the repeating amounts in your dataset.
If you run the following query in QA
select a.SalesOrderID, PurchaseOrderNumber, SubTotal + TaxAmt + Freight as TotalAmt, LineNumber, OrderQty, UnitPrice, LineTotal
from SalesOrderHeader a
join salesOrderDetail b on a.SalesOrderID = b.SalesOrderID
where a.OrderDate = '2004-06-01'
and a.SalesOrderID in (5003, 5005)
you get
SalesOrderID,PurchaseOrderNumber,TotalAmt,LineNumber,OrderQty,UnitPrice,LineTotal
5003 PO348186287 972.7850 1 1 440.1742 440.17419999999998
5003 PO348186287 972.7850 2 1 440.1742 440.17419999999998
5005 PO58159451 2313.1346 1 1 1716.5304 1716.5304000000001
5005 PO58159451 2313.1346 2 3 78.8100 236.43000000000001
5005 PO58159451 2313.1346 3 4 35.0935 140.374
So if I wanted to sum up TotalAmount the Total for Order 5003 is 972.785, Not (972.785 * NumberDetailItems) The problem w/ reporting services is there is no way to do this with this data set.
To resolve the problem you have to figure out a way to eliminate the repeating values for TotalAmount. One way would be to create a stored procedure, put the values to a temp table, and make the go through and make the all but the first value for each order = 0
Or in this case I have an item # for a case statement when its the first item # then make it the amount else zero
select a.SalesOrderID, PurchaseOrderNumber, case LineNumber when 1 then SubTotal + TaxAmt + Freight else 0 end as TotalAmt, LineNumber, OrderQty, UnitPrice, LineTotal
from SalesOrderHeader a
join salesOrderDetail b on a.SalesOrderID = b.SalesOrderID
where a.OrderDate = '2004-06-01'
and a.SalesOrderID in (5003, 5005)
Results =
5003 PO348186287 972.7850 1 1 440.1742 440.17419999999998
5003 PO348186287 .0000 2 1 440.1742 440.17419999999998
5005 PO58159451 2313.1346 1 1 1716.5304 1716.5304000000001
5005 PO58159451 .0000 2 3 78.8100 236.43000000000001
5005 PO58159451 .0000 3 4 35.0935 140.374
I have not found a way to do this otherwise
-
That is exactly my problem.
I wound up writing a second query and a second RS dataset that totaled what I needed. BUT, that means that I have to be very careful when editing this report because the total and addition of the values taken from the report could be different.
I'm just figuring out RS and deployed a production server about a month ago. Some of these seemingly simple things have thrown me.
Thank you for taking the time to help me.
-
This is the same problem thats been stumping me,
I felt soo stupid for not having solved such a seemingly simple problem. Guess reporting services does make these problems bigger than what they are
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply