asked on

Sum and Chart a Crystal Report

I have a Crystal report that works, however I would like to tweak it to sum data and show charts. Right now I have the report selecting data and reporting based on the date and then having a sum of the field SUPPLIER_STAT_REP\"."QTY_ARRIVED. I sum a 30 day total. I have 2 sub reports reporting on 60 and 90 day totals. Now, what I want to do is chart the 30 day, 60 day and 90 day totals, however I cannot chart sub report numbers from what I can tell. Rather than a sub report I tried using running totals for the various 30, 60 and 90 day totals rather than sub reports. Once again, I cannot chart or run another formula off of a running total. What are my options? A sql expression? Can I run a new formula off of a sql expression and also display a chart with the results of a sql expression?

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LRI1

ASKER

I follow your formula but that isn't getting me the result that I want. I guess I need a summary formula first for each of the ranges, 30, 60 and 90. I used this formula based on what you gave me and it returns the total for all 90 days. I wanted to get a total for 30 days.

If ({LRI_SUPPLIER_STAT_REP\.REAL_ARRIVAL_DATE} <= currentdate and {LRI_SUPPLIER_STAT_REP\.REAL_ARRIVAL_DATE} >= currentdate -30) then
sum ({LRI_SUPPLIER_STAT_REP\.QTY_ARRIVED})
else
0

SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LRI1

ASKER

OK. Thanks! I have the formula's working, however, I still cannot chart on the formula's. I can only use the data from the database fields. Am I doing something wrong?

SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LRI1

ASKER

I understand what you are saying but how do you accomplish this? When I insert a chart, then right mouse click on the chart, select chart expert and look at the data tab, all I can choose are report fields from the database. I would expect to see my formula listed or at least a place where I can add a formula. I must be way off base. Any help is appreciated.

Mike McCracken

I just put a similar formula in a report and it was available for charting.

Did you enter the formula exactly as shown?

mlmcc

LRI1

ASKER

I got it figured out. Thanks for your help!