Hi All,

I have to calculate a metric on the fly in the Dashboard Prompt for using in the report for further calculations. one of the dimension in the dashboard page has default set as '(All Column Values)'. I am getting null data/no data because of this defaulted value.

The formula i am using is below.

select case when '@{category}'='(All Column Values)' then sum("Paint"."sales") end FROM "Paint"   where ("Product"."group"='G' and "Today"."date"=date '@{to_date}' and "Category"."Cat" IN ('A','B','C'))

(trying to pull all values from category)

I need to write an else statement for this too after i get this correct.

like

select case when '@{category}'='(All Column Values)' then sum("Paint"."sales") end FROM "Paint"   where ("Product"."group"='G' and "Today"."date"=date '@{to_date}'  and "Category"."Cat" IN ('A','B','C'))

OR

select  sum("Paint"."sales") end FROM "Paint"   where "Product"."group"='G' and "Today"."date"=date '@{to_date}'  and "Category"."Cat"='@{category}'

Please suggest if i am missing anything here.

Thanks,

Kalpana