How to pass Query Variables to Planning sequence in Analysis for Office

[former_member242420]

Hello all,

We have a Integrated Planning workbook, where different Excel form buttons are used to execute Planning Sequences. These button actions are implemented using VBA modules.

What i am trying to understand is "how do we pass Query variables to the PS filter?". When executing the PS i am getting error "Specify a value for Reporting Period XXX"

If we are using same Variable in Query  & in PS Filter,  why does not it consider the value entered through Prompt?

There are settings in Components tab, but there is no-way to assign it directly to a variable. And i am also not sure how I can pass the variable value in VBA code using SETPLANPARAMETER command.

Version:

BW: 7.4 SP8

AFO: 1.4.9

Please let us know, if there is a standard was of doing this.

Thanks & Regards,

vamsi.

[gerd_schoeffl · Accepted solution]

Hi,

Analysis for Office does not automatically map variables in queries onto variables in Planning Sequences. This is only possible for queries (so-called variable merge).

What you have to do is to actively map the variable value in the query onto the variable in the planning sequence.

When you go to the Design Panel in AO and click on your planning sequence then you have a tab (at the bottom of the screen) called 'Variables of Planning Sequence'. Here you find all variables in the sequence. You can either set the variable value to a (fixed) value or retrieve the variable value from a cell in Excel. In order to retrieve the variable value fro a cell (this is what you will need to do ) just mark a cell in Excel (outside any analysis item) and select 'Cell' as source for the variable. The variable value will now be retrieved from the selected cell in the Excel Sheet.

Now you want to link this cell to the variable value used in your query. In the cell enter the formula '=SAPGetVariable("DS_1", "Variable_Name")' ,  replace the name of the data source (DS_1) with the name of the data source containing your query, and replace 'Variable_name' with the name of your variable (in capital letters). The cell should now display the selected value and when you execute the sequence this value will be used in the sequence.    
Best regards,

Gerd

[Former Member]

Hi all,

I know this topic is quite a few days old 😉 ... but I think my question matches best in here.

I understood what Gerd explained ... but for one thing which isn't working in our Excel workbook, I have no real explanation so far:

We're on 1.4.10 and, having inserted a regular query, we can store the prompts in the workbook, and when re-opening it, the initial refresh will use the variables.

When trying to do the same with input-ready queries however, the prompts are not stored in the workbook, EVEN if the checkbox is marked to do so.

Whenever the initial refresh takes place or the variable screen is called manually, all variables are blank.

Is this a bug or a feature?

Thanks for any comments,

Martin

[former_member207028]

Hi Vamsi,

are you able to pass from and to variable parameters for executing planning sequence in analysis office for Microsoft excel. please share how you did it.

Thanks

Daya Sagar

[sheldon_piao]

Hi Daya,

If the variable can contain a range value, it is possible to transfer a range value.
See the following syntax:

Best Regards,

Sheldon.

[former_member242420]

Hello Daya,

The only way to pass variable values to Planning sequences is as Sheldon mentioned. VBA is the only way to do it.

Best Regards,

Vamsi.

[former_member242420]

Thanks for the replies..

@Gerd,

That's exactly what i did now. But i am not sure how safe this is considering any change in the layout (moving cells around) will cause an ERROR in the execution of Planning Sequence.

How about reading the Variable in VBA code and assigning it to PS using SetPlanparameter command?

Best Regards,

Vamsi.

[gerd_schoeffl]

Hi Vamsi,

Well, you can also do it with VBA. Read the variable value:

Sub test()

Value = Application.Run("SAPGetVariable", "DS_1", "my_variable")

End Sub

and pass the value using SetPlanparamter.    

Best regards,

Gerd

[sheldon_piao]

Hi,

Recently, I write a wiki page talking about RSTT trace for AO:

How to Create RSTT trace for Analysis of Planning Objects in Analysis Office

In it, I use two buttons which involves two variables usages for both planning function and sequence.

You may understand it better.

Best regards,
Sheldon.

[Freeter]

Hi Sheldon,

So these two variables are separated, right?  you can set different values for them?

[sheldon_piao]

Hi,

Actually, there is only one variable used in planning function PF_1 and planning sequence PS_1.
You may take it as examples to implement your requirement.

Best regards,
Sheldon.

[adamjab1]

Hi,

I think the only way you can implement such a functionality is customer exit variables and pass these variable values through some table. The reason for this is that probably variable  values are not calculated in one pass in case when you run a sequence from some button in excel workbook/query.

Regards