Pass the Parameter Bex Analyzer With the Help of VBA

[Former Member]

Hi All,

I have to run more than 1000 reports every month, Now I am entering criteria to the variable manually in pop up window.  Now, how I can pass the parameter i.e. month/Year and some ID in the Bex Analyzer with the help of VBA so that I don't have to manually enter it in the pop up window?

And where is the variable values actually stored in workbook?

Thanks in advance guys.

[Former Member]

Why don´t you save these selections as "favorite selection" in your BEx-Analyzer variable screen for this characteristic ?

[BenedictV]

Hi Mohamed,

Can you please let us know the reason for executing 1000 reports manually? Why are you not using broadcast techniques? You can make use of the TVARVC tables if you can save variants for your input variables.

Benedict

[Former Member]

Hi Vasanth,

     I am new to SAP & Bex. But I can handle VBA Coding . I just want to pass the variables to bex and I have a list varialbe values in excel. I want to know the way to pass those list of variables to SAP Bex. Is there any coding for this ? , How do i save the variant and how can i access those variant ?

There is a bunch of questions, But answer will be very useful. Thanks

[BenedictV]

Hi Mohamed,

Lets keep this simple. Since you want to pass values using VB, so I assume that you have fixed values for your variables (eg. your input values do not change evry month or so)

You can execute your report in analyzer and the input the values for user entry and save variants using the 'Save as New Variant' button.u Next time you execute the report you can access this variant from the drop down for 'Available Variants'.

Benedict

[franois_gyngysi]

Mohamed,

Have a look at this blog http://scn.sap.com/community/business-explorer/blog/2011/06/24/list-bex-analyzer-7x-variables-and-ge...

You may have other techniques but you need to explain a bit more what you're trying to do. Do you have to refresh 1000 times the same report with different variable values? Or do you have 1000 different reports to refresh?

François.

[Former Member]

Hi Vasanth,

Now i got an idea regarding variant, how to create and save at all. But however i needs to click drop down every month manually. that is what i am asking ,

Is that possible to change the variant name automatically and Execute the query ?

So that I shall prepare the reports by using VBA.

[former_member182470]

Check this : http://scn.sap.com/docs/DOC-11805

[BenedictV]

As usual Suman to the rescue

Mohamed, try the option suggested by Suman first. Once you save your broadcast setting, use the program 'RSRD_BROADCAST_STARTER' and give the technical name of the broadcast setting as input.

For your thousand reports you can use the same program 1000 times in a process chain with different variants for the different broadcast settings.

Again, you have answered the 'How'(how you plan to do it), you still have not answeres the WHY?(the reason for executing 1000 reports manually). If you let us know why? then people would be able to provide better solutions.

If you find our answers helpful, then please mark them so.

Benedict

[Former Member]

Yes Francois, Report is same but variable values are different.

It has 10 variable but we won't enter values for all the 10 variables mostly we change the month name and very few creteria (Variable values) Thats why planning to automate.

[franois_gyngysi]

Mohamed,

I would definitively explore the BEx broadcasting option as suggested above by Benedict. You have several options like using variants or using control query to apply filters as explained here http://help.sap.com/saphelp_nw73/helpdata/en/64/c674402a71d162e10000000a155106/content.htm. With broadcasting, you can distribute reports by mail or publish them in a portal.

If you want to use VBA, please follow the instructions in the blog mentioned above. Once you have inserted a button to execute the PROCESS_VARIABLE command, just start recording a macro and do the following: modify variable values in the Excel sheet that contains the PROCESS_VARIABLE commands (e.g. cells C7 and C8 in the blog) and then click on the button you inserted to refresh the report.

When you stop recording, you'll end up with VBA code that looks like that (obviously filename are going to be different as well as cell references. Don't forget to add single quote in front of variable values like month):

    Sheets("Sheet1").Select

    Range("C7").Select

    ActiveCell.FormulaR1C1 = "'05.2013"

    Range("C8").Select

    ActiveCell.FormulaR1C1 = "'06.2013"

    Application.Run "TEST.xls!Sheet4.BUTTON_33_Click"

You can now adapt this VBA code to pass variable values from another Excel sheet or whatever technique you'd like to use.

Hope this helps.

François.

[former_member182470]

Check these links :--------

http://help.sap.com/saphelp_nw04/helpdata/en/f1/0a55f9e09411d2acb90000e829fbfe/frameset.htm

http://scn.sap.com/docs/DOC-1393

http://wiki.sdn.sap.com/wiki/display/VC/How+To...+Integrate+Visual+Composer+Applications+and+BEx+Web...

Regards,

Suman