All Column values issue in the Dashboard Prompt

Question

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

3193829 · Answer

All Column values passes null value to the report. For my requirement, i could not use it.So, altered prompt and removed all column values choice.

3193829 · Answer

Anyone?