incorrect Sum or RunningValue from group footer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

incorrect Sum or RunningValue from group footer

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2008-04-22 : 11:50:11

I've read a bunch of threads and can't seem to find an exact problem as what I'm having.

I believe my problem is simple enough to understand, but might not be that simple to solve. I'm hoping otherwise.

My report shows columns:

CustomerNumber

CustomerName

SummedInvoiceAmounts (summed from SQL query)

Region

The problem is that we have the same CustomerNumber with multiple CustomerNames:

CustomerNumber CustomerName Sum Region

ABC advancedballoon 1000 East

ABC AdvancedBall. 1000 East

As you can see they are the same company but in our Accounting system they show up mulitple times (about 20 companies like this). This is from clerks adding in multiple ways or misspelling, etc. Can't fix, but have to work around.

Anyhow, I have my detail row that shows two lines for the companies like this and one line for the companies that are correct. I have this report I have added two groups "Region", "CustomerNumber".

If I put all the detail Fields down in the CustomerNumber footer and hide the detail row the report looks fine to the eye. When adding a Sum down in the Region footer field though; it pulls in the added amount and makes the total wrong.

I tried running value, but it's basically the same as the sum for what I need and it doesn't work. Is there a simple way to do this using the reporting tool? In Crystal this was very easy, but I haven't figured this out yet. Any help would be greatly appreciated. Thanks.

--------------------------------------------------------------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 14:01:24

One method to deal with this is to add a bit field in your result set which designates whether its a duplicate record or not. This bit field will be updated as follows;-

UPDATE y
SET y.Unique=1
FROM YourTempTable y
INNER JOIN (SELECT CustomerNumber,MIN(CustomerName) AS CustName
FROM YourTable
GROUP BY CustomerNumber)t
ON y.CustomerNumber=t.CustomerNumber
AND y.CustomerName=t.CustName

and in report take sum like this

=SUM(IIF(Fields!Unique.Value=1,Fields!YourName.Value,0))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources