Dynamic MDX for User Selection

[MarenC]

Hi,

I want a a view which shows Version A - Version B, but version a and b are based on user selections.

I am having trouble making the MDX view dynamic.

The following does not work, it throws a MDX syntax error

Code: Select all

WITH 
MEMBER [Version].[Version].[Version Variance] AS
STRTOMEMBER("[Version].[Version].[" + STRTOMEMBER("[}Clients].[" + USERNAME + "]").PROPERTIES("TestVersionA") + "]" ) - 
[Version].[Version].[Version B],

But this does work:

Code: Select all

WITH 
MEMBER [Version].[Version].[Version Variance] AS
[Version].[Version].[Version A] - [Version].[Version].[Version B],

and this works too:

Code: Select all

WITH 
MEMBER [Version].[Version].[Version Variance] AS STRTOMEMBER("[Version].[Version].[" + "Version A" + "]") -
[STRTOMEMBER("[Version].[Version].[" + "Version B" + "]"), 

[gtonkin]

It is likely the nesting of the StrToMember

Try create another intermediate/calculated member to get the value of TestVersionA then use that in the outer StrToMember.

[MarenC]

Hi George,

thank you for the reply.

I am not sure I follow your advice, how do I get the value of TestVersionA without using this mdx,

STRTOMEMBER("[Version].[Version].[" + STRTOMEMBER("[}Clients].[" + USERNAME + "]").PROPERTIES("TestVersionA") + "]" )

Are you meaning using something like the following mdx to derive another member:

Member [Version].[Version].[First Version] as STRTOMEMBER("[}Clients].[" + USERNAME + "]").PROPERTIES("TestVersionA").value

and then using this new member in the variance calculation, so something like

STRTOMEMBER("[Version].[Version].[" + [Version].[Version].[First Version] + "]" )

?

Maren

[gtonkin]

Yes, something like:

Code: Select all

WITH 
MEMBER [Version].[Version].[UserVersion] AS
    STRTOMEMBER("[}Clients].[" + USERNAME + "]").PROPERTIES("TestVersionA")

MEMBER [Version].[Version].[Version Variance] AS
    STRTOMEMBER("[Version].[Version].[" + [Version].[Version].[UserVersion] + "]") 
    - [Version].[Version].[Version B]...

Not tested so just moving code around as an example...

[MarenC]

Hi George,

It doesn't appear to work,

I tried this:

Code: Select all

WITH 
MEMBER [Version].[Version].[UserVersion] AS 
STRTOMEMBER("[}Clients].[" + USERNAME + "]").PROPERTIES("PAW Version Compare A") 
MEMBER [Version].[Version].[Version Variance] AS 
STRTOMEMBER("[Version].[Version].[" + [Version].[Version].[UserVersion] + "]") - [Version].[Version].[Version B] 
SELECT NON EMPTY

Which causes the MDX to return but then shows error in value expression where the variance should be and when I reopen the mdx it appears to have removed a chunk of the mdx, leaving this behind:

Code: Select all

WITH 
MEMBER [Version].[Version].[Version Variance] AS 
STRTOMEMBER("[Version].[Version].[Version].[UserVersion] + "]") - [Version].[Version].[Version B] SELECT NON EMPTY

Maren

[gtonkin]

HI Maren,

I tried this on PAW 2.0.101 with a reference to my User Preferences cube rather than an attribute on the }Clients dimension:

Code: Select all

WITH 
   MEMBER [Scenario].[Scenario].[UserVersion] AS 
   STRTOMEMBER("[Scenario].[Scenario].[" +
   	 [_S-User Preferences].(
      		STRTOMEMBER("[}Clients].[" + UserName + "]"),
      		[_S-User Preferences].[Scenario]) + "]") 
   MEMBER [Scenario].[Scenario].[Version Variance] AS 
   [Scenario].[Scenario].[UserVersion] - [Scenario].[Scenario].[Actual] 
SELECT 
   {
      [Scenario].[Scenario].[Actual],
      [Scenario].[Scenario].[UserVersion],
      [Scenario].[Scenario].[Version Variance]
   } ON 0, NON EMPTY... 

For me, the code is saving and view refreshes when changing the Scenario in the User Preferences cube.

Maybe a version thing, maybe something to do with attributes?

[MarenC]

Hi George,

Appreciate you testing this.

I followed your idea and it worked up to a point. The MDX didn't error and the MDX editor didn't remove code, so when I opened the MDX back up after closing, it looked the same.

However, a couple of issues arose.

First, nothing was being shown in the cube view, no data at all.

I determined the problem was this bit:

Code: Select all

