running totals

[swhitten]

Here's what I'm trying to do:

Two Tables:
Sales Order Header - with basic top-level info re. the sales order - customer, date, sales order number, and total freight for the order.

Sales Order Line - with the individual line by line info regarding what was sold, including whether or not this line item is taxable. Note: freight is not recorded at the line level, only at the header level.

The header and line tables are linked via a unique sales order number.

I want a total that sums the line items sold plus the freight for the total order.

My issue is this. The line items are grouped first by taxable / not taxable then by sales order number. So one sales order may appear in multiple groups on the report.

If I do a running total that totals freight on each change of sales order number, freight will be added in more than once for the sales order that is split among groups.

What can I do to get freight included only once?

Thanks in advance for your help.

Sherry Whitten
swhitten@idrivellc.com

 

[dgillz]

Create a running total field as follows: Insert, field object, running total field, the field to perform a running total on would be your freight field. Set the evaluation to once per group=Order number, and reset the field on change of group=order number.

Then sum your line items sales amount by sales order, which I assume you know how to do.

Finally, just add the 2 objects together. The summary field will have a sigma at the far left, and the running total field will have a # sign.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[swhitten]

I tried this, but the problem is that the order number group is under the taxable/nontaxable group. So a sales order that has both taxable and non-taxable line items will appear in the order number group once under the taxable section and once under the non-taxable section. Does that make sense? I don't know of a way around this. If I'm resetting on change of group order number, it's going to pull in the freight once from the taxable group and once from the non-taxable group.

what am I missing?

 

[lbass]

Do you have the option of redesigning the report? It would make more sense to group on sales order as Group 1 and use taxable/nontaxable as Group 2. Go to report->change group expert and use the direction keys to make sales order the higher order group. Then you could easily calculate the tax to be added to each order and freight would be represented only once.

You could always do a report level running total on taxable sales. Just select {table.salesamt}, sum, evaluate based on a formula:

{table.taxable} = "Y" //or however you define taxable items

Reset never.

Otherwise, if you must keep the report design, you could create a formula for each {table.salesorderID} like:

if {table.salesorderID} = 123 then {table.freight}

And then insert a maximum on this formula at the grand total level.

-LB

 

[dgillz]

Why even have a group for taxable and non taxable?

You can easily create a formula like the following:

If {TaxableFlag}=&quot;Y&quot; then << insert tax amount or tax calc here>> else 0

Then just sum this formula by order as well.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[swhitten]

Thank you all for your feedback / input. Re-designing the report is not an option. The user wants to see his sales grouped by taxable and non-taxable. The issue is that one sales order may have both taxable lines and non-taxable lines, which means that particular sales order will be in both sections of the report. The accounting software he is using is calculating the tax amount, so all I have to do is sum it by group (only in the taxable group, obviously). But splitting out the freight, which is only assigned at the header level, not at the detail level, is still my issue.

 

[lbass]

It seems to me that in order to total an individual order across two groups you will need to do running totals or formulas which are specific to each order ID, which is really unwieldy, and these would be displayed in the report footer.

Whichever route you take, you could use an average on freight to get just the one value, since the result would always equal the field value you're looking for.

An easier way to get the results in the report footer might be to use a crosstab, with taxable/nontaxable as a column, order ID as a row, and sales, freight, etc., as summary fields.

Although not a direct solution, maybe these ideas will help.

-LB