Print Page | Close Window

Running total field contains summary field

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=9968
Printed Date: 06 May 2024 at 3:45am


Topic: Running total field contains summary field
Posted By: dv-1028
Subject: Running total field contains summary field
Date Posted: 10 May 2010 at 11:22pm
Hi,

I would like to create a running total field. (RT0)
But I can't do that because the field inside have summary field [Sum(SF1)].

For e.g. RT0 = Sum(SF1) + Sum(SF2) ....


Is there any way that I can do to get the running total RT0?

Thank you.



regards,


Replies:
Posted By: lockwelle
Date Posted: 11 May 2010 at 3:25am
I don't use running totals, DBlank is the expert there.  Quite often, CR won't allow certain function to work if they contain functions like Sum as the result is not predictable at the time the report designed...It takes a while to get, but if you can't 'look' at the data and see the value, then CR might have an issue...you have to 'read' the data to get the value, but there is still a way to get the value that you want....variables.
 
Instead of have CR do the running total, create your own. If the values are just to be seen inside the main report, global variables are fine to use in the formulas.
 
formula 1, set/reset values to 0, usually in a group header
globabl numbervar aTotal :=0
"" //hide the 0
 
formula 2, increment, since you are using SUM, I would suspect a group footer situation:
global numbervar aTotal := aTotal + SUM(SF1);
""//same reason...hide the output
 
formula 3, display, again usually in a group footer (the one that matches the reset formula...usually):
global numbervar aTotal;
 
HTH

Posted By: delstar
Date Posted: 11 May 2010 at 3:30am
Originally posted by lockwelle

Quite often, CR won't allow certain function to work if they contain functions like Sum as the result is not predictable at the time the report designed...It takes a while to get, but if you can't 'look' at the data and see the value, then CR might have an issue...you have to 'read' the data to get the value, but there is still a way to get the value that you want....variables.


I never quite understood why this would matter. You would think that there would be a 'post-processing' step somewhere in there that would handle situations like this.

Posted By: lockwelle
Date Posted: 11 May 2010 at 3:40am
the post processing is the actually reading of the data for the report...at least that is how I conceive of it.  CR makes several passes through the data, but I believe it is to reorder and do some prep, but it is all on 'existing' data as found in the raw data.  You can't group on an aggregate as it doesn't 'know' that value when it reads the data originally. The same applies to some of the other functions as well.
 
As I mentioned, I don't use running totals, as I taught myself variables in formulas, and seem to be more flexible, but require more coding.

Posted By: BrianBischof
Date Posted: 11 May 2010 at 6:02pm
Crystal Reports makes 2 passes to process a report (plus some minor tasks). Basically, on the first pass it reads the data and processes formulas. On the second pass it groups data and does the subreports. Doing a summary of a summary would require a third pass, which they decided not to implement. As Lockwelle mentioned, doing a manual running total effectively lets you create a 'third pass' b/c it happens after CR does it's passes.

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: kevlray
Date Posted: 12 May 2010 at 5:58am
I have a document that actually shows three passes (but I do not have it in electronic format).
Pre-Pass #1 - Constant formulas
Pass #1 - Database records - Recuring formulas - Record selection formulas - Sorting, groupng and totaling - Saved records
Pre-Pass #2 - Group sorting, Top N - Hiearchical grouping
Pass #2 - Group selection - Running Totals - PrintTime formulas - Subreports Charts, Maps - OLAP grids, Cross-Tabs - Page on-demand
Pass # 3 - Total page count.
 
I hope this helps.

Posted By: savethepennies
Date Posted: 10 Jun 2010 at 1:30pm
So I came upon this thread looking to solve the same problem. I have a running total field that basically just gives me a count of how many members belong to a group and wanted to sort on that. I like lockwelle's idea of using formulas instead of the running total but am having trouble getting the summing to work. Here is where I have put the formulas:

Group Header #1 - formula to reset count variable
Details - formula to increment count variable (add 1)
Group Footer #1 - formula to display count variable

The problem is that the count is always 1 so I'm guessing putting the formula in the details is not working like I expected it to. Can anyone shed some light on the formula placement or behavior in certain sections?

Posted By: lockwelle
Date Posted: 11 Jun 2010 at 3:04am
for Debugging, I would put the display formula in the details as well...it might shed some light as to what is happening in the report (being reset unexpectedly or not incrementing as you suppose.
 
it's silly, but you do have something like x := x+ 1 and not x = x + 1...I've made that mistake before...
 
HTH

Posted By: savethepennies
Date Posted: 11 Jun 2010 at 7:32am
Good suggestions. I was able to get the counting to work by adding WhileReadingRecords to the formulas to count and display but as soon as I added it to the reset formula the count got reset for every detail line. So I changed to WhilePrintingRecords which works perfectly. The problem then is that the formula fields are no longer available for sorting (probably because the evaluation is taking place too late) and that was the whole purpose. The good news is that I'm getting closer though.


Print Page | Close Window