Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Defining Timeliness and then counting timely & Not Post Reply Post New Topic
<< Prev Page  of 2
Author Message
Babea
Newbie
Newbie
Avatar

Joined: 07 Mar 2012
Location: United States
Online Status: Offline
Posts: 12
Quote Babea Replybullet Posted: 09 Mar 2012 at 1:29am
Thank you very much!
 
Is there a way that I could also get the pending number to show on this report too?
 
I currently have the report record selection set to the following:
 
{DISPOSITION.DISPOSITIONENTRY_DATE}=LastFullMonth AND
{DISPOSITION.DISPOSITION}=["OVR", "SUS"]
 
If I add this to the "Timely" formula, then perhaps I could also get the ones that are
ISNULL{DISPOSITION.DISPOSITION}
and whether or not they are considered timely at this time too? If I can show these records, it may help prevent them from becoming untimely. :)
Thanks! Babea
IP IP Logged
arigatou
Newbie
Newbie


Joined: 13 Mar 2012
Online Status: Offline
Posts: 3
Quote arigatou Replybullet Posted: 18 Mar 2012 at 4:15pm
Place in the report footer is where to place the canvas in the report after RT donw creting its direction. The running total of the first does not work.
HP 97
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 19 Mar 2012 at 4:17am
Sorry for not repsonding, I lost track of this post.
You likely can do what you wanted but I am not sure exactly how as I do not know how your data or the structure.
basically you can change your select statement to include all rows you want to look at and then adjust your conditions for how to count itmes in the Running Totals.
Be aware that the when using an "isnull condition" it needs to be the first part of the statement.


Edited by DBlank - 19 Mar 2012 at 4:19am
IP IP Logged
Babea
Newbie
Newbie
Avatar

Joined: 07 Mar 2012
Location: United States
Online Status: Offline
Posts: 12
Quote Babea Replybullet Posted: 21 Mar 2012 at 9:03am
Thanks!
 
I have another question.
 
I created a report that shows cases that are older than 90 days.
 
I am trying to get only the most recent hearing date to pop on the report because they would like me to not include things that have already been scheduled in the future & probably the last 7 days.
 
This way they can hunt these cases down and schedule them or get on someone to take the needed action.
 
I tried to take care of this in the record selection, but it only seem to remove the most recent date from the report printout.
 
Thanks again!
Babeas
 
 
Thanks! Babea
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Mar 2012 at 9:13am
please show some sample row level data and how explain how you need it to be viewed or 'pop'.
IP IP Logged
Babea
Newbie
Newbie
Avatar

Joined: 07 Mar 2012
Location: United States
Online Status: Offline
Posts: 12
Quote Babea Replybullet Posted: 26 Mar 2012 at 4:31am
Data - hearing officer(last name & first name fields), client id(######), hearing date, hearing id(#######), appeal #(#######), disposition(PND, ABN, OVR, SUS,W) request date, disposition date, program, peppers(xx), hearing type(O, R,A)
 
There are multiple hearing ids & hearing officers due to reschedules, but only the most recent is relevant.
 
There is a 30 day timeline unless there is an peppers or hearing type R then it becomes 90 days.
 
The first report should show cases that are within 5 days of becoming late, so I can help prevent them from being late.
 
The second report should have cases older than 90 days that do not have a hearing date in the past 7 days or in the next 30 days. This will help indentify cases that may have been lost in the rescheduling process.
 
The third report that I need is to show how many cases were timely & late per hearing officer per week for the past 6 months. In addition, I need to show the percentage of cases that were timely. They are suppose to have 6 or more cases per week or 90% timely. These cases would have a disposition of OVR or SUS. My timely report is not coming out perfect at this point, so I need to fine tune it. I think some of it is coming from duplicate records in our old system.
 
Thanks! Babea
 


Edited by Babea - 26 Mar 2012 at 4:33am
Thanks! Babea
IP IP Logged
Babea
Newbie
Newbie
Avatar

Joined: 07 Mar 2012
Location: United States
Online Status: Offline
Posts: 12
Quote Babea Replybullet Posted: 28 Mar 2012 at 2:12am
I was able to get the sql code behind the main system timeliness report, but I am unsure how I use it within crystal.
 

Peppers Report Line 3 SQL: Administrative Appeals

 

 SELECT DISTINCT A.Handling_District, 3 AS PeppersLine,'

 D.DispositionEntry_Date, D.Appeal_Number

 FROM DISPOSITION D, APPEAL A, ASSISTANCEPROGRAM AP, APPEALDISPOCODE AD

 WHERE ( (INSTR(AD.DESCRIPTION, ''WITHDRAW'') = 0) OR

 ((INSTR(AD.DESCRIPTION, ''WITHDRAW'') > 0)

 AND (UPPER(D.COMPLIANCE) = ''Y'')) )

 AND ( A.Appeal_Number = D.Appeal_Number )

 AND ( AP.Assistance_Program = A.Assistance_Program )

 AND ( UPPER(AP.Peppers_Exclude) <> ''Y'' )

 AND ( AD.Disposition = D.Disposition )

 AND ( AD.Decision_Required = ''Y'' )

 AND ( D.Disposition_Date >= :p_BeginDate )

 AND ( D.Disposition_Date <= :p_EndDate )

 AND ( (D.Disposition_Date - A.Request_Date) > 30 )

 AND ( RTRIM(D.Docket_Number) IS NOT NULL )

 ParamByName('p _BeginDate').AsDate := FBeginDate

 ParamByName('p_EndDate').AsDate := FEndDate

 

Peppers Report Line 4 SQL: Client Requested Postponements

 

SELECT DISTINCT A.Handling_District, 4 AS PeppersLine,

D.DispositionEntry_Date, D.Appeal_Number

FROM DISPOSITION D, APPEAL A, ASSISTANCEPROGRAM AP,

APPEALDISPOCODE AD, HEARINGAPPEAL HA

WHERE ( (INSTR(AD.DESCRIPTION, ''WITHDRAW'') = 0) OR'

((INSTR(AD.DESCRIPTION, ''WITHDRAW'') > 0)

AND (UPPER(D.COMPLIANCE) = ''Y'')) )

AND ( A.Appeal_Number = D.Appeal_Number )

AND ( (D.Disposition_Date - A.Request_Date) > 30 )

AND ( AP.Assistance_Program = A.Assistance_Program )

AND ( UPPER(AP.Peppers_Exclude) <> ''Y'' )

AND ( AD.Disposition = D.Disposition )

AND ( AD.Decision_Required = ''Y'' )

AND ( D.Disposition_Date >= :p_BeginDate )

AND ( D.Disposition_Date <= :p_EndDate )

AND ( RTRIM(D.Docket_Number) IS NULL )

AND (( HA.Appeal_Number = A.Appeal_Number)

AND ( UPPER(HA.Hearing_Type) = ''R'' ))

AND ( ( (AP.GroupID <> ''4'') AND

((D.Disposition_Date - A.Request_Date) < 91) ) OR

(AP.GroupID = ''4'' ) AND

((D.Disposition_Date - A.Request_Date) < (61 + GETCREDIT(D.Appeal_Number))) ) )

