Grouping and Conditional Formatting

[mbaml]

Trying to group a 'risk class' using if/then. And then name the group based on that result. So if greater than blank and less than blank, group those together, and then I want the title of that group to be BLANK.

So here is what I have which is not working because I am trying to write it in the group name formula editor.

if {Risk.RiskClass} = ' > 66' then {high.risk} else
if {Risk.RiskClass} = '33 <= 66' then {medium.risk} else
if {Risk.RiskClass} = '< 34' then {low.risk}

The {high.risk} part of my formulas above are NOT in the tables I am pulling from. That's just what I want to name the group.

Please help!

 

[lbass]

If you place {Risk.RiskClass} in the detail section, how does it display? Your examples don't really make sense, since the middle category overlaps with the low risk category.

-LB

 

[mbaml]

I am trying to group the report based off of the risk number within the report. So anything within the risk class field that is between 0-33 I want it to be grouped and say "LOW" as the group name, I dont have the classification of "LOW" anwhere in the table I am pulling the data from, I need to name that in crystal. Anything 34-66 I wanted grouped and say "Medium" as the group name. Anything 67 and above I want grouped and to say "HIGH" as the group name. I have tried the formula editor and the select expert, not working , saying I need a number string in the "" where I have "High".

{Risk.RiskClass} > 66 and
{Risk.RiskClass} = "High

 

[lbass]

Please provide a sample of how the field actually displays and also tell us what the datatype of the field is. Your first post seemed to indicate that it was a string, but your last one suggests it is a number. I understand what you are trying to do--I just can't tell what you are working with in terms of the field itself.

-LB

 

[mbaml]

Hi LB,

I am no longer in the office but will be back into this problem in about 10 hours.

The field name in the table I am linking to is {Risk.RiskClass} the data in the field is a number (between 1 and 100). I want to group the report so that if that number is between certain parameters, it's in the "low, medium or high group". There is no group specification within the table itself that I am pulling the data from. So I need to be able to specify within crystal "if a number in the {Risk.RiskClass} field is <30 then it should be grouped in the 'low risk' category for example. I tried using the 'if/then' commands to create this, but because my data table does not actually have the words "low, medium, high" associated with the risk numbers I'm running into a problem, I cant tell crystal to associate the word 'low' with a certain number apparently.

My second course of action was to try and create the code in SQL itself as a view table grouping the risk by category low, medium or high, and then tried linking the views to crystal and was just going to try and rename the goup headers in design view, but that did not work either.

I have never written this kind of report, nor am a familiar with SQL, learning on the fly here and having a very hard time with what I think is a pretty easy task for someone who knows the system well. Any help would be appreciated!

 

[lbass]

Try:

if isnull({Risk.RiskClass} then
"" else //or replace "" with "Blank" if you want the word "Blank"
if {Risk.RiskClass} > 66 then
"High Risk" else
if {Risk.RiskClass} in 34 to 66 then
"Medium Risk" else
if {Risk.RiskClass} < 34 then
"Low Risk"

Adjust the numbers to correctly reflect the ranges you want. Right now low risk = 0 to 33, 34 to 66 = medium risk, and 67 and above = high risk.

Create this formula in the field explorer->formulas->new and then insert a group on it.

-LB

 

[mbaml]

OK, so I have the formula as the following:

if isnull({Risk.RiskClass} <34 then"Low risk" else if {Risk.RiskClass} > 66 then"High Risk" elseif {Risk.RiskClass} in 34 to 66 then"Medium Risk"

When I check it in the formula editor, It says I am missing ")" somewhere.

Also, I have no grouping yet on this report, thats what I wanted the formula to do, so how do I insert a group into the formula?

Your help is VERY appreciated, you have no idea, thanks!!!

 

[mbaml]

OK, I figure out how to get the wording in there, finally got the formula right. NOW I need to figure out how to group the risk class by the words I added. Here is the formula I used and here is an exmaple of the report.

Formula:

if {Risk.RiskClass} < 34
Then "Low Risk"
else if {Risk.RiskClass} > 66
Then "High Risk"
else if {Risk.RiskClass} > 33 and {Risk.RiskClass} < 67
Then "Medium Risk"

Report:

Risk Grouping / Customer ID / Customer Name / Risk Class
Low
Medium
High
Medium
Low
High

SO, I need to group the formulated risk grouping by class, "low", "med" or "high"

THANKS!!

 

[mbaml]

Answered my own question, I grouped the saved formula by "special" order and chose which order I wanted to see the risk classes in.

THANKS VERY MUCH FOR YOUR HELP!!!!

I may have more questions before the day is done.

VERY appreciated LB!!!!!!!

 

[mbaml]

Hi LB,

Running into another problem with this formula. So, The formula posted above that does work it what I am using, only I am adding to it that IF the risk class is "NULL" in the table I am pulling the data from, then I want it to say "In-Process" in the grouping. Right now, the current formula is for some reason lableing the data with a "NULL" field with "Others".

We tried an "IsNull" formula and that did not work.

Here is the latest formula we tried and it did not work, we are still getting the Null fields listed as "Others" within the group detail:

if {Risk.RiskClass} > 66
Then "High Risk"
else if {Risk.RiskClass} > 33 and {Risk.RiskClass} < 67
Then "Medium Risk"
else if {Risk.RiskClass} < 34 and {Risk.RiskClass} > 0
Then "Low Risk"
else
"In-Process"

ANY HELP WOULD BE APPRECIATED!!!

 

[lbass]

You should have followed the layout in my original post, as null checks must always come first:

if isnull({Risk.RiskClass} then
"In-Process" else
if {Risk.RiskClass} > 66 then
"High Risk" else
if {Risk.RiskClass} > 33 and
{Risk.RiskClass} < 67 then
"Medium Risk" else
if {Risk.RiskClass} < 34 and
{Risk.RiskClass} > 0 then
"Low Risk" else
"In-Process" //for any zeros

-LB

 

[mbaml]

I am entering the formula exactly as you have it and am getting the error: ")Missing"

That is why I changed it the first time because by taking the Is Null out, I was able to have the formula work.

 

[fisheromacse]

There is a parenthesis missing after .RiskClass}
it is:
if isnull({Risk.RiskClass}

it should be:

if isnull({Risk.RiskClass})

 

[mbaml]

OK, thank you. I checked the updated formula...no errors found. However, it is still saying "others" in my report where the null fields are and its supposed to say "In-Process"

Here is the formula exactly as I ran it:

if isnull({Risk.RiskClass}) then
"In-Process" else
if {Risk.RiskClass} > 66 then
"High Risk" else
if {Risk.RiskClass} > 33
and{Risk.RiskClass} < 67 then
"Medium Risk" else
if {Risk.RiskClass} < 34 and
{Risk.RiskClass} > 0 then
"Low Risk" else
"In-Process" //for any zeros

 

[lbass]

That's because you used specified order originally. Change it to ascending order and run the report and then go back and use specified order. This time you should see "In-Process" in the list instead of "Other".

-LB

 

[mbaml]

PERFECT, thanks LB! Again, your help has been invaluable this week and very much appreciated!!!!!