Report or Input Form with Many-to-many Relationship between 2 Dimensions (BPC Standard)

Report or Input Form with Many-to-many Relationship between 2 Dimensions: It’s a common question in EPM and BPC NW tags: How to create a report with many-to-many relationship between members of 2 dimensions?

For example there are 2 dimensions: ENTITY and ACCOUNT

For each ENTITY base member there is a list of valid ACCOUNT base members to be used in reports and data entry.

Any ACCOUNT member can be related to multiple ENTITY members.

Simple answer – not possible, there is no dedicated table to store many-to-many relationship of master data. For one-to-many relationship some property can be used, but for many-to-many property will not work!

There is a workaround explained in the following example. This workaround is not perfect (report can be slow for huge number of members) but looks like it’s the only possible solution.

Solution:

We have to create an input form with the matrix: ENTITY in Column Axis, ACCOUNT in Row Axis. Other dimensions are in Page Axis with some dummy members (unused data area). In my sample ENTITY = BE, ACCOUNT = INACCT and TIME = PERIODS.

Result:

This matrix is filled with “1” for the valid combinations of ENTITY and ACCOUNT and data is saved to the model.

Then we can create a real report or input form that will be a combination of special report to get correct list of rows and the real report that will use shared row axis from the special report.

Special report (Default Report 000) is created with ENTITY and ACCOUNT in Row Axis, some dimension in Column Axis (TIME in this sample – same member as in matrix) and Page Axis containing same dummy members as in matrix.

Report Options:

Empty lines will be removed, only valid combinations of ENTITY and ACCOUNT will be shown in the Row Axis.

Also it’s better to set User Option (will help with huge number of records):

Then the real report or input form is created. Row Axis has to be shared with Special Default report:

Page and Column Axis members are selected as required.

Result:

Report or Input Form with many-to-many relationship is created!