Showing results for 
Search instead for 
Did you mean: 

BPC 10 NW - Dynamic expansion in columns

Former Member
0 Kudos

Hello Experts,

I have a report with Accounts in the Row Axis and a combination of Entity, Time and Category in the Column Axis.  The Entity Selection in the Column Axis is dynamic – in other words users can select “base members”, “dependents”, “children”, or “member only”.  This area of the report is hidden completely.


Now, I should create local members to calculate variances for each Entity displayed in the hidden section (this variance could be between 2 time periods or 2 categories).  Does anyone know how to make these local members expand with the Entity?  In other words, if the user selects “base
members”, the variances should be calculated for each base member of the Entity, and if the user selects “member only” only 1 variance should be calculated and displayed..  Any thoughts?


Please note – We would like to avoid using VB if possible.


Thanks in advance for your time and help!

Best regards,


Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Vijay,

I've done a similar thing for one of my customers, but without using local members and a small VBA script was required - just a couple of lines:

Step 1:

I have created an object to give the user the opportunity to select the entity (drop down field or what ever you need)

Step 2:

I have created a drop down field where the user can select the display method (BAS, DEP, SELF a.s.o)

Step 3:

Referred to the selected entity I've created a hidden area in a report with some EPM-Reports to retrieve the required master data from entity dimension for each possible view: BASEMEMBERS, DEPENDENTS a.s.o.

Step 4:

After step 3 I got a list for each possible selection of master data.

Step 5:

Now I created a small Excel formula which dynamically provides all entries of the required list and writes them into an Excel variable, which I have created with the names manager. (I used the OFFSET formula in Excel)

Note: If the master data will be updated in the data model, no adjustments are required within the report, since the Excel formula dynamically retrieves all entries!

Step 6:

Now I wrote a small VBA function which concatenates the entries provided by the variable in the format: ELEMENT1,ELEMENT2,ELEMENT3,ELEMENT4, ...)

Note: The VBA function was required since the standard Excel function to concatenate values doesn't support cell ranges.

Step 7:

Now I used the function created in step 6 to fill an Excel cell with the master data in the format provided by the function.

Step 8:

I used the cell from step 7 as reference for the EPMDimensionOverride to update my columns.

Hope, my points became clear and it will help you,


Answers (0)