SELECT 
   {
      [Scenario].[Scenario].[Actual],
      [Scenario].[Scenario].[UserVersion],
      [Scenario].[Scenario].[Version Variance]

It just wouldn't recognise the UserVersion version, at least this is my theory. To get around this problem I included within the select statement the same MDX used to derive the [Scenario].[Scenario].[UserVersion].

This then started working as expected.

However, I then found another problem. When I tried to change the Year nothing happened, I mean I would go into the subset editor, literally change the year and apply but the year didn't update. I couldn't actually change any of the context dimension elements.

To get around this, I added a User Year selection element in the User Selection Cube, and put the MDX to this in the MDX view.

This didn't work at first because the MDX kept getting replaced by the hard coded year value, so the mdx became, [Year].[Year].[2024/2025] even though it was using:

Code: Select all

STRTOMEMBER("[Scenario].[Scenario].[" +
   	 [_S-User Preferences].(
      		STRTOMEMBER("[}Clients].[" + UserName + "]"),
      		[_S-User Preferences].[User Year]) + "]")

I discovered the above was happening because the Year dimension was in the context area of the view, once I moved the Year to the columns, the MDX did not get replaced by the hard coded year and the report worked!

Maren

[Wim Gielis]

Hello Maren,

Good that you have it working, thanks to George too.
Can you post the final MDX for the benefit of all ? Obviously take out any sensitive information where applicable.

[MarenC]

Hi Wim,

MDX is:

Code: Select all

WITH 
   MEMBER [Version].[Version].[UserVersionA] AS 
   STRTOMEMBER("[Version].[Version].[" +
   	 [User Selection].(
      		STRTOMEMBER("[}Clients].[" + UserName + "]"),
      		[User Selection Measures].[Version Compare A]) + "]")
   MEMBER [Version].[Version].[UserVersionB] AS 
   STRTOMEMBER("[Version].[Version].[" +
   	 [User Selection].(
      		STRTOMEMBER("[}Clients].[" + UserName + "]"),
      		[User Selection Measures].[Version Compare B]) + "]") 
   MEMBER [Version].[Version].[Version Variance] AS 
   [Version].[Version].[UserVersionA] - [Version].[Version].[UserVersionB]

SELECT
 {STRTOMEMBER("[Year].[Year].[" + [User Selection].(STRTOMEMBER("[}Clients].[" + UserName + "]"),[User Selection Measures].[Version Year Compare]) + "]")}*
	{UNION({STRTOMEMBER("[Version].[Version].[" + [User Selection].(
			STRTOMEMBER("[}Clients].[" + UserName + "]"),[User Selection Measures].[Version Compare A]) + "]")},
	       {STRTOMEMBER("[Version].[Version].[" + [User Selection].(
			STRTOMEMBER("[}Clients].[" + UserName + "]"),[User Selection Measures].[Version Compare B]) + "]")}
	,ALL),
	[Version].[Version].[Version Variance]} ON 0, 
  NON EMPTY {
	TM1SubsetToSet([Cost Centre].[Cost Centre],"Cost Centre Hierarchy Immediate Children","public")} ON 1 
 FROM [Employee Forecasts] 
 WHERE (
	[Period].[Period].[All Periods], 
	[System Source].[System Source].[Default], 
	[Company].[Company].[All Company], 
	[Analysis].[Analysis].[All Analysis], 
	[Employee].[Employee].[All Employees], 
	[Job Role].[Job Role].[All Job Roles], 
	[Grade].[Grade].[All Grades], 
	[Scale].[Scale].[All Scales], 
	[Appointment Type].[Appointment Type].[All Appointment Types], 
	[Forecast Measures].[Forecast Measures].[Employer Cost Measures])

[Wim Gielis]

Thanks !

[PavoGa]

I am seeing problems in v12 with MDX. I posted in this thread because of the StrToMember references. Consider the following MDX structure:

Code: Select all

GENERATE( 
        FILTER( TM1SUBSETALL( [dimname2].[dimname2] ),
            [dimname2].[dimname2].currentmember.PROPERTIES("Element_Level") = "0" AND
            LEN([dimname2].[dimname2].currentmember.PROPERTIES("dimname1attr")) > 0),
        {StrToMember( "[dimname1].[dimname1].[" + [dimname2].[dimname2].currentmember.PROPERTIES("dimname1attr") + "]")}
        )

The idea is we are creating a subset of members in dimname1 based on the attribute mappings in dimname2.

The problem. This works fine in a "fresh" subset editor opened from the database dimension tree. (we had a problem with a subset editor that was "stale" or had been opened in a workbench for some weeks)

But open the subset editor in a cube view and it will not work. Says [dimname2].[dimname2] is undefined.

Cannot save the subset in the independent subset editor and then attempt to use in the cube view. This MDX structure works in a TI in creating a classic view. Have not tried it yet in an MDX view.

Have some more testing to do, but wondered if anyone else is experiencing problems with STRTOMEMBER now. Remember our issues with crashing the server some years ago with STRTOMEMBER?

[MarenC]

Hi,

I would try replacing the STRTOMEMBER part of your MDX, so rather than looking at currentmember.PROPERTIES("dimname1attr") it looks at a mapping cube.

Even if you don't like the idea, if it works, you know it is an issue with the currentmember.PROPERTIES.

Maren

[PavoGa]

Hi,

I would try replacing the STRTOMEMBER part of your MDX, so rather than looking at currentmember.PROPERTIES("dimname1attr") it looks at a mapping cube.

Even if you don't like the idea, if it works, you know it is an issue with the currentmember.PROPERTIES.

Maren

Think you missed the point, although to be fair, I was unclear about not looking for a workaround. This was more a PSA there is a problem with the v12 subset editor. The MDX is syntactically correct, otherwise it would not work at all. It does work, but not in certain instances of the subset editor. However, I had recently submitted to IBM three queries two of which were failing and one which did exactly what you suggested. It was failing as well. Until we opened a fresh subset editor whereupon it and other failing queries worked perfectly. Not going to repeat all that here.

Now some people just jump to the first workaround they come across. And while I've done workarounds for obvious issues, I tend not to do that when there seems to be a problem with the system and just ignore the issue going forward. I literally have hundreds or thousands of subsets in earlier versions using currentmember.PROPERTIES. Not working in v12 is an IBM issue to me, not MDX syntax.

[MarenC]

Hi,

Thanks for clarifying.

I did say try it out if only to narrow down the problem, wasn't suggesting IBM should do nothing about it.

I will be honest v12 is not something I have had the pleasure yet, but good to know someone is going through the pain on my behalf!

Hope IBM sort the issue out.

Maren