Hello,

has anybody been able to develop an MDX where we can dynamically swap row and title dimensions in a View based on a selection of another View?

For example, in a Picture in my attachment, I want to swap "Organization" Dimension from the Rows and put in the Title Dimension and "ESG Measure" dimension from Titles to Rows in my Target View.

I am using another view as a source to define the structure of my Target view. I built an MDX that reads from this Source View and updates my target view but I am having issues to successfully build an MDX with which I can dynamically swap my title and Row dimensions just by changing the values in my Source Definion View. In this case, I would specify "aESGMeasure" (dimension name) in my "Row Dimension 1" and "Amount" as an "Row Dimension Element". In other direction, I would specify "aOrganization" under "Dimension 1 (Title)" and "World" under "Dimension 1 (Title Element)".

Any ideas or examples of dynamically swapping Rows and Title Dimension via MDX? Any case would be welcomed.

Many thanks for the support.

I have a rough idea that may work...

You already have a cube that looks to help drive some of the MDX. You could potentially use this to return a set needed for rows and for columns.

You could then use code similar to the below to leverage the "WITH SET <name> AS <set>" syntax to read from your configuration cube and apply to the SET in the MDX.

These calculated sets would then be shown on 0 or 1 as required.

WITH 
SET [CP] AS 
	{TM1SubsetToSet([Period].[Period],"Planning Period", "public").Item(0).Item(0)}
SET [Periods] AS
		{OpeningPeriod([Period].[Period].[Months], [CP].Item(0).Item(0).Parent)}

SELECT 
{[Periods]} *
	{[Sales Measures].[Sales Measures].[Units]} ON 0, 
NON EMPTY {TM1SubsetToSet([Product].[Product],"All Products","public")} ON 1 
FROM [Sales] WHERE (
	[Scenario].[Scenario].[Rolling Forecast], 
	[Customer].[Customer].[Total Customers])

In the above, I create two sets, CP and Periods and then use Periods on 0 along with my measures.

In your case you could use SET [MDXRows] As ... and SET [MDXColumns] As ... based on the configuration cube.

See if that makes sense first and then try apply it.

Hello George,

first of all, many thanks for providing an answer. Your help is really appreciated!

We have tried the WITH clasue like in Your example and we can build the view like we want, which is great news. 

One thing we noticed - when we apply the MDX and get the MDX we want, the View itself starts behaving like a static snapshot. Specifically, we can not select any other value in the Title Dimensions or select different values in rows and columns using the View Riw and COlumn Definition sets. Even more, consolidated eleemnts will not expand/collapse anymore.

Here is a simple example MDX below. I have have also uploaded a screenshot of the result. Inthe Screenshot elements "Eastern Europe" and "2008" are consolidated elements.

Best regards,

Djordje Stojic

WITH SET [CP] AS 	{TM1SubsetToSet([Period].[Period],"Planning Period", "public").Item(0).Item(0)}SET [Periods] AS		{OpeningPeriod([Period].[Period].[Months], [CP].Item(0).Item(0).Parent)}SELECT {[Periods]} *	{[Sales Measures].[Sales Measures].[Units]} ON 0, NON EMPTY {TM1SubsetToSet([Product].[Product],"All Products","public")} ON 1 FROM [Sales] WHERE (	[Scenario].[Scenario].[Rolling Forecast], 	[Customer].[Customer].[Total Customers])

Yes, unfortunately using SET will likely reduce some of the functionality as members in the SET are returned and no interaction is expected.

Did you try some of the options on right-click to drill down etc?

Essentially what you need is a way to drilldown on the period. The only thing that comes to mind right now which is probably an awful work around is a dropdown in a user preferences cube that allows you to select from a picklist something like Member, Member and Children, Descendants and then link this back into your set using a Case statement.

Hoping someone else has something more practical...

WITH SET [CP] AS 	{TM1SubsetToSet([Period].[Period],"Planning Period", "public").Item(0).Item(0)}SET [Periods] AS		{OpeningPeriod([Period].[Period].[Months], [CP].Item(0).Item(0).Parent)}SELECT {[Periods]} *	{[Sales Measures].[Sales Measures].[Units]} ON 0, NON EMPTY {TM1SubsetToSet([Product].[Product],"All Products","public")} ON 1 FROM [Sales] WHERE (	[Scenario].[Scenario].[Rolling Forecast], 	[Customer].[Customer].[Total Customers])