Running totals and group header

I have a report that has both major and minor program categories. The client wants to see 13 months worth of data in columns, but only 12 months (months 2 through 13) get totalled. I have created a manual cross-tab to handle this issue and in other versions of the report (which don't have multiple groupings) I used running totals in the group footer and just suppressed the header. In this version, of course, what happens is this:

Major Program #1
  Minor Program #1
Major Program #1
  Minor Program #2

It doesn't actually display properly. I  know if I could use the group header it would work fine. The question becomes, how can I calculate the last 12 months totals (count and unique count both) with a formula that changes when the group changes so that I can then display it in the group header?

Thanks!
ROTRTechTeamAsked:
Who is Participating?
 
peter57rConnect With a Mentor Commented:
I don't believe you can achieve this putting the totals in the group header (unless you use a subreport to get each total which would be an extremely heavy handed way of doing it.).  You would have to use footers.

The way I read your original post, you say that you have been producing  reports with only one grouping level OK.
You must have been creating a lot of formula fields to get data into the correct columns and then, I assume, using several running totals field to add up these formula fields.

You cannot create a total of running total fields, but there is nothing to stop you creating a separate running total field which only adds the last 12 periods and ignores the 13th period , assuming you can define a rule for what 'the last 12 periods' means.  

That would mean that you can use the same technique for your multi-level-group reports, although there is a danger with multi-levels if the inner group value could be the last value in one major group and the first value in the next major group- the running total 'reset when'  would need to be defined using a formula to test both the inner group value and the major group values.
0
 
peter57rCommented:
Without knowing exactly what ypou have done before it is difficult to say much.

The normal structure for creating your own report totals is..

Report Header:
Formula field(s) to set up variables and initialise them to 0.

Detail section:
Formula field(s) to add to variables

Report Footer:
Formula field(s) to display results

If this is what you have done then to display totals at group level instead of grand totals, all that is required is to move the first and last formulas to group header and footer respectively.
 
0
 
ROTRTechTeamAuthor Commented:
peter57r, thanks for the response. I've never actually had to do what you've described, though I've read about it. My question would then be, if I use the approach you describe and have a display forumula in the report footer, how to I get that to display where I want in the group header?
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
peter57rCommented:
Re-reading the Q i don't think I am clear on what result you are expecting.  

I assume that your previous 'manual crosstabs' have just been totals in the group footer  section, with all other sections suppressed.
0
 
ROTRTechTeamAuthor Commented:
Well, as stated, we have two groupings -- Major Program and Minor program. We have a third grouping called Site. The expected results would be as in the attached Word document. There is one row per major program with how many people were served by that program in each month. To the right is a count of just month 2 through month 13 and a unique count of month 2 through month 13. Under each major program line is rows of minor program lines and the same kinds of totals.  See the example. I want to be able to display the count and unique count for just the months 2 through 13.

Is there a way to do that in cross-tab report itself? I don't think you can control what gets summarized in that way.

Thanks!

Example.doc
0
 
mlmccCommented:
You are correct you can't tell the cross tab to ignore a column.

mlmcc
0
 
ROTRTechTeamAuthor Commented:
I modified the approach to be like that of the single grouping reports and that solved the issue. Thanks!
0
All Courses

From novice to tech pro — start learning today.