Crystal Reports 1

[chomzky]

Hello,
I have a command object created to extract the following fields:

AreaAudited: (ex Slots, Table Games)
Section: (ex BlackJack, Live Payments)
GameAuditStartDateTime: (date time field)
ReviewMethod: (Pass, Fail, Live)
Number: (Audit number ex GA20120002122)
Violation: (

I want to create a monthly summary report which will count the number of audits (based on start date time) using the follwing formula:

if {Command.GameAuditStartDateTime} >= #1/1/2012 12:00:00# and {Command.GameAuditStartDateTime} <= #1/31/2012 11:59:59# then 1 else 0

So far I have a summary field to count the number of audits that occured based on the GameAuditStartDateTime field. The problem I am having is there can be multiple violations in each audit so my summary field (GameAuditStartDateTime) is not accurate. If there are 3 violations my summary is off by +2. Is there any other way to accomplish this ?

 

[xxyyzz49]

Perform a distinct count summary on the audit number

 

[lbass]

The distinct count will work if you limit the command records by using date parameters created within the command so that your dataset is only for the particular month. If you must include other records then change your formula to the following:

if {Command.GameAuditStartDateTime} >= #1/1/2012 12:00:00# and {Command.GameAuditStartDateTime} <= #1/31/2012 11:59:59# then
{Command.AuditNumber} else
{@null}

...where {@null} is a new formula created in the field explorer by opening a new formula and saving it without entering anything. Then insert a distinct count on this formula. If you don't use the {@null} approach with a conditional formula, a distinctcount will count records that do not meet your criteria as 1.

-LB

 

[chomzky]

your suggestion worked Pam, thanks so much. The only issue I am having is the folowing:

I am reporting on totals quarterly as well as the rolling year total. Strange as it may seem, everything works great but the total for the year is exactly +1? '

Example:

Black Jack
15 for th5 first quarter
15 for the second quarter
15 for the third quarter
15 for the fourth quarter
YTD total is 61?

Any help is appreciated

 

[lbass]

For the reason I gave...

-LB

 

[chomzky]

I will give it a shot...I have the formula by Audit Number but never inserted the else null...