Conditional Style Based on Division Name

[Cognos_Jan2017]

For simplicity, we will report three Division Names ...
1 - AAA
2 - BBB
3 - CCC

We have a Query Calculation (CkDays) we need to compare to its Division Name Target Value.
Each Division will probably have different Target Values.

Target Values are set for all 12 months of a Fiscal Year.

We can write three different Conditional Styles to cover AAA, BBB, and CCC's Target Values.
We want to use the Conditional Styles to color green or red backgrounds for the values.

Is there a way to ..
CASE
WHEN 'Division Name' = 'AAA' Then Use Conditional Style 1
WHEN 'Division Name' = 'BBB' Then Use Conditional Style 2
WHEN 'Division Name' = 'CCC' Then Use Conditional Style 3
END
... or similar code?

TIA, Bob

[New_Guy]

Hi,
Did you try a string variable with the same case statement and use it for styling, use the condition explorer, turn on the condition and do th estyling as required.
CASE
WHEN 'Division Name' = 'AAA' Then 'AAA'
WHEN 'Division Name' = 'BBB' Then 'BBB'
WHEN 'Division Name' = 'CCC' Then 'CCC'
END
Let us know if you still need help.

Good luck
New guy

[Cognos_Jan2017]

Thank you New_Guy.

I will need to try that tomorrow as I must attend a non-work obligation very soon.  I am still learning
the condition explorer variables.  I just learned how to get Conditional Styles to work w/ our data.

I will try that early tomorrow am, and will report back to this thread.

[Cognos_Jan2017]

I don't know how to use Condition Explorer.

Code below simplified from the Report.
I have created three Boolean variables of ...
1 - Ops_Boolean
     Expression of ...
     Case
     When [For_ABC] < 1.32 Then Green
     When [For_ABC] > 1.31 Then Red
     End
2 - SGA_Boolean
     Case
     When [For_ABC] < 0.24 Then Green
     When [For_ABC] > 0.23 Then Red
     End
3 - V_Boolean
     Case
     When [For_ABC] < 1.18 Then Green
     When [For_ABC] > 1.17 Then Red
     End

'For_ABC' is the column to display green or red background depending on Target
value in the Case When.

Looking at that column, how do I define "Green" and "Red" as Style Variables,
and then assign them to the "For_ABC" Property of "Style variable"?
... and/ or the Boolean Variable Name is assigned somewhere?

I'm sure it's simple ... once I learn how to assign the Style Variables of "Green" and "Red".

TIA, Bob

[New_Guy]

Hi,
Refer to chapter 15 report studio user guide(10.2.2). There are step by step instructions. If you have samples installed, you can buy report studio cook book which is very good for beginners.
Good luck
New guy

[Cognos_Jan2017]

Thank you New_Guy.  No samples installed but your help and just found excellent YouTube "Cognos Style Variable" video should work.

Am involved in Community Volunteer work next 2 days.  Will try this Monday at Work, and report results in this thread.

Can you recommend any good Report Studio cook books?  Saw, on Amazon, a Report Studio Practical Examples 278-pages book.

Bob

[Cognos_Jan2017]

I found an excellent Style Variable video ...
https://www.youtube.com/watch?v=NxgzDrVMqJU

All is well so far.  I tried a string variable, and want to apply it to a Column.
The video uses a List Column, but my Report is a Crosstab.  So far, I am
unable to assign Foreground/ Background Colors of Green and Red to the
"Green" and "Red" variables for the needed Column.

Will report more as I try to accomplish this.  Must do taxes tomorrow so
may not get results until this Wednesday.

[Cognos_Jan2017]

I am able to set Background Color in the Crosstab Column.

Now need to figure out where my Expression Definition is failing.
Should be able to do that.

The video I referenced above has excellent instruction.

[New_Guy]

Hi,
Try setting it up on crosstab member fact cells.  Try the below
Case when query1.dataitem like 'abc' and measure > 1.18
Try an expression like the above.
Good luck
New guy


