Creating Private Subsets using Subdefine

[Maianbarian]

Hi All,

I'm building some reporting in excel using perspectives which requires custom subsets so that I can consolidate data in ways outside of the official hierarchies. I don't have write access to the server so I can't create any public subsets.

My workaround has been to create some private subsets, which work fine on my machine, but of course when anyone else tries to run my reports they get errors. A bit of googling, led me to the Subdefine function, which should be able to create a subset from a range in Excel, but I can't for the life of me get it to work. The code I'm using to test creation of a subset is:

Code: Select all

Sub createsubsets()

bResult = Application.Run("SUBDEFINE", "TM1_Financials:All GL:Account", "My Test Subset", ActiveSheet.Range("A2:A3"))
Debug.Print bResult


End Sub

The server I'm accessing is called TM1_Financials, the cube is All GL and the dimension is Account. Whenever I run the code, bResult comes back as False. I'm sure I'm missing something simple, but the user docs for Subdefine aren't particularly enlightening

[tomok]

Why do you think that creating a subset using Subdefine is going to let you create a public subset? It's a security thing, not a user interface thing.

[Maianbarian]

I'm not trying to create a public subset, I want to embed a macro in the report that will create private subsets on the user's PC.

[Alan Kirk]

I'm not trying to create a public subset, I want to embed a macro in the report that will create private subsets on the user's PC.

To be fair I read your question the same way.

I can't look at your question just at the moment but that first argument doesn't look right to me. That should be the dimension which will be in the format ServerName:DimName. Instead you have two colons in the expression that you've entered. Specifically TM1_Financials:All GL:Account

There's no way that would be right.

[Maianbarian]

Thanks Alan, I see what I was doing wrong. I thought that I would have to specify the cube that I was pulling the data from, but turns out that I don't. Once I dropped the cube "All GL" from the first argument the function works like a charm. Appreciate the help

FYI, my now working code reads:

Code: Select all

Sub createsubsets()

bResult = Application.Run("SUBDEFINE", "TM1_Financials:Account", "My Test Subset", ActiveSheet.Range("A2:A3"))
Debug.Print bResult


End Sub