Print Page | Close Window

Cumulative sum not working in charts

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=13254
Printed Date: 05 May 2024 at 1:16pm


Topic: Cumulative sum not working in charts
Posted By: moontide
Subject: Cumulative sum not working in charts
Date 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.


Replies:
Posted By: DBlank
Date Posted: 18 May 2011 at 3:54am
why not just convert the string to a number type
tonumber(field)

Posted By: moontide
Date 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.

Posted By: DBlank
Date 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
 

Posted By: moontide
Date 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.

Posted By: DBlank
Date Posted: 18 May 2011 at 6:32am
do you have duplicate data rows?

Posted By: moontide
Date 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.

Posted By: DBlank
Date 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.

Posted By: moontide
Date 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.

Posted By: DBlank
Date 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)?

Posted By: moontide
Date Posted: 18 May 2011 at 8:04am

Yes. the RT shows the correct value at the group footer in the report.

Posted By: DBlank
Date Posted: 18 May 2011 at 8:05am
so are you trying to do a bar chart per month?

Posted By: moontide
Date Posted: 18 May 2011 at 8:09am
I am actually doing a line chart per month. They want to see the trend lines across the months. e.g. The first value should be the starting number 3 and then 9, then 11 and 21 in the chart, but thats not happening.

Posted By: DBlank
Date Posted: 18 May 2011 at 8:12am

chart = line (horizontal)

data = advnced
on change of use your same date fuield in your group and set it to monthly
show value use only the RT that you verified in the rest of the report. (don't summarize should already be chacked as TRUE

Posted By: moontide
Date Posted: 18 May 2011 at 8:41am

They want the line chart to be set to vertical so that they could see the months on the x-axis (across). Anyways I did all the things you mentioned and also included to see the data value on the line chart but all i see is 20, 21 and 26 on the markers that i have set up. I also plotted the actual count of the structure have and it on the chart to compare the cumulative count and actual count, but the cumulative count is still showing strange values and are not adding up in the chart.

Posted By: DBlank
Date Posted: 18 May 2011 at 9:00am
use the RT in your main report to tease out where there are unexpected numbers and look at your data to figure it out. If the RT is wrong the graph will be wrong.
You can set the graph horizontal if needed, jut make sure to make your date field sort orter asc or desc to have it make sense. It naturally is asc which woul make your chart visually appear to descend in value rather than ascend in value.

Posted By: moontide
Date Posted: 18 May 2011 at 9:11am
ok. It looks like the the asc or desc order is affecting how the values are spread out. I think its close now. THANK YOU SO MUCH for all the time you put in helping. God bless You Buddy !


Print Page | Close Window