Solved

Running totals and group header

Posted on 2011-03-14
7
515 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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 500 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying 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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

688 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