schedule period date different from send date?

[jacobsson_axel]

How can you achieve scheduling a webi report to take one month of data, from 18:th to 18:th each month, but run and send it at the 28:th each month?

Thank you in advance,

//Axel

[Former Member · Accepted solution]

Hi,

What formula did you try? and what error values error or values you are getting with that?

As you mentioned your data base is oracle

You can create below filter at the universe level to filter the data for 18th of last month to current month 18.

table.date between

to_date(extract(year from add_months(sysdate,-1))*10000 + extract(month from add_months(sysdate,-1))*100+18,'yyyymmdd')

and

to_date(extract(year from sysdate)*10000+ extract(month from sysdate)*100+18 ,'yyyymmdd')

based on todays date the result of above variable will be

18-APR-16 and 18-MAY-16

If you want the same formula to be worked based on the user given date then you need to replace the sysdate from above formula with @prompt(date) ...

[kohesco]

Hi,

Via RESTful you can write whatever your want to schedule

http://help.sap.com/saphelp_me150/helpdata/en/1a/ff69524d30617fe10000000a44538d/frameset.htm

[Former Member]

Hi,

Refer this link How to use new Dynamic Default Value for Universe (UNX) Prompts feature

if you want to create the filters at the report level itself. As Mahaboob mentioned and as per this link write formula to derive the current month and year and then convert it to date y appending 18 to it.

If you want to do it at the universe level,

if your database is SQL server then

convert(varchar,18+""+datepart(day,getdate())+""+datepart(year,getdate()),110)

This is an example and you may need to follow different syntax based on your database

[jacobsson_axel]

Thanks a ton, i´ve tried something similar to this but had problems with getting the datetime formatted correctly.
Especially hard to extract the right amount Days plus also making sure to get the results also from the last day, (18:th this month).

This works like a charm!

regards Axel

[mhmohammed]

Hi Axel,

You'll have to create the dyamic dates filter in the Universe and schedule the report to run on 28th of every month.

To create the filter to dynamically select dates from 18th of last month of 18th of current/selected month, we'll have to know the backend database, as the function to use and syntax differ by database. The idea is to create a date in text format and then use the formatdate or convert or cast function to convert and format that text as a date. Makes sense?

To create that Date in text, we can extract MonthNumber from the CurrentDate which we get using Sysdate (function for oracle), Getdate() (function for SQL Server or Sybase), Date is fixed as 18th, per your requirement, and finally we can extract YearNumber from the CurrentDate.

Thnaks,

Mahboob Mohammed

[jacobsson_axel]

Thank you for your anwer, its a single source Oracle database, I tried the below example from DIVYA, although with sysdate function but can´t seem to get the filter quite right..