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

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.

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!

3051139

Thanks Team,

We don't have time dimension also.

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

Thanks,

Avin

[Deleted User]

@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.

Thomas Dodds

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

3051139

will add the code once testing is done on this friday

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