MDX Wildcards in Active Forms

[merowen]

Hi
I am trying to add a Wildcard element search as part of the MDX statement in the TM1RPTROW formula in an Active Form.
(TM1 9.5.2)

The idea is to produce a list of all account codes that start with P-, include AT and end with P. The wildcard looks like this P-*AT*P
Here is the full MDX when produced as a recorded expression in the subset editor.
{TM1DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [Account] )}, "P-*AT*P")}, ALL, RECURSIVE )}
This works as expected.

However, I want to produce this in an Excel cell to be captured by the TM1RPTROW formula. I can use char(34) referencing other cells but I need to be able to tell it that this is a wildcard search and not a text string element.
The formula I currently have in the cell in Excel is...(where Wildcard is a named ranged to P-*AT*P)
="{TM1DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [Account] )}, "&CHAR(34)&Wildcard&CHAR(34)&")}, ALL, RECURSIVE )"

If anyone as an example of an MDX Excel formula that works with Wildcards I would be grateful to have a look. Or if there is any documentation on using Excel cell references within TM1 MDX
that would also be useful.
This ideally needs to be an Excel solution only otherwise I can update a subset separately using TI via Action Button.

Thanks

[AmbPin]

Is this the sort of thing you are looking for?

Capture.PNG (3.8 KiB) Viewed 28747 times

[Michel Zijlema]

I have a more or less similar issue:

I have a 3-dim InputCube, consisting of a Years, InputLines and InputVariables dimensions. I've built an Active Form with the InputLines on the rows and the InputVariables on the colums.
I'm trying to simulate some autofilter behaviour in this AF using an MDX filter on the InputLines dimension, filterering on parts of values in the input data. F.i. I would like to filter on all lines that have the Accountmanager Name field set to a value containing "Doe". I use the Instr() function for the simple 'wildcard' selection.
The following expression works in the Subset Editor when applied to the InputLines dimension:

Code: Select all

{ FILTER( {[InputLines].Members}, Instr([InputCube].([Years].[All years^2012],[InputVariables].[Accountmanager Name]), 'Doe')> = 1) }

But the following TM1RPTROW function containing the same expression is not returning any rows:

Code: Select all

=TM1RPTROW($B$9;"TM1Serv:InputLines";"";"";"";0;"{ FILTER( {[InputLines].Members}, Instr([InputCube].([Years].[All years^2012],[InputVariables].[Accountmanager Name]), 'Doe')> = 1) }")

When I only use {[InputLines].Members} in the MDX part of the TM1RPTROW formula the function works (returning all rows).
Are there known limitations to the use of MDX in Active Forms opposed to the use in the Subset Editor?

Michel

PS. I'm on TM1 10.1, using Excel 2010

[AmbPin]

I tend to build the MDX statement in a seperate cell which I the reference from the TM1RPTROW function as shown below:-

Capture.PNG (6.29 KiB) Viewed 28725 times

This seems to work OK for me.

[AmbPin]

so a simple dynamic version might look like this:-

Capture.PNG (10.64 KiB) Viewed 28723 times

[Michel Zijlema]

Hi AmbPin,

Thanks for your reply. The example I posted is simplified - I build up the expression in a separate cell using parameter cells for f.i. year and search string. This was not working, so I step by step simplified the code, but nothing seems to work. Please note that I'm not selection on a pattern of the element names in a dimension. I'm selecting the elements in the InputLines dimension (line 1 to 1000) for which the cell values (in the example regarding the InputVariable "Accountmanager Name" and the Year "2012") in the InputCube cube contain the search value.

Michel

[AmbPin]

Hello Michel,

I dont think your issue is the same as the one I was trying to address for merowen. In your case Michel it may be that you are simply not able to call the VBA type function "INSTR" in an MDX statement from within Excel. I created a test statement similar to yours in my test environment, it works within Architect but I cannot pass it via the TM1RPTROW function successfully.

[Michel Zijlema]

I dont think your issue is the same as the one I was trying to address for merowen. In your case Michel it may be that you are simply not able to call the VBA type function "INSTR" in an MDX statement from within Excel. I created a test statement similar to yours in my test environment, it works within Architect but I cannot pass it via the TM1RPTROW function successfully.

Hi Ambpin,

The Instr function is an MDX function - I'm not doing anything with VBA. As mentioned in my first post the example code is delivering the required results when pasted in the expression pane in the Subset Editor, but not when passed as an argument to the TM1RPTROW function.

Michel

[Michel Zijlema]

I dont think your issue is the same as the one I was trying to address for merowen. In your case Michel it may be that you are simply not able to call the VBA type function "INSTR" in an MDX statement from within Excel. I created a test statement similar to yours in my test environment, it works within Architect but I cannot pass it via the TM1RPTROW function successfully.

Hi Ambpin,

The Instr function is an MDX function - I'm not doing anything with VBA. As mentioned in my first post the example code is delivering the required results when pasted in the expression pane in the Subset Editor, but not when passed as an argument to the TM1RPTROW function.

Michel

I read your answer too quickly - I see we're actually saying the same thing here...

[AmbPin]

Michel,
This works for me which proves it is OK to call the INSTR function from Excel.

Capture.PNG (6.98 KiB) Viewed 28682 times

I guess the problem must be with the other aspects of your statement:-

Code: Select all

=TM1RPTROW($B$9;"TM1Serv:InputLines";"";"";"";0;"{ FILTER( {[InputLines].Members}, Instr([InputCube].([Years].[All years^2012],[InputVariables].[Accountmanager Name]), 'Doe')> = 1) }")

Why are there ";" after "TM1RPTROW($B$9;"TM1Serv:InputLines"? In my statement these are commas.

[Michel Zijlema]

Why are there ";" after "TM1RPTROW($B$9;"TM1Serv:InputLines"? In my statement these are commas.

Hi AmbPin,

Thanks for your efforts. I suspect the problem is that the Active Form TM1RPTROW function has problems with filtering on a cell value, whereas the Subset Editor is not - not the first piece of TM1 inconsistent behaviour

The semicolon instead of comma (and vice verssa) has to do with a difference in our Regional Settings.

Michel