How to set a filter on a string member?

[wang_chris]

For some reason, I had to maintain a relation-table like cube, and do some processing on it.

As a simple example, the table has 2 dimensions. One is Staff dimension, the other staff_info dimension.

Staff Dimension consists of serial no, like Staff1, Staff2, etc.
Staff_info dimension are like below:
join date,
leave date,
Role,
Department,
...

The members listed above are all in String.

Role has a pick list of Par, Manager, Associate, etc.

Data input into the cube (table) like below.

Join Date, Leave Date, Role, Dapartment
Staff1, 2010-01-01, - , Par , SWG
Staff2, 2010-01-01, - , Par , STG
Staff3, 2010-01-01, - , Mgr , STG
Staff4, 2010-01-01, - , Mgr , GBS
Staff5, 2010-01-01, - , Par , GBS

Now I wish to make a view to display only the rows with Role='Par', How could I make it?

TM1 can filter numeric data by percetage, or top N, etc, but it seems no way to string data.

Regards,
Chris

[declanr]

Code: Select all

{FILTER({TM1SUBSETALL( [StaffDimension] )},[StaffCube].([StaffInfo].[Role]) = "PAR")}

Something like that should do it, needs to be checked for typos etc though.

[wang_chris]

Yes, it does work.

Thanks very much, though MDX is quite uneasy to understand.

[wang_chris]

Sorry, still a quick question.

to above sample. If there are some staffs whose role are in blank. I wish to use similar MDX to filter out these staffs with no role input.

The statement like below.
{FILTER({TM1SUBSETALL( [StaffDimension] )},[StaffCube].([StaffInfo].[Role]) <> "" )}

I just change "Par" to "", then it doesn't work.

What happens? we can't use "" to represent null or no value?


Chris

[wang_chris]

I do more test, and find below situation.

4 dimensions total, each dimension and its member looks like below.

Year: 2014, 2015, 2016
Month : Jan, Feb, Mar, ...
Staff : Staff1, Staff2, Staff3, etc
Staff_Info : Name, Grade, Salary, etc.

The cube consists of above 4 dimensions. and I load below data into the cube.

Year, MOnth, Staff, Name, Grade
2014, Jan, Staff1, Chris, Manager
2014, Jan, Staff2, Debbie, Director
2014, Jan, Staff3, Cuby, Manager
2014, Jan, Staff4, Nancy,
2014, Jan, Staff5, , Manager
2014, Jan, Staff6, Bob, Director
2014, Jan, Staff7, ,
2014, Jan, Staff8, Cindy, Director

WHen I put Year, Month as filter dimensions, and use staff in row, staff_info in column,
then apply dynamic subset on Staff dimension with below MDX statements.

Statement1: to dispaly rows with name not in blank
{ Filter(
{TM1FilterbyLevel( {TM1SubsetAll( [Staff] )}, 0 )},
[CUbe].( [Staff_Info].[Name] ) <> ""
)
}

Statement2: to display rows wth name in blank
{ Filter(
{TM1FilterbyLevel( {TM1SubsetAll( [Staff] )}, 0 )},
[CUbe].( [Staff_Info].[Name] ) = ""
)
}

Statement3: to display rows with Grade=Manager
{ Filter(
{TM1FilterbyLevel( {TM1SubsetAll( [Staff] )}, 0 )},
[CUbe].( [Staff_Info].[Grade] ) ="Manager"
)
}

Statement4: to display rows with Grade <> Manager
{ Filter(
{TM1FilterbyLevel( {TM1SubsetAll( [Staff] )}, 0 )},
[CUbe].( [Staff_Info].[Grade] ) <>"Manager"
)
}

All 4 statements above wrok fine.

But when I try to move Month (or Year) dimension down to put in the row , for example, before Staff dimension. Now both Month and Staff Dimensions are in row.
What I want to display with statement 3 are those staffs who are Manager in that particular Month. But What system display is different from my expectation. It reports no value to display.

How could I write the MDX to work in such environment?

Regards,
Chris

[wang_chris]

no suggestion?

[ndivine]

I'm surprised those 4 statements work, as you are not specifying members for all four dimensions. Keep in mind that the MDX you are writing is to create a subset within a single dimension. The MDX would look something like this for a given month:
{ Filter(
{TM1FilterbyLevel( {TM1SubsetAll( [Staff] )}, 0 )},
[Staff].( [Year].[2014], [Month].[01], [Staff].CurrentMember, [Staff_Info].[Grade] ) ="Manager"
)
}

[declanr]

I'm surprised those 4 statements work, as you are not specifying members for all four dimensions. Keep in mind that the MDX you are writing is to create a subset within a single dimension. The MDX would look something like this for a given month:
{ Filter(
{TM1FilterbyLevel( {TM1SubsetAll( [Staff] )}, 0 )},
[Staff].( [Year].[2014], [Month].[01], [Staff].CurrentMember, [Staff_Info].[Grade] ) ="Manager"
)
}

MDX expressions to filter by cube value can automatically work out element members that are context filters or relate to the dimension itself, so they would work fine as long as the other dims stay as title elements.

Moving the period to the row stopped the MDX from knowing what to pass as the period element.


However I presume that the OP is wanting have a cube view where dimensions are nested as Month (with all months displayed) then Staff with the cube showing perhaps 5 staff in Jan, 7 in Feb, 6 in Mar etc.
This isn't how MDX works, as with static subsets; dynamic subsets can only return 1 single list of elements. So if your MDX expression returns 10 staff members, it will display all of those 10 for every month.

[wang_chris]

If MDX can't make this, then in which way can I meet OP's need?

'0 filter' can do this, but it not only removes rows in blank, but also removes columns in blank, while I just want remove/filter those rows of non-Manager, but keep the blank columns for rows of Manager, leave these columns for OP team to input.

How can I do it?

Reards,
Chris

[declanr]

If MDX can't make this, then in which way can I meet OP's need?

"OP" in the world of forums means "original poster", so by saying that I was referring to you rather than your end customer.

'0 filter' can do this, but it not only removes rows in blank, but also removes columns in blank, while I just want remove/filter those rows of non-Manager, but keep the blank columns for rows of Manager, leave these columns for OP team to input.

There is an option to only suppress zeroes on rows or columns as opposed to both, this will only be useful however in the event that all rows you want to enter data to have the Manager field populated and the rows that don't have it populated also would need to not have any other visible data displayed in order for them to be suppressed.

If you are using an active form as opposed to a cube viewer; you can create the active form just with the "Manager" field visible; zero suppress the active form and then manually add extra columns that are dependent on the active form but not part of it.

If you need to go the cube viewer way then you can create a 2-d cube (as in your original question) where the period is actually a measure itself and then your MDX will work fine.

There are a number of other ways to do this also but there are a couple of starters there that you can think about and see if you can expand/develop them to meet the needs of your customers.

[Harvey]

Just thinking outside the box a little, is there any reason you couldn't build a reporting cube for this? What you have is a classic case of what I call an "Item Based" input cube. Why not create another cube that includes all the columns you would like to filter by as dimensions?

Here's an article explaining it better, if the idea interests you.