eliminating duplicates

[swhitten]

New to SRS. Trying to determine how to do something I would do in Crystal with conditional running total.

Imagine in the tables an invoice with a header table and a detail table.

The Header table has a doc number and doc amount.

Te Detail table has each of the item numbers that we sold on the invoice.

When I join them, the joined rows each have:
doc number, item number, doc amount.

So the doc amount is repeated multiple times. I need to do a sum of the doc amount, summing each doc amount ONLY ONCE, not for every row.

Ideas? I hope I explained that well. Thanks in advance.

 

[swhitten]

I did it!!!!! It's working!!!!! I used the Vendor Items report for testing.

Dataset:
SELECT IV00101.ITEMNMBR, IV00101.ITEMDESC, cast(IV00101.CURRCOST as decimal(18,2)) as CURRCOST, IV00103.VENDORID, IV00103.VNDITNUM, IV00101.ITMCLSCD,
cast(row_number() over(partition by iv00101.itemnmbr order by iv00101.itemnmbr) as decimal(18,2)) as rownum
FROM IV00101 INNER JOIN
IV00103 ON IV00101.ITEMNMBR = IV00103.ITEMNMBR

I did the row number calculated field in the data set so I could return it as a field value on the report. Had to do some funky stuff with casting as decimal; still not sure I fully understand all that.

I inserted the rownum field from the dataset onto the detail line of the report. So each line gets a row number - 1,2,3 if I have three detail lines.

Then the Sum fields for the Item header, the class footer, and the grand total footer say:

=Sum(iif(cdec(Fields!RowNum.Value)=cdec(1),cdec(Fields!
CURRCOST.Value),cdec(0)))

This is where I had to also do some funky cdec formatting to get the sums to work correctly. It kept telling me I was trying to aggregate values of different data types. Whatever ….

Essentially this says “If the row number is 1, give me the current cost value, else give me 0.” And it’s summing at the appropriate level – item, class, or grand total.

It seems SO simple now!!!!

I want to go do the invoice report next Thanks again for all your help!!!!

 

[jymm]

one small thing - if you have any other GP SSRS type things - you also might try the forum below - even though most of that stuff is only GP a few of those folks have some great experience to tap.

http://www.tek-tips.com/threadminder.cfm?pid=632

 

[swhitten]

Good suggestion. Thanks again for all your help! I got the sales/invoice report working last night too. Same process. Worked great.