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.

 

[jymm]

check out how to do the scope for the sum function.

http://technet.microsoft.com/en-us/library/ms159134.aspx

there are a bunch of ways around it if this does not help. I usually take care of it in the sql/data source, but when all else fails...

 

[swhitten]

If I sum, it still gets the row all three times Not sure what else to try. RunningValues does true running totals - no way to specify when to reset it. I miss Crystal

 

[jymm]

I may have mis-read the first note

so - are you doing the sql to get the joined rows or how are you getting the data?

I USE to miss Crystal - not anymore, so I felt your pain...

 

[swhitten]

Here's what the first three rows of data look like:
Cust ID Doc # Item # Doc Amt
12345 Inv123 A23 $100.00
12345 Inv123 B27 $100.00
12345 Inv123 C98 $100.00

This represents Invoice #INV123 for $100 total invoice (doc amt). This invoice has three lines on it - Items A23, B27, and C98. Each of those items has an individual cost, not pulled in here. The total of the three items is $100, which is the doc amt.

So if I want to total the outstanding invoices for this customer, it should be $100 total.

But everything I'm trying is adding the doc amount three times for a total of $300, which is not correct.

This is a query in SRS that joins the header table and the detail table. I need the detail item numbers to show on the report, but I need the customer total to be $100.

I hope that clarifies what I'm trying to do. thanks for your help!!!!!!!

 

[jymm]

so - are you getting this data using SQL in Visual Studio data source or through Reporting Services' report builder.

if you are doing the sql & Studio - you can make it happen... not sure I can help if you are using builder...

 

[swhitten]

Visual Studio BI tool. Not full Visual Studio install. But I am NOT using the scaled down Report Builder. Hope that makes sense.

 

[jymm]

perfect sense - any chance of posting the sql...

OR you can change what is in the cell of the invoice 'header' from sum(Doc Amt) to First(Doc Amt)

make sense?

 

[swhitten]

Here's the query for the data:
SELECT SOP30200.CUSTNMBR,
SOP30200.SOPNUMBE,
SOP30300.ITEMNMBR,
SOP30200.DOCAMNT
FROM SOP30200

LEFT OUTER JOIN SOP30300 ON SOP30200.SOPTYPE = SOP30300.SOPTYPE AND SOP30200.SOPNUMBE = SOP30300.SOPNUMBE

ORDER BY SOP30200.CUSTNMBR, SOP30200.SOPNUMBE

Here are three rows showing the duplicated doc amt:
AARON INVS308 5-FEE 938.93000
AARON INVS308 5-rec 938.93000
AARON INVS308 5-STDLABOR 938.93000

AARON is the customer.
INVS308 is the invoice #.
The 5- items are the individual line items sold on the invoice.
$938.93 is the invoice total amount.


