Selecting a specific record within a group 1

[elsenorjose]

I have a report that is grouped by Role Code and in the group footer displays the role code, role name, and current status for that role. Additionally, the role can exist in any of several different organizations, but there is no grouping on organization and the organizations are not displayed in the report. The way the report is currently, I get one record for each role, with only one status. However, since the role can exist in several organizations they can have multiple statuses. I am being asked to retrieve 'Current' if the role is 'Current' across all organizations but 'Pending' if the role is 'Pending' in ANY organization. Right now, if the role is 'Current' or 'Pending' in all organizations, it correctly displays that status. However, if there is a combination of 'Current' or 'Pending', the report only displays 'Current'. Is it possible to accomplish what I am being asked to do?

I am using CRXI SP2 on Windows XP against a SQL Server 2005 database.

Thank you.

 

[Madawc]

Do a running-total count with a formula that checks for Pending and only counts for Pending. Start of displaying this, to confirm it works.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[elsenorjose]

Thanks for the reply. Could you elaborate a little on how I would do this? Could you show me how I would write the formulas and display the correct status?

Thank you.

 

[lbass]

If there are only those two statuses, you can just insert a maximum on the status field, and the correct value will display in the group footer.

-LB

 

[elsenorjose]

LB,

There are actually 3 statuses, sorry I didn't realize that. The report developer used a formula for it but I never saw the 3rd status so I thought there were only 2.

If {V_PCR_PERSON_ROLE.ACR_PRS_STATUS} = 'CURRENT' then 'Current' 
else if {V_PCR_PERSON_ROLE.ACR_PRS_STATUS} = 'NOT STARTED' then 'Not Started'
else if {V_PCR_PERSON_ROLE.ACR_PRS_STATUS} = 'INWORKS' then 'In Works' 
else 'Unknown'

Would using a MAXIMUM still work? I inserted it and for the record I am testing with, it works fine, but that particular record only has 2 statuses. If one had 'Not Started', would the MAXIMUM still retrieve the correct status?

Thank you

 

[lbass]

No, the maximum would select the text with the latest letter in the alphabet. But what is supposed to happen if the results are the following:

case 1:
current
not started
current

case 2:
current
notstarted
in works

-LB

 

[elsenorjose]

'Not Started' is exclusive. You can't be in 'In Works' or 'Current' if you've 'Not Started'. I think we're safe but I've asked business users to test.

Thank you again for your help! I'll report back on the conclusion.

 

[lbass]

I thought it was possible because there are different organizations involved.

-LB

 

[elsenorjose]

Yes, you're right. I wasn't thinking through this correctly. I'll need further direction from the business on how to address that scenario. I don't think they were thinking it through either as I was only asked to address 'Current' and 'In Works'. Thanks.

 

[lbass]

Maybe you should be filtering out the non-starts in the selection formula.

-LB

 

[Madawc]

For a running total, you either right-click on the field or create it from the Field Explorer. You get several options, including an option to enter a formla. You'd need something like

{V_PCR_PERSON_ROLE.ACR_PRS_STATUS} = 'NOT STARTED' 
or{V_PCR_PERSON_ROLE.ACR_PRS_STATUS} = 'INWORKS'

(I am assuming that both of these count as 'Pending'. You should be able to adjust it, display it next to the data to check that it is working.)

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[elsenorjose]

LB,

Just wanted to follow up on your last post. It turns out that if the status is 'Not Started' that is what should display. The business would want to see that as a flag that someone forgot to activate a role and needs to get on the ball and fix it. The only 'real' statuses should be 'Current' or 'In Works'.

Thank you both for your help.

 

[lbass]

So you are all set using maximum()?

-LB

 

[elsenorjose]

Sorry for the late reply. Yes, it looks like MAXIMUM() works just fine.

Thanks again.

 

[elsenorjose]

Sorry to trouble you again, but I found out that there is one more criteria to determine if a user is In Works.

