Maintenance of Same Account member for Different Stores entity

[Former Member]

Hello BPC Experts,

I am working for SAP BPC 10.1 NW ...

I am having one requirement for that in Account dimension one Income Item is common for more then ten Stores, Now i am having problem to get that where i have maintain this kind of Hierarchy in account or need to create one user defined dimension .

As you can see in my account dimension ID INCESTORE, which is common for Store 1,Store 2, STORE 3 ...STORE9.

Note:- I have not given any id for repeated INCESTORE because i do not want to have repeated value in my account dimension and the image is from Excel sheet which is having this account dimension.

Now for for my report there is requirement that if user is selecting Store 1 on that case All related INCOME item should appear in the report with value like below ..

SO now my problem is how to maintain this kind of hierarchy to have repeated Income for different Stores and where i have to maintain ? In a separate User defined dimension or in Account dimension.

Happy Learning ..

Regards,

ANDO

[former_member186338 · Accepted solution]

Please read my new detailed blog about many-to-many relationship!

https://blogs.sap.com/2017/03/04/report-or-input-form-with-many-to-many-relationship-between-2-dimen...

Perform the described steps!

[former_member186338]

Can't comment under your last comment!

Unfortunately I am UNABLE to understand your requirements... Looks like you are doing something basically wrong

[Former Member]

Hi Vadim,

Yes, may be, I am doing some thing wrong, I will again explain the requirement.

I am having an Account & Entity dimension as given below ..

You can see in the Account dimension i am having below given Accounts..

INCESTORE

INCEIMPSTORE

INCECORESTORE

INCEUDSTORE

Now INCESTORE is used by different stores STORE1 TO STROE9

In row number 31 you can seeon tha same report if user is selecting STORE1 then in row its displaying dynamically as

INCESTORE

INCEIMPSTORE

INCECORESTORE

INCEUDSTORE

On the same report if your is selecting STORE2 then only INCESTORE is coming in row dynamically ..

Now my problem is how and where i have to map that X account is map with Y store ?

Please suggest ..

Regards,

ANDO

[former_member186338]

I don't see real reports and real screenshots of the dimension administration!

[Former Member]

Hello Vadim,

Find the dimensions ...

Account --

Entity ..

Report ..

But here problem is at what basis i will display this report as i am not having mapping in between My entity dimension and Account dimension. and this report should dynamic a if i will changes Entity from Store1 to Store2 then it should display only store2 related Acounts .

Regards,

ANDO

[former_member186338]

Then we return to the very beginning!

"i am not having mapping in between My entity dimension and Account dimension."

You have many-to-many relationship between Entity and Account

For STORE1 you have to show some accounts

For STORE2 you have to show some accounts

...

Each account can be shown for number of stores!

This is "many-to-many"

[Former Member]

Hi Vadim,

Yes but the solution which is there in the given link is some things i am not getting sorry for this ..

Just tested the following approach:

Cube dimensions/members

TIME: TMATRIX, normal time members

ENTITY: EMATRIX, normal entity members

CATEGORY: CMATRIX, normal category members

ACCOUNT: AMATRIX, normal account members

FG: FG1,FG2...

RM: RM1,RM2,...

Where TMATRIX,EMATRIX,CMATRIX,AMATRIX - base members created to store FG/RM matrix

FG/RM Matrix is loaded using input schedule:

Page axis (fixed members):

TIME: TMATRIX

ENTITY: EMATRIX

CATEGORY: CMATRIX

ACCOUNT: AMATRIX

Column axis:

FG: all members

Row axis:

RM: all members

"1" was entered on the intersections of FG and RM according to FG specifications.

Reporting:

2 reports were created with shared row axis:

First report:

Page axis (fixed members):

ENTITY: EMATRIX

CATEGORY: CMATRIX

ACCOUNT: AMATRIX

Column Axis (fixed member)

TIME: TMATRIX

Row axis (will be shared with second report):

FG: Context (Member and base members)

RM: ALL base members

In Edit Report -> Filtering was set with criteria TMATRIX=1

Second report:

Page axis

ENTITY: Context: some entity (real)

CATEGORY: Context: some category (real)

ACCOUNT: Context: some account (real)

Column Axis

TIME: Context (Member and base members)

Row axis - from first report!

Result:

First report is executed with suppression of rows by filter.

Then second report will be executed using row axis prepared by first report!

--------------------------------------------------------------------------------

For my case how it will be as i need to create a report as well as input template ?

Regards,

ANDO

[former_member186338]

