By using this website, you agree to our Terms of Use (click here)
Looking to add additional levels of summary to the sales order summary report. I was able to edit the current grouping to summarize by order type and it shows that correctly, but I have not been able to add an additional group at the end of the report that shows the summary total of the whole report i.e. summary of all order type summaries. Any thoughts on what I am missing here?
To summarize a group, you generally go "up" a group and then take a sum of the fields in the previous group you needed. As an example:
Header Group2:
Some kind of header info, such as titles for your data
Header Group 1:
Subheaders if you need them
groupDetails:
Your Data Goes Here
Footer Group 1:
Here I take a sum for items in "groupDetails"
Footer Group2:
Here I take a sum of the items from Group 1's footers.
Attached is a VERY dense (and semi-ugly) .rpx file I made showing the total for a given container being shipped (total of data found in groupDetails), totals for all containers on a date (total of data found in the group "container"), then totals for all containers for each specific agent we have (total of data found in the group "Dates").
It's like playing with Russian Stacking Dolls. If you're open to posting your .rpx file publicly I can see if anything is amiss with your report. I have a feeling you just need to sort out some groups though and slap in some sums in your footers.
The issue I am running into is that this report is not using the detail section see attached example.
I think you're really close! If you copy your lines from your SubTotals and place them down into the next footer, they should give you a grand total of the entire document. Of course you need to re-work your first cell that includes your currency ID and probably just call it "grand total" or something. I can't test this very well on my side as all my sales orders are in USD, so my totals look the same on both lines.
Hope this fixes your issue!
That's the first thing I tried and it just doubles up the summary by order type as that footer is part of group 1 so it is using group 1 settings for grouping. Basically I am trying to get both the summary by order type and a summary by report total. See the attached example of how it doubles up the order type summary.
Sales-Order-Summary-2018-01-16.pdf
Hmm, I see you don't have a report footer. Maybe try adding that? Right click the report, then select "report footer". Add your totals to that footer. I'm out for the day, but I'll be back in tomorrow and will try and work to get this resolved with you. Hopefully Tim drops in with a quick response.
That Worked!!!!!!! I knew it was going to be something simple, I didn't even think about the footer as it is usually always there.
Just noticed that the report total is to high, it seems as the sum in the footer is summing the orders and the summary section by order type together. Any thoughts on how to get a summary with out it doubling up?
When I have this error, it's usually an issue with my table joins, but yours appear to be clean at a glance.
You have two footers for group 1, I would delete one of those, not sure if it creates duplicity or not, but I don't think you're using the second one.
Further, have you tried filtering out one type? I would attempt to filter out a set if possible. I've made some really ugly iif statements to do this before, but they can save you from making duplicity errors. You just need to find some unique key to filter on.
Lastly, If you are 100% certain it's a double and will only EVER BE a double, you can hard code a division into your totals (total/2). I avoid that last method at all costs, but sometimes I need to get a report on someone's desk and don't have the time to find my issue right away. Hopefully the issue is just the second footer. I've never created a duplicate footer for a group in my reports, so I'm not certain of the behavior we can expect.
That didn't help, I also thought about the division but if they add more order types it would require changing the report every time, I am noticing that the formula is pulling from the soline table and I changed one of the fields to the soorder table =sum([SOOrder.CuryOrderTotal]) and that's when the report total section started to multiply. I changed it back to the below formula and it didn't double???
=sum([SOLine.CuryLineAmt]*-[SOLine.InvtMult])
the problem is that the first formula that doesn't double gives me a order total does not include the premium freight so I added this formula but still need to make sure it is not doubling the numbers.
=sum([SOLine.CuryLineAmt]*-[SOLine.InvtMult]+[SOOrder.CuryPremiumFreightAmt])
I wish I could provide more help, but since all my currencies are in USD (we don't even have the currency module running), I just get a single total that matches my sub-total, so trouble-shooting is difficult. I'm amazed Tim hasn't popped in yet, he's likely got the module up and running and could run your rpx against his system.
Since your were getting a double when you don't multiply, my guess is that your duplicity is being removed when -[SOLine.InvtMult]= 0 (or null). If you alternate duplicate lines and one has the "real" value for -[SOLine.InvtMult] and the other is pulling null or assigning 0, it'd drop that line from your summations since anything * 0=0. This makes me want to think your joins might be causing an issue again if this is indeed the situation.
This is a stock report that is most likely the reason for the currency but this client is not using multi currency either so I could pull that out but I believe it is on other areas of the report, also this is a R2 version.
My company is still being gated by a 3rd party from entering R2. So I know very little about any changes on that system.
I removed all the relations to the soline as this report allows to run by inventory ID which they do not need and now it works, so it had something to do with the inventory lines that was causing the doubling. A detail I failed to mention is this client does not use stock items only non stock.
I'm glad you found your issue! I find I often have to start removing segments of reports as well when tracking down some of those duplicity errors. If you ever need a quick sanity check on what line items are being passed through, toss your tables and groups into a GI and you can easily see your output lines. Also, there's a button when you reports that will show you the groupings. It too can be handy when troubleshooting. It's located just to the left of your page scrolling buttons.
Online Members
