Solved

Running totals and group header

Posted on 2011-03-14
7
506 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
  • 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 100

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now