Variable Dimension Subset name in TI

[mnasra]

Hi Experts,

I am trying to write a TI proccess and I want to read a subset by using a variable (instead of hardcoding the subset name in the ti Data source name).

I know how to do that for a cube view using the ViewextractSkipCalcsSet in the Prolog, but I dont seem to find something similar for dimension subsets. ( and I cannot find a control cube for those dimensions hierarchies).

ANy help is really appreciated.
Thank you
Micheline

[qml]

Hi Experts,

I am trying to write a TI proccess and I want to read a subset by using a variable (instead of hardcoding the subset name in the ti Data source name).

I know how to do that for a cube view using the ViewextractSkipCalcsSet in the Prolog, but I dont seem to find something similar for dimension subsets. ( and I cannot find a control cube for those dimensions hierarchies).

ANy help is really appreciated.
Thank you
Micheline

In the case of a dimension subset as your data source you will need these three variables:

DatasourceType = 'SUBSET';
DatasourceNameForServer = sDimName;
DatasourceDimensionSubset = sSubsetName;

In the case of a cube view used as your data source you would write something like this:

DatasourceType = 'VIEW';
DatasourceNameForServer = sCubeName;
DatasourceCubeview = sViewName;

Please read up on ViewextractSkipCalcsSet as I don't think it does what you think it does.

[mnasra]

Hi Qml,

Pardon my translations.
But what I do when I want to extract data from a view in a cube, I create a Temporary view in the prolog using ViewCreate (cube, extractfromhere)
and the 3 viewextractskipcalcet, valueset and zeroset.
I do whatever I want in metadata and data and then
I finish by deleting this view in the epilog). Instead of leaving those ugly z-views to the endusers eyes.

I wanted to do the same thing when a dimension subset is my source data.
I created the subset using MDX but the name is not fixed (it has the year number in it. like now I am closing 2013 so my subset name is ACCOUNT-2013).

How do I say (and where) to TI that my data source is a dimension subset called account-2013
where 2013 is a variable.

Thanks
Micheline

[declanr]

Hi Qml,

Pardon my translations.
But what I do when I want to extract data from a view in a cube, I create a Temporary view in the prolog using ViewCreate (cube, extractfromhere)
and the 3 viewextractskipcalcet, valueset and zeroset.
I do whatever I want in metadata and data and then
I finish by deleting this view in the epilog). Instead of leaving those ugly z-views to the endusers eyes.

I wanted to do the same thing when a dimension subset is my source data.
I created the subset using MDX but the name is not fixed (it has the year number in it. like now I am closing 2013 so my subset name is ACCOUNT-2013).

How do I say (and where) to TI that my data source is a dimension subset called account-2013
where 2013 is a variable.

Thanks
Micheline

Code: Select all

Prolog:

sSubsetName = 'account-' | pYear;
sMDX = Whatever your MDX is (looking at pYear probably);
SubsetCreateByMdx ( sSubsetName, sMdx );

DataSourceType = Subset;
DataSourceNameForServer = sDim;
DataSourceDimensionSubset = sSubsetName;




Epilog;

SubsetDestroy ( sDim, sSubsetName );

Note that "pYear" is the parameter where you pass in the variable.

[mnasra]

Thanks A million.
I never knew about the datasource type commands.
Much simpler than what I was doing for the cube thing.

I think it is going to work.
THank you

[mnasra]

hi qml,

Thanks for being online this sunday.
I know you answered this question in a different thread, but I dont seem to be able to make it work.
It is the subset filter by pattern where the pattern is a variable. I tried almost all combinations but not working for me. Could you help?

merci.
Micheline

SubsetCreatebyMDX(nameasYear, '{TM1FILTERBYpattern( {TM1SUBSETALL([account] )},
" sname ")} ' );

[declanr]

hi qml,

Thanks for being online this sunday.
I know you answered this question in a different thread, but I dont seem to be able to make it work.
It is the subset filter by pattern where the pattern is a variable. I tried almost all combinations but not working for me. Could you help?

merci.
Micheline

SubsetCreatebyMDX(nameasYear, '{TM1FILTERBYpattern( {TM1SUBSETALL([account] )},
" sname ")} ' );

I am assuming that in this case you mean sName is the variable you wish to pass into the mdx code. You have enclosed it within the quotations of the whole string so TM1 can't differentiate it as a variable.

Try:

Code: Select all

SubsetCreatebyMDX(nameasYear, '{TM1FILTERBYpattern( {TM1SUBSETALL([account] )},
 "' | sname |   '")} ' );

Also think about what exactly you are passing in and whether you need to use a wildcard * etc.
Try running a few combinations through the editor manually with the record mdx option turned on and see what it gives you.

Or if its possible you could consider ditching the MDX altogether, do a while loop through the dimension on each element using the scan function (and possibly a DType or AttrS etc) then add them to a static subset if they meet your criteria.

I'm not against MDX per se but there is another option, there is also an extra parameter in the newest versions to through into the SubsetCreatebyMDX to dictate how it handles creating a subset with no elements.

[mnasra]

Thanks a million. And thank you for all the suggestions.
Yes, working perfectly.