Counting rows where date field falls into range. 2

[nharmon]

You would think that this would be simple to do in Crystal Reports, but alas, I have not found a way.

CR 9 is connecting to a MS SQL database. Under the table "Tasks", I would like to know how many rows (entries) exist where the column "OPENDATE" is within a date range that I have specified in a Parameter Field.

At first, I figured the easiest thing to do would be to create a Running Total Field. It would summarize "TASKS.WO_NUM" (this is the table key) with type being "count", and evaluate based on formula "{TASKS.OPENDATE} in {?Report Range}".

When I test this, it will only return zero. To troubleshoot, I removed the evaluation, telling it to "evaluate for each record". When I do this, it returns "1". Then I switched the running total type to "sum", and it returns "9,080.00". This is the value of 'WO_NUM' on the first row.

Apparently, Crystal Reports is only evaluating one row when calculating this Running Total. However, this is not the case with formulas, because if I make a simple formula "Count ({TASKS.WO_NUM})", it will return "7,026.00", the number of rows in this table.

Any suggestions would be greatly appreciated.

 

[lupins46]

When are you re-setting the running total?

 

[nharmon]

I have it set to "never" reset. I've also tried to have it reset based on a formula that could never be true. Both produce the same results.

 

[lupins46]

Use:
{TASKS.OPENDATE} = {?Report Range}

 

[jdemmi]

I am doing something very similar. I am counting "tickets" within a date range.

Here's how I had to set mine up.

RUNNING TOTAL
1. Summarize on CALL_ID (wo_num in your case)
2. Evaluate - USE A FORMULA - {CALLS.Call_DateEntered} >= {@StartDate} and {CALLS.Call_DateCompleted} <= {@EndDate}
3. Reset ON CHANGE OF GROUP (in my case I am grouping by "teams". But I could change this to never reset, without any problem.)

Now here's the "tricky" part. I had to place my totals in the GROUP FOOTER in order for them to display properly.

Also note that in my report:
1. DATERANGE is a CR DATE Paramter with the RANGE option selected.
2. I then use this formula to determine the START of the range minimum({?DateRange})
3. And I use this formula to determine the END of the date range maximum({?DateRange})

And as a final note. In a similar report, where I am NOT grouping. I had to place the running totals in the REPORT FOOTER.

Kind of messy and maybe a lil confusing to explain here. But it works.

-- Jason
"It's Just Ones and Zeros

 

[nharmon]

And as a final note. In a similar report, where I am NOT grouping. I had to place the running totals in the REPORT FOOTER.

This was it! This made the difference. Now don't I feel dumb, lol.

BTW, Lupins suggestion to use "{TASKS.OPENDATE} = {?Report Range}" worked very well as an evaluator for the Running Total Field.

Thanks for all of your help!