How do I suppress a "sorting order" prefix being displayed in a list?

[qu1ncy]

Hello,

I'm currently using CR 8.5 & have a report pulling from a Oracle 9.2 backend. The report indicates how many performance reviews are overdue in a given chosen period of time.
As the formula below indicates, we've broken out the numbers being returned, into groups under 30 days overdue, 30-60 days overdue, etc.

To have this info display in the correct order, I've had to add the alphabetical 'prefix' in my formula. The problem is that if no reviews are due in a particular grouping, I can end up with missing groupings such as the missing C grouping for example:

A - Overdue less than 30 days. 5
B - Overdue 30 - 60 days. 21
D - Overdue 90 - 120 days. 9
E - Overdue exceeding 120 days 13
.........................................................

if {VU_ALL_EMPS_REVIEW.DAYS_OVER_DUE} in 1 to 29 then "A - Overdue less than 30 days." else
if {VU_ALL_EMPS_REVIEW.DAYS_OVER_DUE} in 30 to 59 then "B - Overdue 30 - 60 days." else
if {VU_ALL_EMPS_REVIEW.DAYS_OVER_DUE} in 60 to 89 then "C - Overdue 60 - 90 days." else
if {VU_ALL_EMPS_REVIEW.DAYS_OVER_DUE} in 90 to 120 then "D - Overdue 90 - 120 days." else
if {VU_ALL_EMPS_REVIEW.DAYS_OVER_DUE} > 120 then "E - Overdue exceeding 120 days." else
"F - Other"
.........................................................
This missing C grouping confuses the client. An explanation doesn't work, even though they have MBAs.
How can I suppress the alphabetical prefix, or how else could I deal with this?

Thanks,
Q

 

[synapsevampire]

How can I suppress the alphabetical prefix, or how else could I deal with this?" Why would suppressing the prefix eliminate confusion, they'll still be missing the 60-89 day range?

Anyway, you don't need to add a prefix, and it seems a bad idea anyway, just use the following for the sorting formula:

if {VU_ALL_EMPS_REVIEW.DAYS_OVER_DUE} in 1 to 29 then "A" else
if {VU_ALL_EMPS_REVIEW.DAYS_OVER_DUE} in 30 to 59 then "B" else
if {VU_ALL_EMPS_REVIEW.DAYS_OVER_DUE} in 60 to 89 then "C" else
if {VU_ALL_EMPS_REVIEW.DAYS_OVER_DUE} in 90 to 120 then "D" else
if {VU_ALL_EMPS_REVIEW.DAYS_OVER_DUE} > 120 then "E" else
"F"

Then drop the A,B,C, etc. from the formula for display purposes.

If you want to show the missing groups instead of just eliminating them, then you could create a formula to do that as well, which should eliminate all confusion:

So create an additional section where the group usually displays and use a formula of:

if onfirstrecord and {@MySortFormula} <> "A" then
"Overdue less than 30 days."
else
if previous({@MySortFormula}) = "A"
and
{@MySortFormula} = "C" then
"Overdue 30 - 60 days."
else
if previous({@MySortFormula}) = "B"
and
{@MySortFormula} = "D" then
"Overdue 60 - 90 days."
else
...you get the idea...

The point is that I'd represent ALL of the groupings every time.

Whatever you decide to do, this should encompass the solution.

-k

 

[synapsevampire]

I should also have mentioned that the formula used in the additional section should have a conditional suppression using the same criteria.

-k

 

[qu1ncy]

Hello,

Thank you for your reply Kai. I didn't communicate my intent correctly. Using my formula, the displayed result (if any) will be e.g. "A - Overdue less than 30 days. 5"

What I'm looking for is to maintain the sort order I've established in the formula, but to remove the alphabetical prefix "A - " so the user only sees "Overdue less than 30 days. 5"

The end users have no problem understanding that if one of the groupings is not displayed, it's because there's no data to pull back. However, when they see a missing alphabetical prefix such as I described in my original post, they find it confusing and "messy" (as one senior manager described it).

I've come up with a temporary cosmetic fix but I would prefer a programmatic one if possible.

Thanks,
Q

 

[Turkbear]

Hi,
Edit the Group Name ( one of the options on 'Change Group') to display

MID(@Group_Formula,5)

Assumes the the actual text you want to display starts at the 5th position



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[qu1ncy]

Hello Turkbear,

I used your solution in the "Use a Formula as Group Name" in the Group Options section, and it works beautifully.

Thank you.

Q