Kasper de Jonge PowerPivot Blog

On of the benefits of moving to Office 2010 (besides of course getting access to PowerPivot ) is the existence of slicers. You can use slicers for all the Pivottable inside Excel 2010, not only PowerPivot but also a regular Pivottable on Excel data or based on SSAS Molap.

In this example I have connected to a Molap database and created a Pivottable to show the sales per Currency type:

Now I want this report to be dynamic per Year. We can use a slicer to slice the data per year.

Go to the ribbon, go to Pivottable tools, Options, Insert Slicer:

This will open a window that let’s you select the attribute you want to use in your slicer :

In our case we want to use CalendarYear. This will give us the following report on top of Molap:

Very cool

There has been a lot of talk in MS BI land about the upcoming release of the SSAS team with Denali and what that means to UDM during the SQLPass convention this week and also in the blogsphere, most notably is the blog post by Chris Webb

T.K. Anand, Principal Lead PM from the team,  has made a blog post that answer the questions around what the roadmap is for SQL Server “Denali” and beyond, you can  read it right here.

If you have questions or comments please post them there. The MS BI team will be reading them.

I have a report where the client wants to exclude all but 3 members below a certain level from a hierarchy.

The easiest way to achieve this is to exclude them when designing the query in BIDS, but this is much work and not easy to maintain, especially when you want to use this in multiple reports. And when this hierarchy is changed it will automatically be included even when we don’t want it.

I thendecided to create a set that includes all the members that need to be excluded, so i can use a NOT IN while designing the query. After browsing the internet i found How to exclude a certain member from a MDX call that gets all descendants of a member at a higher level. Exactly what i needed. It works like this:

Select all the members from the level i need from the hierarchy by using DESCENDANTS :

DESCENDANTS([Product].[Product Categories].[Category].&[4],
[Product].[Product Categories].[Product],
SELF)

This will select all the Products from the Category “Accessories”,

Next we want to Exclude the Products we still want in our set, we do this by using the Except function, this function lets you distract the values from one set from another:

Except(
DESCENDANTS([Product].[Product Categories].[Category].&[4],
[Product].[Product Categories].[Product],
SELF)
,  { [Product].[Product Categories].[Product].&[483]
,[Product].[Product Categories].[Product].&[486]
}
)

The { } around the products make it a set so they can be distracted using Except. This statement can be added as a Set to the Cube, and thus used in the Query to build the dataset by using NOT IN and selecting the SET.

To test this I made the following MDX statement to execute at the Query analyzer:

SELECT
{} ON COLUMNS,
{
Except(
DESCENDANTS([Product].[Product Categories].[Category].&[4],
[Product].[Product Categories].[Product],
SELF)
,{[Product].[Product Categories].[Product].&[483]
,[Product].[Product Categories].[Product].&[486]})
}
ON ROWS
from [Adventure Works]