SQL using Presentation variable from dashboard prompt, not working

Question

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

Bouman, Arie · Answer

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.

Jerry Casey · Answer

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

Gianni Ceresa · Answer

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.