on 03-05-2013 2:57 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
81 | |
24 | |
11 | |
9 | |
7 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.