Delivery a report last business day of month

3051139 · Sep 21, 2018 9:00AM

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 · Sep 21, 2018 9:12AM

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 · Sep 21, 2018 9:16AM

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 · Sep 23, 2018 3:02AM

Thanks Team,

We don't have time dimension also.

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

Thanks,

Avin

[Deleted User] · Sep 23, 2018 5:46AM

@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 · Sep 24, 2018 8:21AM

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

3051139 · Sep 26, 2018 4:23PM

will add the code once testing is done on this friday

3051139 · Sep 28, 2018 12:51PM

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

MFA for Oracle Community

Oracle Analytics Cloud and Server

Categories

Delivery a report last business day of month

Answers