Thursday, February 9, 2012

How to Restrict the Values in OBIEE PROMTS

It is common practice to have year and month promts on the OBIEE reports.

One can set a default year and month by creating a dynamic variable in the RPD (or) using SQL Results. When users want to see the  year-2(2010) or  year-1(2011) one he has to scroll down. When he scrolls down he will see all the rows in the column.

For ex  : The below screenshot shows all the values  in the column





Issues: How to Restrict the values in the Promt to show current year, current year-1, current year-2 when user scrolls down

Solution:  In the following screen, under the "show" field select "SQLRESULTS"   use the following code
Code :
SELECT "Order Dates"."Order Calendar Year" FROM "BOM-RNA Analysis" where "Order Dates"."Order Calendar Year" =YEAR(CURRENT_DATE)
union
 SELECT "Order Dates"."Order Calendar Year"-1 FROM "BOM-RNA Analysis" where "Order Dates"."Order Calendar Year" =YEAR(CURRENT_DATE)
union
SELECT  "Order Dates"."Order Calendar Year"-2 FROM "BOM-RNA Analysis" where "Order Dates"."Order Calendar Year" =YEAR(CURRENT_DATE)
In the code "Order Dates"."Order Calendar Year" is table.column name and "BOM-RNA Analysis" is Subject Area.
In the screen shot I am using server variable in "DEFAULT TO" field and respective presentation variable.




 OUTPUT : See the screen shot below. Now I am only seeing 2012, 2011, 2010













Thanks
Venkat

No comments:

Post a Comment