Named Set Question

I want to create a named set that includes a list of some of our major customers. As part of the set, I also want to add a calculated member that represents a subtotal of customers. For example:

{[Customer].[Customer Hierarchy].[Store].&[Smith Stores],[Customer].[Customer Hierarchy].[Store].&[Williams Stores],[Customer].[Customer Hierarchy].[Store].&[Lion Stores]}

represents a named set of three customers. In the named set, I want to add Smith Stores and Williams Stores together to create a subtotal called Midwest Group. So, my revised set would look like:

{[Customer].[Customer Hierarchy].[Store].&[Smith Stores],[Customer].[Customer Hierarchy].[Store].&[Williams Stores],[Customer].[Customer Hierarchy].[Store].&[Lion Stores], [Customer].[Customer Hierarchy].[Store].&[Midwest Group]}.

First, can I create a calculation such as [Customer].[Customer Hierarchy].[Store].&[Smith Stores] + [Customer].[Customer Hierarchy].[Store].&[Williams Stores] AS 'Midwest Group' and add it directly to the named set. Or can I create this as a calculated member and add this member to the named set.

Is what I would like to do possible with named sets?

Thank you.

David

You will have to first create calculated member and then add it to the set. Alternative approach is to add new attribute to the Customer dimension with members Major/Minor. This way you will be able to get your Major customer by slicing on that attribute. You also will be able to do any grouping you want. This is more scalable approach once your company grows and you have more than 3 major customers |||

Mosha, when adding the calculated member to the named set, are there any restrictions. For example, does the calculated member need to be in the same hierarchy as the other members of the set. Also, are there any syntax differences when adding the calculated member. For example, is it treated like any other tuple and I would just separate it from the other members with a comma? Thank you.

David

|||There are no special restrictions. The calculated member will have to be from the same hierarchy as other members, but this is not special requirements for the calculated members, all tuples in the set must have same dimensionality (hierarchility). Syntax is the same - if you enumerate the members, than use comma as separator.|||

Mosha, Thank you for the answer. One problem remains. I get the named set working correctly when I run an MDX query in Management Studio. I have the named set appearing in the row axis and everything displays correctly. When I use the same named set in an Excel 2007 pivot table, I get an error message -- "A set has been encountered that cannot contain calculated members". I do have "show calculated members from OLAP Server" box checked in Pivot Table Options in Excel. Is this a bug with Excel 2007?

David

|||I am not sure what exactly you do in Excel - but it is possible that it is limitation of either Excel or AS (the error message is actually from AS, not from Excel).|||

Mosha,

Is it unusual that an MDX query would work correctly in SQL Server Management Studio but yield an error in a front end client?

David

|||Well, obviously the query that you wrote in Management Studio and the query that Excel generated are different queries.