We have a number of Crystal Reports 2011 reports that are currently automatically executed and exported by a custom .Net Winforms scheduling application. These reports all use SQL Commands with hand-crafted SQL to get the best performance against a third party schema that we cannot modify.
A number of the reports include date parameters that are usually set to something like the previous business day, or start of last month etc, and the values to be applied to these parameters when the reports are executed are calculated by the scheduling application against the current date. These parameters are SQL level parameters, in order to reduce the result set coming back from the DB.
We now have to migrate the Crystal Reports reporting onto Business Objects. In order to maintain the performance of the report SQL, we want to re-use the Freehand SQL from the Crystal Reports in Web Intelligence reports, or in Derived Tables in a minimal Universe - we don't want to create a Universe against the target schema.
We need to be able to automatically schedule the Webi reports on a daily basis, and have the date-based parameters calculated and passed in, with the results exported in Excel and PDF format, as they are today (the users will not be getting access via a web interface - they will just pick up the exports from a network drive). We ideally want to keep parameters so that reports could be run adhoc for different date ranges.
What I want to know is, how should this parameterisation and automatic default value calculation best be supported in the BI technology? I cannot seem to get default values working with the @Prompt() function, and when the reports are refreshed daily, there won't be a user there to respond to a prompt anyway. What would be the best approach here?
I eventually managed to determine a way of doing what I needed.
Using the BO4.2 Information Design Tool, I performed the following steps to support Start Of Last Month and End Of Last Month dynamic report parameters:
1) In the Data Foundation, create a new Parameter called StartOfLastMonth, ticking the "Prompt to users" checkbox (this allows manual overriding of the default values if required).
2) Tick the "Set default values" checkbox at the bottom of the Parameter Options tab, and click the "Formula" radio button.
3) Click the ellipsis at the right of the Formula bar, and entered the Expression:
RelativeDate(RelativeDate(RelativeDate(CurrentDate();-DayNumberOfMonth(CurrentDate()));1);-1;MonthPeriod)
4) Create a Derived Table based on my Crystal Report Command SQL, and reference this Parameter in the Where clause:
where l.datetime >= @Prompt(StartOfLastMonth)
5) Add my report's Derived Table to the Business Layer and Publish the Business Layer to a Repository.
6) Create a new Report in Webi, against the published Universe, and use the Derived Table in the report Query. When the report is executed, the Start Date prompt pops up with the dynamically calculated date equal to the start of the previous month. This can be manually changed to any other date, or left as is. When the report is scheduled, this dynamic date value is used by default.