OBIEE 11g: Function to compare 2 data values in an ad hoc report formula

Robert Angel

re: @Gianni Ceresa 's comment "because the model you have isn't really matching the business needs you try to solve" - ideally this means you go back to the ETL and add your additional logic there, then your answer is precanned and fastest.

Possible but not as performant would be to put the logic along with key(s) to hook it where you need it in a opaque view.

Manoj Dixit

Hi,

I have tried to create an Analysis where I have tried to replicate your scenario. It's based on SampleApp version 607p. The subject area is "U - Usage Tracking" and the columns selected are:

"Query Time"."Start Date"

"Query"."Query Type"

"Query Time"."Start Time".

All these presentation columns belong to S_NQ_ACCT physical table.

Scenario mapping is as under.

Tool ID = Start Date

Event = Query Type and

Use Date = Start Time

Replication case: On each day, if prompt is not the first to be executed then indicate 'No' to the Query Type. Query Type values are: DashboardPrompt, Report, ValuePrompt, Visual Analyzer - to name a few.

Solution: Created DaysFirstPromptTime as

MIN (case when "Query"."Query Type" in ('DashboardPrompt', 'Value Prompt') then "Query Time"."Start Time" end BY "Query Time"."Start Date")

This gives me the first time stamp of a day when Query Type is Dashboard Prompt or Value Prompt. This is to replicate your 'Qual' event and its Use Date value per Tool ID.

Next, created FirstPromptRanIndicator as (only pseudo code given as I am not able to copy from the SampleApp VM to my host).

when Start Time > DaysFirstPromptTime then 'Y' when Start Time < DaysFirstPromptTime then 'No' else 'Unknown'.

I am aware of the fact that your OBIEE version is lower than this 12c environment.

I am not able to copy the downloaded PDF file from the VM to my host. So I have taken screen shots (of the PDF file) and pasted here.

Regards.

Gianni Ceresa

Which is the last option I posted: it will depend on the underneath model, on which we have no idea.

NicoLavio

Hello all,

The solution with CASE WHEN finally worked

Thank you so much! it's amazing seeing so helpful people!

Warm regards

Nico

Robert Angel

Hi, for the benefit of other users can you kindly mark Gianni's answer as correct and any others that helped you get there as helpful - this tells others that Gianni is a genius with OBIEE and that if they have a problem like yours which answer worked for you....

I think you get points for doing so too, so everyone is a winner!