BPC 10/ EPM - Copy/Paste Members Functionality into Reports

[Former Member]

Hello,

I have a requirement for an input schedule in an excel spreadsheet BPC 10/ EPM:

- 1st step: fill the input schedule members (rows) with copy/paste functionality;

- 2nd step: when the user opens the spreadsheet after saving the data in the 1st step, he wants the report to display the inserted values;

To acomplish this, I have defined my input schedule with base level members I want to visualize, selected the option "remove emptys" and the sheet option "member recognition".

What happens is that when I copy/paste the members to the input schedule it refreshes the spreadsheet and the values dissapear because they are "empty".

When I click right-button option EPM->Insert Members-> and choose the members, the procedure is the same, the spreadsheet refreshes but the lines don't dissapear, even tough they are empty.

Can you please help me?

Is this even possible? If not, should I write a SAP note to request assistance?

Thanks in advance!

Best regards

[Former Member · Accepted solution]

Hello everyone,

I have finally a answer to this problem.

I have reported the bug to SAP, they have said that the difference between the two options "Insert Member" or "Copy/Paste" is that:

- In copy/paste option, the refresh report event is triggered automatically after the copy/paste; - this is why the empty rows dissapear;

- In "Insert Member" option, the refresh report event is only triggered after the user refreshes data - so the lines don't dissapear.

Proposed solution by SAP:

1. EPM tab -> Options -> User Options -> "Freeze Data Refresh"

     If the user enables this option when he wants to copy/paste the values, the empty rows won't dissapear because the report won't refresh data;

     Disadvantage: it can induce the user into thinking that he has no data, when the option is selected.

Solution proposed by me (more user friendly):

