?
Solved

MDX with multiple dimensions potential named set

Posted on 2011-02-23
7
Medium Priority
?
1,264 Views
Last Modified: 2016-02-15
Hi all,

i want to create a named set where i have a set of dimensions which i want to include but a set of dimensions i want to exclude at the same time when applied to a measure.

So for example, If i had codes 1-10 as one dimension (CodeGroup) , and product codes 1-1000 for another dimension (ProductCode)
And we are dealing with sales dollars:

I want to be able to create a named set, which includes CodeGroup 1,
                                                                                         CodeGroup 2 but not ProductCode 221

how could i do this in a named set. and how would i code to exlude a product code for a given codegroup (as per above)

I think i have to use exists, and i'm guessing something like a join that i need to make to make the named set work.
Is there something like not exists?
0
Comment
Question by:adammet04
  • 5
  • 2
7 Comments
 
LVL 10

Expert Comment

by:itcouple
ID: 34967775
Hi

I will try to send you an example in several hours time.... When you create a set you can list the dimension members you want and exclude the ones you don't want you combine different dimensionality members using crossjoin (*) you don't use join because relationships between them are defined in 'usage tab' in SSAS cube designer (if you use SSAS).

Regards
Emil
0
 
LVL 10

Accepted Solution

by:
itcouple earned 2000 total points
ID: 34969028
Hi

I had quick go with that (I'm not an expert in that) and so far I able to create a set which excludes the values you want.

See below
 
with set test as (
[Dim1].[CodeGroup].[CodeGroup],
	Except([Dim2].[ProductCode].[ProductCode],[Dim2].[ProductCode].&[ProductCode221])
)

SELECT  {[Measures].[Value]} on 0,
test on 1
FROM    [CubeName]

Open in new window


This will show the entire set so in select you can replace
test on 1
with
extract(test,[Dim1].[CodeGroup]) on 1

I'm not sure what measures will return (valeus) and how it will exactly behave but I hope that will bring it at least a step closer to what you are trying to achieve.

Regards
Emil
0
 
LVL 3

Author Comment

by:adammet04
ID: 34992868
Thanks for this I will test. Ill also post what i ended up with as well, but i think the except keyword might be what i want. However this is a straight MDX query rather than generating a named set. Maybe that is all that is needed. I will see how i go.

Sorry for tardiness of reply.

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:adammet04
ID: 34992910
apologies it is a set.. my bad.
0
 
LVL 3

Author Comment

by:adammet04
ID: 34993554
Unfortunatly the code fragment did not work.

this is the message it returns.
"Two sets specified in the  function have different dimensionality."

I think i need to use cross join with except, but having trouble putting it together.

some more help please!
0
 
LVL 3

Author Comment

by:adammet04
ID: 34994258
Actually i fixed it. I had multiple tuples, but didn't enclose them properly.

Thankyou for your help!
0
 
LVL 3

Author Closing Comment

by:adammet04
ID: 34994260
Thankyou!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question