Group Subtotals

[Former Member]

Crystal 2008

I'm trying to display hours for each workorder with subtotals then grandtotals. I currently have the workorders (wo) setup as a group and have tried both a running total and a summary to get my subtotals/grandtotals; however, I'm running into a few issues:

1) Where the wo have no hours, 0 does not display

2) The 2nd wo listed displays alll the hours from the 1st wo along with that wo's hours

3) The subtotals are adding to the previous wo subtotals even when I use a running total and request that it reset at each group

Any advice would be greatly appreciate.

[Former Member]

Try to insert a summary field in the group footer and see whether you are able to see the sub totals

Regards,

Raghavendra

[Former Member]

I've done that; however, like I said, it adds to ... is cumulative and I need it to summarize for each group individually. Also when a wo doesn't have any hours attached to it, it displays the previous wo hours instead of 0.

[Former Member]

Anyone?

[Former Member]

Try this

sum(,)

place this in group footer. It should give you the sub totals without cumulative.

Regards,

Raghavendra

[Former Member]

Thanks so much for your reply. This is the exact formula that I entered in the Group Footer:

sum({FMS_LABOR_DETAILS.WorkedHrs},{FMS_WORKORDER_MASTER.WoCode})

However, it's still showing a cumulative summary. The only difference it made was pushed everything down a line.

I noticed in another post that there was a formula to "reset" to zero that goes in the Group Header; however, I couldn't get that to work either.

Is there a way to add the displayed values on a report?

Anyone have any other suggestions?

[Former Member]

I should clarify that what I'm trying to create subtotals and grand total for is a summary of total worked hours for a given workorder placed in the Group Header (Grouped by Workorder).

[Former Member]

Any ideas?

[Former Member]

Krista,

You have to use running totals if you are trying to summarize a summarized field.

I think the built-in Crystal running total features can help you achieve this.

Make sure you reset after each group you are trying to summarize to.

I hope this helps,

Regards,

Zack H.

[Former Member]

Thanks for the reply; however, unless I'm missing something, Running Totals doesn't allow me to run totals on a summary. Here's what I'm trying to accomplish:

-I have a rpt with the search criteria for a specific unit

-Then grouped by all the workorders (wo) for that unit with each wo listed as a separate line item

-Then I need the total of all the hours worked for a given wo listed in the wo row/line item

The only way I've been able to accomplish this thus far is to list all the fileld data in the Group Header section of the report with the worked hours set up as a summary of the worked hours. If I place this data in the details field, it throws everything else off because it lists an additional line item for each labor record in the db.

Please let me know if there's another way to look at this.

Thanks!

[Former Member]

OK ... I've got it to work placing all the fields in the GF1 section alongside the running total; however, I can't seem to correctly figure out the reset formula to get it to recalculate for each group. I currently have the following in the GH1:

whileprintingrecords;

numbervar i:=0;

Thanks in advance for further assistance.

[Former Member]

That should do it if your variable i is the variable that you defined in GF1 that calculates your running total.

Regards,

Zack H.

[Former Member]

Please forgive my ignorance, but I'm doing something wrong then, as the exact formula that I have is:

whileprintingrecords;

numbervar WkdHrs:=0;

WkdHrs is the name of the running total calc that's in the GF1. Still not getting the desired results. What am I doing wrong?

Thanks!

[Former Member]

Firstly, I rarely need running totals unless I need running (cumulative) totals. They are messy to set up and edit and you can't copy them. BUT, you have an option on when to clear a running total (eg on change of a group).

Instead, I use simple Summaries almost exclusively when I can, often based on a formula.

For example, imagine you wanted to sum all the chargeable and non-chargeable hours for staff. I'd make 2 formulas, Chg and NonChg, which return only the wanted hours (0 otherwise). Each Detail item is a transaction and you have groups Staff and Division.

Make a Summary (sum) on Chg in the Staff footer and set up its format. That gives me the chargeable total for the staff member.

Then copy the summary to the non-chargeable column, edit it and base it on the formula NonChg. That fixes non-chargeables for the staff member (note that the format is copied from the Chg summary, which saves time).

Then copy both of these to the Division footer. They automatically get grouped by Division, so I now have the sums for the Division.

Andrew

[Former Member]

I understand this concept, but doesn't resolve my issue. All my data is already in the GF1 footer with the Worked Hours (WH) as a summary for each Work Order (WO) line item. My issue is that the summary of the WH is running a cumulative total for each WO line item and I need it to list the total of all the WH for each WO separately and not cumulatively.

Any other ideas.

Thanks!

[Former Member]

Sorry, I was just trying to point out that there's an easier way to do it, where you don't have to worry about cumulative totals.

I also said at the top of my post that you can determine when a running total is cleared, so you don't get cumulative totals. In your case this would be on a change in your Group 1, presumably.

Am I missing something here?

Andrew

[Former Member]

One other thing.

If a summary is null (ie there are no entries which are relevant) then the summary won't display - you mentioned this earlier.

If you really want it to display 0.00 or whatever, you need a formula which looks something like this.

if isnull(sum(WH, WO)) then 0 else sum(WH, WO)

You use this formula in your footer instead of the actual Summary or Running Summary.

Andrew