This blog is part of a series on reporting techniques to help you get more value from SAP BPC.
DROP-DOWN LISTS help make a worksheet more efficient by provided a controlled list of options that make the report or input template respond to preconfigured options.
To create the list use Excel’s Data Validation tool. We are going to take this a step further by populating the list with EPM reports on a ‘List’ tab within the workbook. In the snapshot below we are using drop-down list for Company, Cost Centre, Program, and Account. Each drop-down list will have its own report to populate the list.
COST CENTER LIST: A list of cost centers is created using fixed members that had been input. In this example, the dropdown has a friendly label that is the result of concatenating ID & Description, but the report will only use the dimension ID’s referenced on the list tab by using Excel’s VLOOKUP function to match the concatenated friendly label to the ID from the right row.
PROGRAM LIST is built similarly to cost centre but this time, we’ll go with just a list of IDs. It is using an EPM function to produce the list of members, filtering on a flag account to pick up the correct members dynamically. Any additional members added to the dimension need data sent to this flag to cause the members to be returned on this grid. Users can “maintain” the list by sending in data to the “BPC_FLAG_ACCT” account member. Once sent, the data will drive the inclusion of members in the list.
As a final example, the Company dimension uses a FIXED LIST, which is perhaps the easiest to configure, but this approach has its drawbacks. There is no EPM report and it is manually maintained. Best practice would be to use a property so it is maintained outside the workbook. For a one-off report this is okay, however if a similar list is required on other reports/schedules consider using a property to drive the list contents. With a property we would be able to create a dynamic report driven by that specific property.
As you can probably envision, by leveraging the ability to build dynamic lists and include drop downs in the user interface, you should be able to create a superior user experience that is also very low on maintenance. Doing so successfully requires some forethought and detailed execution. Getting it right the first time may take some practice, but once you build a few of these valuable reports, your users will get the best experience without being an ongoing burden on your administrative resources.
Journal Import & Export Setup Steps - Part 1
How to Calculate Return on Investment for SAP BPC Projects
The Integrity of Data Entered into SAP BPC via Input Schedules
New Features of BPC 10.x Upgrades
Transform Business Planning using BPC v10 on HANA
Deborah Silverman has been with Column5 Consulting for almost 5 years as a Senior Consultant. She is one of our most popular blog writers with her decades of software experience. She has assisted in hundreds of implementations and specializes in SAP BPC.
Deborah Silverman, Senior Consultant, US Team