Microsoft KB Archive/301537

= How To Obtain the Current Day with a MDX Query or Expression in SQL Server 2000 Analysis Services =

Article ID: 301537

Article Last Modified on 11/21/2006

-

APPLIES TO


 * Microsoft SQL Server 2000 Analysis Services
 * Microsoft SQL Server 2000 Analysis Services
 * Microsoft SQL Server 2000 Analysis Services

-



This article was previously published under Q301537





IN THIS TASK
SUMMARY
 * Create a MDX Query in a MDX Sample Application
 * Create a MDX Expression in a Cube

REFERENCES



SUMMARY
For a number of reasons, you may want to create a Calculated Member or a Multidimensional expression (MDX) that returns the current date. This article explains how to use the Microsoft Visual Basic NOW function in a MDX statement and a MDX expression to return the current date. The article also explains how to use the Microsoft Visual Basic DAY, MONTH, and YEAR functions in a MDX expression to return the Day, Month, and Year values of a date.

OLAP Services and Analysis Services both support many of the functions in the Microsoft Visual Basic for Applications Expression Services library. The first part of this article uses the MDX sample application to test the formulas that are later used to create calculated measures.

back to the top

Create a MDX Query in a MDX Sample Application
 On the Taskbar, click Start. Point to Programs, point to Microsoft SQL Server, point to Analysis Services, and then click MDX Sample Application. In the MDX Sample Application Connection dialog box, type the Server name of the Analysis Server to which you want to connect. In the Database drop-down list box, double-click the FoodMart 2000 database.  Paste the following MDX query into the query window: -- The First Calculated member is the value of NOW WITH MEMBER [Measures].[Full Date] as 'NOW' -- The Second Calculated Member is the Day part of the first calculated member. MEMBER [Measures].[What Day] as 'DAY([Full Date])' -- The Third Calculated Member is the Month part of the first calculated member. MEMBER [Measures].[What Month] as 'MONTH([Full Date])' -- The Fourth Calculated Member is the Year part of the first calculated member. Member [Measures].[What Year] as 'YEAR([Full Date])' SELECT {[Full Date],[What Day],[What Month],[What Year]} ON COLUMNS FROM Sales  Execute the query by clicking the green play button. Note the values that display in the query grid. Here is what you see:

</li> Close the MDX Sample Application.</li></ol>

back to the top

Create a MDX Expression in a Cube
The MDX Sample application has been used to prototype four calculated members that will be added to the Sales cube of the FoodMart 2000 database. <ol> Open Analysis Manager by clicking Start on the Taskbar. Point to Programs, point to Microsoft SQL Server, point to Analysis Services, and then click Analysis Manager.</li> Expand the folder for your server name that appears in the tree pane.</li> Expand the FoodMart 2000 database.</li> Expand the Cubes folder.</li> Right-click the Sales cube, and then click Edit.</li> In the Cube Editor window that appears, scroll down in the tree pane of objects until you see the &quot;Calculated Member&quot; heading.</li> Right-click the Calculated Member, and then click New Calculated Member.</li> In the Calculated Member Builder window, change the Member Name to Full Date, and then in the Value Expression box, add the following:

NOW

</li> Click OK.</li> A new calculated member is listed in the tree pane that is named Full Date. Click Full Date.</li> In the Properties pane click Advanced, and then change the value of the Format String property to Short Date.</li>  Add the following calculated members by using steps 5 through 11: <pre class="fixed_text">Parent Dimension: Measures Name           : The Day Value          : DAY([Full Date]) Format String  : #

<pre class="fixed_text">Parent Dimension: Measures Name           : The Month Value          : MONTH([Full Date]) Format String  : #

<pre class="fixed_text">Parent Dimension: Measures Name           : The Year Value          : YEAR([Full Date]) Format String  : # </li> Click Data in the bottom of the Cube Editor window, and then browse the cube data. Scroll all the way to the right and note the values in the last four (4) columns. The data looks similar to:

</li></ol>

back to the top