SubsetCreatebyMDX

[pmakulski]

I'm trying to create a drill through process that uses a dynamic subset.
I almost have it working.

Code: Select all

SubsetCreatebyMDX('PeopleDrillOrganization1','{ DRILLDOWNMEMBER(  {TM1FILTERBYPATTERN( {TM1SUBSETALL( [Organization] )}, "' | forecastOrg  | '*")}, {[Organization].[' | forecastOrg | ']} ) }'  );

But I seem to have a catch 22.
If the Subset does not exist, it works great.
If the subset exists, it needs to be deleted.
But, the subset is being used by a view, so when I try and use DestroySubset, I'm told I can't because the subset is used by a view.
I have had only limited success in destroying or changing the view (to, say, point to the default subset).

Is there anyway to change the mdx in an existing subset using TI?

How do I get around this?

I don't want to have to create a new subset everytime the drill through is invoked.

[jstrygner]

Hi,

There was some similar topic here. I think it is not going to help you in your case, but I think it is worth to be aware of what was said there.

But I hope this will help you

What I do in drill through processes (where the drill result is a view):
- I use a static subset, because in such a case you can SubsetDeleteAllElements and fill them again with needed values (and no problem with deleting subset that is used by a view).
- To prepare such a static subset I first create a dynamic subset with different name via SubsetDestroy, SubsetCreateByMDX (this is not attached to a view, so no problem here).
- I could use dynamic subset to iterate through its elements and add them one by one to my static subset, but this makes process last significantly long, because after adding every new element to static subset my dynamic subset is "rebuild" by TM1 engine (although result does not change, but TM1 does not know it won't).
- So I first create a sList variable that stores all elements from dynamic subset (you can figure out a separator for yourself or just resign from separator if your elements are of same, fixed length) and then I build static subset basing on this sList variable - in this case TM1 doesn't refresh MDX every time new element to static subset is added.

I always try to include "Dummy" element both to dynamic and static subsets.
* Dynamic, because this way I am sure my subset will never be empty (otherwise my SubsetCreateByMDX function would end with error).
* Static, because otherwise my drill process will not want to open a view, when there are no elements in "Row" dimension.
My "Dummy" element should have no values in the cube, from which I get the drill result and my view has "SupressZeroesOnRows" turned on, so even if there are no values to display, view will appear with no rows presented.

Hope this is not too confusing (all above works on my 9.5.1 HF 17).
Good luck.

[qml]

I have another idea that may help you and it's a little less convoluted than jstrygner's approach (which is very good, BTW).
How about using two subsets in the following way:
- SubsetA is the main subset that is dynamic (well, MDX-based, anyway), but persistent (it's never deleted and is always assigned to your view). In this subset's definition you place a simple MDX formula similar to the below:

Code: Select all

{[dim_name].[SubsetB]}

- SubsetB is a dynamic subset that you delete and recreate using SubsetCreateByMDX on runtime. SubsetA references it, but it's not assigned directly to your view, so there's no issue with having to delete it.

Basically, you'd be utilising the fact that you can reference subsets in the TM1 MDX syntax in the exact same way as you would reference dimension elements.
This is not a tested solution, but I believe it should work. You might need to create a non-empty SubsetB before defining SubsetA so that it's non-empty when you save it.

[jstrygner]

Yup, this one looks even better!

This is the power of brainstorm

[pmakulski]

That looks great. Thanks for the super help.
I'll try these today.

-----
Yup, that Dynamic that points to the Static worked the charm.

[Olivier]

I just stumped into this "old" post this morning and it made my day !

I just tested it on a dev in progress and I find it to be a very good option !!
It makes the drill through processes much smoother to set up when including additional dynamism via subsets.

Thanks qml for sharing this !