All values as default in Database Direct request from dashboard prompt (presentation variable)

Programmer Analyst

Disclaimer: I know there are a bunch of similar discussions in the forum but none of them worked for me, therefore, created this. If there is already a resolution that I may have missed in my search, please redirect me.

I have a bunch of dashboard prompts and a database direct request(DDR) is being used for reporting.

For DDR, I must have presentation variables in the query, so I have the filters in the query for about 10 columns (including date ranges) that I have in the prompts section.

example:  abc = '@{pv_a1}'      --- text box

and xyz in '@{pv_z2}              -- list box

and st_dt >= @{pv_sd}        --- calendar

and end_dt <= @{pv_ed}     --- calendar

and so on and so forth...

Issue: When I select one of them, the remaining values are populated as blank since they are not selected. This is expected behavior but this will not return any result because there is such value as blank/null in those columns.

What I want - When I select any of the filters, the remaining ones should pull all the records for the selected filter.

Example: when dates are selected as start date =  09/09/2020 and end date = 09/10/2020, the other fields should pull in all the rows for that date range. Just like how 'is prompted' works.

What I tried -  I looked up online and tried one or combination of the following:

xyz = @{pv_z2}

xyz = '@{pv_z2}'

xyz = @{pv_z2}{'@'}{%}

xyz = @{pv_z2}{'@'}{'%'}

and few more...

nothing worked.

Any help would be appreciated.

[Deleted User]

Programmer Analyst wrote:Disclaimer: I know there are a bunch of similar discussions in the forum but none of them worked for me, therefore, created this.

....which ones? We can't know what you looked at already and hence excluded as a solution.

Programmer Analyst

I guess all the discussions related to this issue in OBIEE.

Most of them had the solutions that I tried (under what I tried) in the question posted.

[Deleted User]

You're working with direct DB requests which circumvent pretty much any and all functionality that's in the system.

You'll manually have to check whether variables are empty with loads of CASE WHEN statements and basically skip applying the filter when nothing was entered.

You always pay dearly in manual labour for using direct DB requests instead of leveraging the product you actually pay for.

User_8WW29

https://community.oracle.com/tech/apps-infra/discussion/comment/15689532#Comment_15689532

Hi, did you find a solution to this.I'm facing a similar issue.