7 Replies Latest reply on Sep 28, 2018 5:32 PM by 3051139

    Delivery a report last business day of month

    3051139

      Hi Team,

      I want to schedule a  report, That report should deliver last working day of every month.

      When i'm choosing a schedule options while creating an agent, i'm seeing few options like - last , second e.t.c  day of week in a month.

       

      Can we schedule a report for last business/working day in month.

      Please find the attached screen shot.

       

      Thanks,

      Avin

        • 1. Re: Delivery a report last business day of month
          Robert Angel

          Hi,

           

           

          not as standard functionality no.

           

          Think about the logic of this, with bank holidays, etc etc - it would be difficult to say the least.

           

           

          But if you have the inclination you could build a very brief subject area that has a time dimension with 'LastWorkingDayFlag' and values to be set to 'Y' for all the appropriate days.

           

          Then use this as the conditional analysis that would be used to trigger your agent send, with your agent running with the condition every day.

          • 2. Re: Delivery a report last business day of month
            Thomas Dodds

            Amazing things can be done wil the formation of a proper calendar dimension ... FIRST thing I look for at EVERY client I go to.  If they don't have one - they get one on day one!

            • 3. Re: Delivery a report last business day of month
              3051139

              Thanks Team,

              We don't have time dimension also.

              I fixed this issue, by adding lot of custom code.

               

               

              Thanks,

              Avin

              • 4. Re: Delivery a report last business day of month
                Christian Berg

                3051139 that "answer" will not help any forum user to understand anything at all.

                 

                Post the actual solution and then it can count as a correct answer.

                • 5. Re: Delivery a report last business day of month
                  Thomas Dodds

                  Seriously ... time to build one ... it's foundational

                  • 6. Re: Delivery a report last business day of month
                    3051139

                    will add the code once testing is done on this friday

                    • 7. Re: Delivery a report last business day of month
                      3051139

                      Hi Team,

                      First create a "date" column in RPD to caculate the last date of month by using below formula

                       

                      ( cast( TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) as date))

                       

                      Now we have last day of month,

                      Our Aim is to create a agent , that should execute every month last working day

                      Now to calculate the Last business day  create a "Test" report by taking the CURRENT_DATE as column and pass below formula as a filter

                       

                      CURRENT_DATE IN (case when  DayName("Date")  = 'Sun' then ("Date")  - 2  when DayName("Date")  = 'Sat' Then ("Date")  -1    else  ("Date") end)

                       

                      Now create a agent, that should execute daily

                      and under condition pass this test report rowcount > 0

                      add a delivery content.

                       

                      Agent will execute daily, but pass the condition only on last business day, then delivery the content

                       

                      *******************

                       

                      Second Method, with out creating a column in RPD:

                       

                      Create a Report with the Column CURRENT_DATE,

                      Pass the below condition as a filter

                       

                      CURRENT_DATE IN (case when

                      DayName(

                      ( TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1,

                      TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) )) = 'Sun'

                      then

                      (TimestampAdd(SQL_TSI_DAY,-2,(( cast( TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) as date)))))

                      when

                      DayName(

                      ( TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1,

                      TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) )) = 'Sat'

                      Then

                      (TimestampAdd(SQL_TSI_DAY,-1,(( cast( TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) as date)))))

                      else

                      (

                      ( TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1,

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

                      end)

                       

                      Save this report,

                      Create a Agent by passing this report as condition >0

                      add the delivery content.

                      It will delivery the report every month last business day.

                       

                      Hope this will helpful

                       

                      Thanks,

                      Avin

                      1 person found this helpful