Oracle Communities

Oracle Analytics and AI

Child Item

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

NicoLavio

Hi,

I have to develop an Ad Hoc report on OBIEE 11g but I miss a SQL formula to get a data from the DB.

Here is what I have:

A field named “Tool ID#” to identify different tools.

A field named “Event” with different events (‘Bas’, ‘Proc’, ‘Dev’, ‘Qual’, ‘Aud’) when the tools were used. Note that there is no chronological order in these events.

A field named “Use_date” corresponding to the date when the tools were used for an event.

To visualize, you can imagine a table with “Tool ID” in rows, “Event” in column, and “Use_date” in the cells.

My objective is to create a new field (let’s call it “verification_required”) which would indicate if a verification of the tool is required after an event. At the end I will use this field for a conditional formatting in the table described above.

Rule to define if verification is required is simple: a tool must be verified after an event if the date of this event (use_date) is after the date of the event ‘Qual’ of this same tool.

Is there any SQL function or Tip to retrieve - in a formula - the use_date when event ‘Qual’ took place for a specific Tool_ID#? Is it even possible to do this?

Thanks in advance

Find more posts tagged with

Accepted answers

All comments

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!