Conditional formatting not working with data item

[MikeG]

Hi

I have a reconciliation  crosstab report

Units                  2     1    3
Unit Price           4      3    3
Retail Price         8      3    9

I need to check the Retail price field is correct so I create a query calculation data item 1  Units * Unit Price

Units                  2     1    3
Unit Price           4      3    3
Retail Price         8      3    9
Data item 1        8      3    9

So I want to conditional format Retail Price so its measures are green if it is the same as my Data Item 1
So Advanced Conditional Format  ([Query1]. Retail Price ) = numbertostring(([Query1].Data item1))     validate no errors

set to show green if true , ok, run report nothing happens. If I pick any other field in the query to do my comparison with it works fine .

Any one know why this dosn't work ?  Have tried Bolian variable  method as well with same no green  showing result

[charlie]

What do you mean by "If I pick any other field in the query to do my comparison with it works fine"?

This seems pretty straighforward. I was able to do it using a boolean variable for comparison b/w Retail Price and Data Item1. Steps:

1. Create SQL based query
select * from (values
(2, 4, 8),
(1, 3, 3),
(3, 3, 10))

2. Rename data items in query
Unit
Price
Retail

3. Create a new Data Item1 with expression: [Unit]*[Price]
4. Create a boolean variable, Boolean1: [Query1].[Data Item1] = [Query1].[Retail]
5. Use Boolean1 as Style variable to format the list column. Apply Green formatting for 'Yes'.

Is this what you are looking for?

Charlie

[MikeG]

Charlie

If I pick any item in the query and compare it in the Boolean expression (other than the calculated data item field) eg

[Query1].[Unit Price ] <> [Query1].[Retail Price] then my colour shows on report . (Yellow and red to stand out )

I did the Boolean method you suggested with result - still no colour showing.

I will send attachments
Data is coming from TM1 report cube 

[MikeG]

Another view

[MikeG]

Another

[MikeG]

Another

[MikeG]

Hi

Set the No value on the Boolean to be Acqua ran report and this did show
So it seems Cognos is saying the two values don't equal each other, even though on the report you can see they do !

[MFGF]

Hi

Set the No value on the Boolean to be Acqua ran report and this did show
So it seems Cognos is saying the two values don't equal each other, even though on the report you can see they do !

What exactly are you comparing? It appears to me that you are comparing a member with a value. You might try changing the rows to be calculated items also - tuple([your measure],[the relevant member])

Just a thought...

MF.

[MikeG]

Hi MFGF

Yes that is the reason it dosn't work, I am comparing a member to a value. When I didn't get a coercion error on validation I thought I had got away with it .

I tried the tuple ([period], [units* unit retail price)) but I got the error that the calculated value [units* unit retail price] contains members from the same dimension (which it does !) 

so how do I ask if   [retail Price] ( a member) = [units* unit retail price] ( a query calculation of 2 members)

thanks
Mike

[MFGF]

Hi MFGF

Yes that is the reason it dosn't work, I am comparing a member to a value. When I didn't get a coercion error on validation I thought I had got away with it .

I tried the tuple ([period], [units* unit retail price)) but I got the error that the calculated value [units* unit retail price] contains members from the same dimension (which it does !) 

so how do I ask if   [retail Price] ( a member) = [units* unit retail price] ( a query calculation of 2 members)

thanks
Mike

Hi,

You're getting close, I think. The issue is that your calculation ([units] * [unit retail price]) will always return a value. My suggestion is that instead of having the [retail price] member as a row heading, you replace it with a query calculation:

Remove the [retail price] member from the crosstab
Drag in a query calculation in its place, with the expression tuple([retail price],[your measure value])

This should then return a value, which you can compare with the value in your [units] * [unit retail price] calculation

I haven't tried this but in theory it ought to work. Give it a try and let us know - if you still have no luck we can look a little deeper...

Cheers!

MF.

[MikeG]

Hi

No luck with this

I deleted retail price from the Crosstab ( but still in Query) created a query calculation (Cube retail price) as suggested (see attachment ) changed the Boolean expression to    [Cube retail price] = [units multiplied unit price]

Ran, got Acqua colour , indicating Not true

So changed the Boolean  expression to [Cube retail price] <> [units multiplied unit price]

Ran, got Acqua colour , indicating Not true

So to make it really simple I changed the Boolean  expression to [Cube retail price] > 6

Ran, got Acqua colour , indicating Not true

Baffled!

[MFGF]

Hi

No luck with this

I deleted retail price from the Crosstab ( but still in Query) created a query calculation (Cube retail price) as suggested (see attachment ) changed the Boolean expression to    [Cube retail price] = [units multiplied unit price]

Ran, got Acqua colour , indicating Not true

So changed the Boolean  expression to [Cube retail price] <> [units multiplied unit price]

Ran, got Acqua colour , indicating Not true

So to make it really simple I changed the Boolean  expression to [Cube retail price] > 6

Ran, got Acqua colour , indicating Not true

Baffled!

Hmmmm. I notice you defined the calculation as a "calculated member"? We are trying to get away from this being a member. Delete the Cube Retail Price and define it from scratch again as a query calculation - choose the "other" type when you are defining it. See if this fixes things...

Cheers!

MF.

[MikeG]

Getting Closer

Recreated the [Cube Retail Price] as a 'Other' Query calculation , ran query still got the NO acqua colour

Changed the Boolean expression to  Cube Retail Price > 6  and this time it WORKED, yellow and red colour showing True

So Recreated the [Units multiplied Unit Retail Price] as a 'Other' Query calculation , ran query still got the NO acqua colour for when = and <> was used in the Boolean expression

[MFGF]

Hi,

How about coding the expression as tuple([units], [your measure value]) * tuple([unit retail price], [your measure value])

MF.

[MikeG]

Hi,

No luck again, how can something so simple be so problematic

I made the change and sent a screen print , again a I got the false result when I had either = or <> in the Boolean expression

[MFGF]

Hi,

No luck again, how can something so simple be so problematic

I made the change and sent a screen print , again a I got the false result when I had either = or <> in the Boolean expression

How frustrating!

What if you change your Cube Retail Price calc to be:

tuple([retail price],[your measure value]) * 1

Will this trick it into working?

MF.

[MikeG]

Hi,

No luck on your suggestion, however I have solved it.

The 2 fields in the Boolean expression where valid values, I put them in another query and I could add them together with no trouble so it must be the influence of something else i.e. the nested member 'Current Forecast' . I cut this field and put it in the context filter at the top of the report and hey presto it all works .

So somehow, by having the nested field in the Context filter, it retains it's influence on returning my results but looses it's influence on the Boolean expression.

Thanks for all your support, If I hadn't raised this I would have given up with it, only to be asked by the user for it as a mandatory report in the future