| Author |
Message |
lmfl123
Newbie
Joined: 13 Dec 2012
Online Status: Offline
Posts: 8
|
  
Topic: Running Total Error
Posted: 23 Jan 2013 at 7:22am |
|
I am having an issue with a running total that I have in a report. The report shows total period sales for a number of different products.
Item Sales
10000 150.00
10010 206.00
10020 37.00
The report is set up with the data displaying in the group footer (with Item set-up as the group) and Sales set-up as a running total that Evaluates on a formula and resets on change of group. We need to evaluate on formula because the transaction table with the sales data agglomerates all sales, purchases, and quotes and uses a number to identify each different transaction type (Sale =7, Purchase=6, Quote=13). So in this case the formula is {TransID}=7
I can cross check this data against a report from my warehouse system that is known good. I have 17 records out of 435 that are twice as large as they should be (exactly). 381 records match the warehouse system value and a handful are off by smaller margins that are explainable due to other factors and would be acceptable.
Any thoughts on what might cause this type of problem?
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 23 Jan 2013 at 7:27am |
Do you have any joins that would be causing duplicate rows for some records?
|
| IP Logged |
|
lmfl123
Newbie
Joined: 13 Dec 2012
Online Status: Offline
Posts: 8
|
Posted: 23 Jan 2013 at 7:36am |
|
I don't think. My Item table links to my Transaction table by an Item ID. Each Item ID appears only one time in the Item table. I checked to make sure that the link goes from the Item table to the Transaction table and set for an Inner Left Join.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 23 Jan 2013 at 7:47am |
a n easy way to trouble shoot a running total is to unhide all the detail section, place the RT on the edge of the report on the Detail section and then just walk through each row and watch for the anomolous amount at a specific row. From there analyze the dat in that row to see what you missing. In general, the RT is working "correctly", it just is not working the way we wanted it to. BY fuinding where it is not working the weay yuou want it to you can see how you can alter it to fix that scenario.
What do you find by way of this process?
|
| IP Logged |
|
lmfl123
Newbie
Joined: 13 Dec 2012
Online Status: Offline
Posts: 8
|
Posted: 23 Jan 2013 at 9:02am |
|
It looks like it is just counting each record twice. I recreated a simpler version of the report with just these 2 tables and running summaries for sales and purchases for each item. The running totals are okay until I add a third table. This table (ItemAvailable) provides actual in stock quantities for each item. ItemAvailable also uses the ItemID as the tag. As soon as I add this table the value for the problem records changes. I tried linking to the main Item table via Left Outer Join, but no luck. Also tried linking to the Transaction table via Left Outer Join, but still no luck. Any thoughts?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 23 Jan 2013 at 9:05am |
the only reason to count twice is because there are two rows. Hiding (suppressing rows) do not exclude them. Doing outer joins can make sure you do not drop records but does not mean you only get one record.
make sure you do not have any suppression (including conditional suppressions) going on and that you are not looking at a group footer.
Edited by DBlank - 23 Jan 2013 at 9:34am
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 23 Jan 2013 at 9:36am |
|
Once you verify that the cuplprit here is duplicate rows, you can either alter your data set to exclude them or add another condition to your evaluation formula to exlcude the duplicate rows using a previous() function on a primary key field inside the formula)
Edited by DBlank - 23 Jan 2013 at 9:37am
|
| IP Logged |
|
lmfl123
Newbie
Joined: 13 Dec 2012
Online Status: Offline
Posts: 8
|
Posted: 23 Jan 2013 at 9:57am |
|
It looks like these are in my ItemAvailable table multiple times (each a record for a different warehouse location). I should be able to eliminate some of this problem by deleting locations that are no longer in use. Is there a way to get around this on the CR side for future reference?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 23 Jan 2013 at 10:13am |
I prefer to try and get the source to avoid the duplication by using stored procedures or views but you can use sometimes use other tricks.
in your evaluate formula you can add another condition like
{TransID}=7 and previous(sales_id)<>salesid
note: this is using salesid as the primary key field from your main table that would easily identify that a duplicate row uses the same ID #. Edited by DBlank - 23 Jan 2013 at 10:14am
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 23 Jan 2013 at 10:15am |
|
also make sure your evauate formual i set to 'use default values for nulls'
|
| IP Logged |
|
|
|
