Data validation in Default Logic BPC 10.1

[Former Member]

Hi All,

Is that possible to have following scenario:

In the input template, we have 2 Key figure : QTY and Price. If the quantity is not blank then we want to validate that the PRICE must be entered.

Can we put the validation in the default logic to say to the system if (quantity is not blank and price is blank) then system will not save the data ?

Thank you in advance.

[former_member192555 · Accepted solution]

It is one or another?

Either you are entering Qty or Price? Could you please also tell us your dimensions? We are talking about Standard Environment, not Embedded?

[Former Member · Accepted solution]

Hi Emiliyan,

We are using Standard Environment.

The validation only if the user entering Qty then comment should be entered (and not vise versa).

(note : sorry..it's not price...but comment)

Our dimensions are:

• Entity
• Reporting Currency
• Account
• Time
• Key figure
• Category

Thanks.

[former_member192555 · Accepted solution]

Hi,

If we are talking about comment based on particular intersection, but not all available members, we could use combination of EPMSAVEDATA and EPMSAVECOMMENT. I am not sure if that is the best option as that is the only one I see now.

Column E - Key Figure (Plain column)

Column F - Comment (Plain column)

Column G - If F ="", true = "", False = EPMSAVECOMMENT(F)

Column

Column H - If F=::, true = "", False EPMSAVEDATA(E)

In those cases if you have entered data without comment, the report will show there is no data to save.

[former_member186338 · Accepted solution]

Sorry, but "

The validation only if the user entering Qty then comment should be entered (and not vise versa).

(note : sorry..it's not price...but comment)" - not clear!

Can you provide a screenshot of the desired result?

"validation in the default logic" - is not relevant for this case, default logic is executed AFTER the data is saved to the cube.

Excel data validation will work!

Vadim

[former_member186338 · Accepted solution]

P.S. And you can always perform validation in write back badi!

Vadim

[Former Member · Accepted solution]

Data entry by user :
	Comment	QTY
Account A
Account B		3
Account C
Account D	Test	5
Account E
After enter the above data, user click button "SAVE".
The expected result is system to show an error because Account B has QTY= 3 without any comments.
The comment is mandatory if QTY <> ""

I want to avoid BADI or macro as much as possible.

How do we force user to enter the comment if the QTY<>""? Please correct me if I am wrong, I don't think the data validation in EPM can solve this requirement.

[former_member186338 · Accepted solution]

The only thing you can do - create Excel data validation rule that will prohibit any data entry in QTY column if Comment cell is empty

For the rest - VBA macro or badi is required (not sure if itwill be possible to do in badi).

Vadim

[former_member192555 · Accepted solution]

U-one U-one,

You will be able to achieve it with my suggestions in previous post.

Otherwise VBA could be an option too... But I am not sure how convenient will be with VBA to prohibit entering QTY without comment. The common business usage will be first QTY and then comment...

[Former Member · Accepted solution]

How do we do it in the data validation?

In the formatting for specific member, I add QTY and how do I set the data validation to prohibit any data entry in QTY column if comment cell is empty?

[former_member186338 · Accepted solution]

Like:

Vadim

[former_member186338 · Accepted solution]

EPMSAVEDATA is not a good option in general...

VBA is OK, the code will be executed before save and cancel save in case of missing comment!

Vadim

[former_member192555 · Accepted solution]

Hi Vadim,

What observations do you have against EPMSAVEDATA? Performance?

BR,

Emiliyan

[former_member186338 · Accepted solution]

Yes performance and the idea of EPM Input form report is missing!

[Former Member · Accepted solution]

Hi Emiliyan,

Thank you for the reply.

The suggestion in the previous post is also workable but...if some data has comment and others do not have comment then system will still save data for the one which have comment. Most of the users, they will not check how many data have been saved. They will think that all data have been saved.

[former_member192555 · Accepted solution]

Yep, good point!

[Former Member · Accepted solution]

why does it not work in EPMFormatingsheet as below?

[former_member186338 · Accepted solution]

Incorrect reference in the Data Validation in the Formatting sheet. You have to use cell references in the Formatting Sheet - like:

=E35<>""

if you format cell F35 in the formatting sheet!

Vadim

P.S. You screenshots are unreadable...

[Former Member · Accepted solution]

For example,

I have following input form:

The validation is : if Cell L9 is empty then user cannot key in any value in cell P9.

In the EPMFormatting sheet, I do following:

First, add member property in the column as shown below:

Second, add EPMValidation as shown below: (formula = K9<>"")

still...it doesn't work. In the input form, in column P, it will display formula = U1048488 <>"".

(my understanding, it should show formula = L9<>"", rite?)

P.S : Please kindly let me know if the screenshot is still blur. Thanks.

[former_member186338 · Accepted solution]

Still incorrect!

"The validation is : if Cell L9 is empty then user cannot key in any value in cell P9" - Ok!

"Second, add EPMValidation as shown below: (formula = K9<>"")" - INCORRECT

You apply this validation rule to the cell - something like F35 on the formatting sheet!

The correct formula will be:

=B35<>""

where 35 is the "Quantity" line row number on the formatting sheet.

you can also use:

=$K35

For fixed K column!

Vadim

P.S. Screenshots are fine now!

[Former Member · Accepted solution]

Ok...got it. It works now. Thank you very much.