[Solved] Conditional Display of different measures

[charon]

Hi Folks :)

long time no see/ asking for help, gladly i had some vacation time ;)
Im facing an challenge in a crosstab report, i found a solution but its quite the effort, and maybe somebody will find an easier way.
Environment: Cognos 10.2
Fm Modell: DMR

The challenge is, im using several measures in my crosstab report, and i also calculate based on these measures several
What i wanne do is, in case the displayed measure is >999% (e.g. 25219 %) i just want to display  the String "> 999%".
So imagine pls.
Fact 1            Fact 2     Fact3                         (Fact1/Fact2)                 (Fact1/Fact3)
100000           20000             5                              5                                  20000          -> this should be ">999 %"

In my current implementation, im using a combination of a variable and also some logic statements, the downside is i have to use the logic for every measure and i also have to format every single crosstable cell.


Any other ideas are appreciated :)
cheerz
Charon :P

[Satheesh]

Hi,


In (Fact1/Fact3) data item write like this....


If ([size=78%] (Fact1/Fact3) > 900 [/size]) then
( '> 900%' )
else
( Fact1/Fact3 )


Regards
Sateesh

[charon]

Hey Satheesh,

thx a lot for responding. I get the idea and i like it, though i am geting still the standard syntax error "QE-DEF-0459"
I dont know the "size" function and can not find it in the cognos documentation..are you sure thats the correct syntax?
I tried:

If ([size=78%] ([Fact1]/[Fact2]) > 900 [/size])
then
( '> 900%' )
else
([Fact1]/[Fact2])
end


Would be wonderfull if u could take a look at the line...thank you very much and have a nice day :)
cheerz :P

[Ganesh Kedari]

Hi Charon ,
size seems to be HTML code included in definition.

You can also try case statement for including this condition.

case when [Fact1]/[Fact2] > 900
then '>900%'
else [Fact1]/[Fact2]
end

Using this approach you can have multiple conditions also.

Let me know if you face any issues with this method. 

[charon]

H Ganesh,
ty very much for your effort answering me.

I have tried this approach bevor, the problem are the different data types. Cognos does not like using a string after checking for a number...

"RSV-VAL-0010 Report specification .... QE-DEF-0405 incompatibe data types in case statemens"

Would be to easy if it cvould work that way i guess :/

[MFGF]

Hi Charon,

The other big problem you are facing here (of course) is that you cannot display text values in the cells of a crosstab, so changing the expression to cast the result of [Fact1] / [Fact2] as a char to match the datatype of the literal '>900%' will do you no good.

I wonder if you could do something funky with formatting, and perhaps change the negative sign from a - to a >? If that worked, you could set the fixed value to -9 (a number) and format as percent with a negative symbol of >

Sorry - can't test this at the moment so it may be a complete red herring...

MF.

[charon]

Hey Muppetmaster :D

Intersting Idea, would never get that idea on my own. Ive already learned that cognos does have big issues displaying text values in crosstabs, that why my solution is a "black hering" (never heard the "red hering" reference bevor, sounds awesome!).

In my solution i have to opene each crosstab cell and define the content on my own..thats a lot of work for bigger crosstabs. :S

Im trying your approach later and il give a feedback how it worked out.
Thx a lot and have a nice day :)

[MFGF]

Hey Muppetmaster :D

Intersting Idea, would never get that idea on my own. Ive already learned that cognos does have big issues displaying text values in crosstabs, that why my solution is a "black hering" (never heard the "red hering" reference bevor, sounds awesome!).

In my solution i have to opene each crosstab cell and define the content on my own..thats a lot of work for bigger crosstabs. :S

Im trying your approach later and il give a feedback how it worked out.
Thx a lot and have a nice day :)

Good luck! Hope you manage to get a good result!

Here's the info on red herrings, if you have two minutes to while away...

http://en.wikipedia.org/wiki/Red_herring

MF.

[Satheesh]

hi Charon,
That size is not mentioned me that is mentioned by Cognosie site Because I increased the parenthesis size automatically it takes Code...



if ( fact1/fact3 > 900 ) then
('> 900%')
else
( fact1/fact3 )

;D

[Lynn]

You can do this with conditional styles. Conditionally hide the measure if it is more than 900% and then conditionally show a text item you drag into the crosstab intersection when the measure is more than 900%.

Steps are:

Unlock the report and click on the measure item <#1234#> (not the entire crosstab intersection). Create an advanced conditional style named something like "> 900 hide" with an expression fact1 / fact3 > 9. If it is a percentage then 9 is really 900%, right? Set the style for that condition so that the Visible property (on the advanced tab) is set to no, while all other values remain visible.

Next drag in a text item and put in your > 900% message. Now click on the text item and create a conditional style named "> 900 show". Use the same expression as before, except leave that condition with visible set to yes, but all other values set to not visible.

Run the report and you should see the text instead of the offending 900% and higher measures.

Good luck!

[MFGF]

You can do this with conditional styles. Conditionally hide the measure if it is more than 900% and then conditionally show a text item you drag into the crosstab intersection when the measure is more than 900%.

Steps are:

Unlock the report and click on the measure item <#1234#> (not the entire crosstab intersection). Create an advanced conditional style named something like "> 900 hide" with an expression fact1 / fact3 > 9. If it is a percentage then 9 is really 900%, right? Set the style for that condition so that the Visible property (on the advanced tab) is set to no, while all other values remain visible.

Next drag in a text item and put in your > 900% message. Now click on the text item and create a conditional style named "> 900 show". Use the same expression as before, except leave that condition with visible set to yes, but all other values set to not visible.

Run the report and you should see the text instead of the offending 900% and higher measures.

Good luck!

Simple, elegant, beautiful! :)

I mean the technique, of course!  ::)

[charon]

I totaly agree...pretty sexy.
I mean the solution of course  ;D

thx a lot
cheerz :P