Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Cumulative sum not working in charts Post Reply Post New Topic
Page  of 2 Next >>
Author Message
moontide
Groupie
Groupie
Avatar

Joined: 23 Feb 2011
Online Status: Offline
Posts: 78
Quote moontide Replybullet Topic: Cumulative sum not working in charts
    Posted: 18 May 2011 at 3:51am
I have this report that has a chart over a period of months (x-axis). The Y-axis needs a cumulative sum of a buidling structure. I cannot add a running total, since its a string field, it can only do a count, but I need a cumulative sum like this.
 
Month    Structure #        Cumulative Structure #
 
Jan           3                          3
Feb           6                          9
Mar           2                          11
Apr            10                        21
 
 
So this is what I want to see in a line chart format. First I did a count formula for the structure Field and then plugged it in a shared variable formula to calculate the cumulative sum as shown below:
 
@Cumulativesum
whileprintingrecords;
numbervar sum;
sum :=sum + {table.structure}
 
Now, this variable cannot be seen in the chart expert availabe field section, so i wrote another shared variable like this
 
@sharedsum
whileprintingrecords;
shared numbervar sum;
sum;
 
I placed this formula in the details section and suppressed it, in the group header and suppressed it and also in the report footer where the chart is placed and suppressed it.
                      This formula (@sharedsum) can be seen in the chart expert 'available field' section. I added this formula in the show data section, but it seems like its only showing 0 for all the months. When i  put this formula in the details section, the totals add add correctly, but not in the chart. So i dont know whats causing this problem. i dont need to reset the formula, because i need to add up the totals for each record. so can somebody help here please!. I have tried adding a subreport and pass the variable there in the subreport but even in the subreport all I get is just the total value instead of the range of values over the months.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 18 May 2011 at 3:54am
why not just convert the string to a number type
tonumber(field)
IP IP Logged
moontide
Groupie
Groupie
Avatar

Joined: 23 Feb 2011
Online Status: Offline
Posts: 78
Quote moontide Replybullet Posted: 18 May 2011 at 5:02am
Ok. Convert it to a number field and do what ? I'm confused, because the values show up when i put it in the details section, except in the chart.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 18 May 2011 at 5:24am

Your first statement said you issue was bacause you had a string field when you needed a numeric field. I was trying to give you a simple fix.

NOt sure I understand exactly what youa re doing in the rest of your design but you can make an RT without the variables
Field to summarize=Numeric formula field
type = sum
evaluate = fopr each record
reset = Never (if you are doing one chart for the whole report)
 
Set your report as grouped on the date field set to monthly
insert the bar chart
on change of date field set to monthly
show value = new RT
 
IP IP Logged
moontide
Groupie
Groupie
Avatar

Joined: 23 Feb 2011
Online Status: Offline
Posts: 78
Quote moontide Replybullet Posted: 18 May 2011 at 5:49am
ok. I did everything that you mentioned. I converted the string to a numeric, inserted the date field as a group header and put monthly on the options section. I get a huge number for the running total values though. i put the chart in the report footer. I have a monthname formula where they want to see the name of the month from the date. so i used the monthname formula for the 'on change of' and applied the running total for 'show value', but im getting very large numbers in 7 digits which is way off.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 18 May 2011 at 6:32am
do you have duplicate data rows?
IP IP Logged
moontide
Groupie
Groupie
Avatar

Joined: 23 Feb 2011
Online Status: Offline
Posts: 78
Quote moontide Replybullet Posted: 18 May 2011 at 7:12am
the duplicates have been taken care of in the details section where i put a {field} = next {field} formula. All I want to see is the cumulative count in the line chart over the consecutive months as shown in the example I have shown.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 18 May 2011 at 7:15am
ahhh.
hiding rows do not exclude them from calculations
change your RT evaluation from 'For each record' to 'On change of field' then select your {field} that you are using foor suppression.
If you place this RT on the detail section (or group footer) you should get the correct values.


Edited by DBlank - 18 May 2011 at 7:16am
IP IP Logged
moontide
Groupie
Groupie
Avatar

Joined: 23 Feb 2011
Online Status: Offline
Posts: 78
Quote moontide Replybullet Posted: 18 May 2011 at 7:58am
looks like the running total idea is really not working in the charts because the cumulative values are supposed to be adding up in increasing increments for each month in the chart but the chart shows decreasing values over time for the running total values.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 18 May 2011 at 8:01am
the chart architecture and the report grouping architecture have to match.
Does the RT show you the correct values in the report level (not chart)?
IP IP Logged
Page  of 2 Next >>
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.012 seconds.