Constrained prompt date issue

If you have a constrained prompt on the dashboard with date or date range also there as calendar that is defaulted to current date, then rest of the prompts show data only for that default date even if the date is cleared out. This is a known BI bug. As a work around, run the report once (you can hit cancel after running though), this will update the constrained prompt query behind the scenes.

Going back 12 months based on selected month

If users need capability to get back rolling 12 months of data based on one month selected in dashboard, the following code will do this:

"Month"."Month" between (CASE WHEN SUBSTRING('@{AsOfMonth}' FROM 8 FOR 2) = '12' THEN SUBSTRING('@{AsOfMonth}' FROM 1 FOR 4) ' / 01' ELSE SUBSTRING((CAST((CAST((SUBSTRING('@{AsOfMonth}' FROM 1 FOR 4) SUBSTRING ('@{AsOfMonth}' FROM 8 FOR 2)) AS INTEGER) - 99 ) AS CHAR(6))) FROM 1 FOR 4) ' / ' SUBSTRING((CAST((CAST((SUBSTRING('@{AsOfMonth}' FROM 1 FOR 4) SUBSTRING ('@{AsOfMonth}' FROM 8 FOR 2)) AS INTEGER) - 99 ) AS CHAR(6))) FROM 5 FOR 2) END) AND '@{AsOfMonth}'

This works if the month in database is in the format 'YYYY / MM'. I had earlier posted a way to do rolling 12 months based on date field in prompts and use TIMESTAMPDIFF function also. However that method has a disagvantage that it does not work with constrained prompts unless the date in database is exactly in the format of 'DD-MMM-YYYY'.