How to implement AND/OR in SubsetCreatebyMDX

楼上MJJ

There is a dimension Product:
Product line / Product Category / Product subcat/ product

There are dropdown boxes for Product Category and Product Subcat. I know that it is dummy to have two dropdown boxes in the same dimensions in TM1 websheet (excel).

But I would like to use Product Category and Product Subcat as condition to filter product (level 0) through MDX.

I found a function: TM1FILTERBYLEVEL with sample:
p_ProdCat = 'prodcat A';
p_prodsubcat = 'prodsubcat A1';
p_subsetName = 'temp_subset';
SubsetCreatebyMDX(p_subsetName, '{DISTINCT(TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[Product].[' | p_ProdCat | ']}, ALL, RECURSIVE)}, 0))}');


However, how to add an AND condition (e.g. MDX syntax) for p_prodsubcat. Would anyone give me a sample?

Thanks

西山红叶

Why would you want to do that? According to your existing MDX, if a user selects a category, they are going to get all the leaf elements underneath that, which would include all the subcategories that are under that category, unless you want them to be able to select a sub-category that rolls up to a different category at the same time. However, under that scenario would never meet an AND condition and you wouldn't get any results. If you want the user to be able to either pick a category OR a subcategory (which is the only reason I can think of that would need this logic) then you can modify your form to pass either the category parameter, or the sub-category parameter, as appropriate, to the TI process and create your TM1DrillDownMember on that element.

玩美123

Product line / Product Category / "Product subcat + product"/ product

e.g. product should be A0001, A0002, B0001, and there are three product subcats: MEN, WOMEN, BABY. therefore, the 3rd level ("Product subcat + product")  should be: "MEN - A0001", "MEN - A0002", "BABY - A0003". "MEN - B0001"

There are dropdown boxes product category (e.g. prodcat A), and product subcat (e.g. MEN, WOMEN, BABY, but NOT "MEN - A0001", NOR "MEN - A0002"). Therefore, I need to have MDX to implement AND/OR situation if a situation that user select "prodcat A" in Product Category and select "MEN" in product subcat


================
p_ProdCat = 'prodcat A';
p_prodsubcat = 'prodsubcat A1';
p_subsetName = 'temp_subset';
SubsetCreatebyMDX(p_subsetName, '{DISTINCT(TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[Product].[' | p_ProdCat | ']}, ALL, RECURSIVE)}, 0))}');

黄华民

You could combine the elements of the sets using the + operator. Usefully, this excludes duplicates. E.g.

Code: p_ProdCat = 'prodcat A';
p_prodsubcat = 'prodsubcat A1';
sMdx='{TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER({&#91roduct].[' | p_ProdCat | ']},ALL,RECURSIVE)},0)} + ';
sMdx = sMdx | '{TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER({&#91roduct].[' | p_prodsubcat | ']},ALL,RECURSIVE)},0)}';
p_subsetName = 'temp_subset';
SubsetCreatebyMDX(p_subsetName, sMdx);

It's not completely clear what are trying to do, but it seems to do with multiple SKUs having different sizes. Like a red, blue or green t-shirt for a man, woman or child?