IF Statement in OBIEE

Question

Hi,

I have a report that returns departments in our organisation. However one requirement is to have a report that returns a code in place of the department name. For example my report will return Human Resources and Finance for department, but my reporting requirement is to return 11 for Human resources and 12 for Finance. Is this something that can be done in OBIEE via an IF statement. E.g. IF Human REsources RETURN 11 etc....

Appreciate the help on this. Can anyone explain how this is done or the best way to achieve this.

Thanks

Thomas Dodds · Answer

Why aren't the codes in the dimension table along with the department names?

Gianni Ceresa · Answer

3487945 wrote:Bearing in mind I have about 40 departments all which I want to return a different code.

This definitely make @ORA-01033 comment even more important !

You have a column with the department name and you have around 40 departments.

It is not manageable by hand in a "IF" to maintain that in the front-end, you will have errors, missing codes etc.

This code is something which must be part of your dimensions containing the departments. In your database (or what is your source) you have a column with the department name, next to it you would ideally have another column with the code, so it is managed in a single place and everybody using that source will always get the right code for the right department. You can then decide which column to use when you build your analysis.

If you can't get that code in the source directly, ideally you must add it inside the RPD (the binary file holding all the business models, metadata and mapping of physical sources etc.). You can have your logic (btw, your need can be achieved by a CASE "your column with the department name" WHEN 'value1' THEN 'code1' WHEN 'value 2' THEN 'code2' .... ELSE 'default code if none found' END ).

Adding this column in the RPD you avoid people makes mistake when managing the "IF condition" in the analysis.

PS: do not forget the "ELSE" part of the CASE WHEN, it will help you find errors and missing codes.

3487945 · Answer

Hi,

I have to admit I dont fully understand what you are saying. I am pretty new to OBIEE and I am comfortable with creating the basic reports, but what I want to do im finding pretty tricky. What is it that you recommend? Bearing in mind I have about 40 departments all which I want to return a different code. I know a IF statement could be long winded for 40 departments... are your methods a better option? can you explain further and simpler

Thanks