My report has two groups - custnumbr and sopnumbe (invoice #). I would like a total at each level plus a grand total.

So when I'm done, I would like $938.93 to show as the total for INVS308 and since this customer only has one invoice on the list, that would also be the customer total.

I used your suggestion for First, and that gets me the correct Invoice total. But I don't know what to do now to get the correct total for the customer. First just gives me the value of the first invoice for the customer.

Progress!!! Thougths?

 

[swhitten]

I think I have customer subtotal working!!!! At the customer level, I did a sum like this:
=sum(Fields!DOCAMNT.Value,"Cust")

and it seems to be pulling correctly.

Now I just have to get the report grand total working!

 

[jymm]

ah - great plains reporting --- I feel your pain

summing at customer order is ok unless the customer has more than one invoice...

 

[swhitten]

false alarm. total by customer not working.

 

[jymm]

let me look at what I did with GP in the morning - I am not at HQ where those are... sorry...

 

[swhitten]

Thank you! I will be traveling tomorrow, so I may not answer until Monday. Thanks again!

 

[jymm]

I know the problem... (sorry - did not look closely enough yesterday)

the sop30200.docamt IS the total of the invoice. If you want each line item's total you really want sop30300.xtndprce.

SELECT SOP30200.CUSTNMBR, 
       SOP30200.SOPNUMBE, 
       SOP30300.ITEMNMBR, 
       SOP30200.DOCAMNT as InvoiceTotal,
       SOP30300.XTNDPRCE as ItemTotalPrice
FROM   SOP30200 
LEFT OUTER JOIN SOP30300 ON SOP30200.SOPTYPE = SOP30300.SOPTYPE AND SOP30200.SOPNUMBE = SOP30300.SOPNUMBE
ORDER BY SOP30200.CUSTNMBR, SOP30200.SOPNUMBE

then ssrs can sum all items for the invoice... make sense?

GP 9 here and I abuse SSRS a BUNCH for where we are lacking reports in GP.

 

[swhitten]

Jymm, Thanks for your help. It's nice to find someone with some GP knowledge also! I agree with your approach in this situation except for one thing - the total doc amount may include things other than just the item extended prices (freight, misc. charges, tax, etc.). So I really need the doc amt at the doc level and then be able to sub-total it at the customer, customer class, and grand total levels.

That's what I'm missing.

Here's another example:
SELECT IV00101.ITEMNMBR, IV00101.ITEMDESC, IV00101.CURRCOST, IV00103.VENDORID, IV00103.VNDITNUM, IV00101.ITMCLSCD
FROM IV00101 INNER JOIN
IV00103 ON IV00101.ITEMNMBR = IV00103.ITEMNMBR
ORDER BY IV00101.ITEMNMBR

Join the inventory item table with the vendor item table. The inventory item table has the Current Cost of the item. The 100xlg inventory item is purchased from three different vendors, so the join above yields three rows for item 100xlg - one for each vendor. Each line has the currcost on it. Summing the individual rows in this example would be totally invalid. I need to show the currcost for the inventory item (with the individual vendors listed below). Then I need to be able to subtotal at the item class and grand total levels.

I can get the currcost on the item line by using this expression: =first(Fields!CURRCOST.Value,"ItemNumber")

But once I have that, I can't sum with it to get the item class subtotal or the grand total.

Any other ideas? thanks!

 

[jymm]

ah - see we broke out the frieght (and such) into seperate item numbers since we wanted to be able to show it in 'other income' on my revenue reports.

Ideal world - what is the report trying to show.

cust Total 'invoiced' (less all of the 'other stuff'
Cust class Total
All total

??

I know that folks in the GP arena just develop views of the data that get them their info...

 

[swhitten]

Perfect world:

Grand Total $6,000
Customer Class Total $3,000
Customer ABC Total $2,000
Invoice 1234 $1,000
Item A Quantity 1 $50
Item B Quantity 25 $500
Item C Quantity 75 $$400
[The rest of the inv total is frt that I
don't want to show separately.]
Invoice 1235 $1,000
Item A Quantity 2 $100
Item D Quantity 20 $700
Item F Quantity 75 $200
Customer DEF $1,000
Invoice.....

But beyond this specific situation, I've used the running total with conditional in crystal many times in many reports over the years - whether GP data or SL data or other...

Even if I write a view or re-do my data or whatever, I still have the issue that I have a header level with the invoice total and multiple detail rows that belong to that header. In my other example, adding the detail rows is totally invalid. But I still need the current cost for the inventory item. I don't know how to create a view or a query that does anything other than repeat the current cost on each detail row

The only other thought I've had is to do a subreport. The main report will be based on one line per invoice - just the header level. The detail report will have the line item details - either the invoice details or the vendor item list in the second example.

But I really want to learn how to do the equivalent of conditional running totals

 

[jymm]

try this
first use this as your datasourece (change GP to your company DB name)

select cust.CustClas, clas.clasdscr, Cust.CustName, hdr.SOPNUMBE as InvoiceNum, dtl.itemnmbr, dtl.ItemDesc, dtl.QtyToInv, dtl.xtndPrce, hdr.DocAmnt
from GP..sop30200 as hdr
inner join GP..sop30300 as dtl on hdr.soptype = dtl.soptype and hdr.sopnumbe = dtl.sopnumbe
inner join GP..rm00101 as cust on hdr.custnmbr = cust.custnmbr
inner join GP..rm00201 as clas on cust.custclas = clas.classid
/*inner join GP..iv00101 as itm on dtl.itemnmbr = itm.itemnmbr - if you want the description from the item master instead of the invoice */
where hdr.docdate >= '4/1/9' and (hdr.soptype = '3' /* invoices */ or hdr.soptype = '4' /* returns */)

I am getting all April invoices & returns in here - you can set up parameters in SSRS as your application requires. I know in my case I have something similar but the user can also select the customer class they are looking for.

Now put a new table out on the layout tab. Drag the ItemDesc (or Itemnmbr - the description means more to some folks - you could always build a combo field), QtyToInv and xtndprce into the grid. Now insert three Groups. Invoice, Customer and Class. Drap the DocAmt to the line for Invoice, then the customer then the class (so it looks like this screen shot - ok, so not real pretty - it is an example)

http://www.imageduck.com/images/4jfwi33dct27etimnl3u.jpg

I then would hide (make like a drill down) for each Group so that you can see the class total then can drill tot he customer... and the invoice...

http://www.imageduck.com/images/y2e2rpxpa6mg8vhdta18.jpg

this get you going?

 

[swhitten]

I followed your instructions pretty much exactly. Your query yields the doc amount repeated for each line of the invoice. So if Invoice 1234 is for $938.93, and it has five line items, I see $938.93 listed five times in the query results - once on each detail line.

I added the fields, then the groups as directed. Then I inserted the Doc Amount field first on the Invoice Number group header line, then on the Customer Number group header line, and finally on the Class group header line.

Invoice 1234 shows $4,694.65 for its total. That is five times the $938.93.

So all this is doing is outright summing the doc amount as shown on each line item. This is what I'm trying to avoid.

Did you get different results when set up as described above and in your print screens???

I don't know what else to do.....