Subtotals with one-to-many link 1

[RedBean]

Hi, I have two tables linked in a one-to-many fashion. One contains the Order#, Amount, and Status and the other contains Credits among other things. It looks something like this:

Order# Invoice Amount Status Credits
1 A $10 H $1
1 A $10 H $4
1 B $25 S $15
2 A $15 H $0
2 B $100 G $22
2 B $100 G $19
...

Okay, since it is a one-to-many link, I grouped by Order# then by Status and summed up the Credits to get rid of the duplicates. Now, the problem is that I want to have 2 sums in each Order# group footer, one for the Amounts that are H status and another that is for the non H status. I searched the forum and one solution looked like what I needed, but it did not work. The suggestion was that 2 running totals should be used, one being a count that was reset each subgroup Status, and another that would be a sum running total that evaluated each time #Count = 1(and in my specific case when Status=H and when Status<>H). The problem with this is that it gives me an error saying &quot;A running total cannot refer to a print time formula.&quot; I have been trying to figure this out for a while now, does anyone have any suggestions on how to create the subtotals I need? Any help would be greatly appreciated.

 

[dgillz]

Just make one running total on amount, a sum running total,and you evaluation time in on change of group = status. Yhis way you will not get 2 $10 entries, 2 $100 entries, etc.

Let me know if you have any questions. Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[RedBean]

Well, I am looking to have a breakdown on the amount for H status items and those that are anything but H. If I understood what you suggested that would give me a total amount for all regardless of status. If I misunderstood your post, can you please rephase it in a different way? Thanks for your help.

 

[synapsevampire]

Akin to dgillz post, for:

&quot;Now, the problem is that I want to have 2 sums in each Order# group footer, one for the Amounts that are H status and another that is for the non H status. &quot;

Try:

Create 2 running totals that reset at the Order# group level.

In the Evaluate->Use a formula place something like:

{MyTable.MyStatus} = &quot;H&quot;

and in the other:

{MyTable.MyStatus} <> &quot;H&quot;

Now you have 2 totals, and you let Crystal do the work.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[RedBean]

I tried to do that, but it would sum up the duplicates that show up. I just want to sum up the amount once for those invoices that are duplicated because of the one to many relationship. Doing it the way dgillz suggested seemed to have resolved the duplicate problem. Is there a way to use a formula as well as a change on group for the evaluation?

 

[dgillz]

Red,

I am not sure what you are asking here, please elaborate. Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[Naith]

Apologies for butting in at this late stage, but it seems that where your data displays something like:

Order# Invoice Amount Status Credits
1 A $10 H $1
1 A $10 H $4

You want to cater for the second row, but forget the first?

Because it seems that something like the following should give you what you're after:

//@HSum
WhilePrintingRecords;
NumberVar HSum;
Numbervar XSum;

If {Status} = 'H'
Then If
{Order} = Previous({Order}) and
{Invoice} = Previous({Invoice}) and
{Amount} = Previous({Amount}) Then HSum
Else HSum := HSum + {Credits}
Else
If {Status} <> 'H'
Then If
{Order} = Previous({Order}) and
{Invoice} = Previous({Invoice}) and
{Amount} = Previous({Amount}) Then XSum
Else XSum := XSum + {Credits}

Then call the two variables where required. If you're calling the variables before the end of the report, remember to reset the variables to 0 where necessary.

All the best,

Naith

 

[Naith]

Sorry, if you're trying to count the last occurence of each record, then Previous in the last posts formula should read Next.

Additionally, if your duplicate rows actually have duplicated Credits - (which would make more sense) - then you should stick in a &quot;{Credits} = Next({Credits})&quot; line too.

You probably should bear in mind though, that if you're getting unwanted duplication, then your links are probably incorrect.

Naith

 

[RedBean]

Let me see if I can explain this a bit better. Right now I have a report which has a one-to-many link, so pretty much several records would have all the same information except for the Credits field. (I just realized the example in my first post was not correct, this one is more of what it looks like)

Order# Invoice Amount Status Credits
1 A $10 H $1
1 A $10 H $4
1 B $25 S $1
1 B $25 S $4
2 A $15 H $22
2 A $15 H $19
2 B $100 G $22
2 B $100 G $19
2 B $45 S $22
2 B $45 S $19

So I have the report grouped by Order# then by Status since that is the field I am really looking at. So I hide the details line and drag the field objects to the Status group footer and sum the Credits to account for the various Credit amounts. So now there it looks something like this.

Order# Invoice Amount Status Credits
1
A $10 H $5
B $25 S $5
2
A $15 H $41
B $100 G $41
B $45 S $41

Now one of the things I would like in the Order# group footer is the Amount for all the H status items in an order as well as the Amount for all the non-H status items. So for Order#2 I would have $15 for the H status and $145 for the non-H status. Should I go about another way to produce the report I want? Would using a subreport be a better alternative? Sorry for making this so difficult to understand. And thanks for any help that is offered.

 

[dgillz]

Create a formula: If {status}=&quot;H&quot; then {Amount} else 0. Create a similar formula for non H status records. You do not need tp place these formulas on your report, just create them.

Then create two running total fields on these formulas, evalaute on change of group=invoice, reset on change of group=Order. You will need to create additional running total fields to sgrand total these numbers.

Let me know if you have any questions.

Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[dgillz]

Create a formula: If {status}=&quot;H&quot; then {Amount} else 0. Create a similar formula for non H status records. You do not need tp place these formulas on your report, just create them.

Then create two running total fields on these formulas, evalaute on change of group=invoice, reset on change of group=Order. You will need to create additional running total fields to grand total these numbers.

Let me know if you have any questions.

Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[RedBean]

Thanks, dgillz you really are the greatest. That was simple enough. I could have sworn I already tried that, but I guess not. Thanks again to everyone on this board, most helpful place I have ever been to.

 

[dgillz]

RedBean,

Keep coming back, this place is great. Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com