Technical Questions: Running Total in report header?

Print Page | Close Window

Running Total in report header?

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=14432
Printed Date: 07 May 2024 at 12:49pm

Topic: Running Total in report header?

Posted By: JFinzel
Subject: Running Total in report header?
Date Posted: 21 Sep 2011 at 4:53am

I searched around and found this as the most related, but it was too different from my case (as far as I could see) to make it work:
http://www.crystalreportsbook.com/Forum/forum_posts.asp?TID=13090&KW=running+total+header

I have several running total fields. They are working fine, and I am aware that they work based on counting records and then being displayed at the bottom of the report.

Is there a way to take them and display them at the top? Right now I have six running totals, and two of each are added together, to leave three formula fields:
(Total 1) = {RTotal0}+{RTotal1}
(Total 2) = {RTotal2}+{RTotal3}
(Total 3) = {RTotal4}+{RTotal5}

Any help is much appreciated.

Replies:

Posted By: DBlank
Date Posted: 21 Sep 2011 at 4:58am

RT totals can not be displayed prior to all data that is included the total being printed/read so they do not work in headers (they happen in a later data pass).

There are possible workarounds.

1. Use formula(s) and summary functions instead.

2. use sub report(s).

3 use crosstab(s)

Posted By: JFinzel
Date Posted: 21 Sep 2011 at 6:44am

If I need a running total, how do I summarize a formula? Looking at the past topic, I tried to create a summary but I could only summarize fields, not any of my formulas.

Posted By: DBlank
Date Posted: 21 Sep 2011 at 7:27am

you can summarize certain formulas.

In my very limited understading is has to do with data passes and which pass an item can be (or is) created on. In a very non-technical way I think in general if the formula is created in the "while reading records pass" it can be summarized (using the insert summary function).

If teh formula is created during the 'while printing records pass' it cannot be summarized (using the insert summary function).

Examples:

if gender='Male' then 1 else 0 is created in an early enough to SUM the result (and place in a header).

change this formula doing the same calcualtion to:

whileprintingrecords;
if gender='Male' then 1 else 0 and it can no longer be summarized.

certain formula functions inherently require they are created in later passes, like using previous() or next() so they can never be summarized (using insert summary) and therefore the results cannot be used in headers.

Does this help?

NOTE: there are more than 2 passes and more things happen in each pass, this is just to try and help understand the basic idea.

Posted By: JFinzel
Date Posted: 21 Sep 2011 at 7:38am

I think I generally get what you are saying. One of my running totals does use the previous or next function, so I probably can't do it. I will mess around with it though and see if I can get the numbers I want with summary instead. Thank you for the help again.

Posted By: DBlank
Date Posted: 21 Sep 2011 at 7:47am

if you post sample data and what you want to show in the header maybe someone can give you different ideas/ways to do it.

Posted By: JFinzel
Date Posted: 21 Sep 2011 at 10:01am

My data is based on (Host) and (Guest). It is displayed like so:

Group: (Host.Name) (Registration.Status)
       Details: (Guest.Name) (Registration.Status)

I am counting up "Registration.Status" for both Hosts and Guests, which have three options: "Not Registered", "Registered", "Do Not Register".

This is how I have found I have to count:

Group: (Host.Name) (Registration.Status)
       Details: (Guest.Name) (Registration.Status)
                    (Host.Name)   (Registration.Status) - (BOTH HIDDEN, HERE FOR COUNT)

Report Footer: Rtotal0: Count
                                      Evaluate: Guest.Registrat.Status = "Registered"
                                      Reset: Never
                        Rtotal1: Count
                                      Evaluate: Next ({HostID})<>{HostID}
                                      and Host.Registration.Status = "Registered"
                                      Reset: Never
                        Formula: {@Rtotal0}+{@Rtotal1}

      Displayed: "Total Registered: {Formula}"

I hide the running totals in the footer and just display total registered. I do this for each of the three "Registration" options.

As you can see, I put the group name in the details so that I can running total it. Not sure if that is problematic but it works.

Thanks for any help.

Posted By: lockwelle
Date Posted: 22 Sep 2011 at 3:42am

