Simple Summary

[ugotaccpaced]

How do you summarize what you see on the screen?

Two tables: Order & OrderLineItem

My report lists each Order individually and the Order total (broken out by Brand). However when I go to grab the summary of what I see on the screen the Summary is off. It is summing the total Order amount for each OrderLineItem.

Ie. If I had only 1 Order in my system totaling $2000, which was made up up 4 line items of $500 each, my Summary field would total $8000 not the $2000 as shown on the screen.

Report design view attached

 

[lbass]

What fields are you grouping on for groups #1 and #2? Please show how the data displays (preview mode) if you place the fields in the detail section.

-LB

 

[ugotaccpaced]

If I place the Order fields in the details section it breaks it out by each line item. So from my previous example it would show my only order 4 times with a total of $2000 for each line.

I have tried summarazing from the line item level but those numbers are off as well (though closer to the correct value).

 

[lbass]

You just need to place the total field in the group footer, since the total has already been calculated. If you need a total across orders, insert a running total that does a sum of the total field, evaluate on change of order, reset never. Place the result in the report footer.

-LB

 

[ugotaccpaced]

Thanks for the help but it was a PBKAC. I restarted the report with the OrderLineItems as the Parent TO and that pretty much solved my problem.

 

[ugotaccpaced]

Nevermind I think it's time to just give up on the report. With the amount of time spent trying to get it to work I could have looked the records up individually and totaled them by hand for the next 3 years and still have spent less time.

My best guess is the problem stems mostly from our Order System (Sage Accpac). It has optional fields you can add to a database but stores the multiple fields in a file similar to a flat file. This makes reporting very, very cumbersome.

 

[lbass]

I thought you had it working properly. If not, why not show some sample data at the detail level? Did you try my earlier suggestion?

-LB

 

[ugotaccpaced]

I realized grabbing the Order Total will not work. While most of our orders are confined to one brand only we do have some that may contain more than one brand. Therefore I needed to go back and base the report off of OrderDetails rather than Order.

I went through the trouble to make a sample of the 4 tables required for the report and saved to DBF format. However, when I try to make the connection in Crystal Reports only 2 of the 4 tables listed the Field Names when I went to start creating the links.

Is there an easy way to share sample databases on this board? I have zipped the four DBF files and attached to this post but I'm not sure anyone can open them from Crystal Reports.

 

[lbass]

I can't open these. You could just create some mock data that illustrates the issue, showing multiple rows of details and the summary you are trying to achieve. I can't really tell what the issue if from what you've said so far. If you are no longer using the total field, you can right click on a field and insert a summary at the group and/or report level, or if you have repeat data, you can insert a running total that summarizes on change of some field or group in order to exclude duplicate values.

-LB

 

[ugotaccpaced]

1 more zip file attached containing 3 images:

1 - Tables.jpg. Shows how I have the tables in the report setup

2 - Report.jpg. Shows the report as I would like it to be seen/grouped.

3 - ICITEMO Table.jpg. Shows the funky setup of the Optional Fields table.

 

[lbass]

But this doesn't tell me what the underlying (detail level) data looks like. What results do you get when you insert a sum on the detail level amount? Is it inflated? If so, sort the data on line item ID, and then insert a running total that sums the amount and evaluates on change of item ID, and resets on change of group (Order No). Insert a second running total that resets on change of brand. Place the running totals in group footer sections and then suppress the details.

-LB

 

[ugotaccpaced]

It's working now. I started from scratch late Friday and the totals are now adding up correctly. I don't have an explanation (besides user error) for why they were not adding up right before but I am just relieved that they are now correct.