Categories
- All Categories
- 141 Oracle Analytics News
- 26 Oracle Analytics Videos
- 14.6K Oracle Analytics Forums
- 5.6K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 52 Oracle Analytics Trainings
- 11 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 2 Oracle Analytics Industry
- Find Partners
- For Partners
SQL using Presentation variable from dashboard prompt, not working
Hi all,
I'm trying to get a (or multiple) presentation variable(s) working in the where statement of a sql-clause. I have this specific sql used in bi using the "DirectDatabaseRequest" functionality.
With variables hardcoded it works fine, but my colleagues would like to have the possibility of using an excisting dasboardprompt.
Summary of sql used:
select SDkcoo ORDERCOMPANY, SDdcto ORDERTYPE
from f4211
where
SDdcto in ('SO','SD')
and SDkcoo in ('00001','00002')
Using this SQL gives requested result, however when I try to change for instance last line, as suggested by a few blogs, it gives none or wrong result
and SDkcoo IN ('@{PCompanynr}['@']{00001}') OR '@{PCompanynr}{}' = ''
Can someone give me a hint or clue where to look for please?
Thanking in advance,
Arie
Answers
-
Bouman, Arie wrote:....however when I try to change for instance last line, as suggested by a few blogs, it gives none or wrong result
Everything comes from a query, so how does the query look like? No result or some other result is maybe not wrong in th end ...
Look at what query is executed and that will probably give you the answer of is the issue. And if not, post it back here.
0 -
I'm not familiar with the use of DDR,, but this solution works on dashboard queries:
The value of a multi-select string prompt looks like: 00001,00002,00003 So, you have to do some parsing and replacement.
In an analog to your example, I have a similar prompt for County. I use a regular expression to search the "Project Info"."County" field(%1) for the value of my prompt. In the value of my prompt, I've replaced the comma with a single pipe, '|'. The regex reads the single pipe as 'OR'. This expression returns the position of 'Franklin' OR 'Somerset' in a record, or zero, if not present. The filter returns records where that result >0.
EVALUATE('REGEXP_INSTR(%1,%2)',"Project Info"."County", REPLACE('@{varCounty}{Franklin,Somerset}',',','|')) > 0
1 -
Your last line gave me finally the clue and it gave the answer of the issue. It looked like there were a lot of quotes 'randomly' placed as result. After some trial and error the final syntax had to read:
and SDkcoo in (@{PVCompanynr}['@']{00001})
Please ignore new presentationvariable name since I modified it for testpurposes.
Thanks for your help.
1
