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
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
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.
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
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
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
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.
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.
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
Thank you.
Taxes have me tomorrow, but will try on Wednesday and report result here.
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.
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 '11,'. The variable named &39;[Integrated Incident Management].[Enterprise Structure Level 1].[Level_Name]' 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
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 '11,'. The variable named &39;[Integrated Incident Management].[Enterprise Structure Level 1].[Level_Name]' 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
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
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/
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
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
Quote from: Cognos_Jan2017 on 12 Apr 2017 02:22:58 PM
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.
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.
I know it's probably not needed in this case, but I always like to put a catch-all ELSE statement in:
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.
MFGF - Just tested. Changing from double quote to single quote still doesn't execute the code.
Will add more to BigChris reply below.
Quote from: Cognos_Jan2017 on 13 Apr 2017 10:50:48 AM
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.
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
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
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
How is Cognos code debugged as a "step thru" ... if able to do?
Also, does Cognos have a MessageBox or MsgBox capability to see what ...
the value of [Q_TRPP_Detail].[TPRR] is ... when Case When runs?
TIA, Bob
Please do not use the "report to moderator" option unless a post is abusive or there is a problem, and you should definitely not use it multiple times on the same post. Not getting an answer when you like is not an eligible problem. In the US at least, many people have been on an Easter holiday, and some still are today, not to mention it has been over a weekend. Nearly everyone here is volunteering their time on their own time, it is not a support line with paid staff.
Also, if you have not already done so, you really should read through the Report Studio user guide (accessible as RS help). They have sections devoted to explaining how things work and might help you conceptually understand Cognos.
The short answer is that no, there is no MessageBox. Report Studio is very capable, but it is not a programming environment (ignoring that it can be extended to embed javascript). Really it is more of a declarative tool with a lot of flexible properties, including the bonus of being able to set some things as expressions that are evaluated by the Cognos engine when you run the report.
One thing you could do is put your expression, or salient parts of it, as items on your report to see the values. I would include as items the pieces of your expressions, e.g. [Q_TRPP_Detail].[Level 1 Name], and see what values are actually seen. This may help you figure out what is not working.
The Cognoise volunteers are VERY helpful. I have 30 years programming experience in things other than Cognos.
I hope to become skilled enough in Cognos where I can at least help others by answering their questions.
I just resolved the problem. The "TPRR" Query Calculation was incorrect, but a "For TPRR" Query Calculation
written previously is correct.
All is fine.
Again, thanks to the Cognoise volunteers for helping novice Cognos guys like me learn this valuable Reporting Tool.