ParamByName('p_BeginDate').AsDate := FBeginDate

ParamByName('p_EndDate').AsDate := FEndDate

 

Peppers Report Line 5 SQL: Medical Determinations

 

SELECT DISTINCT A.Handling_District, 5 AS PeppersLine,

D.DispositionEntry_Date, D.Appeal_Number

FROM DISPOSITION D, APPEAL A, ASSISTANCEPROGRAM AP, APPEALDISPOCODE AD

WHERE ( (INSTR(AD.DESCRIPTION, ''WITHDRAW'') = 0) OR

((INSTR(AD.DESCRIPTION, ''WITHDRAW'') > 0)'

AND (UPPER(D.COMPLIANCE) = ''Y'')) )

AND ( A.Appeal_Number = D.Appeal_Number )

AND ( (D.Disposition_Date - A.Request_Date) > 30 )

AND ( AP.Assistance_Program = A.Assistance_Program )

AND ( UPPER(AP.Peppers_Exclude) <> ''Y'' )

AND ( AD.Disposition = D.Disposition )

AND ( AD.Decision_Required = ''Y'' )

AND ( D.Disposition_Date >= :p_BeginDate )

AND ( D.Disposition_Date <= :p_EndDate )

AND ( RTRIM(D.Docket_Number) IS NULL )

AND ( NOT EXISTS')

(SELECT HA.APPEAL_NUMBER')

FROM HEARINGAPPEAL HA')

WHERE ( HA.Appeal_Number = A.Appeal_Number)

AND ( UPPER(HA.Hearing_Type) = ''R'' )))

AND ( UPPER(A.Peppers_Delay) = ''MD'' )

AND ( (D.Disposition_Date - A.Request_Date) < 91 )

ParamByName('p_BeginDate').AsDate := FBeginDate

ParamByName('p_EndDate').AsDate := FEndDate

 

Peppers Report Line 6 SQL: Other Acceptable Exemptions

 

SELECT DISTINCT A.Handling_District, 6 AS PeppersLine,

D.DispositionEntry_Date, D.Appeal_Number

FROM DISPOSITION D, APPEAL A, ASSISTANCEPROGRAM AP, APPEALDISPOCODE AD

WHERE ( (INSTR(AD.DESCRIPTION, ''WITHDRAW'') = 0) OR

((INSTR(AD.DESCRIPTION, ''WITHDRAW'') > 0)

AND (UPPER(D.COMPLIANCE) = ''Y'')) )

AND ( A.Appeal_Number = D.Appeal_Number )

AND ( (D.Disposition_Date - A.Request_Date) > 30 )

AND ( AP.Assistance_Program = A.Assistance_Program )

AND ( UPPER(AP.Peppers_Exclude) <> ''Y'' )

AND ( AD.Disposition = D.Disposition )

AND ( AD.Decision_Required = ''Y'' )

AND ( D.Disposition_Date >= :p_BeginDate )

AND ( D.Disposition_Date <= :p_EndDate )

AND ( RTRIM(D.Docket_Number) IS NULL )

AND ( NOT EXISTS

(SELECT HA.APPEAL_NUMBER

FROM HEARINGAPPEAL HA

WHERE ( HA.Appeal_Number = A.Appeal_Number)

AND ( UPPER(HA.Hearing_Type) = ''R'' )))

AND ( UPPER(A.Peppers_Delay) <> ''MD'' )

AND ( RTRIM(A.Peppers_Delay) IS NOT NULL )

AND ('

( (AP.GroupID <> ''4'') AND

((D.Disposition_Date - A.Request_Date) < 91) ) OR

( (AP.GroupID = ''4'')

AND ((D.Disposition_Date - A.Request_Date) < 61) )

ParamByName('p_BeginDate').AsDate := FBeginDate

ParamByName('p_EndDate').AsDate := FEndDate

 

 

The Total Exemptions figure is these 4 SQL statements’ totals added together.
Thanks! Babea
IP IP Logged
<< Prev Page  of 2
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.