Sent from my iPhone using Tapatalk

[Cognos_Jan2017]

Thank you.

Taxes have me tomorrow, but will try on Wednesday and report result here.

[Cognos_Jan2017]

Have never used crosstab member fact cells ... but will research.

The three Boolean variables should work and are determined by the
three different Division Names, the Fiscal Year, and the value of the
Fiscal Year Target.

Each Division Name is assigned a Target value each Fiscal Year.

[Cognos_Jan2017]

To continue this topic, since I don't know how to try crosstab member facts cells,
I tried Style Variable again ...

I have a long
Case
When
End

... to assign colors Green or Red

Running the Report results in an error concluding ...
If the item exists in a query but is not referenced in the layout, add it to a property list.  CRX-API-0005  An error occurred at or near the position &#39;11,&#39.  The variable named &39;[Integrated Incident Management].[Enterprise Structure Level 1].[Level_Name]&#39; is invalid.

When selecting the Query Item, and "Edit Query Expression", its Expression Definition is ...
[Integrated Incident Management].[Enterprise Structure Level 1].[Level_Name]

When dragging in that into the Query, it appears as ...
Level 1 Name

Even changing part of the code
[Integrated Incident Management].[Enterprise Structure Level 1].[Level_Name]
... to ...
[Integrated Incident Management].[Enterprise Structure Level 1].[Level 1 Name]
fails.

What am I doing incorrectly?

TIA, Bob

[Cognos_Jan2017]

Trying this again.  We have a Style Variable (Green or Red) to be assigned to a Query Calculation "ForABC" (an intersection Field in the crosstab) w/ this simplified code (actual code has fully qualified paths) ...

First, is it correct that the Condition Explorer can have a Case When (like below) running to define Green or Red (which are then assigned appropriate colors)?

[Level Name] is the 2nd Field dragged into the rows of a crosstab, [Fiscal Year] is a Column in the crosstab, [ABC] is a Target NOT in the Package.

Case
When ([Level_Name] = "Ops" And [Fiscal Year] = 2017 And [ABC] < 1.32) Then 'Green'
When ([Level_Name] = "Ops" And [Fiscal Year] = 2017 And [ABC] > 1.31) Then 'Red'
When ([Level_Name] = "Ops" And [Fiscal Year] = 2016 And [ABC] < 1.82) Then 'Green'
When ([Level_Name] = "Ops" And [Fiscal Year] = 2016 And [ABC] > 1.81) Then 'Red'
When ([Level_Name] = "SG&A" And [Fiscal Year] = 2017 And [ABC] < 0.24) Then 'Green'
When ([Level_Name] = "SG&A" And [Fiscal Year] = 2017 And [ABC] > 0.23) Then 'Red'
When ([Level_Name] = "SG&A" And [Fiscal Year] = 2016 And [ABC] < 0.39) Then 'Green'
When ([Level_Name] = "SG&A" And [Fiscal Year] = 2016 And [ABC] > 0.38) Then 'Red'
When ([Level_Name] = "Z" And [Fiscal Year] = 2017 And [ABC] < 1.18) Then 'Green'
When ([Level_Name] = "Z" And [Fiscal Year] = 2017 And [ABC] > 1.17) Then 'Red'
When ([Level_Name] = "Z" And [Fiscal Year] = 2016 And [ABC] < 1.21) Then 'Green'
When ([Level_Name] = "Z" And [Fiscal Year] = 2016 And [ABC] > 1.20) Then 'Red'
end

This results in what I listed in my Post directly above ...
Running the Report results in an error concluding ...
If the item exists in a query but is not referenced in the layout, add it to a property list.  CRX-API-0005  An error occurred at or near the position &#39;11,&#39.  The variable named &39;[Integrated Incident Management].[Enterprise Structure Level 1].[Level_Name]&#39; is invalid.

When selecting the Query Item, and "Edit Query Expression", its Expression Definition is ...
[Integrated Incident Management].[Enterprise Structure Level 1].[Level_Name]

When dragging in that into the Query, it appears as ...
Level 1 Name

Even changing part of the code
[Integrated Incident Management].[Enterprise Structure Level 1].[Level_Name]
... to ...
[Integrated Incident Management].[Enterprise Structure Level 1].[Level 1 Name]
fails.

What am I doing incorrectly?

TIA, Bob

[New_Guy]

Hi,
If you are not using the member that you are referring in the case statement you will get that error. select  a row on the crosstab and in the properties pane you will see a propert called 'properties' and select the data item (Level Name)that you are referring in the case statement.
Good luck
New guy

[Cognos_Jan2017]

Thank you.

I replaced the three Names in the Case When w/ the exact Data Item names.  The Case When validates
w/ No errors.  Running the Report does NOT run the defined Green, Red Style Variables, yet ...

I will preface the Data Items w/ the Query Name, and test.  That may be better than the next Test below ...
If that doesn't work, I will fully qualify the package path.

Will report back results/

[Cognos_Jan2017]

SO CLOSE ...

I prefaced the Data Items w/ the Query Name (No fully qualified Package Path).  Validates, No Errors, and runs the Report.

The first "TPRR" value results in Green, but the ALL of the rest of
the TPRR values (18 of them) also are Green.  Does it require something
like a Visual Basic for Applications "Repaint" or "Requery"?  Is there a
similar Cognos statement?

The code is ...
Case
When ([Q_TRPP_Detail].[Level 1 Name] = "Ops" And [Q_TRPP_Detail].[Fiscal Year] = 2017 And [Q_TRPP_Detail].[TPRR] < 1.32) Then ('Green')
When ([Q_TRPP_Detail].[Level 1 Name] = "Ops" And [Q_TRPP_Detail].[Fiscal Year] = 2017 And [Q_TRPP_Detail].[TPRR] > 1.31) Then ('Red')
When ([Q_TRPP_Detail].[Level 1 Name] = "Ops" And [Q_TRPP_Detail].[Fiscal Year] = 2016 And [Q_TRPP_Detail].[TPRR] < 1.82) Then ('Green')
When ([Q_TRPP_Detail].[Level 1 Name] = "Ops" And [Q_TRPP_Detail].[Fiscal Year] = 2016 And [Q_TRPP_Detail].[TPRR] > 1.81) Then ('Red')
When ([Q_TRPP_Detail].[Level 1 Name] = "SG&A" And [Q_TRPP_Detail].[Fiscal Year] = 2017 And [Q_TRPP_Detail].[TPRR] < 0.24) Then ('Green')
When ([Q_TRPP_Detail].[Level 1 Name] = "SG&A" And [Q_TRPP_Detail].[Fiscal Year] = 2017 And [Q_TRPP_Detail].[TPRR] > 0.23) Then ('Red')
When ([Q_TRPP_Detail].[Level 1 Name] = "SG&A" And [Q_TRPP_Detail].[Fiscal Year] = 2016 And [Q_TRPP_Detail].[TPRR] < 0.39) Then ('Green')
When ([Q_TRPP_Detail].[Level 1 Name] = "SG&A" And [Q_TRPP_Detail].[Fiscal Year] = 2016 And [Q_TRPP_Detail].[TPRR] > 0.38) Then ('Red')
When ([Q_TRPP_Detail].[Level 1 Name] = "Z" And [Q_TRPP_Detail].[Fiscal Year] = 2017 And [Q_TRPP_Detail].[TPRR] < 1.18) Then ('Green')
When ([Q_TRPP_Detail].[Level 1 Name] = "Z" And [Q_TRPP_Detail].[Fiscal Year] = 2017
And [Q_TRPP_Detail].[TPRR] > 1.17) Then ('Red')
When ([Q_TRPP_Detail].[Level 1 Name] = "Z" And [Q_TRPP_Detail].[Fiscal Year] = 2016
And [Q_TRPP_Detail].[TPRR] < 1.21) Then ('Green')
When ([Q_TRPP_Detail].[Level 1 Name] = "Z" And [Q_TRPP_Detail].[Fiscal Year] = 2016
And [Q_TRPP_Detail].[TPRR] > 1.20) Then ('Red')
end

