Sum and Chart a Crystal Report

LRI1
LRI1 used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
I don't believe you can use SQL expressions in charts.

Charts are built early in the report rendering process.  Subreports and running totals are done in the last pass.

Is the data for the 60 and 90 day summary available in the main report?
If so try this
Add 3 formulas to the report
30 Day
If ({DateFIeld} >= CurrrentDate - 30) then
     {ValueField}
else
     0

60 Day
If ({DateFIeld} >= CurrrentDate - 60) then
     {ValueField}
else
     0

90 Day
If ({DateFIeld} >= CurrrentDate - 90) then
     {ValueField}
else
     0

You should be able to use those formulas on the chart

mlmcc

Author

Commented:
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
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
You can't do the summary in that formula you have to just put the value from the record.
You can then summarize the formula

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

Another formula - put this on the report rather than the 30 day formula
Sum({@30 Day})

Same for the other 2

mlmcc
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Author

Commented:
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?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
Which formula are you trying to chart on?

You need to chart on the 30Day formula

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

mlmcc

Author

Commented:
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 McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

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

Did you enter the formula exactly as shown?

mlmcc

Author

Commented:
I got it figured out.  Thanks for your help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial