cancel
Showing results for 
Search instead for 
Did you mean: 

@prompt not working as expected when there are multiple prompts

Former Member
0 Kudos

Hello All,

I have created a derived table with the below structure :

SELECT

  "_SYS_BIC"."scorecard-dev/VAN_SM_GLOB_IL_SALES_MARGIN/olap"."SITE_LEVEL1DESC",

sum(  "_SYS_BIC"."scorecard-dev/VAN_SM_GLOB_IL_SALES_MARGIN/olap"."SALES_VALUE")

FROM

  "_SYS_BIC"."scorecard-dev/VAN_SM_GLOB_IL_SALES_MARGIN/olap"

  where

  "_SYS_BIC"."scorecard-dev/VAN_SM_GLOB_IL_SALES_MARGIN/olap"."LEVEL1DESC" = @Prompt('Enter values for LEVEL1DESC:','A',,Mono,Free,Not_Persistent)

or  @Prompt('Enter values for LEVEL1DESC:','A',,Mono,Free,Not_Persistent)  = 'ALL'

and

  "_SYS_BIC"."scorecard-dev/VAN_SM_GLOB_IL_SALES_MARGIN/olap"."LEVEL2DESC" = @Prompt('Enter values for LEVEL2DESC:','A',,Mono,Free,Not_Persistent)

or  @Prompt('Enter values for LEVEL2DESC:','A',,Mono,Free,Not_Persistent)  = 'ALL'

and

  "_SYS_BIC"."scorecard-dev/VAN_SM_GLOB_IL_SALES_MARGIN/olap"."CAL_YEAR_NO" = @Prompt('Enter values for CAL_YEAR_NO:','N',,Mono,Free,Not_Persistent)

or  @Prompt('Enter values for CAL_YEAR_NO:','N',,Mono,Free,Not_Persistent)  = '0'

 

  group by

  "_SYS_BIC"."scorecard-dev/VAN_SM_GLOB_IL_SALES_MARGIN/olap"."SITE_LEVEL1DESC".

When i consume this Universe from  Xcelsius, Strangely the prompt for only first is getting reflected in the resultset.

whatever values i give for the second and third prompt, it is ignored and i get the result with only first prompt.

Even if i leave the second and third prompts empty, the query returns the same result ( ideally nothing should be returned as where clause does not match).

Please note that the prompts are mandatory prompts.

Is this a product bug or Is there something Im missing, cos this seems to be a simple usecase.

Thanks,
Vinay

Accepted Solutions (0)

Answers (2)

Answers (2)

Sujit
Active Participant
0 Kudos

Hi,

I believe the 'All' value should be on the left of expression than on right, could you try the @Prompt with below syntax,

@Select(Country\Country) in @Prompt('Enter Country:','A','Country\Country',Multi,Free,) OR 'All'  IN @Prompt('Enter Country:','A','Country\Country',Multi,Free,)

In addition, modify the LOV SQL of Country object by going to the object properties and add a UNION clause, so the entire SQL looks like

SELECT DISTINCT

  Country.country

FROM

  Country

UNION   SELECT 'All'

FROM

  Country

Don't forget the check 'Do not generate SQL' option at the bottom of the screen.

Let me know if this helps.

Sujit Honrao

Former Member
0 Kudos

Hello Vinay,

Why is @prompt() equated to 'ALL'  in your query? I have never seen @prompt() present at the left hand side of an expression

Thanks,

Madhumitha

Former Member
0 Kudos

Hi Madhu,


The @Prompt(**) = 'ALL'  will return data for all values if user enters ALL as input to the prompt.

As you can see there is a or condition,

so when user enters 'ALL' , the equation will become 'ALL'='ALL' and hence true thereby making hte where condition redundant.

Thanks,
Vinay

Former Member
0 Kudos

Hello Vinay,

If the condition is redundant, try removing it and test the output.

Thanks,

Madhumitha

Former Member
0 Kudos

Madhu,

But that condition becomes  redundant only when 'ALL' entered by user.

It is one of the requirements that User should be able to choose one of hte values or All values.

and hence that condition is required.

Thanks,
Vinay

Former Member
0 Kudos

Vinay,

If my understanding is not wrong, if there are two @prompts, then i guess it will prompt the user two times. Just for testing purpose, remove this condition and validate the behavior.

Thanks,

Madhumitha