Entering Dates in PPS Planning Assignments

In the recent PPS Planning projects that I’ve been involved in, the challenges have often been around subjects such as business rules, hence the often recurring theme of this blog. Recently the tables were turned though, as I was told by a user that they wanted to enter dates into a PPS assignment.

I was initially a bit concerned that the Excel add-in may not be able to deliver here – after all its great at capturing numbers, but knowing the rigid structure of the fact tables, I couldn’t see how it would manage to store a date. Then I remembered something from my VBA days many years ago – that is that Excel stores dates as a number from 30/12/1899, meaning in theory it should be possible to get dates working in PPS. Thankfully it is possible, as this post explains.

Excel Setup

The first step to get this working when designing your form template is to set the matrix to have a matrix style of ‘none’. If you don’t do this, then the built-in matrix styles will over-ride your formatting changes to the required cells. Speaking of formatting, the next step is to format the data entry cells that will contain dates, just using the standard Excel formatting window:

Once these few simple steps are done, then the assignment will behave just like any other. As the date is stored as a number, the numeric representation of the date will end up in the fact table just as any other piece of data.

Dates in Business Rules

Once the numbers are in the fact table, we need to convert them to dates to use them in business rules in some way. We can’t do much in PEL unfortunately, so the options are either NativeMDX or NativeSQL.

As Analysis Services can pickup some of the VBA functions, it’s possible to use the VBA DateAdd() function to convert the stored number back into a date. So in the example below, I’m using the DateAdd() function to convert the number to a date, before comparing the resulting date against another date using the VBA DateDiff() function:

WITH 
MEMBER    [Measures].[DateExample] AS VBA!DateAdd("d", [Measures].[Value], "30/12/1899")
MEMBER    [Measures].[DateDiff] AS VBA!DateDiff("d", [Measures].[DateExample], "01/07/1987")
SELECT    Descendants([Time].[Monthly].[Year].&[2008],,leaves) ON 0
FROM      [Strategic Planning]
WHERE     ([Account].[Profit and Loss].&[5010], [Measures].[DateDiff], [Entity].[Divisions].&[5003])

Although the above is just a simple example, it should give you the idea of the kind of calculations that can be performed in Analysis Services. It’s possible to use these functions via a NativeMDXScript or a NativeMDXQuery.

It’s a similar story with SQL, as it also has its own DateAdd() function, as shown in the simple select statement below:

SELECT    DateAdd(d, [Value], '30/12/1899')
FROM      dbo.[MG_Strategic Planning_MeasureGroup_default_partition]
WHERE     Scenario_memberid = 4 AND Account_MemberId = 5010

So it’s a shame that PEL can’t work with dates, but the fact that both the database engine and Analysis Services have a DateAdd function means that it’s possible to use dates for logic in both definition and procedural business rules.