How to replace NOSEND functionality in release V10.0 BPC for Netweaver

[Former Member]

Hi Gurus,

I'am currently facing an issue on a implementation projet of SAP BPC 10 Netweaver on HANA.

Customer used to use BPC 7.5 MS. In every inputschedules they had set up a lot of controls based on the EVDRE "NOSEND" functionality. This function is very easy to use and very helpful to control the data and prevent user from sending inconsistant data to the DB. The cell "NOSEND" is filled with "Y" with Excel Formula when for instance the user has put a too large budget amount.

On the release 10.0 I cannot find any similar function ? Of course I already checked the "controls" functionality ; but nothing to see with NOSEND function because it cannot work with Inputschedules but only with data already send to the DB.

Thanks in advance for your help.

Vincent.

[former_member186338 · Accepted solution]

Hi Vincent,

Unfortunately there is no direct equivalent in EPM formulas. You can change the option Use as input form in the Edit report, but not using formula...

You can set report options by formula:

=EPMReportOptions("000","SomeOption=true")

But

=EPMReportOptions("000";"UseAsInputForm=false") - is not working!

The only alternative is to use the equivalent of EVDRE GetOnlyRange:

=EPMReadOnlyData("000",A4:D8)

where A4:D8 will be read only range....

B.R. Vadim

[Former Member · Accepted solution]

Hi Vadim,

Thanks for your very quick answer.

EPMReadOnlyData seems to be a very good alternative. Unfortunatefly we cannot use this formule within an excel conditionnal formula. Indeed, it seems that once the when read only range data is defined the range cannot be turned again to a "send data" range.

I tryed this for instance : =IF(C13>100, EPMReadOnlyData("000",C13),"")

Once amount is below 100 I can save data ; as soon as I put amount over 100 the range is set to read only ; but then if i try to put again an amount below 100 the range stays in read only.

Do you have any other idea ?

With the Custom Menu (which has disappeared in V10) I assume that this is another major regression which will really affect customers...

[former_member186338 · Accepted solution]

Ups,

I am normally using EPMReadOnlyData to make some range read only, for example I want to make read only number of months columns based on some month number set in some cell. But this approach requires refresh of template and after refresh the read only range is correctly updated.

If you want to prevent user sending data greater then 100, it's better to use Excel validation rule for the cell that will prevent even entering this value.

And for sure, you can create validation or write back badi that will reject records before writing to database depending on value condition.

B.R. Vadim

[Former Member · Accepted solution]

Vadim,

The case > 100 was just the test case. To be more specific the customer need is exactly the following :

User can choose to input budget on a year level : 2014.INP. Then they choose a repartition key which allocate amount on 12 months. But if they select "manual key" they can spread the amount to the 12 months the way they want.

In the former application the "NOSEND" what set to "Y" is the amount on 2014.INP whas no equal to the SUM of amount put on the 12 separate Months ; indeed to avoid the user from sending wrong allocations.

I tryed what you told me but even after refreshing ; in my report once the cell range pass to "read-only" then I cannot find any way to turn is back to "write" ; every records are rejected on this whatever the amount is.

Best Regards,

Vincent

[Former Member · Accepted solution]

Hi Vincent,

I don't understand why this is so complex.  Just use "Read-Only Data" tab and make the column read only.  And add a local member as with the formula =IF(C4?100,EPMSAVEDATAONLY(C4)).  It works perfectly fine.

Thx, Mulak

[former_member186338 · Accepted solution]

Try this:

=IF(C13>100, EPMReadOnlyData("000",C13),EPMReadOnlyData("000",C14))

Where C14 is some other cell. Looks like you have to provide something to EPMReadOnlyData before refresh.

Vadim

[Former Member · Accepted solution]

Hi J Mulak,

Thanks for your answer ; this seems to work indeed. But in a complex Input Form with a lot of columns concerned and dynamic member set i think that, compared to the old NOSEND function, this this very complex to set up and to maintain. I would be glad to find an easier solution....

[Former Member · Accepted solution]

