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

Gianni Ceresa

Hi,

you can use FILTER(... USING ...), it's supposed to get you what you look for. https://gerardnico.com/dat/obiee/obips/filter_function

NicoLavio

Hi,

Thank you Gianni, it seems to be exactly what I was looking for. I will test and come back to you if needed.

Thanks again!

Gianni Ceresa

Feel free to come back even if not needed (meaning it works) just to close the thread

NicoLavio

... got some issues...

I tried a first step to retrieve the date of the event':     FILTER("Use_date" USING ("Event" = 'Qual'))

but I have an error message "[nQSError : 22032] Function FILTER requires at least one measure colun in its first argument. Please have you System Admin...."

any idea?

Gianni Ceresa

I supposed your "Use_date" was a measure as you use it as cell value in your pivot ...

Why isn't it a measure if you make analysis using it as a measure?

NicoLavio

Sorry, I am not sure to understand what you mean by a measure. "Use_date" is just a date recorded in the DB

Gianni Ceresa

In OBIEE there 2 main kind of values: attributes (dimensional attributes) and measures. OBIEE requires a star schema, so the basic concept of a start schema is a fact table with dimensions linked to it (I'm skipping degenerated dimensions and other kind of "exotic" models on purpose to keep it simple). All the columns of the fact table are supposed to be measures in OBIEE: logical column for which you define an aggregation function. When you than create analysis you select attributes from the dimensions and measures, and the measure will aggregate automatically at the required level based on the attributes because that's what OBIEE does: take the models in the RPD and generate queries based on the designed analysis.

So ... your "Use_date" is acting as measure in your case.

If you have other measures in your model and this is a kind of dimensions-only analysis you will need to solve your need in the RPD. If this is a common kind of analysis you maybe need to challenge your model as you are using columns with a different role than what has been modelled.

NicoLavio

Thanks for the clarification.

I better understand now, first that my problem is not solvable on ad hoc environment with the only attributes. Also that I should look for some training material.

Should I close the topic as unresolved?

Thanks again for your time and support.

Kind regards

Robert Angel

Only been following this peripherally, but you might want to have a look into lookup tables with OBIEE - https://gerardnico.com/dat/obiee/obis/logical_sql/lookup_table  - if you have a single key, or could concatenate a number of values to a single key and you can construct and populate the lookup table (or view equivalent) then that might be a workaround.

Gianni Ceresa

You could maybe try with a CASE WHEN to filter the "use_date" for the required "event" value, and than hoping you at least have the right dimensions to aggregate so that the value can be available next to any "event" value. There is still a little chance it could work, but really depends a lot on the model you have in front of you.

But yes, globally you don't have a simple easy out of the box solution because the model you have isn't really matching the business needs you try to solve.