[Cognos_Jan2017]

More ... I realized there are 4 Values needed ... the already shown [Level 1 Name], [Fiscal Year], [TPRR] AND added [Month Name1].

I dissected the code to only look at "Ops", Fiscal Year, TPRR's values, and Month Names (code BELOW).

I set the background color of the crosstab intersection to Yellow.  I selected that column, and selected the Green then Red of the String Variable.  Each selection changed to the appropriate color and displayed that in the intersection's properties w/ the Style Variable name and appropriate background color.  I double-clicked the "vertical green bar" to leave the variable environment, ran the report and realized all the colors (in the test for January and February) stayed Yellow ... which means the code didn't execute.

Ideas why the code wouldn't execute?  TIA.

Here's the simple code test ...
Case
When ([Q_TRPP_Detail].[Level 1 Name] = "Valvoline Ops" And [Q_TRPP_Detail].[Fiscal Year] = 2017 And [Q_TRPP_Detail].[Month Name1] = 'January' And [Q_TRPP_Detail].[TPRR] < 1.32) Then 'Green'

When ([Q_TRPP_Detail].[Level 1 Name] = "Valvoline Ops" And [Q_TRPP_Detail].[Fiscal Year] = 2017 And [Q_TRPP_Detail].[Month Name1] = 'January' And [Q_TRPP_Detail].[TPRR] > 1.31) Then 'Red'

When ([Q_TRPP_Detail].[Level 1 Name] = "Valvoline Ops" And [Q_TRPP_Detail].[Fiscal Year] = 2017 And [Q_TRPP_Detail].[Month Name1] = 'February' And [Q_TRPP_Detail].[TPRR] < 1.32) Then 'Green'

When ([Q_TRPP_Detail].[Level 1 Name] = "Valvoline Ops" And [Q_TRPP_Detail].[Fiscal Year] = 2017 And [Q_TRPP_Detail].[Month Name1] = 'February' And [Q_TRPP_Detail].[TPRR] > 1.31) Then 'Red'

end

[MFGF]

More ... I realized there are 4 Values needed ... the already shown [Level 1 Name], [Fiscal Year], [TPRR] AND added [Month Name1].

I dissected the code to only look at "Ops", Fiscal Year, TPRR's values, and Month Names (code BELOW).

I set the background color of the crosstab intersection to Yellow.  I selected that column, and selected the Green then Red of the String Variable.  Each selection changed to the appropriate color and displayed that in the intersection's properties w/ the Style Variable name and appropriate background color.  I double-clicked the "vertical green bar" to leave the variable environment, ran the report and realized all the colors (in the test for January and February) stayed Yellow ... which means the code didn't execute.

Ideas why the code wouldn't execute?  TIA.

Here's the simple code test ...
Case
When ([Q_TRPP_Detail].[Level 1 Name] = "Valvoline Ops" And [Q_TRPP_Detail].[Fiscal Year] = 2017 And [Q_TRPP_Detail].[Month Name1] = 'January' And [Q_TRPP_Detail].[TPRR] < 1.32) Then 'Green'

When ([Q_TRPP_Detail].[Level 1 Name] = "Valvoline Ops" And [Q_TRPP_Detail].[Fiscal Year] = 2017 And [Q_TRPP_Detail].[Month Name1] = 'January' And [Q_TRPP_Detail].[TPRR] > 1.31) Then 'Red'