"For my case how it will be as i need to create a report as well as input template ?"

Sorry, please try to think yourself! I can't do your job...

FG = ENTITY

RM = Account

Instead of filtering use empty rows suppression!

Report Editor -> Options -> Rows: Remove Empty

[Former Member]

Hello Vadim,

I am following the given steps..

having Account ..

Entity ..

Now on report 1... is like this

report 1 filter is ..

Report 2 is having below given selection in which Row axis is common to first report 1..

But i am not getting expected out put which is given below ..

For my Case - FG = ENTITY

RM = Account

Again i gone through the answer which you have given to map many to many ...and i found that ..

FG/RM Matrix is loaded using input schedule:

Page axis (fixed members):

TIME: TMATRIX

ENTITY: EMATRIX

CATEGORY: CMATRIX

ACCOUNT: AMATRIX

Column axis:

FG: all members

Row axis:

RM: all members

"1" was entered on the intersections of FG and RM according to FG specifications.

but i am not getting this point as for my case i have i am having Entity and Account two separate dimension now i to have create third dimension ?

And in third dimension i have to map Entity to Account ?

and where i have to put this 1 ? in a property of Account dimension ?

Regards,

ANDO

[former_member186338]

Read blog!

[former_member186338]

Can you clearly explain what is StoreX - member of what dimension?

[Former Member]

Hello Vadim,

StoreX is a member of Entity dimension.

Regards,

ANDO

[former_member186338]

Then please explain once again what do you want to achieve! Not clear from your explanation...

Provide sample report you want to get.

[Former Member]

Vadim,

According to requirement each store is having related Account - GlobalIncome (Account Type is Income)and few of them are common to n number of stores as given in below given image ..

You can see that INCESTORE which is a Income and common for Store 1 to Store 9,

and in report I have to display like below ..

For Global Income parent member are are n number of child members are there but in the report if user is selecting Store 1 on that case only Store 1 related Global Income should come.

I know that i can maintain it with the Parent H1 but the problem is on that case only Id will be different but description will remains same(which @Ravi has approached me in above given comment) but this approach is not good.

and more over this Global Income will may increase in future for example for this year 2017, they have Income item for Store 1 is 4..

INCESTORE, INCEIMPSTORE, INCECORESTORE, INCEUDSTORE

but it may increase in 2018 from 4 to 6.

Let me know how to maintain how to configure that kind of things in dimension, need to create user defined dimension or we can maintain in same Account dimension .

Regards,

ANDO

[former_member186338]

You are talking about many-to-many relationship between entity dimension member and account dimension member (each store can have number of related accounts and some accounts are related to multiple stores).

I have explained this case number of times. Please read:

https://archive.sap.com/discussions/thread/3654090

[Former Member]

Hi Vadim,

That solution is something different or may be i am not able to relate with my requirement.

Please let me know though i am having a two dimensions

1- ACCOUNT (WHERE INCOME Items are there and one income is common for more then 2 entity)

2-ENTITY (here i am having a list of STORES)

Now if i will have unique id and will have stores details in PARENT H1 on that case also we can resolve this issue ? PLEASE refer below screen ..

You can see "Maintenance Prof enty" is common in the dimension for Store1 to Store9 but i have created unique ID, So i think it will work, Please guide me .

Regards,

ANDO

[former_member186338]

Sorry, but you are doing something absolutely strange!

What for you created second hierarchy in ACCOUNT dimension??

How do you want the final report to look like?

[Former Member]

Hi Vadim,

Sure it's strange, But think that no other option that is why i have asked this question here in SCN.

I need a Input Template where .

If user is selecting Store1

and id user is selecting Store2 then on the same Input template ,

But the problem is how to map this as INCESTORE(Maintenance Prof enty) is common in between Store 1 and Store 2 that is why i have created Parent H2 and used Unique id as

INCESTORE1, INCESTORE2, INCESTORE3, INCESTORE4 ,INCESTORE5 INCESTORE6, INCESTORE7, INCESTORE8, INCESTORE9

But description is same ..Any way to avoid this repetition ?

Here again providing the dimension member snap ..

Regards,

ANDO

[Former Member]

Hi Ando,

You can have another Hierarchy in the same account dimension and there you can maintain Store ID for respective unique account id, but on that case your id will be unique but it will repeat for the same Income description.

Thanks,

Ravi

[Former Member]

Ravi,

But it will repeat the same account type with having different id and same description more then 2 times ..and more over i do not thing so that in account maintaining a Store related Hierarchy is good option.

Regards

ANDO