Filtering in case of mxn relationship in BPC Input Form

[gunesbt]

Hi all,

I have COST_CENTER in the context bar and COST_ELEMENT in the rows.ACCOUNT_TYPE is a property of COST_CENTER and NUMBER is a property of COST_ELEMENT referring the first 3 numbers of COST_ELEMENT.

In addition, COST_ELEMENT has account types,too (like cost center) but 1 cost element may have more then 1 account type. For example 730xxxxxx has 3 account types( 1 , 2 ,3 ) plz see the table below.. So ACCOUNT_TYPE can not be a property of COST_ELEMENT.

Lets say the user selects CostCenterA  and the ACCOUNT_TYPE of CostCenterA is 2. Then what I want is to go the table above and find the COST_ELEMENTS having ACCOUNT_TYPE 2 ( in this case 730,740) then filter the COST_ELEMENTS in rows having the property of NUMBER as 730 and 740.

I tried a combination of epmcontextmember,dimensionproperty and dimensionoverride but failed.

[former_member186338 · Accepted solution]

Example:

You have 2 cells containing 730 and 740 in column (A1 and A2)

You can have 2 cells with ="NUMBER="&A1 and ="NUMBER="&A2 (in B1 and B2)

Then you can concatenate B1 and B2 and put the result as DimensionOverride criteria.

Vadim

[gunesbt · Accepted solution]

Hi Vadim,

I realized that I was very close in the previous thread, you know

It works now, thanks.

Details:

1-I created the table above in input form.

2-Read the acount_type of cost center via epmcontextmember and via vlookup I fetch 730 and 740 and write them to 2 cells lets say Cell 1 and Cell 2. Then into another two cells namely D1 and D2 I write ="NUMBER="&Cell1 ="NUMBER="&Cell2 (If there were 3 numbers then I would write to 3 cells and I have to write epmdimensionoverride in step 3 by considering the maximum number of cells.In this case it is 2).

3- Write the formula =EPMDimensionOverride("000","COST_ELEMENT",CONCATENATE(D1,",",D2))

[former_member186338 · Accepted solution]

You can also use the same method to get a comma separated string as here:

Vadim