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.

 

[elsenorjose]

Hi LB, that's what I did and the data above is what I get. The last record which should be Current, displays In Works.

This is the complete 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 {REP_REQUIREMENT_POLICY.REP_REFRESHER_DUR} <= 90 then 'In Works' 

else 'Current'

And I have a summary for the MAXIMUM on Status as you showed me for the original problem.

 

[lbass]

I'm a little unclear--are you displaying the formula result or the status field? The refresher duration shows 0, so the formula should read "in works".

-LB

 

[elsenorjose]

Just wanted to close out this thread with one more 'Thank you' LB. All your instructions were spot on. It looks like we have the report working as expected.