1. If you only have a report in the same sheet (I've tried for several reports and this solution does'nt work - see my other post (http://scn.sap.com/thread/3344216)

     1.1 Create a checkbox entitled "Remove/Show Empty rows";

     1.2 Point the result of this checkbox to a excel cell (for example B29) that you can reference with TRUE (if checkbox selected) or FALSE (checkbox not selected);

     1.3 Build the formula, based on the previous cell, to change the ReportOptions "KeepEmptyRows":

     =IF(B29=TRUE;EPMReportOptions(EPMReportID(E13);"KeepEmptyRows=FALSE");EPMReportOptions(EPMReportID(E13);"KeepEmptyRows=TRUE"))

     Hope this helps!

Thank you for all your help!

[bishwajit_das]

Hi Raquel,

If you want to input the members(i.e your master data) in the row first and display it.You have to select the option 'Keep All'.

Then only you can input the data(ie. transaction data) for the members.

Just use 'Keep All' and 'Member Recognition- ON' in your first step.

Then enter the data beside each member.

Hope it works,

Bishwajit.

[Former Member]

Hello Bishwajit,

Thank you for your reply!

Could you please read my post in reply to Nilajan?

I understood what you said, but if I keep all members the template won't be empty when the user opens the spreadsheet.

Thank you!

[Former Member]

Hello Raquel,

What version of EPM Addin are you using?

Did you try to update to the lastest version of EPM addin available from SAP? (SP13 Pacth 3 released on 22 Feb 2013)

Best Regards,

Telmo

[Former Member]

Hello Telmo,

I am currently downloading the latest patch. I'll try and let you know after!

Thank you!

[Former Member]

Hello Telmo,

I have installed the new patch and the problem is still the same!

Thank you

[former_member186338]

Hi Raquel,

I have the same strange behavior with the member recognition with copy/paste. If I copy and paste 1 member the recognition will work properly, for number of members the recognition will not work at all.

BPC NW 10 EPM SP13

B.R. Vadim

P.S. It happens in case were members are numbers! If I precede each number with ' then copy paste of the multiple numbers will work.

Message was edited by: Vadim Kalinin

[Former Member]

Hello Vadim,

But after the copy/paste (if it works) can you visualize the values you saved for those members, after you close and reopen the spreadsheet?

In response to your problem, can do you have any row that has an ID starting with characters? Maybe you could switch the order.

I am able to copy/paste but only if the option "remove emptys" is not enable. If it is, the value dissapears. In your case, do you have this option enabled?

Thank you!

[former_member186338]

Well, if you select "Remove empty" the system will remove empty members after automatic member recognition and refresh. It's ok.

If you use Insert members, then its a bug in the system - it will not remove the empty line. But it will remove it after first refresh.

B.R. Vadim

[Former Member]

Hello Vadim,

I agree with what you said in a reporting scenario.

In this case it is an input schedule, so it shouldn't remove the members before we inserted values. This way the user can't insert values in the members he copy/pasted.

Thank you for the fast replies and help!

[former_member186338]

Then, why do you use this option ("Remove empty")? If it's an input schedule with empty lines to enter data then what is the profit to have this option enabled? As I already mentioned, the empty lines will anyway disappear after first refresh (with option enabled). And for the input schedule the ability to refresh before entering new data is critical (to get fresh values).

Another issue - if the user will enter data only on some empty lines and save data at this moment the lines not filled will disappear...

B.R. Vadim

[Former Member]

I agree with you 100%.

The profit is that when the input schedule has values saved, that option allows the user to see the data already inserted schedule, without expanding all the members combinations.

Basically it is an input schedule with the chance of inserting members with copy paste and visualizing the data already inserted..

Have I been clear? If not please let me know.

Do you have any other solution for this? Maybe I'm not thinking the right way.

[former_member186338]

Still do not understand, why "Remove empty" is necessary in this case? To my mind, this option is meaningful only in reports.

Without Remove empty you copy, paste, see lines, some lines empty, some not, you can refresh the sheet, no expansion will happen, you can enter values see the result etc...

[Former Member]

Vadim, let me try to explain:

The first time you open the speadsheet where you have your input schedule to copy/paste the members, are there any values displayed?

If not, correct, because the cube is empty.

Then you copy paste the values and save.

The second time you open the input schedule are there any values? No.

I would like the second time to have the values saved in the first time, in the input schedule, and have still the possibility to copy/paste new members.

If I'm still not clear enough please let me know.

Thank you!

[Former Member]

Hi Raquel,

The first time it will be empty, as you mentioned, since there is no data in the cube.

But, if you open the template second time, and if the template still has the same data region, then the previously stored data should be visible. This is the standard behavior of the system.

Are you not able to see that data when you open the same template second time?

[Former Member]

Hello Nilajan,

Thanks for your reply.

My goal is for the template to be empty for the first time - then the user can copy/paste the rows and insert the values. Second time he can visualize the values and still do copy/paste (without changing the template definition)

PROBLEM 1 - If I define a data region and keep all option, the report won't be empty when the user opens the template.

PROBLEM 2 - If I don't define a data region (I only define, 1 member NONE and hide this row) the user sees the template empty for the first time, copy/paste the values and save.

When he opens the second time, the template definition is still the same (1 member NONE and hide this row), this is why the user cannot see the data he inserted.

What I really wanted was to define a data region with the option remove emptys. That would allow the user to see the template empty for the first time and then at the second time he would have values.

The problem with this option is that when the the user copy/pastes the values, the rows dissapear.

I think the conclusion is:

OPTION 1 - no data region (only one member to hide) and the user never sees the data saved, but can do copy/paste (the rows keep and he can insert the values).

OPTION 2 - define the data region, but the user cannot do copy paste because the rows disappear and he cannot insert values.

Thank you so much!

[former_member186338]

Ups!

What "Remove empty" option is doing?

On each refresh, on each expand, on each member insertion (with member recognition) it will remove 100% empty lines (rows or/and columns) and nothing more. The behavior of this option with insert member is a bug.

But this option is useless with input schedule and even confusing. "Use Keep All" for input schedule!

With "Use Keep All":

The first time you open the speadsheet where you have your input schedule to copy/paste the members, are there any values displayed?

If not, correct, because the cube is empty.

If you inserted some members using copy/paste the input schedule will show the values existing in the cube. If the cube is empty - you will see empty lines.

Then you copy paste the values and save.

If you press save to BPC then the values will be stored to database.

If you simply save an Excel file then the values will be saved in Excel file only.

The second time you open the input schedule are there any values? No.

If you save values to BPC then you will see the values.

If you save values only inside Excel file and If you disable automatic refresh on open then you will still see the values and will be able to send them to BPC.

I would like the second time to have the values saved in the first time, in the input schedule, and have still the possibility to copy/paste new members.

If you save values only inside Excel file and want to copy/paste additional members without prior saving values to BPC - then it's not possible because adding new members will always generate refresh event.

B.R. Vadim

[Former Member]

Vadim,

I agree with eveything you said. Altough, in my first sentence I think I have explained it the wrong way:

The first time you open the speadsheet where you have your input schedule to copy/paste the members, are there any values displayed?

If not, correct, because the cube is empty.

What I wanted to say is that I don't want any rows (not values, but rows) to appear in the input schedule. If I "keep all", the rows defined in the data region will expand. That's why I had the option "remove emptys"

Thank you so much for your patient!

[former_member186338]

Why "PROBLEM 1 - If I define a data region and keep all option, the report won't be empty when the user opens the template."???? The report will have members previously inserted and if there is no data in the region - empty cells for values.

What do you mean by empty? Completely empty without members?

Please, provide some screenshots to explain.

Vadim

[former_member186338]

You can insert "blank" word as the only member and you will have one blank line (with member recognition turned on!)

And yes, in future always provide pictures for such questions

Vadim

[Former Member]

Yes I can! But if I do, the user won't see the data saved in the cube, because there is no data region.. That's my problem!

Maybe what I want to do is not possible!

I'm sorry for the trouble Vadim.

I wanted to take pictures, I din't because I'm Portuguese and the template is in Portuguese so I was affraid you wouldn't understand I'm sorry!

[Former Member]

Hi Raquel,

I guess your problem is pretty clear to all of us.

The first time, with suppression, the user copies the entire member set alongwith the data.

Now the problem is when you open the same template second time, the suppression is still on.

In short, you actually want the user to create the entire template, by presenting them a blank template. But, please note that since the user is not saving the template, their copied members will not be retained.

How have you created the initial template? Is it with all the members of your dimension? And after that you did a suppression? So, this will suppress all the line items. Then the user copies the master data alongwith the data, and then saves the data. Is this data available in the BW cube? If yes, then if you open the same template, next time, then the saved data should be visible, provided the template has been created with all the members of the dimension in the row axis.

Hope this helps.

[former_member186338]

Or try to have empty members for rows dimension simply Insert one member and delete it!

[former_member186338]

When the user will insert members in the member recognition mode the data region will be created automatically...

[Former Member]

Hello everyone,

I've made a XML document explaining both options (1 and 2) and respective problems!

Please let me know if it helped!

Thank you so much for your attention

[Former Member]

Hi Raquel,

The document helped in understanding the problem.

Option 1 is not the correct option. Because your template is created on only 1 member. So, even if the user saves the data, next time when the template is opened, it will be opened only for 1 member.

This is without doubt that you need to go with option 2. The reason is:

a. First time, there is no data in the cube, hence all the members are suppressed.

b. User copies, and saves data.

c. User opens the template, and since the row axis has been filtered to all the members of the dimension, it will show the members against which data was saved in step a.

Now, the issue is why is the system suppressing the newly copied line items. I guess you should raise this issue with SAP.

Hope this helps.

[former_member186338]

Hi Raquel,

Now I was able to view your attached document and have some comments:

In the first option the step 4 is absolutely incorrect: "the user doesn’t save the template"

If the user doesn't save the template how the system will know what to show except the first member that was saved in the original template (and this member is actually the scope of the report for this dimension)? If you want to provide users with the ability to add rows to the input schedule then provide them also the way to save the template.

Alternatively, if the user want to see current non empty rows, he have to ask system to show some base members of some defined node and enable option to suppress empty. The user will see non empty rows.

If the next step is to add some rows using copy/paste - then the user have to disable empty suppression because this option is applied to all members including copied. The system have no information to differentiate the new members (refresh is done). By the way, when the member recognition happens the scope changes to static list:

Ex.: Before copy:

Scope for Row dimension:

NODEMEMBER1 (Member and Base level)

After copy and recognition:

Scope for Row dimension:

BASEMEMBER1 (Member Only)

BASEMEMBER2 (Member Only)

...

BASEMEMBERn (Member Only)

COPIEDBASEMEMBER (Member Only)

B.R. Vadim