Chart on Maximum

[mrichey]

I would now like to group and chart off the formula below which creates a category bucket based off the max value of a series.
Can this be done? I'm not seeing the formula in the group expert.

if (Maximum ({TIV_AGG.W_PCENTPROCTIME}, {TIV_AGG.SERVER}) in 9.99 to 29.99) then "10 - 30% Max" else
if (Maximum ({TIV_AGG.W_PCENTPROCTIME}, {TIV_AGG.SERVER}) in 30.00 to 49.99) then "30 - 50% Max" else
if (Maximum ({TIV_AGG.W_PCENTPROCTIME}, {TIV_AGG.SERVER}) in 50.00 to 69.99) then "50 - 70% Max" else
if (Maximum ({TIV_AGG.W_PCENTPROCTIME}, {TIV_AGG.SERVER}) in 70.00 to 89.99) then "70 - 90% Max" else
if (Maximum ({TIV_AGG.W_PCENTPROCTIME}, {TIV_AGG.SERVER}) in 90.00 to 100.00) then "90 - 100% Max" else
"0 - 10% Max"

 

[lbass]

I think you would need a SQL statement that returned only the record with the maximum value for this to work. If you have CR 8.0 or 8.5, you can change the SQL query directly. First save your report under another name, and then go to database->show SQL query and at the end of the WHERE clause add:

and {TIV_AGG.W_PCENTPROCTIME} = (select MAX(AKA."W_PCENTPROCTIME") from TIV_AGG AKA where AKA."SERVER" = TIV_AGG."SERVER")

This should return only the maximum record, and therefore you can change your formula to:

if {TIV_AGG.W_PCENTPROCTIME} in 9.99 to 29.99 then "10 - 30% Max" else
if {TIV_AGG.W_PCENTPROCTIME} in 30.00 to 49.99 then "30 - 50% Max" else
if {TIV_AGG.W_PCENTPROCTIME}in 50.00 to 69.99 then "50 - 70% Max" else
if {TIV_AGG.W_PCENTPROCTIME} in 70.00 to 89.99 then "70 - 90% Max" else
if {TIV_AGG.W_PCENTPROCTIME} in 90.00 to 100.00 then "90 - 100% Max" else
"0 - 10% Max"

This will be available for grouping and charting.

If you have a later version, I think this is accomplished through "Add command", though I'm not sure.

-LB

 

[mrichey]

Thanks ... good idea, but I can't find out how to accomplish this with Crystal 9.0. I'll keep looking. Really appreciate your help!

 

[lbass]

From what I remember, "Add command" is available when you choose your datasource. I don't know much more than that--whether it entails writing the entire SQL statement or not. Maybe someone with 9.0 can jump in on this.

-LB

 

[mrichey]

I found out how to add it using the add command, as shown below. but receive and ORA-00911 (invalid character) error. Any suggestions?

SELECT "TIV_AGG"."W_PCENTPROCTIME", "TIV_AGG"."S_DATE", "TIV_AGG"."SERVER", "TIV_AGG"."S_TIME"
FROM "TIVOLI"."TIV_AGG" "TIV_AGG"
WHERE "TIV_AGG"."S_DATE"='20040621' and {TIV_AGG.W_PCENTPROCTIME} = (select MAX(AKA."W_PCENTPROCTIME") from TIV_AGG AKA where AKA."SERVER" = TIV_AGG."SERVER")





ora-00911 invalid character

 

[lbass]

When I view my SQL query, it doesn't show quotes around the table names as yours does, so how quotes are used must be version dependent. After seeing the rest of your SQL, I'm not sure this will work (or how to make it work) with the fact that you are also selecting a date, so that the time field is the maximum within that date, not within the server group as a whole (I was assuming the time was actually a datetime). Someone else here may know how to deal with that, but I don't. Sorry. You might want to start a new thread to catch the attention of someone who knows how to use the SQL statement to do this. You should explain that you need the time to be the maximum within the date per server group.

-LB

 

[mrichey]

Thanks so much for the help. You've definitely pointed me in the right direction. Have a good day!