Converting a code in its textual description in report

[pbopbo]

Hello,

I would like to convert a numeric code in its textual description in a report.

The situation is the following one :

I have table which looks like

[tt]
ID EVENTTYPE EVENTDATE
1 1 14.08.2003
2 1 14.08.2003
3 2 14.08.2003
4 3 15.08.2003
5 1 15.08.2003
. . .
. . .[/tt]

In another table i have code values :
[tt]
EVENTTYPE DESCRIPTION
1 OK
2 KO
3 UNDEFINED
[/tt]
My report should seem like that :
[tt]
MONTH EVENTYPE TOTAL
08.2003 OK 10
KO 8
UNDEFINED 0
... ... ...
[/tt]
When I perform the join with codes direct in database in a view, the performances are weaks. So, what I would like to do is, first perform grouping calculation and obtain the result :
[tt]
MONTH EVENTYPE TOTAL
08.2003 1 10
2 8
3 0
... ... ...
[/tt]
and then convert the "EVENTTYPE" in its description in the report with a local correspondence table.

Any idea how to do this ?

Thanks in advance.

Philippe

 

[griffindm]

Phillipe,

If you only have three event types, why not just code the descriptions directly into the report with an If-Then-Else calculated column based on the event type?

Don't know why the join performance would be weak, unless there are a LOT more rows in the event description table and it is not indexed on event type.

Hope this helps,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[pbopbo]

Dave,

Thank you for the response. In fact the situation is a little bit more complex than what I showed in my example.
I have more than 10 codes for this field and the description are translated in three langages (in another table). And I have more than one field in the report that is coded (with different codifications...). That's why performance problems appear (there are multiple joins, which can be outer joins, ... and so on)

So, hardcoding the descriptions in one or more functions seems to be difficult even impossible...

Philippe