In the last article we looked at alternatives to returning the system date in the report queries. This article looks at the report and alternatives to using system date functions in report calculations
- Problem Overview
- Creating a Query that Returns Current Data
- The Easiest Method
- The Max Date Method
- Flagging Today’s Date in the Time Dimension
- Creating Our Own System Date Function
- Replacing use of Current Date within a Web Intelligence Report
- Method 1. Formula to use when using the Max Date method to determine current date
- Method 2. Return the Is Today and Is Current Week objects in the query
- Method 3: Add an extra column to the returning data set that returns the value of the custom system date function
- Other Refinements
Replacing use of Current Date within a Web Intelligence Report
The methods in the previous article allow us to control what data is returned by a query when the filtering for ‘current’ data. However there are still some situations where the report also needs to know what the current date is. The example at the start of the article was analysing the dates returned by a query to determine if the dates fell in the current week or previous week and then group the data appropriately in the crosstab. The following Web Intelligence formula was used for this,
=If [Date] < = LastDayOfWeek(CurrentDate()) And [Date] >= RelativeDate(LastDayOfWeek(CurrentDate());-6) Then "This Week" Else "Last Week"
CurrentDate() function here returns the system date according to the Web Intelligence server however what we really want is the system date according to the data set returned. Again there is more than one way of resolving this and here we’ll look at a couple of these techniques.
Method 1. Formula to use when using the Max Date method to determine current date
If you are planning on using the max date method then as far as I am aware it isn’t then possible to use the maximum date in the data set to determine whether the data is This Week or Last Week. At first sight the following formula should work,
=If [Date] < = LastDayOfWeek(Max([Date])) And [Date] > RelativeDate(LastDayOfWeek(Max([Date]));-7) Then "This Week" Else "Last Week"
max() aggregation formula can return multiple values depending on where it is used (e.g. report body versus footer). Even if we use calculation contexts such as
Max([Date]) In Report we still get
#MULTIVALUE errors when trying to group by this formula.
To resolve this you would need to push the calculation down to the database and have the query return an extra column that is the maximum date in the data set. Then we can use the technique discussed in method 3 below in the report.
Method 2. Return the Is Today and Is Current Week objects in the query
Use this method when the time dimension flags the date and week that are current. We edit our query and include the object Is Current Week and then in the report we can check this object to determine if the data falls in this week or last week. The Web Intelligence formula is quite simple and becomes,
=If [Is Current Week] = 'Y' Then "This Week" Else "Last Week"
If we create this formula as a variable we can then easily create the required crosstab,
Method 3: Add an extra column to the returning data set that returns the value of the custom system date function
Use this method when using the custom function solution in the data mart. Essentially we add
dbo.getMyDate() to the select clause and then in report we can use this value to determine if a date is the current date or is in the current week. We do this by first creating a Current Date object in the Time class of the universe this then allows use to create the query,
SELECT dbo.getMyDate(), DT.DATE, DP.DEPARTMENT_NAME, sum(FT.SALES_AMOUNT) FROM FACT_TRANSACTION AS FT INNER JOIN DIM_TIME AS DT ON FT.TIME_ID = DT.TIME_ID INNER JOIN DIM_PRODUCT AS DP ON FT.PRODUCT_ID = DP.PRODUCT_ID WHERE DT.DATE >= dateadd(wk, datediff(wk, 7, dbo.getMyDate()), 0) AND DT.DATE < = dateadd(wk, datediff(wk, 0, dbo.getMyDate()), 6) GROUP BY DT.DATE, DP.DEPARTMENT_NAME
Then we can create the following Web Intelligence formula that uses the Current Date object to determine whether the data falls either in This Week or Last Week,
=If [Date] < = LastDayOfWeek([Current Date]) And [Date] > RelativeDate(LastDayOfWeek([Current Date]);-7) Then "This Week" Else "Last Week"
From here we create this formula as a variable and build the crosstab is we did in method 2 above.
Creating a Current Date object that returns either the system date or the value of your current date function has further advantages over the other methods. Primarily we can refer to the Current Date object in other objects whenever we need to use system date functionality. For example if we want to create a predefined condition that filters for the last two weeks data we could write,
DT.DATE >= dateadd(wk, datediff(wk, 7, dbo.getMyDate()), 0) AND DT.DATE < = dateadd(wk, datediff(wk, 0, dbo.getMyDate()), 6)
However we would be better to refer to the Current Date object,
DIM_TIME.DATE >= dateadd(wk,datediff(wk,7,@Select(Time DimensionCurrent Date)),0) AND DIM_TIME.DATE < = dateadd(wk,datediff(wk,0,@Select(Time DimensionCurrent Date)),6)
As this then allows us to easily redefine the Current Date without having to rewrite all objects and conditions that use the current date. So for example if the schema name changes from ‘dbo’ to ‘myschema’ then all we need to do is update the Current Date object and all other objects and predefined conditions will pick up this change.
This article looked at why using system date can lead to difficulties in development and testing of a BI system when you can’t guarantee that you will always have the ‘latest’ data. To resolve this issue we have several options the easiest is to create the report to prompt for a date and then default to system date based on the user entering a ‘key’ date. Although easy to implement it does have some limitations and the table below summaries the advantages and limitations of each method. Another option was to assume that the maximum date in the data set was today’s date, a third option was to flag a row in the time dimension to indicate what is the current date. Lastly the final option was to create a custom function that returns a date set in a control table or if none set to return the system date.
The table below summarises the methods
|Report Prompt with Key Date||Easiest to Implement||Not user friendly
Can’t force report to always use system date â€“ user can change prompt
Need to set test date in all reports
Can’t guarantee consistency in test date value
Not easy to identify current date in report
|Max Date in Data Set||Not difficult to implement||Query performance may be adversely affected
Max date in dataset isn’t always most recent â€“ could be future date
Not possible to determine current date in report
|Flagged Date in Time Dimension||Current Date centrally defined
Can be manually changed
|The data mart load process needs to maintain the current date
Need a method of telling the data mart process what to use as a current date rather than system date
|Custom Function||Very flexible, virtually replaces functionality of system date functions
All reports will use same current date
Can also be used by other processes such as the data mart load process
Easy to identify current date in report
|Need to code and test custom function.|
So in conclusion the custom function is probably the best method to use, offering the greatest flexibility while keeping the extra development overhead to a minimum.