Summing Distinct Records In Report Summary

Question

Post reply

Summing Distinct Records In Report Summary

treyagrimes

Ten Centuries

Points: 1001
More actions
July 8, 2015 at 3:00 pm

#299881

Hello,
I am hoping I might be able to get some assistance with an issue I'm having on a report. The summaries work great unless I have two or more incentive assigned to the drive (event).
As you can see below, if the drive does have two incentives (gift cards, t-shirts, etc) - it creates a row for each incentive assigned to the drive.
But my report then dynamically creates additional columns for the incentives while keeping the drive on one row in the report:
But then my summaries are off because it basically doubles the procedure projections, product projections, and procedures performed. I understand why it is doing this, but I am not sure how I can correct this.
I believe I might need to use the ROW_NUMBER () function - possibly. Any suggestions on how to resolve this.
And here is my simplified T-SQL in which I'm getting the data:
SELECT
DM.DriveID,
DM.FromDateTime,
Acct.AccountID,
Acct.Name,
Inc.Description [Incentive],
DPaCT.ProcedureProjection [Proc_Proj],
DPaCT.ProductProjection [Prod_Proj],
DPaCT.ProceduresPerformed [Proc_Perf],
DPaCT.ProductsCollected [Prod_Coll],
DPaCT.QNS [QNS],
DPaCT.FTD [FTD],
(isnull(DPaCT.ProductsCollected,0))-(isnull(DPaCT.ProceduresPerformed,0)) [DRBC]
FROM
Production.[dbo].rpt_DriveMaster DM
Left Outer Join Production.[dbo].rpt_Accounts Acct on DM.AccountID=Acct.AccountID
Inner Join Production.[dbo].rpt_CenterDetail CD on DM.CenterID=CD.CenterID
Inner Join Production.[dbo].DriveProjectionandCollectedTotals DPaCT on DM.DriveID=DPaCT.DriveID
Left Outer Join OBAPPS.[dbo].OBI_Incentives Inc on DM.DriveID=Inc.DriveID
WHERE
DM.StatusID <>5
AND DM.FROMDateTime Between '06/10/2015' AND '06/12/2015'
And CD.CenterID In (107)
ORDER BY
DM.FromDateTime, Acct.AccountID

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

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

pietlinden SSC Guru Points: 63519 More actions · Answer 1

You probably need to change the stored procedure something like this:

SELECT

DM.DriveID,

DM.FromDateTime,

Acct.AccountID,

Acct.Name,

Inc.Description [Incentive],

DPaCT.ProcedureProjection [Proc_Proj],

DPaCT.ProductProjection [Prod_Proj],

DPaCT.ProceduresPerformed [Proc_Perf],

DPaCT.ProductsCollected [Prod_Coll],

DPaCT.QNS [QNS],

DPaCT.FTD [FTD],

(isnull(DPaCT.ProductsCollected,0))-(isnull(DPaCT.ProceduresPerformed,0)) [DRBC]

,ROW_NUMBER() OVER (PARTITION BY Act.Name ORDER BY Acct.Name) AS rn /* note change */

FROM

Production.[dbo].rpt_DriveMaster DM

Left Outer Join Production.[dbo].rpt_Accounts Acct on DM.AccountID=Acct.AccountID

Inner Join Production.[dbo].rpt_CenterDetail CD on DM.CenterID=CD.CenterID

Inner Join Production.[dbo].DriveProjectionandCollectedTotals DPaCT on DM.DriveID=DPaCT.DriveID

Left Outer Join OBAPPS.[dbo].OBI_Incentives Inc on DM.DriveID=Inc.DriveID

WHERE

DM.StatusID <>5

AND DM.FROMDateTime Between '06/10/2015' AND '06/12/2015'

And CD.CenterID In (107)

/* note second change */

AND ROW_NUMBER() OVER (PARTITION BY DriveID ORDER BY DriveID)=1

ORDER BY

DM.FromDateTime, DM.DriveID

That way, you'll only return one record for each DriveID, so you won't get double counts. I may have the columns slightly off, but hopefully this will point you in the right direction.

treyagrimes Ten Centuries Points: 1001 More actions · Answer 2

Thanks pietlinden,

I used your query - except for the ROW_NUMBER in the WHERE clause - got an error about Windowed functions can only appear in the SELECT or ORDER BY clauses.

to get a row number for each record.

And then in my summary, I changed the summary expression from:

=Sum(Fields!Proc_Proj.Value)

to:

=Sum(IIF(Fields!RN.Value=1,Fields!Proc_Proj.Value,0))

to only summarize the first record.

Thanks again!

pietlinden SSC Guru Points: 63519 More actions · Answer 3

I should have realized that you can't use that expression in the filter. I was just trying to point you in the right direction (and that part seems to have worked!)

Glad you got it sorted.

Pieter