Conditional count with drilldown

[mdole]

Hello,

I have a report which displays order information. I have several groups/sections suppressed to allow the user to see the data summarized by:
- Year (total order value and count of orders by month for the year)
- Order number (total order value and order status for each order)
- detail section is the detailed order information at line item level

The problem is that I would like to have a conditional count of orders for each month at year level (example: for the yearh 2001, there were 4 orders in October, 2 orders in November, etc)
I have tried to use the following formula to do this:

if {@OrderMonth} = 10
then 1
else 0

The problem is that this will count the number of line items instead of just the number of orders. I have tried many different things including adding the following conditions in my formula:
- if recordnumber = 1
- if lineitemnumber = max(lineitemnumber, ordernumber)
- on first record

None of these works. I think part of the issue is that the suppressed sections are not evaluated until drill-down. Does anyone have any ideas?

Thanks,
Madlene

 

[Reebo99]

You can sum the formula to give you a total in the year group. Reebo
Scotland (Going mad in the mist!)

 

[mdole]

Sorry - didn't make it clear - that's what I've been trying to do. The issue is that it doesn't work correctly... Either it won't let me sum at year level or the sum is incorrect.

 

[Reebo99]

I'm just trying to work out the prcedure you used :

Create data level formula : if month(datefield) = 10 then 1 else 0
place in detail of report
right click
insert summary
sum and choose group corresponding to year

Is this correct? Reebo
Scotland (Going mad in the mist!)

 

[mdole]

Yes, this is correct except that I need to add another condition to make sure that each order is only counted once. This means that I would only like to count the first detail per group. I have tried a few different methods (such as checking if the line item number = max line item number for example) but I can't get this to work.

 

[Reebo99]

The above example will give you a total of all orders within October and will only count them once.

Basically it gives a single order within October a value of 1, therefore when summing these values, there will only ever be a value of 1 for an individual order.

Keep me posted. Reebo
Scotland (Going mad in the mist!)

 

[kenhamady]

Use a running total, and set the 'evaluate' to be once per group. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[mdole]

I'm still having trouble getting this to work... I started out with the following formula in the details section (which is supressed):

@OctNo:
if {@OrderMonth} = 1
then 1
else 0;

Then I sum @OctNo at group level. The problem is that this does not give me a count of each order but rather a count of each line-item in each order. So, I changed the formula to read as follows:

@OctNo:
if {@OrderMonth} = 1
and ({0BBP_POITEM} = Minimum ({0BBP_POITEM}, {0BBP_PO_ID}))
then 1
else 0;

This works correctly until I refresh the report. Once I re-run the report the total can no longer be evaluated (I get a "0" or blank displayed on the report). If I try to reinsert the running total for @OctNo I am not able to do so (I no longer have the option to select @OctNo to form the running total.

Any thoughts?

 

[kenhamady]

You can't total the second formula. So, use the first formula, but set the running total to "Evaluate" "Once per group", instead of "On Each Record". Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com