I have a report( Actual Report) which we need to schedule on every month for example Apr 1, May 1, June 1.
we are using Infoview for scheduling the report. for this report we have prompts like monitor start date and monitor end date.
Coming to scheduling, we have given the below criteria.
Instant Title: Report Name.
Recurrence: Monthly.
Month(N) = 12.
Start Date/Time: 01-Apr-2010.
End Date/Time: 01-Apr-2020.
Prompts: Start End: 05-Jun-2009.
End Date: 13-July-2009.
Formats and Destinations: we have given excel sheet and File LOcation.
In the File Location: we have given the path of the folder.
Destination Options we have given like user name and password.
after that we clicked schedule. but here we are getting the problem. we are getting the message like Failed.
I am sending the screenshot of that history. please find it and reply back.
Please attach a screenshot, or (better yet), paste the text of the error message.
Regardless of the error you’re getting, in order to have the report run dynamically, each time geting data for the most recent month, you’ll have to modify it and remove the prompts.
Prompts are great for runnign reports with user interaction, but when it comes to scheduling, they’re not the right solution. What you need is an object that will dynamically return the first day of last month, and the first day of this month, and set a condition in the query, that your date be between them.
The syntax for these objects, in MS-SQL is:
Last month:
DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0, GETDATE()),0))
this month:
DATEADD(mm, DATEDIFF(mm,0, GETDATE()),0)
For a more dynamic and interactive option, combining dynamic dates with user promps, see my solution here
One approach you can take is to create a set up dynamic date filters. For example, if you wanted the past 12 months, use the code ethan1701 provided and build a filter in the universe, call it something like Last 12 months. We create about 30 different filters for each date field used as a filter. Some examples are: Yesterday, Prior week, Prior Month, Prior Quarter, MonthToDate, YearToDate… etc. Once these have all been created for one date field, it is easy to copy them for another date changing them where appropriate.
(BOB member since 2004-05-05)
(BOB member since 2005-10-02)