Count, Group by Date/Time Field (Crystal Reports) 3

[Drepso]

I am new to crystal reports and a novice using SQL so any help with the below would be appreciated.

I have two fields:

D.Case (Date/Time) Format dd/mm/yyyy hh:mm:ss
T.Case (String)

These are fields in Q_Case Table.

I would like to count T.Case but group by day of month.

eg.
1/07/2003 25
2/07/2003 25
3/07/2003 25

I have tried unsuccessfully to use the functions to convert the Date/Time field to dd/mm/yyyy and crystal won't accept the sql generated by the sql designer.

Could anyone help me with this?

Thanks in advance

 

[Naith]

Group your report on a formula stating CDate({D.Case}) set to daily, to convert the datetime to date. Insert another formula stating Count({T.Case},{D.Case}) to provide the count.

Naith

 

[Drepso]

Thanks Naith.

The first formula works fine but when I try inserting the second formula:

Count({T.Case},[D.Case})

I receive the error message "The summary / running total field could not be created"

Any ideas?

Thanks again for your help.

 

[lbass]

The second part of the formula should be the name of your formula for cdate({D.Case}) that you grouped on:

count({T.Case},{@yourgroupformula})

-LB

 

[Drepso]

Thanks Naith & Ibass

I was still having problems and realised that I hadn't set the frequency on how my formula was grouped:

count({T.Case},{@groupformula}"daily&quot

I have the count correct for each date however it is repeating itself through the report:

1/07/03 170
1/07/03 170
1/07/03 170

Could any of you fine tune this grouping for me?

Thanks again

 

[Drepso]

Ok I have fixed the grouping but have another problem.

In my report heading I have regional calculations:

Count of Cases
Count Cases < 1 hour
Percentage of Cases < 1 hour

This is a monthly report and I have been asked to show more detail ie. Same calculations by day of month.

I have grouped by date and now have the following problem: My percentage is returning the regional calculation shown at the top of my report.

Date Count All Cases Count < 1 hour % < 1 hour
1/07/03 170 60 42
2/07/03 145 63 42

I copied the formula from the report header for
- count all cases
- count < 1 hour
and these work with the grouping but the daily percentage does not.

Any further help would be greatly appreciated.

 

[Naith]

Your % should be based on CountAllCases,GroupDate,&quot;daily&quot; % Count<1hour,GroupField, rather than a % of your report level count.

Naith

 

[Drepso]

Thanks for you help Naith. Greatly appreciated.