July 1, 2011 at 1:30 pm
I have a report that displays duplicate values. To eliminate the dupes, I grouped the rows based on a certain field and everything looks great.....except for the totals.
What appears to be happening is the report is still summing up values that are hidden. What's the best way to get a sum based off of visible values?
July 1, 2011 at 2:24 pm
You should probably work on eliminating the duplicates from the data - instead of trying to hide the duplicates in the report.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 1, 2011 at 2:33 pm
Already tried that....if only it were that easy 🙂
What I'm doing is making a list of all sales that have been paid and shipped. If a partial payment has been made on an account, we'll still ship part of that shipment. In our ERP system, this will show the record more than once on the same purchase #, so the sale total will duplicate as many times as the shipment is split.
See where I'm going here?
There's nothing I can do to fix that, it is how it is.
July 2, 2011 at 8:22 am
Sorry - that doesn't make sense to me. If you are getting actual duplicate values - you have an issue with your query. Otherwise, your totals would be correct.
Either you are not getting duplicates in the data and you have grouped the data incorrectly causing invalid totals, or you are getting duplicates and need to eliminate those duplicates.
If you could show the query you are using, some sample data and expected results we should be able to figure out a query that does not have duplicates or how to total the values correctly.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 3, 2011 at 11:30 pm
That's correct if you do at the query level. It will eliminate all issues
July 4, 2011 at 11:00 pm
When doing sums on stuff that can be repeated... add a field to your SQL:
, RANK() OVER (PARTITION BY OrderNumber ORDER BY OrderNumber, PartialPaymentID) As Ranker
Then in your report, do this:
=Sum(IIf(Fields!Ranker.Value = 1, Fields!Amount.Value, Nothing))
I think this is the defacto way to handle it in SSRS.
July 5, 2011 at 7:58 am
Jeff,
Here's the query. Our 3 database systems don't actually interface with each other. I failed to mention that in my previous post. But here's the query; there's nothing overly complex about it.
SELECT DocNo
,Created
,CreatedBy
,Terms
,SoldToPONumber
,ShippingAmount
,TotalTax
,SubTotal
,GrandTotal
,CASE
WHEN KEY1 IN ('QA', 'EA') OR SOURCE = 'ECOM'
THEN ((SubTotal * 5)/100)
ELSE ((SubTotal * 8)/100)
END AS Commision
,SoldToCMAccountNo
,ShipToCMAccountNo
,KEY1
,KEY2
,SoldToContact
,SoldToCompany
,OrderDate
,ReqShipDate
,SalesOrder
,CASE
WHEN MonthInvBalZero <= 9
THEN '0' + CAST([MonthInvBalZero] as varchar) + '/' + CAST([YearInvBalZero] as varchar)
ELSE
CAST([MonthInvBalZero] as varchar) + '/' + CAST([YearInvBalZero] as varchar)
END as InvPaid
,Source
,Salesperson
,Customer
,CUSTOMTEXT03
,CUSTOMTEXT04
FROM DocumentHeadersItems AS DH
JOIN SorArCombined AS SA
ON AA.DocNo = ('AA' + SA.CustomerPoNumber)
JOIN CONTACT1 AS C1
ON DH.SoldToCMAccountNo = C1.ACCOUNTNO
WHERE CreatedBy != 'PARTS'
AND KEY1 IN (@ORG)
AND CustomText01 = @SPECIALIST
AND LEN(INVOICE) > 0
AND
(
(
CUSTOMER = 'QAA'
AND (INVOICEDATE IS NOT NULL)
AND (DATEPART(MM,ReqShipDate) = DATEPART(MM,@END))
AND (DATEPART(YYYY,ReqShipDate) = DATEPART(YYYY,@END))
)
OR
(
CUSTOMER != 'QAA'
AND (YearInvBalZero > 0)
AND (YearInvBalZero = DATEPART(YYYY,@START))
AND (MonthInvBalZero = DATEPART(MM,@START))
AND (YearInvBalZero = DATEPART(YYYY,@END))
AND (MonthInvBalZero = DATEPART(MM,@END))
)
)
ORDER BY DocNo ASC
And here's my result set (not a full set, obviously). As you can see, this shipment has the same document number, but 3 different order numbers. This is a shipment that was split into 3 seperate shipments as seen by the order numbers. The totals carry over from our ERP system and those are also duplicated. I have to group by the Document # in order for the data to be consisent and "clean". The only issue is the sum at the bottom.
Doc #CreatedOrder DateShip DateOrder #Grand TotalSub TotalCommision
AAAQ567574/19/20115/17/20115/17/2011900383"$20,256.74""$20,256.74""$1,012.84"
AAAQ567574/19/20115/2/20115/5/2011037682"$20,256.74""$20,256.74""$1,012.84"
AAAQ567574/19/20115/17/20115/17/2011802316"$20,256.74""$20,256.74""$1,012.84"
As soon as I group by Document #, my numbers match up with our accounting records (when I manually sum them myself).
SSRSTips, I'll give your suggestion a try also.
July 5, 2011 at 8:00 pm
I don't see what columns you are totaling, and what the expected total should be.
For this example, what is the expected value - and what is the actual value you are getting?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 6, 2011 at 7:16 am
Jeff,
This dataset example needs to have these sums
Grand Total: $20,256.74
Subtotal: $20,256.74
Commission: $1,012.84
But what my report is giving me is
Grand Total: $60,770.22
Subtotal: $60,770.22
Commission: $3,038.52
I only want to see one, distinct Doc # in this report. Since there are three different Order #'s attached to this Doc #, I need to group by Doc # number to get my desired result. I don't care about the Order # data, just the Doc # data. The Order # just shows that this shipment had 3 shipments. So when I group by Doc #, the report shows the correct numerical money data, but it isn't summing the visible values.
July 6, 2011 at 7:52 am
This is my full-time job, I use it every day. What I suggested will work.
July 6, 2011 at 1:58 pm
SSRSTips,
I wanted to finish explaining to Jeff what was going. I'm not a fan of unanswered questions 🙂
I just tried your solution and it actually didn't work, but it did spark an idea on how I can get my desired results.
What I did was use the Ranker field as a division field. Since I was getting 3 duplicate values when grouping, I took the field and divided it by the ranker value
=(Fields!GrandTotal.Value/Fields!Ranker.Value)
So when I was getting these results without the grouping on Doc #
Doc #CreatedOrder #Grand Total
AAAQ567574/19/2011900383"$20,256.74"
AAAQ567574/19/2011037682"$20,256.74"
AAAQ567574/19/2011802316"$20,256.74"
AAAQ567904/26/2011301569"$10,896.51"
AAAQ576545/15/2011608971 "$7,659.13"
This game me the results I wanted when grouped together
Doc #CreatedOrder #Grand TotalRankerWithoutRankerWithRanker
AAAQ567574/19/2011900383"$20,256.74"3"$60,770.22""$20,256.74"
AAAQ567904/26/2011301569"$10,896.51"1"$10,896.51""$10,896.51"
AAAQ576545/15/2011608971 "$7,659.13"1 "$7,659.13" "$7,659.13"
Thanks everyone for your help!
July 6, 2011 at 2:38 pm
What SSRS tips suggested should have worked for you. I blogged about a slightly different but very similar approach to the same problem here.
The blog is pretty detailed and may help you figure out where you went wrong following SSRS Tips comments.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply