Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 77 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Filtering data from Union Report
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?
Answers
-
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?
0 -
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?
0 -
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.
0 -
Both will work
if you add a seperate report and keep a condition in section or try the above formula.
0 -
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.
0 -
Dear Souvik,
Try the formula and above and let us know if you need any further inputs.
0 -
Hi Asim,
Will this approach work if the date prompt has "between" operator and presentation variable stores two date values separated by comma?
0 -
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.
0 -
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.
0 -
Thanks Asim.
This has worked with some customization
0