Named Set with TOPCOUNT Not Working Correctly

I need to create reports that contain our top 15 customers at any given point in time. I created the following Named Set using the TopCount function in SSAS 2005:

TOPCOUNT([Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])

When I bring this set into the client (Excel 2007 pivot table), its shows our top 15 customers for all time. When I add a date filter for just 2007, it doesn't update the list to just show the top customers for 2007. It just keeps the same "all time" group.

When I create this named set in a browser query, it works correctly. That is, it shows the top 15 customers for 2007. Is there any way I can get the Named Set created in SSAS to show the correct data based on the filtered date in the client?

Here is the browser query:

WITH SET

[Top Performers]

AS

'TOPCOUNT([Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])'

SELECT

{[Measures].[Net Ship Dollars]} ON COLUMNS,

{[Top Performers]}ON ROWS

FROM Shipments

Where ([Date].[Calendar Year].&[2007])

Thank you.

David

Are you using AS 2005 SP2? This problem has been fixed there.|||

The problem here is because the named set in the MDX Script gets resolved when it is first parsed at which time all the other dimensions will be set to their default members (which is usually the "All" member). Using the EXISTING statement in your set definition should force it to be re-evaluated with the current context each time it is used.

eg

TOPCOUNT(EXISTING [Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])

|||I am using SP2 but still experiencing the problem.|||

Darren,

I tried this but the same problem is occuring. Apparently, the default member of the time hierarchy determines what customer members to return. When I changed the default member to 2007, the named set showed the top customers for 2007. I came across another post which confirmed this as a problem. Does anybody have any workarounds? Thyank you.

David

|||

Hi David,

It seems that I did not read your full question and missed some information. So, just want to clarify. Named sets in AS 2005 are static named sets, which means that they are calculated just once in the context of the current member awailable at that moment. They are not reevaluated during execution of the select statement. Daren is right in the fact that named sets created in the cube scope or session scope (using Create statement) are evaluated when the CREATE statement is executed, therefore TopCount is calculated in the context of the default member. Sets created in the query scope using With clause are evaluated right after the WHERE clause and therefore take current member defined by the where clause in concideration (this is why your query with WITH statement works as you expect and query with named set from CREATE statement return different results). There are it's own pros on having static named sets (mostly performance), but in your case you should try to avoid using TopCount in the named set, if you want the members on other axis to affect results of the topcount.

Irina

P.S.

>>> I came across another post which confirmed this as a problem.

Could you please point me to this thread?

|||

Irina Gorbach wrote:

Named sets in AS 2005 are static named sets, which means that they are calculated just once in the context of the current member awailable at that moment. They are not reevaluated during execution of the select statement.

Of course! sorry for leading you slightly astray. The EXISTING statement will work if you have a set in a calculation

eg

CREATE MEMBER CurrentCube.Measures.CustomersInTop10Pcnt AS COUNT(TOPPERCENT(EXISTING Customer.Customer.Members, 150 Measures.Sales)

This would re-evaluate the set used in the toppercent function based on the current slicing conditions, which is similar, but different.

|||so is this the solution then?

TOPCOUNT(EXISTING [Customer].[Customer Type-Name-Door Hierarchy].[Customer Name].members, 15, [Measures].[Net Ship Dollars])

or is there another way?

Also, how do you get it to sort correctly in excel, if you pull over the named set it just shows you the top X sorted by the SET Expression, whereas if you do an MDX query it sorts it by the Numeric Expression
sql