Help Required on MDX Query

[dhims]

Hi Gurus,

I am new to MDX and could not get right solution for my problem. I have a cube having 'team' dimension.

This cubes has a string data field where i need to enter project code as data.

'Project' it self is a dimension but not part of this cube. I have created and assigned the picklist in such a way that while entering project value in the cube, user gets dropdown of all the projects.

Now my challenge is to filter the 'project' based on the 'team' selection. I have team as an attribute to project.

So, basically my challenge is to filter project list by comparing the attribute of project members with the current team in context of the cube view.

Can I write some MDX for the same??

Please help.........

Dhims

[jim wood]

Have you tried recording an dynamic subset as your starting point? Once you have that there is enough material on here to take you from there,

Jim.

[George Regateiro]

If you have not looked the MDX primer is a great place to start. I think there is a copy on this forum somewhere, but the latest and greatest can be found using a google search. This is a great document for getting into MDX in TM1.

[AmbPin]

Hello

This is the sort of thing you want I think

Code: Select all

{FILTER( {TM1DRILLDOWNMEMBER( { [Product Code].[All Product Codes] }, ALL, RECURSIVE )}, [Product Code].[Business Unit] = "02")}

[tomok]

Regardless of what the others have posted, the short answer is that you can not do what you've asked, in the context of a cube view. I wish you could because I have been in your situation before and wanted it to be so. The problem is that an MDX-based subset can only be dynamic in regards to the dimension itself, not a cube view. If you read the MDX primer, which is great BTW, you'll see that you CAN create an MDX subset based on a value in a cube that contains your target dimension, but the element selections from all the other dimensions have to be absolute. For example, let's say you have a cube with three dimensions, ACCOUNT, CENTER and PERIOD and you want to have a subset in the ACCOUNT dimension that lists all the ACCOUNT elements that have a value in the cube greater than 100. You can build an MDX subset to do this but you have to specify the CENTER and PERIOD, it can't be dependent on where in the cube you are looking. This is because MDX subsets exist only within the context of a dimension, not within the context of the cube in which the dimension is being used. In my example, the MDX would look something like:

Code: Select all

{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [ACCOUNT] )}, 0)},[CubeNameHere].([CENTER].[ABC],[PERIOD].[January]) > 100 )}

As you can see, I had to hard code the CENTER element (ABC) and the PERIOD element (January). Wouldn't it have been nice if I could have just done:

Code: Select all

{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [ACCOUNT] )}, 0)},[CubeNameHere].([CENTER],[PERIOD]) > 100 )}

basically not mentioning the CENTER and PERIOD elements and letting them be dynamic based on where I am in the cube when I open the cube view? Unfortunately, that doesn't work. Now, having said that you CAN accomplish sort of what you want in an Active Form report, because you can filter the report rows for ACCOUNT based on an MDX query, and you can build the MDX query inside the report itself, based on user selections, and not a subset in the dimension.

[qml]

There is an eye-opening thread that suggests that this sort of thing is in fact possible and that when used within Cube Viewer dynamic subsets actually have access to the context of the view. So although you cannot leave the context out of the MDX formula as tomok would want, you can still populate your MDX with references to other dimensions taken from the context (i.e. the currently selected title dimension elements).

[lotsaram]

In the context of the OP's question which was I think to make a picklist context sensitive based on the position in the cube the answer is on first pass "no" but really yes it can be done but not in a single step fully automated way. MDX may play a part but not necessarily. First step you will need a picklist cube as opposed to a picklist attribute and you will need rules and subsets, lots of subsets and a naming convention for these subsets in the project dimension based on the element in the team dimension that the list of projects should be filtered on. Whether the subsets are static or dynamic really doesn't matter but I would suggest the best way to create and maintain them is via TI. Then in the picklist rule pick up the name of the team member (pun intended?) and use this to parse a string for the name of the appropriate subset in the project dimension to display in the picklist.

Clear?

[dhims]

Guys,

Thanks alot for your opinions. I have been through MDX primer and tried many things prior to posting my question but its not working. I think, the solution given lotsaram seems the only way out. Let me try it and see how it works.

Thanks again....
Dhims