Crystal Reports 2008: Graphing a formula

[Oscar115]

I am needing to graph the average cpu usage for 12 months.
I am graphing 3 different types of servers, Application, Web, and database along with After Hours average and business hours, 6 line graphs in one graph/chart.
6 months of the line graphs are an actual average and the next 6 months are forcasted based exponentially on the 6th month of actual data.
I have calculated my monthly averages using a running total.
My 6 line graph is correct for actual data.
I have defined the variables to calculate the 1st, 2nd, 3rd, 4th, 5th, and 6th forcasted values per server type and hours (36 formulas), calculated forcast values are correct.
Here is a sample of one of the forcast values:

shared numbervar firstMonthForcastAppAfterHours;
shared numbervar lastmonthactualAppAfterHours;
firstMonthForcastAppAfterHours:=lastmonthactualAppAfterHours*(1.2^(1/12))

I am having problems getting the forcast values to be part of the line graph.

I have defined an Application After Hours formula (intending to define Application Business Hours, Web After Hours, Web Business Hours, DB After Hours, and DB Business Hours) intending to the graph each set of values.
The formula for Application After Hours looks like the following:

Shared numbervar valuetographAppAfterHours;
shared numbervar lastmonthactualAppAfterHours;
if {@Actual Or Forcast} = "Actual" then valuetographAppAfterHours:= {#Monthly CPU Average}
else
if {@Actual Or Forcast} = "Forcast" and {@Group of Dates} = dateadd("m", 1, {?Ending Quarterly Date}) then valuetographAppAfterHours:=(lastmonthactualAppAfterHours*(1.2^(1/12)))
else
if {@Actual Or Forcast} = "Forcast" and {@Group of Dates} = dateadd("m", 2, {?Ending Quarterly Date}) then valuetographAppAfterHours:=(lastmonthactualAppAfterHours*(1.2^(2/12)))
else
if {@Actual Or Forcast} = "Forcast" and {@Group of Dates} = dateadd("m", 3, {?Ending Quarterly Date}) then valuetographAppAfterHours:=(lastmonthactualAppAfterHours*(1.2^(3/12)))
else
if {@Actual Or Forcast} = "Forcast" and {@Group of Dates} = dateadd("m", 4, {?Ending Quarterly Date}) then valuetographAppAfterHours:=(lastmonthactualAppAfterHours*(1.2^(4/12)))
else
if {@Actual Or Forcast} = "Forcast" and {@Group of Dates} = dateadd("m", 5, {?Ending Quarterly Date}) then valuetographAppAfterHours:=(lastmonthactualAppAfterHours*(1.2^(5/12)))
else
if {@Actual Or Forcast} = "Forcast" and {@Group of Dates} = dateadd("m", 6, {?Ending Quarterly Date}) then valuetographAppAfterHours:=(lastmonthactualAppAfterHours*(1.2^(6/12)))

The variable does not appear as a choice to graph.


My report have the following groups:
Actual or Forcast
Month-year
Server Type (Application, Web, Database)
Hours Type (After Hours, Business Hours.

I have tried a sub report to make the line graphs.
I have passed the values as parameters and shared variables and cannot get the fields appear as available values to graph.

I have thought to create a running total field for valuetographAppAfterHours but the formula does not appear as a field to summarize.

Please let me know if you need any more information

Sincerely,
Oscar

 

[hilfy]

Unfortunately, I don't think Crystal will let you do the graph this way. The numbers really need to be part of the data that Crystal is pulling. In order to do this you have two options:

1. Create a stored procedure in the database that will calculate both the past averages and the forecast numbers, returning the data in a single dataset.

2. Write a Command (SQL Select Statement) that will return the numbers in a single dataset.

Since the forecast is a fairly complicated calculation, you may have better luck doing this in a stored proc.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com