Creating a Line Graph in Crystal reports using multiple formulas/value

[simonfisher]

Hi,

I am using Crystal Reports XI with an Oracle 8 database.

I am trying tp draw a line graph of the report below. Each value is a calculated value. For each period I have a
formula to calculate Sum((Tran_Cnt), Prod_line) (@SumPeriodxx) and have a Running Total that is a distinct count of the Account Number in each product line (#RTAcntPeriodxx). The reason I have a #RT is because I have an outer join with another table to determine the total number of accounts for the period, not just those having a transaction.
I also have a formula that determines the period the transaction belongs to (@Periodxx). The format of the PERIOD data in the table is CCYYMM. Each value is calculated by dividing @SumPeriodxx by #RTAcntPeriodxx. The graph should display a line for each Product Line across each period.
In the 'On Change' field I have Period and then Product Line. The Show Values field will only accept one value and not the twelve that I need to graph.

My data is stored in the following tables

Table ACNT Table TRAN

ACNT_NO ------- ACNT_NO
PERIOD ------- PERIOD
SYSTEM TRAN_NO
ACNT_TYPE TRAN_CNT
INT_CAT
PROD_LINE
PROD_TYPE

(Left Outer Join gives me all the Account Numbers in the ACNT Table for the Period and not just those with a Tran_no
in the TRAN Table.)

Is it possible to graph this information using the calculations and formulas I am using or is there an another way of doing this?

Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May
Product Line 1 2.86 3.07 2.84 2.83 0.00 0.00 0.00 2.97 2.90 3.32 3.11 901 3.13
Product Line 2 3.97 3.89 3.89 3.85 0.00 0.00 0.00 3.65 4.11 4.18 4.33 111 4.61
Product Line 3 2.33 2.43 2.28 2.07 0.00 0.00 0.00 2.67 2.96 3.25 2.73 493 2.73
Product Line 4 3.00 3.26 2.97 2.91 0.00 0.00 0.00 2.75 2.81 3.06 3.01 87 2.95
Product Line 5 2.34 2.59 2.34 2.36 0.00 0.00 0.00 2.37 2.34 2.59 2.66 785 2.54
Product Line 6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1.00 2 1.00
Product Line 7 1.00 1.00 1.00 0.00 0.00 0.00 0.00 0.00 1.00 1.50 0.00 0 0.00
Product Line 8 2.98 3.29 3.01 3.08 0.00 0.00 0.00 3.09 2.99 3.28 3.21 7290 3.20
Product Line 9 2.03 2.31 2.05 2.04 0.00 0.00 0.00 2.07 1.98 2.15 2.08 782 2.01
Product Line 10 2.79 2.98 2.76 2.75 0.00 0.00 0.00 2.88 2.69 3.00 3.01 122 2.90
Product Line 11 2.26 2.46 2.26 2.30 0.00 0.00 0.00 2.31 2.25 2.46 2.41 950 2.37
Product Line 12 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1.00 2.00 0.00 0.00 0 0.00

Thanx

Simon Fisher
Perth
Western Australia