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.