Overwrite values in a column with new data based on Variable Screen Input

[Former Member]

Hello Gurus,

Today has been more of a learning experience when it came to working in BPC, for the first time.

I had a series of doubts and rather than posting it one by one, I thought I will bring them all together into one single discussion.

(1)     Is it possible to have a Variable Screen / User Prompt for a BPC report.

     (1.1)     If yes, How do I maintain the Variables for the Variable Screen

     (1.2)     Where do I maintain them

(2)     I have a report that has been setup with Forecast values, and this report is run at the end of every month

Account	Forecast Month1	Forecast Month2
Account1	100	200
Account2	110	210
Account3	120	220

At the end of the month, when the report is run, the report will have data updated for that month.

Report run at the end of Month1 - where Actual data is posted into the report

Account	Actual Month1	Forecast Month2
Account1	456789	200
Account2	123456	210
Account3	789123	220

Kindly provide guidance how to come around this output, I understand for the month end - we need to run the load, but how is it that we can overwrite the Column value Forecast Month1 - Actual Month1, based on the output from the data load; Basically the data in the columns should be overwritten, it could be a Bonus if possible you could tell me how to overwrite Header values as well.

(3)     I would like to publish the BPC reports; can we publish BPC excel reports? How can I publish BPC reports?

I look forward to your expert opinions and responses.

Thank you.

Leonel Sachin

[former_member186338 · Accepted solution]

Sorry, but really hard to understand!

What do you mean by "Variable Screen / User Prompt"???

We use 12 forecast versions to prepare at the beginning of each month.

FC01-FC12

At the beginning of February we copy (using DM) Jan from Actual and the rest months from budget

Then users are able to correct FEB-DEC

Ater correction it's the final version of FC01

At the beginning of March we copy (using DM) Jan-FEB from Actual and the rest months from FC01

...

Vadim

[former_member186338 · Accepted solution]

Each version FC01-FC12 has property PLANMONTH - starting month for planning

Using this property and Excel validation rule we are able to protect Actual months from changes automatically. Also conditional formatting to show input-able months

[Former Member · Accepted solution]

Vadim,

When I talk about variable screen, it is something similar to the BEx Variable screen, that is, when the user runs the report, he / she is prompted to input certain values that are optional/mandatory and based on the input selections the report will run.

Thanks,

Sachin

[Former Member · Accepted solution]

HI Sachin,

The fact that you say you are new to BPC, i am not sure if you have already build reports in EPM.

Ans: Variable Selection

For variable selection you can use EPM functions like (EPMCONTEXTMEMBER - 1 selection only) or Page Axis within the Edit Report itself (Multiple selection possible). This can then be used to populate various reporting option of the selection you made (Member only, basemembers, children etc.)

example of using context member is that you can ask the user only to select the year and you can populate either quarter or month based on the above selection.

Page Axis selection is mainly used as an aggregation factor - Example you want to view revenue statement as an aggregate of 2 products.

Ans: Actual & Forecast Reporting

There are multiple ways in which you can do it but one of the ways is:

1. Build 2 Reports with a shared Row Axis.

2. In Report 1 - Have category (Actual) and Time (Context Member Selection) - Use Context Selection as mentioned in Answer 1 above)

    In Report 2 - Have Category (Forecast) and Time (based on Memberoffset of Context)

With this you can get the Actual and Forecast in 2 different columns and will work dynmically at the end of each month selection of that particular month.

For More reporting options and how to everything above, Please read the coursework EPM BPC 430.

And: EPM Reports Publish

Honestly i have not used this in my projects yet. But you can refer to the below thread and i hope it clarifies your doubt

http://scn.sap.com/thread/3541817

Regards,

Saijes

[former_member186338 · Accepted solution]

There are no variable screens in EPM, you can create something like this yourself with VBA...

But what for?

What model you are talking about - standard or embedded?

Vadim

[Former Member · Accepted solution]

Hello Vadim,

This is a Standard Model.

Ok, then we can ignore the Variable screen, I can load the data from the BW Info provider into BPC cube at the end of the month.

