Microsoft KB Archive/301537

Knowledge Base

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

1. On the Taskbar, click Start.
2. Point to Programs, point to Microsoft SQL Server, point to Analysis Services, and then click MDX Sample Application.
3. In the MDX Sample Application Connection dialog box, type the Server name of the Analysis Server to which you want to connect.
4. In the Database drop-down list box, double-click the FoodMart 2000 database.

5. 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
                       

6. Execute the query by clicking the green play button. Note the values that display in the query grid. Here is what you see:
   
   

   Full Date	What Day	What Month	What Year
   1:16:16 AM	19	9	2001

7. Close the MDX Sample Application.

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.

1. 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.
2. Expand the folder for your server name that appears in the tree pane.
3. Expand the FoodMart 2000 database.
4. Expand the Cubes folder.
5. Right-click the Sales cube, and then click Edit.
6. In the Cube Editor window that appears, scroll down in the tree pane of objects until you see the "Calculated Member" heading.
7. Right-click the Calculated Member, and then click New Calculated Member.
8. In the Calculated Member Builder window, change the Member Name to Full Date, and then in the Value Expression box, add the following:

   NOW()

9. Click OK.
10. A new calculated member is listed in the tree pane that is named Full Date. Click Full Date.
11. In the Properties pane click Advanced, and then change the value of the Format String property to Short Date.

12. Add the following calculated members by using steps 5 through 11:

    Parent Dimension: Measures
    Name            : The Day
    Value           : DAY([Full Date])
    Format String   : #
                        

    
    

    Parent Dimension: Measures
    Name            : The Month
    Value           : MONTH([Full Date])
    Format String   : #
                        

    
    

    Parent Dimension: Measures
    Name            : The Year
    Value           : YEAR([Full Date])
    Format String   : #
                        

13. 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:
    
    

    Country	Full Date	The Day	The Month	The Year
    All Countries	9/19/2001	19	9	2001
    USA	9/19/2001	19	9	2001

back to the top

REFERENCES

For more information regarding the use of External Function Libraries with Analysis Services, refer to the SQL Server 2000 Books Online topics "Registered Function Libraries" and "Creating and Using User-Defined Functions in MDX."

back to the top

Additional query words: MDX MEASURE NOW MONTH YEAR DAY OLAP External Function BIHowto

Keywords: kbhowtomaster kbgraphxlink KB301537

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.