How to change query variables with the EPM Excel Add-In

[schweizer_benjamin]

Hi everyone,

we are trying to build up a planning scenario with the BPC 10.1 Unified model.

Due to the fact, that BPC took over the data locking feature from IP, we need to make sure that the fronted tools (Excel and web) can be restricted to defined data slices.

Our first idea was to create a query and use variables with a dummy value as default value for the initial display. The user would be able to select proper elements afterwards and the layout transfers this element to the variable.

I've seen multiple planning application build in WAD that uses this concept, but I'm not sure whether this is still the way to go in a BPC 10.1 Unified model.

Unfortunately, this doesn't work in the EPM Excel Add-In. I was not able to find a EPM formula, function or VBA code to modify query variables. The only possibility is to use the variable screen, which is not very user-friendly. We want to use a EPMSelectMember-formula and transfer the selection to the variable.

We also included a planning function that uses the same variables and we modified them using VBA. However, even if the variables are the same as in the query, the selection will not be transferred to the query.

While in the WAD, in the Design Studio and in BEx this is a standard function, in EPM, apparently this is not possible.

Is there any chance to make this happen or are there any other ideas/concepts to avoid the locking issue with BPC 10.1 Unified?

Thanks in advance for any help.

Regards,

Benjamin

[gregor_dieckmann · Accepted solution]

Hi Benjamin,

before the query is started the variables used in the filter of the BW query have to be set (fix filter); this filter determines the selection used for transaction data locks. So you have to set these variable values in the EPM Add-In variable dialog. In SP19 the dialog also has a history function.

In fact, there seems to be no API to set variables values (except for planning functions and sequences). I only know of an API to open a query (cf. EPM Add-In documentation, SP19). There one can also specify the variable values.

But one still wants to have an F4 help to select the dimensions values (as an end user). So you also need an API to read dimension values ...

Another option is to use AO instead; the only BPC embedded feature not supported in AO is to set the work status. In AO you can also specify the BPC environment/model in the workbook so AO is able to 'consume' the work status.

Regards,

Gregor

[schweizer_benjamin · Accepted solution]

Hi Gregor,

thanks for your reply.

I understand the locking concepts of integrated planning.

However, in WAD, in BEx and in Design Studio it is possible to change the values of variables even if the variables are used in the query filter. The result is, that the query needs the get loaded again and the locks are set again. This is exactly the behavior I want to force EPM to do.

It is still possible to provide a F4 help for the users in a selection that is linked to the master data or another query. However, the variable screen is not necessary in WAD, BEx and DS, because the selected elements can be transferred from one query to another.

Maybe I'll have to test that with AO.

Regards,

Benjamin

[gregor_dieckmann · Accepted solution]

Hi Benjamin,

I see, you miss something like the data provider concept and to be able programmatically set variable values. As mentioned, there is only the variable screen button and the API 'OpenQuery'; whether this closes the old query instance and creates a new one with the new variable values is unclear to me.

I can clarify this.

Regards,

Gregor

[gregor_dieckmann · Accepted solution]

Hi Benjamin,

here is the clarification: the existing API opens a new query instance. An new API is needed to have the same behaviour as in the variable pop-up. This is on the feature list for some of the next SPs.

Regards,

Gregor

[schweizer_benjamin · Accepted solution]

Hi Gregor,

as usual, thanks for your clarifying reply.

I really appreciate it.

We might go with AO for the excel frontend anyway, but the decision has not been made yet.

I read something that EPM and AO will merge in the future.

Is there a release date for a first version of this product?

Regards,

Benjamin