Chart shows zero values from formula 1

[billove]

The information:

I'm using Crystal Reports 9 for Peachtree. The report is titled Monthly Inventory Sales which has one parameter "Category". The first group is by "TransactionDate", monthly. The second group is by LineItem.ItemID which includes the Sum of JrnlRow.StockingNumber (Units Sold), and a formula {@Monthly Units per Order}:

WhilePrintingRecords;
If {@Gross Number of Orders} = 0
Then 0
Else Sum ({JrnlRow.StockingQuantity}, {LineItem.ItemID}) / {@Gross Number of Orders}

{@Gross Number of Orders} is a formula that receives a calculated number from a subreport (the gross (total) number of orders per month from all categories).

The detail section is suppressed.

Everything works just fine, except for the chart that is in the footer. This chart is a simple line chart that groups by TransactionDate (monthly) and LineItem.ItemID, and shows {@Monthly Units per Order}. The problem is that all the values are 0.

The Select Expert includes "{LineItem.Category} = {?Category}". I tried to delete this line and suppress the info to show only the Categories that the user selected, and removed the subreport and calculated the Gross Number of Orders via a distinct count, but then the chart showed all the ItemIDs, not just from the Category selected.

I'm thinking that the chart being in the footer is the problem...


The Question:

Any ideas how to make this work?

TIA.

 

[lbass]

First, change your formula to:

If distinctcount({table.orderID}, {table.transdate},"monthly")= 0
Then 0
Else Sum ({JrnlRow.StockingQuantity}, {LineItem.ItemID}) / distinctcount({table.orderID},{table.transdate},"monthly")

Remove the parameter from the record selection formula, and instead use it as your group selection formula (report->edit selection formula->GROUP):

{LineItem.Category} = {?Category}

The chart will only pick up the group-selected line items, but because all line item categories are still in the report, though not group selected and therefore not showing in the chart, they will still be used in the distinctcount in your formula.

-LB

 

[billove]

Works great, just what I needed!

Thanks!

I changed the formula to:

If DistinctCount({JrnlRow.PostOrder}, {JrnlHdr.TransactionDate}, "monthly") = 0
Then 0
Else Sum ({JrnlRow.StockingQuantity}, {LineItem.ItemID}) / DistinctCount({JrnlRow.PostOrder}, {JrnlHdr.TransactionDate}, "monthly")