nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Filtering data from Union Report

souvik88_570

Hi,

I have a scenario where I am building a report that does union of two reports - "Open Purchase Order" and "Closed Purchased Order" - built from two different subject areas.

"Closed Purchase Order" has a column "Received Date" and also a filter on this column as "is prompted".

"Open Purchase Order" does not have this column and instead has a hard coded date value.

From the dashboard, when I do not select any value for "Received Date", both the reports returns records which is expected.

But when I select some date value for "Received Date" on dashboard, "Closed Purchase Order" report is filtered as per the date, while all the records from "Open Purchase Order" reports are returned.

I want to put some restriction so that when a "Received Date" is passed from dashboard, union report will return only the valid records from "Closed Purchase Order" report and no record from "Open Purchase Order".

Any idea how I can achieve this?

Find more posts tagged with

Accepted answers

All comments

Robert Angel

Sure.

Assign your received date value to a presentation variable.

Use the received date variable on a dummy field populated with todays date +1, with the condition <

Use the presentation value as the value, and make its default date '01-JAN-2449' {ReceivedDate}{01-Jan-2449} - note the edit meant to include the word default, and note this may not be the default date format, as I don't have access to OBIEE at present

Get the idea?

souvik88_570

Hi Robert,

Thanks for your suggestion.

I tried this approach, but the problem is that the "received date" prompt has "between" operator on dashboard. So, the presentation variable captures two values delimited by comma.

When i run the union report without selecting any value in "received date" it works fine, however if i select the values, report throws error.

Any other suggestion?

asim cholas

Hello Souvik,

Add a column formula like below

case when Date '@{DD}{2099-01-01}' >CURRENT_DATE then yourColumn else NULL end

and apply not equal to NULL in filter in the first criteria.

asim cholas

Both will work

if you add a seperate report and keep a condition in section or try the above formula.

souvik88_570

Hi Asim,

Thanks for your inputs.

We already have two separate reports for "Open" and "Closed" PO in two different sections with conditions. This union report has some additional columns and user needs it in this way to do some consolidation.

asim cholas

Dear Souvik,

Try the formula and above and let us know if you need any further inputs.

souvik88_570

Hi Asim,

Will this approach work if the date prompt has "between" operator and presentation variable stores two date values separated by comma?

asim cholas

In OBIEE 12c it will take the second value in variable when you use it in a condition, as far as i know. Try it anyway. meanwhile i will also try.

Filter

case when Date '@{YourVariable}{2099-01-01}' >CURRENT_DATE then YourColumn else NULL end = YourStaticValue

Global Region is from first criteria.

After applying the prompt for date between.

souvik88_570

Sure, let me try.

But my OBIEE version is 11g and i was trying something similar in filter condition, (i did not add any dummy column in the report though) and it was throwing error when i select date values from prompt.

souvik88_570

Thanks Asim.

This has worked with some customization