PDA

View Full Version : CDE Pass dimension as parameter



Raja
12-03-2014, 07:00 AM
In sql select statement for CDE dashboard , we have an option to pass numeric-field as parameter :

SELECT
DISTINCT brand_name AS "Brand Name",
CASE
WHEN ${param_Measure}='unit_sales' THEN sum(unit_sales)
ELSE sum(store_sales)

END AS "Sales"

FROM product p
INNER JOIN sales_fact_1997 sf7
ON p.product_id=sf7.product_id
INNER JOIN time_by_day t
ON sf7.time_id=t.time_id
WHERE
t.the_date>='2012-01-01' AND t.the_date<='2012-01-07'

GROUP BY "Brand Name"

ORDER BY "Sales" DESC

But I tried passing Non-numeric field as parameter and got error processing component . I tried the below code :

select
case when ${Dimension} = 'gender_code'
then gender_code
when ${Dimension} = 'Grade_code'
then Grade_code
end as "dimension",
sum(leave_count) as leave_count
from hcm_leave_itf_tb
group by ${Dimension}

Kindly help me to resolve the issue.

pamval
12-22-2014, 11:56 AM
the group by clause will certainly not work like you have it - We're using prepared statements to inject the parameters into the query and they don't allow for that.