Our forecast is maintained for every term. So when I load the actual data for every month in that term, how will I get the data to reflect / overwrite the Forecast value of the month and then lock the Actual data from being edited.

Regards,

Sachin.

[former_member186338 · Accepted solution]

You load Actual data to the Actual category that will be locked by work status after loading.

Then you copy from Actual category to FC category the required months.

For FC category you also lock Actual moths with work status. In addition to work status lock (will reject any attempts to write data) it's better to prevent data entry on the template level.

I prefer to use Excel Data validation using formula: to compare the column number with the PLANMONTH property of FCx category (can explain details)

Vadim

P.S.

[Former Member · Accepted solution]

Hi Vadim,

Sorry for my ignorance but correct me if I am wrong.

-     I load Actual Data (vai DM Package) to the Actual Category.

-     Once that is done, I use the Copy Package (DM) to copy from Actual Category to FC Category.

In your last statement, validating data using formula, can you kindly explain more about comparing the data.

Regards,

Sachin

[former_member186338 · Accepted solution]

"I load Actual Data (vai DM Package) to the Actual Category" - Yes, load from infoprovider.

"Once that is done, I use the Copy Package (DM) to copy from Actual Category to FC Category" - Yes, Copy DM package or some script logic for the same.

[former_member186338 · Accepted solution]

Data validation to prevent ACTUAL changes (can be set on the formatting sheet for all data columns):

Result (attempt to change 2016.06 Actual data):

[former_member186338 · Accepted solution]

Conditional formatting sample:

[Former Member · Accepted solution]

Thank you Vadim.

I am working on the scenario currently following the guidelines provided by you.

Will get back to you if I get stuck on the way.

In the meantime, I will mark this discussion closed and, once again, thanks a lot for your time and help in my query.

Regards,

Leonel Sachin

[former_member186338 · Accepted solution]

The only disadvantage of this method is data duplication.

Advantages: easy to get total year values, compare forecasts etc...

[former_member186338 · Accepted solution]

"1. Build 2 Reports with a shared Row Axis.

2. In Report 1 - Have category (Actual) and Time (Context Member Selection) - Use Context Selection as mentioned in Answer 1 above)

    In Report 2 - Have Category (Forecast) and Time (based on Memberoffset of Context)"

I do not recommend this method. You will not get year and quarter totals from bpc and you will not be able to see YTD values for the months of forecast.

Vadim

[Former Member · Accepted solution]

Hello Vadim,

Is this possible with just one report? The color change format, alone.

Regards,

Leonel

[former_member186338 · Accepted solution]

"Is this possible with just one report?" - not clear "this"!

Please explain...

[Former Member · Accepted solution]

Sorry for the confusion, that was accidentally entered. Please ignore my message.

[Former Member · Accepted solution]

Hello Vadim,

As per your suggestion, I have used Conditional Formatting to change the color format; I have used Data Validation for locking the cells. But I noticed that whenever I refresh the sheet or when I run a DM to load Actual for another period, the Formatting and Data Validation get eliminated, as in the data doesn't reflect the Conditional Formatting and Data Validation.

Regards,

Sachin

[former_member186338 · Accepted solution]

I have already told you - use data validation and conditional formatting in the correct cells of the Formatting sheet! Then validation and formatting will be applied after report refresh!

DM has no relation to the subject! You have to use different categories - each category has a fixed number of Actual months... Is it clear?

[Former Member · Accepted solution]

Hello Vadim,

Can you kindly elaborate on the the topic. I had applied for each cell the Formatting as well as Validation but when I refresh the sheet, the Formatting and Validation disappear.

[former_member186338 · Accepted solution]

Have you read help how to work with dynamic Formatting sheet?

"28 Dynamic Formatting"

"28.1.1 Defining a Dynamic Formatting Template"

etc...

[Former Member · Accepted solution]

I will read through these sections and come back with my questions. Thank you for your suggestions and advise

[former_member186338 · Accepted solution]

Sample:

To have the report like:

with custom column headers (local members), with protection and formatting input cells with yellow color on the formatting sheet you have to do:

And Conditional formatting:

Vadim