Dynamically Adding Default Values to Dashbaord Prompt

Here is the way to automatically add the default dates of current month in the dashboard prompt.

undergiven code can be used for setting up the day first of the current month in the dasboard prompt automatically.

For this purpose, goto the prompt and under "Default to", select "SQL Results" and add following code;

For the first day of the current month add following code;

 

SELECT   CASE

            WHEN 1 = 0

            THEN

               "Office Visit"."Visit Dt"

            ELSE

               TIMESTAMPADD (SQL_TSI_DAY,

                             (DAYOFMONTH (CURRENT_DATE) * -1) + 1,

                             CURRENT_DATE)

         END

  FROM   Payments 

For the Last day of the current month add following code;

SELECT   CASE

            WHEN 1 = 0

            THEN

               "Office Visit"."Visit Dt"

            ELSE

               TIMESTAMPADD (

                  SQL_TSI_DAY,

                  -1,

                  TIMESTAMPADD (

                     SQL_TSI_MONTH,

                     1,

                     TIMESTAMPADD (

                        SQL_TSI_DAY,

                        1,

                        TIMESTAMPADD (SQL_TSI_DAY,

                                      DAYOFMONTH (CURRENT_DATE) * -1,

                                      CURRENT_DATE)

                     )

                  )

               )

         END

  FROM   Payments

In Between operation both cases can be used combined.