MDX can be extended with Visual Basic functions like the FORMAT() and NOW() functions to dynamically generate the current year, month or date member. For example, it can be used as part of a named set to generate current year budget and actuals. It can also be used as part of a filter in a Performance Point dashboard.
First, a little review of VB NOW() and FORMAT() function:
NOW()
The NOW() VB function returns the current system date and time. It is similar to the T-SQL GETDATE() function.
FORMAT()
The FORMAT() VB function returns a string formatted according to instructions contained in a format String expression.
Source: http://msdn.microsoft.com/en-us/library/59bz1f0h(v=vs.90).aspx
There is an extensive list of characters you can use to create your own date and time formats. You can see the complete list here: http://msdn.microsoft.com/en-us/library/73ctwf33(v=vs.90).aspx
For example, you can extract the year portion of your date in the following formats:
y: Displays the year number (0-9) without leading zeros. Use %y if this is the only character in your user-defined numeric format.
Example: FORMAT(NOW(), “y”)
Result: 1 (for any date in 2011)
yy: Displays the year in two-digit numeric format with a leading zero, if applicable.
Example: FORMAT(NOW(), “yy”)
Result: 11 (for any date in 2011)
yyy or yyyy: Displays the year in four-digit numeric format.
Example: FORMAT(NOW(), “yyy”) or FORMAT(NOW(),”yyyy”)
Result: 2011 (for any date in 2011)
FORMAT() and NOW() with MDX
The following MDX example uses the Adventure Works 2008R2 Analysis Services OLAP cube. Suppose we need to return all Internet Sales for the year 2011. The MDX script would look something like this:
SELECT [Measures].[Internet Sales Amount] ON COLUMNS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2011]
This works just fine and will return the aggregated sales of 2011. (Note: the Adventure Works 2008R2 does not contain sample sales data for 2011, result will be an Empty dataset).
But what if instead of a specific year, you are asked to return the Internet Sales amount for the current year? You have 3 options:
- On January 1st at 12:01 AM every year you need to change your MDX script to the new current year.
CONS: You might be passed out from celebrating New Year’s Eve and most probably you will not get to it until a few days out into the new current year. - You could use some MDX functions like LasNonEmpty and LastChild to figure out the last amounts recorded by the most recent date.
CONS: You may have forecast data or simply bad data with transactions occurring on dates out into the future. (Trust me, I’ve seen it) - You can build a dynamic MDX script that will return the current year based on the system datetime.
CONS: It may be too sexy for your cube.
The trick is to construct the Date dimension member using the STRTOMEMBER MDX function. The STRTOMEMBER MDX function stands for “String to Member” and as the name implies, it will convert a string to a dimension member. In this case, we want to generate the [Date].[Calendar Year].&[2011] dimension member from a string using the Year portion of the system datetime.
The dynamic string will look like this:
StrToMember(“[Date].[Calendar Year].&[“+FORMAT(NOW(), “yyyy”)+”]”)
Now we can use it in our MDX script:
SELECT [Measures].[Internet Sales Amount] ON COLUMNS
FROM [Adventure Works]
WHERE StrToMember(“[Date].[Calendar Year].&[“+Format(now(), “yyyy”)+”]”)
The same applies if you want to select or filter data for the current month. In the following example I’m be using the Month level from the Calendar Hierarchy and specifying the month of July (7) for the year 2011:
SELECT [Measures].[Internet Sales Amount] ON COLUMNS
FROM [Adventure Works]
WHERE [Date].[Calendar].[Month].&[2011]&[7]
We can generate the current month member as follows:
SELECT [Measures].[Internet Sales Amount] ON COLUMNS FROM [Adventure Works] WHERE StrToMember(“[Date].[Calendar].[Month].&[“+Format(now(), “yyyy”)+”]&[“+Format(now(), “M”)+”]”)
Note that to specify the Month portion we use CAPITAL letter M because it is case-sensitive. Smallcase letter m stands for minute. The complete list of characters can be viewed here: http://msdn.microsoft.com/en-us/library/73ctwf33(v=vs.90).aspx
Filed under: Business Intelligence, How to, MDX, Performance Point, SQLServerPedia Syndication | Tagged: #sqlserver, 2005, 2008, analysis services, business intelligence, mdx, R2, SQL, ssas | 1 Comment »
SQL Joe's Blog 