Subreport Data vs. Main Report Data

[MiagiSan]

Hi There,

I apologize for the length of this posting however I want to include as much detail as possible.

I am using CR 10 and I am having trouble with a Subreport.

My main report is very straight forward.
It shows all the cases created within a particular date which is sorted by the person's name that created the case.
Here are the fields that I am using in the main and subreport.

Case = {ACTION_HIST.CASE_ID}
Person = {ACTION_HIST.ROW_ADDED_OPERID}
Created case = {PS_RC_ACTION_HIST.EVENTNAME}= "Case Created"
Updated case= {PS_RC_ACTION_HIST.EVENTNAME}= "Case changed"
Date = {PS_RC_ACTION_HIST.ROW_ADDED_DTTM}

My SubReport shows all the cases that have been updated in a particular date range and is also sorted by person. If the update is less than 15 minutes old and contains the same Case ID then it is not counted unless the person name changed.

Here is the formula for the Subreport:

if ({PS_RC_ACTION_HIST.EVENTNAME}= "Case changed"
and
datediff("n",previous({PS_RC_ACTION_HIST.ROW_LASTMANT_DTTM}),{PS_RC_ACTION_HIST.ROW_LASTMANT_DTTM}) >= 15
and
(previous({PS_RC_ACTION_HIST.CASE_ID})={PS_RC_ACTION_HIST.CASE_ID}))
or (previous ({PS_RC_ACTION_HIST.ROW_LASTMANT_OPRID})<>{PS_RC_ACTION_HIST.ROW_LASTMANT_OPRID})then 1
else if (previous({PS_RC_ACTION_HIST.CASE_ID})<>{PS_RC_ACTION_HIST.CASE_ID}) then 1 else 0


This formula works well however in the Database when a ticket is created there are 2 things happening at the same time:

({PS_RC_ACTION_HIST.EVENTNAME}= "Case changed" and
({PS_RC_ACTION_HIST.EVENTNAME}= "Case Created"


This creates a problem because the main report will record that case XXXXXX was created by Person1 and the Subreport will record an update for person1; for that case it will be counted twice for the same person.


There could be several updates to a case by several different people but is there a way that I could link the main and subreports and say something like.....

If the Case ID containing the Earliest PS_RC_ACTION_HIST.EVENTNAME}="Case changed" is found on the Main report then 0


My End Product will be:

Cases Created
Person1 - ##
Person2 - ##
Person3 - ##

Cases Updated
Person1 - ##
Person2 - ##
Person3 - ##

Thank you very much, I hope this makes sense.

R

 

[lbass]

Maybe you could use an introductory clause to your formula like:

if {PS_RC_ACTION_HIST.ROW_ADDED_DTTM} <> minimum({PS_RC_ACTION_HIST.ROW_ADDED_DTTM},{ACTION_HIST.CASE_ID}) and //etc.

This assumes that you insert a group on {ACTION_HIST.CASE_ID}.

-LB

 

[MiagiSan]

Thank you very much!!

I appreciate the help!!!

R