Record Selection - why does order of search change results?

[dacards]

Using Crystal 10. I've got a report with 4 sub reports. My Record Selection for one is as follows:

({HIS_MOD_TRB_Trouble.Submitter Primary Group}={?Pm-@Work Group} and
{HIS_MOD_TRB_Trouble.Create Date}=lastfullmonth) or
({HIS_MOD_TRB_Trouble.Assigned to Group}={?Pm-@Work Group} and
{HIS_MOD_TRB_Trouble.Ticket Closed Time}=lastfullmonth) or
({HIS_MOD_TRB_Trouble.Closer Primary Group}={?Pm-@Work Group} and
{HIS_MOD_TRB_Trouble.Ticket Closed Time}=lastfullmonth) or
({HIS_MOD_TRB_Trouble.Resolver Primary Group}= {?Pm-@Work Group} and
{HIS_MOD_TRB_Trouble.Ticket Resolved Time}=lastfullmonth)and
{HIS_MOD_TRB_Trouble.Core Status}<>"Cancel"

I receive the below results with this record selection:

11 tickets submitted
18 tickets resolved
16 tickets closed


HOWEVER, if I change it and move "Closer Primary Group" to the first spot I get the following results:

({HIS_MOD_TRB_Trouble.Closer Primary Group}={?Pm-@Work Group} and
{HIS_MOD_TRB_Trouble.Ticket Closed Time}=lastfullmonth) or
({HIS_MOD_TRB_Trouble.Submitter Primary Group}={?Pm-@Work Group} and
{HIS_MOD_TRB_Trouble.Create Date}=lastfullmonth) or
({HIS_MOD_TRB_Trouble.Assigned to Group}={?Pm-@Work Group} and
{HIS_MOD_TRB_Trouble.Ticket Closed Time}=lastfullmonth) or
({HIS_MOD_TRB_Trouble.Resolver Primary Group}= {?Pm-@Work Group} and
{HIS_MOD_TRB_Trouble.Ticket Resolved Time}=lastfullmonth)and
{HIS_MOD_TRB_Trouble.Core Status}<>"Cancel"

11 tickets submitted
21 tickets resolved
22 tickets closed


Why do the #s change? My goal is to pull back tickets based on a particular work group that they submitted, resolved, or closed each month.

 

[Madawc]

You should use brackets, make it clearer what is intended.

You could also put each of the tests into a formula field, without an IF so that it is a Boolian that will return True or False. Do a test version in which the test goes below the details.

You may find that some of the tests come out blank, that would mean nulls.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

The issue is that you are using an "or" statement, and once a record satisfies one of the conditions in the record selection formula, the formula stops evaluating for that record.

Also, one record can only contribute to one group and therefore only to one group summary.

If you want to see all instances that meet each set of criteria, then you need to return the same set of records multiple times. You could do this in a union statement in a command.

-LB

 

[dacards]

Thank you for the replies guys.

LB - I'm not familiar with "union statements" and i'm searching on them now. But can you show me an example of how that would look?

Tx again.

 

[lbass]

You would start a new report->your datasource->add command and enter something like the following:

select 'Submitted' as Type, HIS_MOD_TRB_Trouble.`Submitter Primary Group` as Group, HIS_MOD_TRB_Trouble.`Create Date` as Date
from HIS_MOD_TRB_Trouble
where HIS_MOD_TRB_Trouble.`Submitter Primary Group` = {?WorkGrp} and
HIS_MOD_TRB_Trouble.`Create Date` >= dateadd("m",-1, {fn Now()}-day({fn now()})+1) and
HIS_MOD_TRB_Trouble.`Create Date` < {fn Now()}-day({fn now()})and
HIS_MOD_TRB_Trouble.`Core Status`<>'Cancel'
Union
select 'Assigned' as Type, HIS_MOD_TRB_Trouble.`Assigned to Group` as Group, HIS_MOD_TRB_Trouble.`Ticket Closed Time` as Date
from HIS_MOD_TRB_Trouble
where HIS_MOD_TRB_Trouble.`Assigned to Group` = {?WorkGrp} and
HIS_MOD_TRB_Trouble.`Ticket Closed Time` >= dateadd("m",-1, {fn Now()}-day({fn now()})+1) and
HIS_MOD_TRB_Trouble.`Ticket Closed Time` < {fn Now()}-day({fn now()})and
HIS_MOD_TRB_Trouble.`Core Status`<>'Cancel'
Union
select 'Closer' as Type, HIS_MOD_TRB_Trouble.`Closer Primary Group` as Group, HIS_MOD_TRB_Trouble.`Ticket Closed Time` as Date
from HIS_MOD_TRB_Trouble
where HIS_MOD_TRB_Trouble.`Closer Primary Group` = {?WorkGrp} and
HIS_MOD_TRB_Trouble.`Ticket Closed Time` >= dateadd("m",-1, {fn Now()}-day({fn now()})+1) and
HIS_MOD_TRB_Trouble.`Ticket Closed Time` < {fn Now()}-day({fn now()})and
HIS_MOD_TRB_Trouble.`Core Status`<>'Cancel'
Union
select 'Closer' as Type, HIS_MOD_TRB_Trouble.`Resolver Primary Group` as Group, HIS_MOD_TRB_Trouble.`Ticket Resolved Time` as Date
from HIS_MOD_TRB_Trouble
where HIS_MOD_TRB_Trouble.`Resolver Primary Group` = {?WorkGrp} and
HIS_MOD_TRB_Trouble.`Ticket Resolved Time` >= dateadd("m",-1, {fn Now()}-day({fn now()})+1) and
HIS_MOD_TRB_Trouble.`Ticket Resolved Time` < {fn Now()}-day({fn now()})and
HIS_MOD_TRB_Trouble.`Core Status`<>'Cancel'

The punctuation and the functions available to do the lastfullmonth calculation will vary based on your datasource, and the above isn't the best way, but I'm not sure how else to do it. Maybe someone else can jump in on that. Also I replaced the linking field/formula with a parameter created within the command. You could try naming it "pm-@Work Group" and maybe it would work as a linking field. Not sure.

Then the groups will all be in one field and the dates will be all in one field. You can distinguish them by adding the type field to the report and grouping on it.

-LB