Crystal Reports Running total field contains summary field

Report Design
Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design

Topic: Running total field contains summary field

Author	Message
dv-1028 Newbie Joined: 12 Apr 2010 Location: Malaysia Online Status: Offline Posts: 4	Topic: Running total field contains summary field 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,
	IP Logged

lockwelle Moderator Joined: 21 Dec 2007 Online Status: Offline Posts: 4372	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
	IP Logged

delstar Newbie Joined: 11 Jun 2009 Online Status: Offline Posts: 26	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.
	IP Logged

lockwelle Moderator Joined: 21 Dec 2007 Online Status: Offline Posts: 4372	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.
	IP Logged

BrianBischof Admin Group Joined: 09 Nov 2006 Online Status: Offline Posts: 2458	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>
	IP Logged

kevlray Admin Group Joined: 29 Oct 2009 Online Status: Offline Posts: 1587	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.
	IP Logged

savethepennies Newbie Joined: 14 Oct 2009 Online Status: Offline Posts: 14	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?
	IP Logged

lockwelle Moderator Joined: 21 Dec 2007 Online Status: Offline Posts: 4372	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
	IP Logged

savethepennies Newbie Joined: 14 Oct 2009 Online Status: Offline Posts: 14	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.
	IP Logged

Post Reply

Post New Topic

Printable version

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

This page was generated in 0.031 seconds.