Conditional Formatting on Crosstab Column (based on separate measure value)

[godawgs85]

Hi Everyone,

I have hit a challenging roadblock that I wanted to throw out and see if someone can assist.  Here is the challenge:

DataSource:  Dimensional - OLAP
Cognos Version: 10.2.1
Please refer to the attached screenshots in reading my explanation below.

I've created a crosstab off in which I'd like to display one Dynamic Measure - see Screenshot 1.  This dynamic measure looks as the currentMember of the row context and displays a certain measure based on whether or not that currentMember is in a particular set (I've created these sets in the query but they are not in the report).  So in essence, this column is displaying several different measures all in one column based on the row context of each individual cell.  While this part was hard in of itself to create, I have this working and now the challenge is conditional formatting.  Each of the distinct measures that can be displayed in the one column have a different number format I'd like to achieve. 

To achieve this conditional formatting, I created a separate measure called Dynamic Measure Label.  This measure gives a value (1-4) based on the row context of the dynamic measure.  I then have a string variable defined as follows:

Code Select Expand

IF ( [PLDetailCubeQuery].[Dynamic Measure Label] < 4 ) THEN
    ( '%' )
ELSE
    ( '$' )

Posting this variable as the Style Variable of the Dynamic Measure column, I am able to achieve the number formatting I want, but only when the Dynamic Measure Label is present underneath the same nested column (in this example a date - Screenshot 1).  Some values are in % format while others are in $ format.  This is what I'm trying to achieve however I'd prefer to not have the Dynamic Measure Label column present, especially if I want to bring a set out and see multiple columns of the Dynamic Measure - see Screenshot 2.  I don't really need to see the DM Label column at all.  I can't set box type to none as that messes up my columns.  I could make these columns blank which would work fine for HTML however I'd like to export to excel and not have blank columns between every column.  Even if I bring out the DM Label under a separate nested column at the end of the crosstab (Screenshot 3), it eliminates my formatting on the DM column back to the default.

I've googled hundreds of pages and can't seem to find a way to achieve this, either through Style Variables or Advanced Conditional Styles.  Does anyone know how to achieve this type of formatting based off a separate user created measure value?  Is there a way to hide the label column or remove it completely and still retain the conditional formatting?

Thanks in Advance!

[navissar]

Just a quick thought:what if we hack it? You could try something along these lines: add to your measure 0.0000x,x being your format measure. Then, use a style variable that checks what the digit at position x is for styling, and style to only show the first n-1 places after the decimal period. This just might work.

[godawgs85]

Just a quick thought:what if we hack it? You could try something along these lines: add to your measure 0.0000x,x being your format measure. Then, use a style variable that checks what the digit at position x is for styling, and style to only show the first n-1 places after the decimal period. This just might work.

Great idea!  Thanks!  Didn't think of this but makes perfect sense.  That way the style variable is on the Dynamic Measure itself vs. another measure and should work.  I will give it a go and see if I can get it to work.  I'll follow up, as I may need some assistance with the variable.

Thanks again!

[godawgs85]

Hi Nimrod,

So I'm halfway there.  I have successfully added the 0.0000x value to my Dynamic Measure (DM).  I'm struggling a little now with how to build the style variable.  I've never really had to extract a certain digit from a value so I'm not sure of the best function here to do so.  Say my DM value is 224.0400x, with the x representing my styling value.  How would I go about creating a variable that can extract this?  What function is best used for this on a dimensional database?

Thanks again!

[godawgs85]

As an update, I was able to write a style variable that gave me the right values.  It is a little convoluted but it works.  See below:

Code Select Expand

IF ( round ( ( round ( [PLDetailCubeQuery].[Dynamic Measure] , 5 ) - round ( [PLDetailCubeQuery].[Dynamic Measure] , 4 ) ) * 100000 , 0 ) < 2 ) THEN
    ( '%' )
ELSE
    ( '$' )



So in essence I round the DM to the 'x' decimal place (my style value) and then subtract the DM rounded to the 'x'-1 decimal place to isolate the style variable value decimal.  I then multiply this isolation by 100000 and round to the nearest integer to obtain the style value in the form of an integer.  I then do some comparisons with IF/THEN to determine which number format to use.  It works like a charm but I'm not sure if its the most efficient manner of obtaining.

I'm so glad to have accomplished this.  Thanks Nimrod very much for your brainstorming as it set me on the right path.  Hopefully this post can help someone else as well.  It's great to have this forum to ask questions/bounce off ideas.    :D

[navissar]

Great job. Sometimes all we need is  an idea crazy enough to work.
I would take a different approach with the style variable, although yours is fine. I'd just turn the whole thing to string and get the last char.
Anyway, thumbs up for performance and good forum citizenship.