Greetings, I need to report information by State and I don't have the option
of modifying our cube to include a new dimension. I have State as an
attribute on my locations dimension and I was wondering if by using MDX
could I create a temporary virtual dimension for reporting on the client?
What other options do I have that don't involve changing the cube?

Thanks in advance
John

Hi John,

If you're still at AS 2000, you can try CreatePropertySet() - see this
earlier post:

http://groups.google.com/group/micro...olap/msg/a0549
165137c937f
Email: Deepak Puri
Groups: microsoft.public.sqlserver.olap

Here is a Foodmart Sales cube query that categorizes all descendants of
a product at the Brand Name level. Note that, although a Brand Name may
appear multiple times in the hierarchy, each is listed only once in the
results. This seems similar to what you are trying to do:

With Set BrandNames AS
'CreatePropertySet([Store],
Descendants([Product].CurrentMember,[Product].[Brand
Name]),
[Product].CurrentMember.Name)'
Select {[Unit Sales]} ON COLUMNS,
TopCount(BrandNames,10,[Measures].[Unit Sales]) ON ROWS
From Sales
Where [Product].[All Products].[Drink]


The "CreatePropertySet()" MDX function is not documented in BOL, but
here is a SQL Server mag article by Russ Whitney.
In it, limitations on the maximum set size are discussed:

http://www.sqlmag.com/Articles/Index...rticleID=16302
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***