Hold group constant across time?

Hi eveyone,

My users asked me a question today that I wasn't sure of so I was hoping you guys could give me some advice.

Is it possible to make the cube hold a group constant across time? For example, I want to take all of the customers that were here in December 2006 and see how they performed during the year. Is that something I can do in a cube? Obviously, I could create a seperate cube with only those customers in it, but I'd like to avoid that if possible.

Thanks!

Yes, you should be able to accomplish this. You'd likely want to create a named set in your Customers dimension that defines the group of customers in Dec 2006 (however you want to define that -- they had a value for a given measure, they have an attribute that defines their current status, etc.) Then, you (or your users) should be able to put that named set of customers into a pivot table and see measures across time just for that set of customers.

HTH,

Dave Fackler

|||

Thanks for your response! I guess I'm confused how to define the group of customers in 2006 and show them across time since 'time' is what defines them. If I limit the query in the where statement to those in 2006 I don't get data for anything but 2006. How do I write the query so it keeps 2006 customers and then shows those customers over time?

|||

You'll need to define the set of 2006 customers as a named set and then use that named set in your query...

For example, the following query shows sales to customers in the US for 2004 from the Adventure Works cube:

select

{[Measures].[Internet Sales Amount]} on columns,

[Customer].[Customer Geography].[United States].Children on rows

from

[Adventure Works]

where

([Date].[Calendar Year].[CY 2004])

That is likley similar to the query you are using -- it only shows 2004 sales and the customers associated with those sales since the where clause includes 2004 as a slicer. However, this slightly different query shows all sales over time for the customers who had sales in 2004:

with set [CY 2004 Customers] as

'filter([Customer].[Customer Geography].[United States].Children, ([Measures].[Internet Sales Amount], [Date].[Calendar Year].[CY 2004]) > 0)'

select

[Date].[Calendar Year].Members on columns,

[CY 2004 Customers] on rows

from

[Adventure Works]

where

([Measures].[Internet Sales Amount])

The key here is that the named set returns the set of US customers who had sales in 2004. This set is then used to define the rows in the subsequent query, but the query returns sales across time for that set of customers.

HTH,

Dave Fackler

|||

Ahh Dave, you're excellent!! And completely right.. the first query is exactly what I had. I know SQL pretty well so I knew that wasn't going to work but I'm a novice at MDX. So I guess the named set is basically a SQL subquery?

Your example makes sense but now how do I apply this as a named set in my cube through Visual Studio?

Thanks for you patience! I'm reading several MDX books as quickly as possible!

EDIT:

Well I spent my entire day at work reading up on MDX and trying to apply your example, but no luck I just can't seem to get the filter expression correct. I'm assuming when I apply this code to my cube as a named set I need to put the filter expression in the 'expression' box on the calculations tab? How do I modify the filter statement to show measures no matter how they're sliced?