Group Summary within a Higher Group 1

[lkerr]

Working in CR9 ...

I have a report with three groups:
Group 1: Date (by Year)
Group 2: Job Type
Group 3: Job

What I want to do is put a summary of the totals for the year, listed by job type, in the GF1. So the footer would look like this:

GF3: suppressed
GF2: suppressed
GF1: Job Type #1 $54.45 $65.45 $11.00
Job Type #2 $20.00 $30.00 $10.00
Job Type #3 $45.49 $50.00 $4.51

I've tried a sub-report, but I can't get it to work properly (I'll end up with 3 groupings of Job Types in one footer, where there should be one grouping in three footers). There is no date field available for a subreport link.

I know this should be easy, but I'm missing something obvious! Help, please? TIA!

 

[lbass]

What are the three columns per job type in GF1? Are you trying to show the summaries for all three years in the group footer for one year? Why? YOu could insert a crosstab in the report footer that uses year as the column, job type as the row, and then some summary (you didn't identify what it was).

-LB

 

[lkerr]

I don't think I expressed myself clearly. Sorry.

What I want is to display a summary of all job types for each year .. so the above columns (which are net, gross, and profit) would display a sum.

So the whole report would look like this:

GH1: 2007 ({JobDate} - sorted and grouped by year)
GH2: POP ({JobType} - sorted and grouped by type)
GH3: JOb #234583 ({Job Number} - sorted and grouped by number), sum({net}), sum({gross}), sum({profit})
Detail (suppressed): {Net}, {Gross}, {Profit}
GH3 (suppressed): blank
GH2 (suppressed): blank
GH1: POP (Jobtype 1), net, gross, profit
ROP (Jobtype 2), net, gross, profit
Mail (Jobtype 3), net, gross, profit

Hopefully this makes it a little clearer ... ??

 

[lbass]

Insert a crosstab in the GF1 that uses jobtype as the row, and then add the three summaries that you mentioned as summary fields. Go to the customize style tab->summary fields display->horizontal->show labels.

-LB

 

[lkerr]

Thanks ... I knew it was something simple that I was missing.

 

[lkerr]

Gah ... one more question. I need to sum up a percentage ... how do I get that to show up accurately?

 

[lbass]

In the crosstab? How is the percentage calculated? Please be specific and show a sample of what you are looking for.

-LB

 

[lkerr]

Soryy, yep, in the crosstab. The user who has requested the report also wants to see the profit shown as a percentage - so in the regular report, it would be this formula:

((gross - net) / gross) * 100

But I don't have a clue how to represent the

((sumgross - sumnet) / sumgross) * 100

in the crosstab. Hope this makes sense. Thanks!

 

[lbass]

Isn't your profit field = gross - net? If so, then select the profit summary in the crosstab (in preview mode)->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
currencyvar profit := currentfieldvalue;
false

Then select the gross summary->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
currencyvar gross := currentfieldvalue;
false

In the main report, create a formula to act as a holder for the percentage:

whilereadingrecords;
0

Insert this formula as your fourth summary field in the crosstab->format field->common tab->DISPLAY STRING->x+2 and enter:

whileprintingrecords;
currencyvar profit;
currencyvar gross;
totext(profit%gross,2)+"%"//2 for two decimals

If your fields are numbers, not currency, then change the currencyvar to numbervar in all the above formulas.

-LB

 

[lkerr]

Well, you've done it again! Thanks so much for your help! I can only stand in awe of your knowledge (and ability/willingness to use it to help others.)