When ([Q_TRPP_Detail].[Level 1 Name] = "Valvoline Ops" And [Q_TRPP_Detail].[Fiscal Year] = 2017 And [Q_TRPP_Detail].[Month Name1] = 'February' And [Q_TRPP_Detail].[TPRR] < 1.32) Then 'Green'

When ([Q_TRPP_Detail].[Level 1 Name] = "Valvoline Ops" And [Q_TRPP_Detail].[Fiscal Year] = 2017 And [Q_TRPP_Detail].[Month Name1] = 'February' And [Q_TRPP_Detail].[TPRR] > 1.31) Then 'Red'

end

Hi,

Shouldn't "Valvoline Ops" actually be 'Valvoline Ops' (in single quotes)? You are doing this correctly for 'January' and 'Green' - why are you using double quotes around this specific value?

MF.

[Cognos_Jan2017]

You are correct.  Thank you.  Too many years using double quotes in VBA.  Will correct in office later this am.

Hoping the double-quotes is the reason.

Will report results back here.

[BigChris]

I know it's probably not needed in this case, but I always like to put a catch-all ELSE statement in:

Code Select Expand

case
  When... then 'Red'
  When... then 'Green'
  Else 'Grey'
End
That way if you get anything that falls into 'Grey' you can check your logic or verify the data.

[Cognos_Jan2017]

MFGF - Just tested.  Changing from double quote to single quote still doesn't execute the code.

Will add more to BigChris reply below.

[MFGF]

MFGF - Just tested.  Changing from double quote to single quote still doesn't execute the code.

Hmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm.......

Nope. Not working.

I'm concentrating very hard, but try as I might, I can't conjure up a mental view of the error on your screen. My powers of clairvoyance are not what they once were. :)

Can you tell us more?

MF.

[Cognos_Jan2017]

BigChris and MFGF ...

I added an ...
Else 'Orange'

... and the code DOES execute, meaning the "Whens" are being ignored.

Now I need to see why those "Whens" are being ignored.

Validation of the Case When ... thru Else ... and End results in "No errors"

I know that fully qualifying the path of the Query Item can error because the App can store (in this scenario it does) IE, Data Item = 'Level 1 Name' while the Query Item is 'Level_Name'.  Prefacing the Data Item w/ the Query Name does NOT error.

I have to be somewhere else for the next 45 minutes, but when returning, I will play with the "When"s that are failing by dissecting them until a part works, and continue debugging to isolate the bad guy.  I will report my findings.

Thanks to all assisting me on this, Bob

[Cognos_Jan2017]

The code FAILS in BOLD below.

Case
When ([Q_TRPP_Detail].[Level 1 Name] = "Valvoline Ops" And [Q_TRPP_Detail].[Fiscal Year] = 2017 And [Q_TRPP_Detail].[Month Name1] = 'January' And [Q_TRPP_Detail].[TPRR] < 1.32) Then 'Green'

"TPRR" is a Query Calculation of ... ([Total Recordable Cases1] * 200000) / [Employee and Supervised Contractor Hours1]

Is it possible the "< 1.32" requires something to ensure it is read as a Value, Single, Double ??

TIA, Bob

[Cognos_Jan2017]

I just set the Number Format of TPRR as 2 decimal places, but the value may actually be something like 14 decimal places, so
I just tried a Round( ...., 2).  See BELOW.  That did NOT work.

Case
When ([Q_TRPP_Detail].[Level 1 Name] = 'Valvoline Ops' And [Q_TRPP_Detail].[Fiscal Year] = 2017 And [Q_TRPP_Detail].[Month Name1] = 'January' And Round([Q_TRPP_Detail].[TPRR],2) < 1.32) Then 'Green'

When ([Q_TRPP_Detail].[Level 1 Name] = 'Valvoline Ops' And [Q_TRPP_Detail].[Fiscal Year] = 2017 And [Q_TRPP_Detail].[Month Name1] = 'January' And Round([Q_TRPP_Detail].[TPRR],2) > 1.31) Then 'Red'

Any ideas?  TIA, Bob