Data Validation on a cell in EPM 10

[Former Member]

Hi All,

I wanted to know if there is a way to apply Data Validation in EPM 10. In 7.5 we were setting the memberset of dimensions to self applying the required data validation and later expanding the report or input form based on the requirement, but unfortunately this is no more possible in BPC 10.

Is there any way to apply data validation on a cell.

Thanks,

Sumanth

[Former Member]

Hi Sumanth, Please disregard my first post.  I have found the location and steps to use "Data Validation" in conjunction with dynamic formatting for BPC 10.0 Netweaver.  Data Validation is one of my favorite tools, so I am glad to find the solution. Here it is.

On the “EPMFormattingSheet” you’ll notice that “Data Validation” is greyed out.  Yet, it is an option under the “Use” Column of the “EPMFormattingSheet”.  See image 1 below.

Image 1

Here is where “Data Validation” for the EPMFormattingSheet is located.  See image 2 below.

1. Right click on the Data Cell as shown.
2. Choose “EPM”
3. Choose “Data Validation” and the normal excel
   Data Validation prompts will appear. 

When the worksheet is refreshed with dynamic formatting the cell will now have data validation on it.

Image 2

Again, if this answered your question, then please mark accordingly.

[Former Member]

Hi Sumanth, I encountered a similar issue and am using =EPMCopyRange.  It will copy a cell with Data Validation on it to the data grid.  I found it most simple to use EPMcopyrange for the entire row.

Note:

1.  Remove Data Values from source cells. If you have data values in the source cell remove them, otherwise they are copied into the cells and you cannot see what is retrieved.

2.  Keep formulas.  If you want to keep the formula on the cell, then keep it on the source cells.

3.  I am seeing that data is saving, but on refresh I don't see the value in the cell, but it is there.  Change the formatting or retrieve in another column without the datavalidation and you will see it.

If this answers the question, then please mark the response as such.

[Former Member]

Hi Sumanth,

Can you be more detailed about your requirement?

Thanks

[Former Member]

Hi Venkat,

The basic problem which we are facing is whenever ever we enter #N/A in the input form the record is accepted by the system and the value which will be saved is -2146826246, we want to apply a excel rule to stop sending this #N/A.

Thanks,

Sumanth

[Former Member]

Hi Sumanth,

I am not sure how you are going to achieve this by data validation. Using data validation, you can ensure that the user enters only numbers. Is that what you are looking for?

Unfortunately, the data validation doesnt work in EPM add in. Any kind of dynamic formatting is to be done through the formatting sheet. However, in the formatting sheet, we cannot apply the data validation.

Please raise a message with SAP for this issue. Probably, they will ask you to add this in the idea place.

Hope this helps.

[Former Member]

Hi Nilanjan,

Yes, the purpose of using Data Validation on a cell is to stop user from entering non-numeric figure. As you have listed, in EPM we cannot have data validation on a cell of the input form and even data validation in not possible in format sheet.

I have raised an OSS message regarding the same, waiting to hear from them on this. Thanks a lot for your word of advice in here

Regards,

Sumanth

[tim_atkins]

Hi Gents,

With SP13, Data validation is avaialble in the EPM Formatting sheet. Now waiting for conditional formatting!

Tim

[Former Member]

Ok .. It worked perfectly!

Tks

David

[ashwin_kulkarni2]

Hi Sumanth,

Did you try Excel validation?

There is also UJ_VALIDATION in BI system. You may try there.

- Ashwin.

[Former Member]

Hi Ashwin,

There are couple of problems with excel validation in BPC 10NW. I cannot apply data validation in the Format Sheet of the input form and also the data validation applied on a cell in your input form disappears once we refresh the sheet.

UJ_VALIDATION is obsolete in BPC 10, is UJ_VALIDATION_RULE_LOGIC an alternative for this? I feel that we can handle the issue at sheet level itself without going in for BADI implementation.

The basic problem which we are facing is whenever ever we enter #N/A in the input form the record is accepted by the system and the value which will be saved is -2146826246, we want to apply a excel rule to stop sending this #N/A.

Thanks,

Sumanth