MDX Query

[Abinaya]

Hi
I am testing a query to retrieve an element of a dimension but failing to compile. Can any one see the error in the statment?

"{FILTER( {TM1FILTERBYLEVEL( { TM1SubsetAll([Customer] ) } ,0 ) } ,[Customer].[20001744] ) }"

Customer is dimension and 20001744 is one of the leaf level element.

thanks.

[lotsaram]

Have a look at the TM1 MDX Primer and the Microsoft MDX online help for how to apply functions.

In general Filter works on the following principal
(Pseudo Code)

Code: Select all

Filter(
   Set of members,
   expression = condition
)

You have a condition but no expression, and your condition is a member when it should be a string or numeric value.

[Neil Watson]

Hi,

There is an mdx expression window and expression recorder in the subset editor that you can turn on to help in developing your query
however,

I believe the syntax could be

{TM1FILTERBYPATTERN({TM1FILTERBYLEVEL( { TM1SubsetAll([Customer] ) } ,0 ) } ,"20001744" ) }

so replacing the FILTER function by th tm1 specific TM1FILTERBYPATTERN

HTH
Neil

[declanr]

I believe the syntax could be

{TM1FILTERBYPATTERN({TM1FILTERBYLEVEL( { TM1SubsetAll([Customer] ) } ,0 ) } ,"20001744" ) }

Neil's code should work assuming that your dimension and element names are correct.


I am just intrigued though; are you just doing this test to get to grips with MDX or is there an actual business purpose? The above query will of course only return one element "20001744"


The following code would also work:
{TM1FILTERBYPATTERN({ TM1SubsetAll([Customer] ) } ,"20001744" ) }

But if you only wanted one element then it wouldn't need MDX at all and could use a static subset... or just a one off title selection with no subsets involved (depending on your interface method.)



Also as suggested by Neil you would be able to get to the above code by doing the following:

• [1] Open Subset Editor on "Customer" Dimension
  [2] Select "Tools" => "Record Expression"
  [3] Press "All"
  [4] Select "Filter by wildcard"
  [5] Search on "20001744"
  [6] Select "Tools" => "Stop Recording"
  [7] Select "Yes" on annoying pop-up
  [8] Save subset if one so desires and/or view the code within the expression window ("View" => "Expression Window")

Seems a few too many steps to return one element for my liking though.

[Neil Watson]

Getting the Filter mdx function to work, and this may well be not the only way but

{filter({tm1subsetall([Customer])}, [Customer].CurrentMember.Name = "123456789")}

the fun part is that the "123456789" can also be a reference to another cube if you're exploring mdx

Cheers
Neil

[bplaia]

Also as suggested by Neil you would be able to get to the above code by doing the following:

• [1] Open Subset Editor on "Customer" Dimension
  [2] Select "Tools" => "Record Expression"
  [3] Press "All"
  [4] Select "Filter by wildcard"
  [5] Search on "20001744"
  [6] Select "Tools" => "Stop Recording"
  [7] Select "Yes" on annoying pop-up
  [8] Save subset if one so desires and/or view the code within the expression window ("View" => "Expression Window")

Seems a few too many steps to return one element for my liking though.

WAIT! Don't forget to select View -> Expression Window...wouldn't want to be flying blind here

[ardi]

Did you try the following MDX Statement? If all you want to do is filter one element from Customer Dim, then try the MDX below

{[Customer].[20001744]}

[Abinaya]

Did you try the following MDX Statement? If all you want to do is filter one element from Customer Dim, then try the MDX below

{[Customer].[20001744]}

Thanks all. This worked.

But this one worked in expression window but not working in Excel,bit strange!
{TM1FILTERBYPATTERN({TM1FILTERBYLEVEL( { TM1SubsetAll([Customer] ) } ,0 ) } ,"20001744" ) }

I am not using static subset because I use this statement kind of filter. In the text box the customer number is entered and then

all the records belong to that customer is retrieved.

Thanks again for all the answers.