since you can't summarize directly, you can create a variable to hold the amounts so that they can be reported on later on...though you are already displaying this in the report footer (or at least that is what I would supposed from the Reset:Never.

DBlank knows RTs much better than me.

Posted By: DBlank
Date Posted: 22 Sep 2011 at 3:48am

I am not seeing any immediate 'simple' way to get the data into a group header.

so you are trying to get a total amopunt of people registered but you have hosts in one table and there related guests in another table. A host may have registrered guests but the host is not registered or vice versa, correct?

Posted By: JFinzel
Date Posted: 22 Sep 2011 at 7:36am

Originally posted by DBlank

I am not seeing any immediate 'simple' way to get the data into a group header.

Actually both the guests and hosts have to register. Here is what the data looks like that I am doing an RT on (showing all data, including what is hidden):

John Doe (Host) Registered
(Detail A): James Smith    Registered
(Detail B): John Doe Registered
                    Tim Johns   Not Registered
                    John Doe Registered
                    Janice Thomas    Do Not Register
                    John Doe Registered
                    Philip Smith Registered
                    John Doe Registered

As you can see, John Doe (the host) repeats, and this is the one I do a (Next) formula to get the running total on. Again, not sure if there is a better way to get that count, but it works.

And for (Detail A), I simply count the ones that are marked "Registered".

I add both RTs together to get total "Registered". Make sense?

Is there not a way to make a summary of field (Host.Registration) to include only "Registered"? Likewise for (Guest.Registration)? This would solve my problem.

Posted By: DBlank
Date Posted: 22 Sep 2011 at 7:53am

Does your raw row level data (after joining your tables and no formatted or grouped) looks like this?

Host Host status Guest Guest status
John Doe registered Tim Registered

John Doe registered Bill Registered

John Doe registered Jane Unregistered

John Doe registered Frank Unregistered

Mark Doe unregistered Mary Registered

Mark Doe unregistered Jill Registered

Mark Doe unregistered Andy Registered

Posted By: JFinzel
Date Posted: 28 Sep 2011 at 6:00am

Precisely.

Posted By: DBlank
Date Posted: 28 Sep 2011 at 6:14am

I think I understand.

As an aside, IMO, it is easier to look at raw row level data after tables joins and select expert is applied to see what your overall data looks like and then make designa nd calulation decisions based on that, hence my question as to what the 'raw' data is like. Report formatting tends to confuse or hide issues that the raw data exposes.

1.what do you link Host table to Guest table on? the Host ID?

2. Do you have rights to make a view, stored proc or a crystal command?

Posted By: JFinzel
Date Posted: 28 Sep 2011 at 7:08am

Thank you much for the help and advice. I will keep that in mind.

(1) There are two tables:
Prt (Host)
PrtIDKey Field 1 Field 2 etc...   PrtGst_LINK
1                                                1
2                                                2

PrtGst (Guests)
PrtGskIDKey Field 1   Field 2 etc....   PrtGst_LINK
1                                                        1
2                                                        1

The tables are linked by "PrtGst_LINK", which is simply an ID number, 1 for each host.

(2) I have full access over the database and reports, however I do not believe I have ever done those things.

Posted By: DBlank
Date Posted: 28 Sep 2011 at 7:17am

The only way I see doing this without a command, view or stored proc (or something similar if you are not using a SQL db) is using sub reports which I hate to do.

In a view, stored proc, command i would union HOST to GUEST also creating another field inserting NULL if the status='unregistered' and a PK or a 1 if the status='registered'

then join this unioned result back to HOSTS table.

this way you end up with

John Doe registered Tim Registered 1

John Doe registered Bill Registered 1

John Doe registered Jane Unregistered NULL

John Doe registered Frank Unregistered NULL

John Doe registered John Doe Registered 1

Mark Doe unregistered Mary Registered 1

Mark Doe unregistered Jill Registered 1

Mark Doe unregistered Andy Registered 1

Mark Doe unregistered Mark Doe Unregistered NULL

Posted By: JFinzel
Date Posted: 28 Sep 2011 at 7:28am

I haven't done a view, stored proc command, but perhaps I will look into seeing how this might work. I have a really busy week so can't play around with it now, but thank you much for the help!

Print Page | Close Window