Excel data validation in input schedule & expansion

[former_member185837]

In my scenario, users should be allowed to choose only from a limited set of values--e.g. 10, or 20 or 30.

As shown in the following table, in the rows I have an expansion on e.g. cost centers. In the columns, I have only one member of the account dimension--e.g. labour cost.

    A                                 B                              C                           

1                                                                      LABCST (account)

2  CC1  (Cost Center)        Administration           Drop-down list

3  CC2  (Cost Center)        Production                Drop-down list

4  CC3  (Cost Center)        IT                             Drop-down list

I've implemented the drop-down list taking advantage of Excel's data validation functionality. I've set validation in cell C2, allowing a list of values (e.g. 10, 20, and 30), This works for cell C2 where, as a result, the user can only plan one of such values for cost center Administration.

I'd like that Excel's validation configuration gets copied also in all other cells during expansion--i.e. cells C3 and C4 in the previous example. This would result in a dynamic input schedule. Unfortunately though, validation is not copied. Only formats and values are copied. So when I choose for instance 20 in cell C2, 20 gets copied in C3 and C4. However, the drop-down list is not available in C3 and C4.

Am I missing something? If expansion does not copy validation, I'm not in a position to implement a fully dynamic template

P.S. I'm on BPC NW 7.5

[Former Member · Accepted solution]

Hi Davide

I don't understand.

Is the standard behavior to expand even validation.

I also tried on release 10 with an EVDRE and never problems on 7.5.

It would be possible to see the input schedule?

I think there are no special settings be made.

Regards

Andrea

[former_member185837 · Accepted solution]

Hi Andrea,

unfortunately in my scenatio EvDRE does not copy validation settings.

Here are few snapshots of an example input schedule. In the following, you can see that I have set validation in cell G14:

So I can choose only those values in the drop-down list:

However, after expanding all, validation is not copied in other cells--e.g. cell G15:

[former_member186338 · Accepted solution]

Hi Davide,

Start with single row (EXPANSION2, ROW, OPERAT, SELF). In the single data cell create required data validation. Change SELF to ANNO=2011. Expand.

Vadim

[Former Member · Accepted solution]

Hi Davide

indeed it seems not to expand, even if you wrote that the colors will drag.

Definitely it's not a standard behavior.

Can you try to do the same thing on a new report in Apshell?

Hi Andrea

PS are you working in Alba???

[Former Member · Accepted solution]

Davide

look this sample created in Apshell, without particular configurations.

The validation is created on first cell

After expansion the validation is recreated on every cell on the range.

So, how you can see, this is a normal behavior.

Try to do the same report on Apshell.

Bye

Andrea

[former_member185837 · Accepted solution]

Vadim Kalinin wrote:
Start with single row (EXPANSION2, ROW, OPERAT, SELF). In the single data cell create required data validation. Change SELF to ANNO=2011. Expand.

That's it! Thanks, Vadim.

For some mysterious reason, you need to start with expansion operator SELF on rows, and then change it to what you need--e.g. PROPERTY=PROPERTY_VALUE, or SELF, DEP, etc.

I still have one (little?) issue, though, that I'll describe in a follow-up post.

[former_member185837 · Accepted solution]

Thanks, Andrea. Your screenshots, together with Vadim's suggestion, clarified it to me.

Specifically:

Andrea Rucci wrote:
[,,,]
The validation is created on first cell

As you have only Total Costs in the rows, I understood that you in fact started with MemberSet=SELF (not SELF,DEP !) in EXPANSION 2. You applied data validation and only afterwards you changed MemberSet to SELF,DEP. Finally you expanded.

In this way, data validation is copied to all other cells. Here's a screenshot of mine:

Data validation has been copied to cell G19 and all previous cells.

So, everything seems great... or is it? Well, unfortunately I still experience one issue.

Validation does not get copied to the last cell, G20 in my example:

Have you checked whether validation did get copied also to Activity Type Costs, in your schedule?

P.S. I don't work in Alba, but near Pino Torinese. Brand Pocket Coffee in my original example is clear enough, isn't it? 

[Former Member · Accepted solution]

Dear davide

I checked.

The expansion is correct on all rows and all columns (with one or more than one).

From your image it seems different on your system, right?

If you have problems with the last row you may try inserting a comma in memberset (SELF, DEP,) so you'll have another blank row and the last should have enhanced validation.

Your report was very clear about your customer:-D A very sweet project

See you

Andrea

[former_member185837 · Accepted solution]

Andrea Rucci wrote:
If you have problems with the last row you may try inserting a comma in memberset (SELF, DEP,) so you'll have another blank row and the last should have enhanced validation. 

Hi Andrea,

yes, adding a trailing comma does work, thanks for your trick!

After the first expansion, that copies validation in all cells, you may remove that comma. Luckily validation is kept, and you don't end up with an unnecessary blank row.

I also tried and changed account in the current view, choosing a node with more base members than the original. I checked validation is correctlty expanded to all new rows.

[Former Member · Accepted solution]

Great

happy for you. Probabily they are some differences beetween different releases.

Have a good day.

Andrea