December 10, 2008 at 4:47 am
Hi
Is there a way to filter a Dimension on members on different hierarchie-levels?
Example
ProductArea
-> PurchaseHeadmanager
ProductCategory
-> ProductSubcategory
PurchaseManager
-> ProductNumber
ProductDescription
I need to populate a filtered set with this information based on the name of person
TSQL: WHERE PurchaseHeadmanager = "NAME" OR PurchaseManager = "NAME"
Thanks
Michael
December 10, 2008 at 5:10 am
I believe you are just going to want to create a named set or at least a set of dimension members that you would like to reference from your dimension hierarchy and these could be on different levels. Here would be a simply example from Adventure Works (where the hierarchy is Product Categories with the following levels -- Category-Subcategory-Product):
select {} on 0,
{[Product].[Product Categories].[Category].&[4],
[Product].[Product Categories].[Product].&[310]} on 1
from [adventure works]
I guess I am not sure how you are trying to generate this filter or use it, so that might be the next area that you will need to take a look at, but you can definitely get a list based off different levels.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
December 12, 2008 at 4:58 am
Hi, you can use the mdx descendants expression to retrieve children from multiple levels in a cube dimension
December 13, 2008 at 1:21 am
Thanks both of you
I will try it next week.
/Michael
December 13, 2008 at 5:18 am
Sounds good. In my example I didn't actually reference a named set, but it is a set of members from the hierarchy at different levels. Guess I am not sure how you would use descendants unless that is also used in a named set perhaps. You can create the set on the fly in your query or within the cube. In the query it would look like this:
with set [MySet] as {[Product].[Product Categories].[Category].&[4],
[Product].[Product Categories].[Product].&[310]}
select {} on 0,
[MySet] on 1
from [adventure works]
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
January 21, 2009 at 1:31 pm
Hi
Thanks for your input, it almost worked with
union (Descentants(PurchaseHeadmanager), Descendants(PurchaseManager))
ProductArea
-> PurchaseHeadmanager
ProductCategory
-> ProductSubcategory
PurchaseManager
-> ProductNumber
ProductDescription
My problem is still that the same Purchaseheadmanager and the Purchasemanager can exists multiple times in the same level
Kalle Andersson
- Fruit
- Apples
- Stina Andersson
- Golden delicious
- Ingrid Marie
- Anders Karlsson
- Royal Gala
- Orange
-Stina Andersson
- Brand 1
- Anders Karsson
- Brand 2
Stina Andersson
- Bread
- Toast
- Hans Hansson
- Fiber
- Bengt Bengtsson
- Regular White
If I make a request for Stina Andersson, I want this
Stina Andersson
- Bread
- Toast
- Hans Hansson
- Fiber
- Bengt Bengtsson
- Regular White
- Apples
- Stina Andersson
- Golden delicious
- Ingrid Marie
- Orange
-Stina Andersson
- Brand 1
My Union took the the first Stina Andersson i the two levels and made a Union, skipping the orange part i this example.
Hope that this example can bring some light to the problem to were Iam thinking wrong.
regards
Michael
January 21, 2009 at 3:47 pm
Hi
I solved my problem with filters in my descendants funcion calls
union (Descentants(Filter(PurchaseHeadmanager)), Descendants(Filter(PurchaseManager)))
Before I applied the Descendantsfunction on the dimensionmember, when I actually needed a set of members because the same member name could exist mutiple times.
Thanks for your time, I will be back 😉
/Michael
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply