cancel
Showing results for 
Search instead for 
Did you mean: 

Mapping combination of accounts and cost centres to a report row

cecilia_petersson2
Active Participant
0 Kudos

Hi,

In BPC MS 10.1, I'd like to create a report with some 200 rows. Each row is a combination of accounts and cost centres. Sometimes they are specific (cost centre 100, 102, 104) sometimes they are in a range (cost centre 100-104), see example.

I've been setting up a test scenario where the ReportRow is also a dimension and where each report row is combined with every valid combination of account and cost centre for that row and the multiplier 1 is posted to it. A script looks at combinations of accounts and cost centres and multiplies the posted amount with the posted number. In this case, if the amount 1000 was posted to account A.7200 and cost centre CC.100, it would be multiplied with 1 so that 1000 would land on the Salaries row, see example.

However, the maintenance is very heavy so I have to find a different solution. Does anybody have an idea on how to solve it, either with this ReportRow dimension or with a different solution? Ideally, I’d like to be able to work with intervals of cost centres. Unfortunately, grouping of cost centres with properties is not an option as there are quite a few combinations in the report.

Many thanks for any input,

/Cecilia

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

Hi Cecilia,

This is always a difficult thing to achieve in BPC due to the expansion rules and getting every combination as you have described.

I have solved a problem like this by creating a separate model whereby instead of having account and costcentre as separate dims, I had a concatenated dim that mean the exact combinations I wanted were the base members of this new dim. Yes this does mean pushing data around between models, but it means that the input form / report will be very easy to manage, and ultimately you may end up with a solution that is easier for users and maintenance.

Just a thought.

Tom.

cecilia_petersson2
Active Participant
0 Kudos

Hi Tom,

Thanks for your reply and sorry about my delayed reply, but priorities were a bit changed in the project.

Your solution would work, but focus for the customer was a very easy way of maintaining the mapping, as one report row can contain, say, 30 accounts and a range of profit centres. In the end I used a lot of Excel functionality combined with data retrieval

  • I created a table where they could map report rows to combinations of accounts and profit centres
  • I created a basic BPC report, retrieving monthly amounts by account and profit centre
  • Then, using Excel SUMIF and a concatenation of combinations of account and profit centre, I mapped the data from the BPC report to the correct row in the new report.

/Cecilia

Answers (0)