Data Model Parameters in BI EE (11.1.1.9)

User_87T3O

Hi,

New here. I'm trying to configure a Data Model to use a Parameter. I want a user to be able to select an Order By condition.

The SQL below works fine.

select

"ALL_TABLES"."TABLE_NAME" as "TABLE_NAME",

"ALL_TABLES"."TABLESPACE_NAME" as "TABLESPACE_NAME",

CAST("ALL_TABLES"."NUM_ROWS" AS INT) as "NUM_ROWS",

from "FUSION"."ALL_TABLES" "ALL_TABLES" 

where "NUM_ROWS" >= 0

order by "NUM_ROWS" DESC

If I set a parameter p_order_by to be a String and then type NUM_ROWS into the resulting parameter box the Order By clause fails.

select

"ALL_TABLES"."TABLE_NAME" as "TABLE_NAME",

"ALL_TABLES"."TABLESPACE_NAME" as "TABLESPACE_NAME",

CAST("ALL_TABLES"."NUM_ROWS" AS INT) as "NUM_ROWS",

from "FUSION"."ALL_TABLES" "ALL_TABLES" 

where "NUM_ROWS" >= 0

order by :p_order_by DESC

See attached images.

no_order_by.png

order_by.png

Appreciate any comments or suggestions to understand what needs to happen so that the SQL is parsed correctly where using parameters.

Thanks.