How to have input parameter in select window of query panel

building reports desktopintelligence
1

Hi,

I need a report which will be having input parameters in the where condition as well as in select window inside a decode statement.

Basically i want to show a object in select window, which is a decode statement and dependent on the input parameter.

How can I use it??? Please suggest.

CA_BO (BOB member since 2008-06-10)

2

Hi,

Define such object in a universe. Include @prompt function into its definition, into the decode function or case when syntax.

So the select part of the object definition would something like:

case when @prompt('select a letter','A',{'A','B'},mono,constrained) = 'A'
          then 'you selected A'
     else 'you did not select A'
end

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

My decode statement is

COUNT (DECODE (calendarshift.fiscalmonth,@Prompt(inputmonth), historymainline.xlotname))

Can I write it this way?? “inputmonth” is what will be displayed caption when asking for prompt.

This is not able to parse though and showing me error
“Invalid definition - UNV0023”

Please help.

CA_BO (BOB member since 2008-06-10)

4

Screenshot attached.
Prompt issue.JPG

CA_BO (BOB member since 2008-06-10)

5

Hi,

Change the keyword multi to mono in the @prompt function. Decode function does not accept more values, just one.

Does it work now?

Marek Chladny :slovakia: (BOB member since 2003-11-27)

6

still the same error.
prompt issue.JPG

CA_BO (BOB member since 2008-06-10)

7

OK, another problem spotted:
Do not use @select inside @prompt function. The 3rd parameter of the @prompt function should not be

'@select(Calendarshift\Fiscalmonth)'

but it should be only this

'Calendarshift\Fiscalmonth'

Marek Chladny :slovakia: (BOB member since 2003-11-27)

8

Thanks a lot. Its working. But I need to use for multiple values in a parameter. So I need to use “multi” inside @prompt.

If not decode, what I should use?

CA_BO (BOB member since 2008-06-10)

9

Keyword Multi in @prompt function works only when @prompt is used in the WHERE condition of a query. It does not work when @prompt is used in the SELECT part of a query.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

10

oh ok. But my requirement is user should be allowed to choose may be 1 value or more value from month list.

Then how can I show the multiple values?

CA_BO (BOB member since 2008-06-10)

11

If possible, do not ask in the prompt for multiple values of months, but ask for a range of months. So there will be 2 prompts - start month and end month. Then include this logic into the CASE WHEN syntax rather then into the DECODE function.

Marek Chladny :slovakia: (BOB member since 2003-11-27)