Implement a 'traffic light' feature in report studio

[norakmal.hakim]

Hi!

Say I already have all the required images of traffic light, red,amber and green. And I have 4 different measures each in different queries already completed to display as singletons in the report (measure 1 - measure 4). What I want to do is this:

Each measure has a different range in which it will be labelled, 'red', 'amber', 'green.'
(e.g. if measure 1 is more than 5 it will be red, between 3 - 5 inclusive amber, less than 3 green)

These ranges are stored in the database. If measure 1 is red OR measure 2 is red OR measure 3 is red OR measure 4 is red then the image should display the red traffic light, else if the measure 1 - 4 is all green , then the image should display green traffic light. Else it should display Amber.

I was wondering what's the best way to implement this, given that the four measures are in 4 different queries and the ranges are stored in a separate table (not joined)?

I tried a method where I extracted out all the different ranges as its own data item(Range 1 - Range 4) and then in a string condition variable, I compare it each of the measure with its relevant range which is itself in an IF function where I applied the above logic (if one measure is red, display red, if all green display green, else display amber).

In the end however I got this error:

"...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 '0'. The variable named '[Potential Issues].[Indicator123]' is invalid."


I googled the error and it says that it's because i didn't use the data item in the layout, so I did and hide it, but I got the same error.

Any help will be great! :)


Anw if it helps, I placed this in a string condition variable. Each data item (e.g. [XXX] or [From ID A] is just one number):

IF ((IF([XXX]>=[TL RANGE QUERY].[From ID A])

THEN([TL RANGE QUERY].[Colour ID A])

ELSE(IF([XXX]>= [TL RANGE QUERY].[From ID B] AND
[XXX]< [TL RANGE QUERY].[To ID B])

THEN ([TL RANGE QUERY].[Colour ID B])

ELSE(IF([XXX]<[TL RANGE QUERY].[To ID C])
THEN([TL RANGE QUERY].[Colour ID C])
ELSE(0))) = 1) OR

(IF([YYY]>=[TL RANGE QUERY].[From ID D])

THEN([TL RANGE QUERY].[Colour ID D])

ELSE(IF([YYY]>= [TL RANGE QUERY].[From ID E] AND
[YYY]< [TL RANGE QUERY].[To ID E])

THEN ([TL RANGE QUERY].[Colour ID E])

ELSE(IF([YYY]<[TL RANGE QUERY].[To ID F])
THEN([TL RANGE QUERY].[Colour ID F])
ELSE(0))) = 1) OR

(IF([ZZZ]>=[TL RANGE QUERY].[From ID G])

THEN([TL RANGE QUERY].[Colour ID G])

ELSE(IF([ZZZ]>= [TL RANGE QUERY].[From ID H] AND
[ZZZ]< [TL RANGE QUERY].[To ID H])

THEN ([TL RANGE QUERY].[Colour ID H])

ELSE(IF([ZZZ]<[TL RANGE QUERY].[To ID I])
THEN([TL RANGE QUERY].[Colour ID I])
ELSE(0))) = 1) OR

(IF([PPP]>=[TL RANGE QUERY].[From ID J])

THEN([TL RANGE QUERY].[Colour ID J])

ELSE(IF([PPP]>= [TL RANGE QUERY].[From ID K] AND
[PPP]< [TL RANGE QUERY].[To ID K])

THEN ([TL RANGE QUERY].[Colour ID K])

ELSE(IF([PPP]<[TL RANGE QUERY].[To ID L])
THEN([TL RANGE QUERY].[Colour ID L])
ELSE(0))) = 1) )

THEN ('Red')

ELSE (
IF(

(IF([XXX]>=[TL RANGE QUERY].[From ID A])

THEN([TL RANGE QUERY].[Colour ID A])

ELSE(IF([XXX]>= [TL RANGE QUERY].[From ID B] AND
[XXX]< [TL RANGE QUERY].[To ID B])

THEN ([TL RANGE QUERY].[Colour ID B])

ELSE(IF([XXX]<[TL RANGE QUERY].[To ID C])
THEN([TL RANGE QUERY].[Colour ID C])
ELSE(0)))) = 3 AND

(IF([YYY]>=[TL RANGE QUERY].[From ID D])

THEN([TL RANGE QUERY].[Colour ID D])

ELSE(IF([YYY]>= [TL RANGE QUERY].[From ID E] AND
[YYY]< [TL RANGE QUERY].[To ID E])

THEN ([TL RANGE QUERY].[Colour ID E])

ELSE(IF([YYY]<[TL RANGE QUERY].[To ID F])
THEN([TL RANGE QUERY].[Colour ID F])
ELSE(0)))) = 3 AND

(IF([ZZZ]>=[TL RANGE QUERY].[From ID G])

THEN([TL RANGE QUERY].[Colour ID G])

ELSE(IF([ZZZ]>= [TL RANGE QUERY].[From ID H] AND
[ZZZ]< [TL RANGE QUERY].[To ID H])

THEN ([TL RANGE QUERY].[Colour ID H])

ELSE(IF([XXX]<[TL RANGE QUERY].[To ID I])
THEN([TL RANGE QUERY].[Colour ID I])
ELSE(0)))) = 3 AND

(IF([PPP]>=[TL RANGE QUERY].[From ID J])

THEN([TL RANGE QUERY].[Colour ID J])

ELSE(IF([PPP]>= [TL RANGE QUERY].[From ID K] AND
[PPP]< [TL RANGE QUERY].[To ID K])

THEN ([TL RANGE QUERY].[Colour ID K])

ELSE(IF([PPP]<[TL RANGE QUERY].[To ID L])
THEN([TL RANGE QUERY].[Colour ID L])
ELSE(0)))) = 3

)

THEN ('Green')

ELSE ('Amber')

)

[Nagendar]

Hi norakmal.hakim,

This may help you..

http://www.cognoise.com/index.php?topic=10396.0

Thanks
Nagendar

[norakmal.hakim]

Thanks for your reply, I've tried to add it to properties but as I have many traffic lights and each traffic light derives information from many queries, I can't choose only one particular query to apply to the page and add to the properties. So I add it to the properties of just the particular table where the singleton is placed. Unfortunately that did not work. 

[norakmal.hakim]

Hi all, I may have solved the problem.

The problem is that I cannot just add it to the properties of the page because i am reading from many different queries. My options were:

- combine all the queries into one query (using joins and dummy keys) and then let the page property be defined by this new combined query.

- place the traffic light image within a singleton and let the singleton read from that query (which is itself a combination of queries for measure 1-measure 4) I can then apply the complicated nested if functions as seen above.

The problem was [XXX],[YYY] etc were all from different queries.

I think fundamentally, this issue arose due to the fact that cognos does not run the query unless it is being used in some way (either in the layout or properties). However, this statement has a caveat in that the item in which the condition is being applied on must exists within a container (like singleton) that reads from that query.

i.e. if the condition of the traffic light is like if [query1].[xxx] > 1, then red, the image must either:
- be placed within the same singleton as [xxx] OR
- be placed in another singleton (or any other container) that reads from query 1.

Only then would the condition work.