Dim Subset MDX for filter by leaf node, & return level 2+?

[tez]

Hi, this is an MDX for TM1 subset create question.

We have a dimension that has attributes for the 0 level items (not the rollups). We want to filter by the attribute, display the leaf level items, as well as levels 2, 3 & 4 but not level 1.

I have tried a few functions in MDX but can't seem to get it to work...I know you can filter on levels such as

{TM1FILTERBYLEVEL( {Descendants([Project].[Product Type] )}, 0,2,3,4)}

but I also need the MDX to include being able to filter by attribute that is only attached to the leaf level.

Does anyone have any suggestions?

Many thanks
Terri

[rmackenzie]

We have a dimension that has attributes for the 0 level items (not the rollups). We want to filter by the attribute, display the leaf level items, as well as levels 2, 3 & 4 but not level 1.

Code: Select all

{HIERARCHIZE(
  {TM1FILTERBYLEVEL( {TM1SUBSETALL( [YOUR_DIMENSION_NAME] )}, 0)}+
  {TM1FILTERBYLEVEL( {TM1SUBSETALL( [YOUR_DIMENSION_NAME] )}, 2)}+
  {TM1FILTERBYLEVEL( {TM1SUBSETALL( [YOUR_DIMENSION_NAME] )}, 3)}+
  {TM1FILTERBYLEVEL( {TM1SUBSETALL( [YOUR_DIMENSION_NAME] )}, 4)}
)}

The 'HIERARCHIZE' part will attempt to rebuild the tree structure of the dimension even though level 1 consolidations have been removed from the subset.

but I also need the MDX to include being able to filter by attribute that is only attached to the leaf level.

Code: Select all

{FILTER( {TM1SUBSETALL( [YOUR_DIMENSION_NAME] )}, [YOUR_DIMENSION_NAME].[YOUR_ATTRIBUTE_NAME] = "YOUR_VALUE")}

I guess that one for filter by attribute is pretty generic!

Have you looked at using the MDX 'recorder' functionality in the subset editor window. If you select View->Expression Window you will see a small pane at the bottom of the window. Then select Tools->Record Expression and click around making different subset choices (all subset, filter by wildcard, drill up and down etc) you will see the MDX statement being constructed for you.

HTH
Robin

[tez]

Hi Robin

Yes, I have tried using the Expression builder in the subset editor to no avail.

I have nearly got the MDX working now.....the only problem is the hierarchy sort doesn't work, I think because I have removed level 1.

My mdx currently is:

except(hierarchize(generate(FILTER(TM1FILTERBYLEVEL(descendants( [dim name].[hierarchy name] ), 0),[dim name].[attribute] = "000"),ASCENDANTS( [dim name].CurrentMember ) )),TM1FILTERBYLEVEL(descendants([dim name].[hierarchy name]), 1) )

Is there a way to still use hierarchy sort when you are not showing the full hierarchy levels?

Many thanks
Terri

[rmackenzie]

Yes, I have tried using the Expression builder in the subset editor to no avail.

It only gets you so far and then you have to start doing it by hand... but it's a good way to learn what the different functions are doing.

except(hierarchize(generate(FILTER(TM1FILTERBYLEVEL(descendants( [dim name].[hierarchy name] ), 0),[dim name].[attribute] = "000"),ASCENDANTS( [dim name].CurrentMember ) )),TM1FILTERBYLEVEL(descendants([dim name].[hierarchy name]), 1) )

Is there a way to still use hierarchy sort when you are not showing the full hierarchy levels?

I think you want to have HIERARCHIZE as the outermost function, i.e. the one that will act last on the subset. It could be in your example above that the EXCEPT statement is ruining the hierarchical nature of the subset even with level 1 removed. I see you're using GENERATE - I don't think this is the right place to use it... did the MDX recorder insert that for you?

Can I suggest:

Code: Select all

{HIERARCHIZE(
  {FILTER( {TM1SUBSETALL( [Project] )}, [Project].[YOUR_ATTRIBUTE_NAME] = "000")}+
  {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Project] )}, 2)}+
  {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Project] )}, 3)}+
  {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Project] )}, 4)}
)}

Which is a mish-mash of my two earlier suggestions - it just says chain together all the level-0 elements plus the level-2 elements plus the level-3 elements plus the level-4 elements, then try and put all those elements in hierarchical order.

[tez]

Hi Robin

Unfortunately your suggestion is over the 256 limit of Excel, so it won't work & hence why I was trying other ways......is there a way to split the MDX into multiple cells & join them to produce the whole MDX?

Thanks
Terri

[rmackenzie]

Ah ok, so you are using the expression for an active form? I guess you can split the string out over a few cells and then concatenate them in the TM1RPTROW formula.... e.g.