I've just tryed but it still behave the same. Once the read only is activated on my cell the only way to go back to a write mode is to logoff/logon...I cannot understand why. Is there a cache where the readonly data is put ?

[former_member186338 · Accepted solution]

Please provide SP version, on my system with SP 15 I don't have this issue!

Vadim

[Former Member · Accepted solution]

Hi Vadim ;

I'm currently on SP13 ; I will try tomorrow on a SP15 client and let you know.

Thanks.

Vincent.

[former_member186338 · Accepted solution]

Hi Vincent,

I have also performed some tests on my system with EPM SP15 Patch 2:

In the Edit report I have enabled "Keep formula on Data" and "Use as Input Form"

E9 is the cell in report data area, containing formula =E4 (E4 will be the input cell outside report)

E2 contains =IF($E$9>50,EPMReadOnlyData("000",$E$9),"Save Allowed")

If I put 55 in E4 and press Save - I will get "There is No Data to Save" - correct.

Then I change value in E4 to 45 and press Save - get the same "There is No Data to Save"

Then I press Refresh and then Save - result: "1 data record(s) will be sent. Do you want to continue?" And the data is correctly saved to database.

B.R. Vadim

[Former Member · Accepted solution]

Hi Vadim,

I've just tryed to reproduce exactly what you told and I confirm that with SP13 it doesn't work. On last step "Then I press Refresh and then Save - result: "1 data record(s)" >> On my side the message remains "There is no data to save"....

I'm in contact with basis team to get the SP15 on my system.

I will let you know ASAP.

Thanks again for your help.
Vincent.

[former_member186338 · Accepted solution]

You can install SP 15 EPM Add-ON independent of basis - it's a client!

Vadim

[Former Member · Accepted solution]

Thank you Vadim ; it works fine with SP16.

The remaining issue is that we need to refresh to make it works. Indeed ; while refreshing it will also clear other input cells which may not be concerned by the formulas. Furthermore this implies to add an Excute API button in order to combine Refresh and Send Data in a unique action. (not acceptable for the customer to ask the end-user to refresh and then to send data).

Do you have another idea which could enable to make this EPMReadonly soluton works but without refreshing data before sending ?

Vincent.

[former_member186338 · Accepted solution]

Hi Vincent,

"Do you have another idea which could enable to make this EPMReadonly soluton works but without refreshing data before sending ?" - No other solution, Sorry. As I told you - changing report options doesn't support "UseAsInputForm".

Using Excel Validation rule you can try to prevent entering incorrect amounts...

Vadim

[Former Member · Accepted solution]

OK then thank you I have now a workaround !

[former_member186498 · Accepted solution]

Hi Vadim and Vincent,

I know this is an old thread, just adding anothe possibility:

You can set report options by formula:

=EPMReportOptions("000","SomeOption=true")

But

=EPMReportOptions("000";"UseAsInputForm=false") - is not working!

you can achieve it via API

Dim api As Object

Set api = Application.COMAddIns("FPMXLClient.Connect").Object

api.SetSheetOption Sheet1, 1, False  '(true = input sched, false= report)

see please the 42.4.3.1 paragraph of the epm Add-in user guide for all the the sheetoptions available via api.

Regards

     Roberto

[former_member186338 · Accepted solution]

Hi Roberto,

Unfortunately using api you can only change sheet settings, not for individual report. And if the report is set not to inherit sheet settings - then no effect.

Vadim

[Former Member · Accepted solution]

Vadim/Roberto,

This is how I have solved this issue:

=EPMReadOnlyData("000",IF(C13>1,"",OFFSET(A1,1,1)))

The OFFSET doesn't do anything but provide a range outside of the report so it data can be sent.  It can probably be adjusted to be more specific to a row, but my requirements were to cut it all off to force users to review everything in order to send.

Regards,

Di~An

[db1]

You can also use a macro to interrupt the save process based on a custom set of conditions.  Just use the Function Before_Save() event and then set it to false based on custom VBA code. 

Function Before_Save()

     Before_Save = False

end function