?
Solved

Running totals and group header

Posted on 2011-03-14
7
Medium Priority
?
516 Views
Last Modified: 2012-06-27
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!
0
Comment
Question by:ROTRTechTeam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 35130017
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
 

Author Comment

by:ROTRTechTeam
ID: 35130186
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
 
LVL 77

Expert Comment

by:peter57r
ID: 35130571
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
On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

 

Author Comment

by:ROTRTechTeam
ID: 35130761
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 35131269
You are correct you can't tell the cross tab to ignore a column.

mlmcc
0
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 35138426
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
 

Author Closing Comment

by:ROTRTechTeam
ID: 35140717
I modified the approach to be like that of the single grouping reports and that solved the issue. Thanks!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question