There is a field called Refresh Duration which specifies when someone needs to train on a requirement again. If the refresh duration is more than 90 days out, and the user status is In Works, we actually want the report to display them as Current. Only users with requirements due within 90 days and a status of In Works should display In Works.

I tried modifying my current formula but get the 'A summary has been specified on a Non-Recurring Field' error. Here's my modified formula:

if {V_PCR_PERSON_ROLE.ACR_PRS_STATUS} = 'NOT STARTED' then 'Not Started'

else if {V_PCR_PERSON_ROLE.ACR_PRS_STATUS} = 'INWORKS'
        and Maximum({REP_REQUIREMENT_POLICY.REP_REFRESHER_DUR}) >90 then 'In Works' 

else 'Current'

 

[lbass]

Why are you wrapping the field in maximum()? It might help to see some sample data.

-LB

 

[elsenorjose]

There can be several possible values for the refresher duration depending on which organization the role exists in. The trainer enters a value in days for how long it will be before the user has to train again in a particular role. The system sets a user to In Works if there is any outstanding training but the business would want to see them as current if they have refresher training due in more than 90 days.

My formula was incorrect by the way. It should be:

if {V_PCR_PERSON_ROLE.ACR_PRS_STATUS} = 'NOT STARTED' then 'Not Started'

else if {V_PCR_PERSON_ROLE.ACR_PRS_STATUS} = 'INWORKS'
        and Maximum({REP_REQUIREMENT_POLICY.REP_REFRESHER_DUR}) <= 90 then 'In Works'

else 'Current'

The only sample data I have is what is in the report and that just shows the role name and status.

Role Certification Status

Role Code Role Name Status
SD361-04 QC CHEMIST Not Started
TRR SD AZLI LAB HYGIENE AZLI LAB HYGIENE FOR DTS AND QC LABS Current
TRR SD AZLI SURVEILLANCE MONITORING AZLI SURVEILLANCE MONITORING AT SAN DIMAS In Works
TRR SD EHS CHEMICAL HANDLER San Dimas Chemical Handlers Current
TRR SD QC CHEM BASIC TRR SD QC CHEM BASIC In Works
TRR SD QC CHEMIST SAN DIMAS QC CHEMIST In Works
TRR SD QC DISSOLUTION TRR SD QC DISSOLUTION Current
TRR SD QC GC TRR SD QC GC Current
TRR SD QC GO TRR SD QC GO Current
TRR SD QC HPLC TRR SD QC HPLC Current
TRR SD QC RAW MATERIAL Spray Dry Powder TRR SD QC RAW MATERIAL SPRAY DRY POWER Current
TRR SD QC SPECTRA (FTIR) TRR SD QC SPECTRA FTIR Current
TRR SD QC SPECTRA (UV/VIS) TRR SD QC SPECTRA (UV/VIS) Current
TRR SD SITE BASIC MINIMUM TRAINING REQUIREMENTS FOR ALL SD REGULAR EMPLOYEES In Works

 

[lbass]

I meant some mock data that shows the status, refresher duration, and role, along with the group field.

From your description, I think you would get the correct value if you just removed the maximum().

-LB

 

[elsenorjose]

Oh, sorry about that.

I dropped the refresher duration field into the report (all the fields are in the group footer which is Role Code) and all I get are 0s. There are a couple of roles which have both a 0 day refresher duration and a 730 day duration but all I get are the 0s. So, if you were to take what I posted above and add refresher duration, it looks something like this:

Role Code Role Name Status Refresh
TRR SD QC GC TRR SD QC GC Current 0
TRR SD QC GO TRR SD QC GO Current 0
TRR SD QC HPLC TRR SD QC HPLC In Works 0

For illustration's sake, assume the last record has one refresh value of 730 and my formula was rewritten to remove the MAXIMUM. I should see that role as Current and display 730 for duration.

Thank you for your patience and help LB.

 

[lbass]

So if you remove the maximum, then the formula would work correctly, and the maximum would return "Current". Are you all set?

-LB