Knight's Microsoft® Business Intelligence 24-Hour Trainer: Leveraging Microsoft SQL Server® Integration, Analysis, and Reporting Services with Excel® and SharePoint®

152



LESSON 17 Using MDX

This will return all the quarters under the 2007 year, as shown in Figure 17-6. Notice

that this time we’re using a hierarchy to navigate to the next level.

The

.PrevMember navigation function will allow you to step sideways in the hier-

archy. For example, if you point to the 2007 member and state that you want

.PrevMember, SSAS will take a step to the side and return 2006. You can also do

the same thing with

.NextMember:

SELECT

[Measures].[Sales Amount] on 0,

[Due Date].[Calendar Date Drilldown].[Year].&[2007].PrevMember.Children on 1

FROM [AWDW2008R2Cube]

Often you want to analyze the last x number of years of sales. In T-SQL you might use the Getdate()

function and the

DateAdd function to achieve this goal. One MDX method is to point to the [All]

level of data and use the

.LastChild navigation function. This returns the last member at that level.

You can also use the

.Lag function to navigate through years by passing in (1) to go back a year and

(-1) to go forward a year, and so on, as shown in the following code:

SELECT

[Measures].[Sales Amount] on 0,

{

[Due Date].[Calendar Date Drilldown].[All].LastChild,

[Due Date].[Calendar Date Drilldown].[All].LastChild.Lag(1),

[Due Date].[Calendar Date Drilldown].[All].LastChild.Lag(2)

} on 1

FROM [AWDW2008R2Cube]

This code would return the data shown in Figure 17-7. Note that placing a

Non Empty statement in front of ...