[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Running totals and group header

Posted on 2011-03-14
7
Medium Priority
?
518 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
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

 

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

650 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