Code: Select all

=TM1RptRow(ReportView, Dimension, "", "","", 0,A1&A2&A3&A4)

or are you doing something completely different?

[tez]

I don't think I can concatenate in the TM1RPTROW formula as the user has numerous filtering possibilities - I have many IF statements to find the final MDX to use on the report.

So, the only way I think this will work is to be able to concatenate in a cell so I can reference that in my IF statement. What is the trick to do this? I've tried using concatenate (cellA,cellB), or just cellA&cellB, but these aren't working...I must be doing something wrong with the "{" & "(" etc ?

Appreciate your help Robin!
thanks
Terri

[rmackenzie]

If you suspect that there is some issue with all your matching (s and {s as there usually is with big MDX statements then the best thing to do is get one condition working for the TM1RPTROW and then add one condition at a time, fixing each one as you go. There shouldn't be a problem passing in a cell to the formula that contains a concatenation of other cells. You can test your MDX by using the Expression Window I mentioned above - paste in the MDX statement and click 'Update' and if there are errors it will tell you them. The errors aren't always useful but often will be enough to help you solve the problem.

[tez]

Hi Robin

Thanks for your help, I've now got it working...

I now have another problem where I'm trying to filter on two attributes.....when I filter on just one attribute & have the coding split over multiple cells, it concatenates fine.

Code: Select all

cellA1=("{HIERARCHIZE({FILTER({tm1filterbylevel({descendants([Project].[product type])},0)},[Project].[Division]=""000"")}+{TM1FILTERBYLEVEL({descendants([Project].[Product Type])},2)}")

cellA2=("+{TM1FILTERBYLEVEL({descendants([Project].[product type])},3)})}")

When I change cellA1 to contain the below (& not use cellA2 in the coding), it also works fine, so I can not see an issue with the second filter.

Code: Select all

=("{HIERARCHIZE({FILTER({filter({tm1filterbylevel({descendants([Project].[product type])},0)},[Project].[Division]=""000"")},[project].CurrentMember.Properties(""State"")=""VIC"")}+{TM1FILTERBYLEVEL
({descendants([Project].[Product Type])},2)})}")

But when I try to use two attributes with the two TM1FILTERBYLEVEL & have the coding split over multiple cells, it won't return anything.

Code: Select all

cellA1=("{HIERARCHIZE({FILTER({filter({tm1filterbylevel({descendants([Project].[product type])},0)},[Project].[Division]=""000"")},[project].CurrentMember.Properties(""State"")=""VIC"")}+{TM1FILTERBYLEVEL
({descendants([Project].[Product Type])},2)}")

cellA2=("+{TM1FILTERBYLEVEL({descendants([Project].[product type])},3)})}")

Can someone please help?!

Thanks
Terri

[rmackenzie]

Hi Terri, I guess a better debugging mechanism would be helpful for these sort of problems... I took a look at your formulae and can hazard a guess at the problem. Obviously I can't confirm it because I don't have your environment

When I change cellA1 to contain the below (& not use cellA2 in the coding), it also works fine, so I can not see an issue with the second filter.

CODE: SELECT ALL
=("{HIERARCHIZE({FILTER({filter({tm1filterbylevel({descendants([Project].[product type])},0)},[Project].[Division]=""000"")},[project].CurrentMember.Properties(""State"")=""VIC"")}+{TM1FILTERBYLEVEL
({descendants([Project].[Product Type])},2)})}")

But when I try to use two attributes with the two TM1FILTERBYLEVEL & have the coding split over multiple cells, it won't return anything.

CODE: SELECT ALL
cellA1=("{HIERARCHIZE({FILTER({filter({tm1filterbylevel({descendants([Project].[product type])},0)},[Project].[Division]=""000"")},[project].CurrentMember.Properties(""State"")=""VIC"")}+{TM1FILTERBYLEVEL
({descendants([Project].[Product Type])},2)}")

cellA2=("+{TM1FILTERBYLEVEL({descendants([Project].[product type])},3)})}")

The first and second A1 formulas differ by a )} which is missing from the end of the second example - I've tried to highlight it in bold in the quote above. I think it should be exactly as your first example:

Code: Select all

=("{HIERARCHIZE({FILTER({filter({tm1filterbylevel({descendants([Project].[product type])},0)},[Project].[Division]=""000"")},[project].CurrentMember.Properties(""State"")=""VIC"")}+{TM1FILTERBYLEVEL
({descendants([Project].[Product Type])},2)})}")

I would add that on and then concatenate A1 and A2. I'd actually to be tempted to surround the concatenation with curly brackets to indicate I want the whole thing to evaluate to a single set but it is quite possible this is unnecessary:

Code: Select all

="{"&A1&A2&"}"

[tez]

Hi Robin

Thanks for replying...the saga continues....

Yes, there is a difference between the two A1 cell MDXs as the first one was only using MDX within the cell, whereas the second one was using both cellA1 & cellA2, so needed the extra )}.

I have tried your suggestion with the extra curlies, & some variations on it, but unfortunately this hasn't worked.

I'm now looking at using a combination of MDX via subsets & MDX within the report.

Thanks so much for your help & suggestions though..much appreciated....hopefully you won't hear from me again! I will not let TM1 win this one!

Cheers
Terri

[qml]

hopefully you won't hear from me again!

I for one am hoping the opposite is true and you will come back to share your solution once you have claimed victory over TM1 and its admittedly quirky implementation of MDX.

[rmackenzie]

I'm now looking at using a combination of MDX via subsets & MDX within the report.

That's probably a good idea. You don't need to parameterise where you are always returning the level 2 or 3 elements. These could be predefined subsets (which can still be powered by MDX) that you reference in the parameterised one in the Excel formula. Be careful if you are naming subsets the same as actual element names in the dimension though as this will cause ambiguities, or worse, in the output. E.g. you could have

Code: Select all

{HIERARCHIZE(
  {FILTER(
    {FILTER(
      {[Project].[Level 0 elements]}, 
      [Project].[Division] = "000" )},
    [Project].CurrentMember.Properties("State") = "VIC"
  )}
  +
  [Project].[Level 2 elements]
  +
  [Project].[Level 3 elements]
)}

Here 'Level 0 elements', 'Level 2 elements' and 'Level 3 elements' are names of subsets. But if you had consolidation points with those same names then you should use the TM1SubsetToSet function:

Code: Select all

{HIERARCHIZE(
  {FILTER(
    {FILTER(
      {TM1SubsetToSet([Project], "Level 0 elements")}, 
      [Project].[Division] = "000" )},
    [Project].CurrentMember.Properties("State") = "VIC"
  )}
  +
  {TM1SubsetToSet([Project], "Level 2 elements")}
  +
  {TM1SubsetToSet([Project], "Level 3 elements")}
)}

In fact, I would also refrain from using the [DIMENSION_NAME].[ATTRIBUTE_NAME] syntax which can give rise to ambiguities and problems. So:

Code: Select all

{HIERARCHIZE(
  {FILTER(
    {FILTER(
      {TM1SubsetToSet([Project], "Level 0 elements")}, 
      CurrentMember.Properties("Division") = "000" )},
    [Project].CurrentMember.Properties("State") = "VIC"
  )}
  +
  {TM1SubsetToSet([Project], "Level 2 elements")}
  +
  {TM1SubsetToSet([Project], "Level 3 elements")}
)}

... come back to share your solution once you have claimed victory over TM1 and its admittedly quirky implementation of MDX.

Yes, good luck! Using MDX in Active Forms is a really powerful feature for both Excel and TM1 Web and once you get over all the hurdles, it will be worth it.

[tez]

Thanks Robin...I had already gone too far with my next "solution" to reverse it & try your suggestions....will keep them in mind for next time though

Finally, I have conquered TM1!

My solution was:
TM1RPTROW looks like =TM1RPTROW([report view],"cxmd:[dim name]",cellA1,cellA2,"[Alias]",1,cellA3,,0)

So for:

- cellA1 (subset) - this is where I reference what subset to use where required (for my situation, where the MDX was longer than 256 character limit of Excel). I use an IF statement TRUE = the correct subset, FALSE = "" for blank entry

- cellA2 (subset elements) - this is where I reference what subset elements to use where required (for my situation, if the user selections (filters via attributes) were empty, the report "errors out" & just returns the top consolidated level (total), rather than showing any detail, & when all filters have been selected, = "" to return all subset elements)....another IF statement used.

- cellA3 (report MDX statement) - this is where I reference different MDX statements based on the user selections (filters via attributes) - basically where the MDX length was under the 256 character limit of Excel....yet another IF statement.

To try & make it a bit simpler...
- if I needed to use a subset (cellA1), TRUE returned the subset results, cellA2 = "", cellA3 = ""
- if I needed to use subset elements (cellA2), TRUE returned the subset elements, cellA1 = "", cellA3 = ""
- if I needed to use the report MDX statement (cellA3), TRUE returned the MDX results, cellA1 = "", cellA2 = ""

Glad I've finally got that one sorted & can move onto the next report! Hopefully the above can help someone else!

qml - yes, the quirky implementation of MDX...we have a saying of some of the "features" of TM1....we call it a "FF", probably can work out the first F

Cheers
Terri