Conditional Formatting and Grouping 1

[thelittleone]

Hi All,

I have the following result set and would like to Conditionally Format and then Group the data:

CategoryCode OriginalBudgetValue
18000 5000
18001 3250
18002 1450
18005 6200

Conditional Format - If CategoryCode BETWEEN 18000 and 18005 then 'consultant'. The Conditional text is Consultant (18). ***This portion works correctly***

CategoryCode OriginalBudgetValue
Consultant (18) 5000
Consultant (18) 3250
Consultant (18) 1450
Consultant (18) 6200

The report requirement is to display the data as follows:

Consultant (18) 15900 (the sum of OriginalBudgetValue)

Any help would be appreciated.

Thanks

 

[DoubleD]

Instead of conditional formatting, you could use an If statement in a calculation:

If(CategoryCode > 18000 And CategoryCode < 18005) Then ("Consultant" + Left(CategoryCode,2)) Else (CategoryCode)

Now you can group on this calculated field.

I am what I am based on the decisions I have made.

DoubleD

 

[thelittleone]

Can you please outline the procedures to follow. I added a calculation to the report incorporating the above statement and the application returned a SQL error. The only way that I have been able to progress further is by adding a Group Calculation field to the report. After adding the Group calculation, the report looks as follows

Consultant (18) 5000
Consultant (18) 3250

If I attempt to Group by Consultant (18) I again the SQL error.

Help!

 

[DoubleD]

Is CategoryCode a string or numeric?

If it's a string, you probably need to change your calculation to:

If(CategoryCode > 18000 And CategoryCode < 18005) Then ('Consultant(' + Left(Number-To-String(CategoryCode),2) + ')') Else (Number-To-String(CategoryCode))

You should then be able to Group on this field, and sum on your Budget value field.

I am what I am based on the decisions I have made.

DoubleD

 

[thelittleone]

CategoryCode is a numeric field. For some reason I had to enclose the CategoryCode values in apostrophes. You would think that the datatype was string, but it really is integer.

I have one more step to take. Now that I have the CategoryCode values grouped and the BudgetValues summed,
can I group the following:

Consultant (18) 15900
Consultant (19) 25000

to read

Consultant (18,19) 40900

Thanks for all of your help

 

[DoubleD]

IF(CategoryCode > 18000 And CategoryCode < 18005) THEN ('Consultant(18,19)') ELSE IF (CategoryCode > 19000 And CategoryCode < 19005) THEN ('Consultant(18,19)') ELSE (Number-To-String(CategoryCode))


I am what I am based on the decisions I have made.

DoubleD