Report Question

[Creakinator]

Some day I hope to understand how to do things in CR. Right now I am so grateful for the helpful folks in this forum.

I have a report that has this data
Incident_Type Case# Approval Type Assigned date Esig date Turn around time
Burglary 11-1111 analyst 11/11/2011 11/12/2011
Burglary 11-1111 Verifier 11/13/2011 11/14/2011

I would like the report to show this data:
Incident_Type Case# Assigned date Esig date Turn around time
Burglary 11-1111 11/11/2011* 11/14/2011** 3***

* The analyst Assigned date I tried using a formula "if {X_APPROVAL.APPROVAL_TYPE} = "ANALYST" then {X_APPROVAL.ASSIGNED_DATE}" and put it in the 4th group footer
**The verifier Esig date I tried using a formula "if {X_APPROVAL.APPROVAL_TYPE} = "VERIFIER" then {X_APPROVAL.ASSIGNED_DATE}" and put it in the 4th group footer
***The turn around time = Verifier Esig date - analyst Assigned date

My above mentioned tries didn't help me.

The date data is in one table called x_approval with these field;
x_approval.assigned_date
x_approval.esig_date
x_approval.approval_type

The incident_type is coming from another table - v_parentCase_info

Here's the select criteria:
{LIST_ENTRY.LIST} = "INCIDENTS" and
{CASES.TEMPLATE} = "LATENT_PRT" and
{X_APPROVAL.APPROVAL_TYPE} in ["ANALYST", "VERIFIER"]

I uploaded the relationship screen and group selection (upper right of image) to mediafire if that will help you.

Sql 2005 with an ODBC connection

Thank you.

Christy

 

[IanWaterman]

You are on the right lins but you must capture the dates with a variable

@Analyst
whileprintingrecords;
gloabal datevar assigned;

//* The analyst Assigned date I tried using a formula "
if {X_APPROVAL.APPROVAL_TYPE} = "ANALYST" then
assigned:={X_APPROVAL.ASSIGNED_DATE}"

@Verifier
whileprintingrecords;
gloabal datevar verifier;

//**The verifier Esig date I tried using a formula "
if {X_APPROVAL.APPROVAL_TYPE} = "VERIFIER" then
verifier:= {X_APPROVAL.ASSIGNED_DATE}"

Place these in details and suppress

Place this in group 4 footer
@turnaround
//***The turn around time
whileprintingrecords;
gloabal datevar verifier-gloabal datevar assigned;

@Reset
whileprintingrecords;
gloabal datevar assigned:=Date(1900,1,1);
gloabal datevar verifier:=Date(1900,1,1);
Place this in Group4 Header and suppress

Ian

 

[Creakinator]

Thank you for the help.

But I am having some issues with this.

Do I put all of the following in one formula and ditto with the verifier example also?
--------------
@Analyst
whileprintingrecords;
global datevar assigned;

//* The analyst Assigned date I tried using a formula "
if {X_APPROVAL.APPROVAL_TYPE} = "ANALYST" then
assigned:={X_APPROVAL.ASSIGNED_DATE}
-------------
I did put it all in one formula and am getting an error that says the assigned:= needs to be a date and I can't get past that error. There was a " mark at the end of the last line that I removed as it was giving an error on it too.

Thank you.

Christy

 

[IanWaterman]

Its probably because your field is datetime, if you are not interested in time then use

//* The analyst Assigned date I tried using a formula "if {X_APPROVAL.APPROVAL_TYPE} = "ANALYST" thenassigned:=date({X_APPROVAL.ASSIGNED_DATE})

If you need time then use datetime vars instead.

You can use one formula, I just split them out to keep it simplere for you to foloow incase you were not fmiliar with variables.

Ian