1. 2References sap bw online Documentation 2Creating Workbook Templates in Excel




Download 53.09 Kb.
Name1. 2References sap bw online Documentation 2Creating Workbook Templates in Excel
A typeDocumentation
manual-guide.com > manual > Documentation

BW and Excel Features BW How-To Guide

BW and Excel Features

How-To Guide


Business Information Warehouse




Document Version 2.0


Table of Contents

BW and Excel Features 1

Table of Contents 2

1 Introduction 3

2 Creating Workbook Templates in Excel 3

3 Using VBA for interactivity 6

4 Overview of VBA Functions in SAPBEX.XLA 17


1Introduction


This document describes features and functions of the SAP BW Business Explorer Analyzer, or BEx Analyzer, that can be used in combination with Mircosoft Excel. You can also connect the BEx Analyzer with your own VBA programs (Visual Basic for Applications). Therefore, the BEx add-in can trigger your own VBA routines or you can control BEx add-in functions from your own VBA routines.

1.1Organization of Material


Chapter 2 explains the fundamental ideas using Workbook Templates in Excel. Please note: We have made this chapter SHORT because we would like you to read and thoroughly understand it – BEFORE you start using the BEx Analyzer and VBA code! This holds no matter if you are an Excel guru or an Excel novice.

Chapter 3-5 gives you ideas of which Excel features may combine especially well with what the BEx Analyzer does in Excel. If you are not using Excel regularly up to now, this material may also give you an introduction into some typical Excel features. However, this short document does not mean to substitute the reading of a dedicated textbook on Microsoft Excel.

This document was written specifically for BW version 2.0B.

1.2References


  • SAP BW Online Documentation

2Creating Workbook Templates in Excel


To create new workbooks that already have your preferred formatting, you can base the new workbooks on a template. A template is a workbook that contains specific contents and formatting that you can use as a model for other similar workbooks. A template can include standardized text such as page headers and row and column labels, Visual Basic macros, and custom toolbars.

You can create additional custom workbook templates designed for specific tasks and projects. Once you've created a template, you can open it as you would any workbook and make any changes you like.

  1. Open a new workbook and design it with default text such as page headers and column and row labels, as well as other formatting you want in new workbooks that based on the template. For example, you can set a n image as background for your sheets.



Select the rows and columns and change the fill color via Format Cells Patterns. Insert, for example, the company logo at the left top of the sheet via Insert Picture From File. Next, turn off the gridlines and row and column headers by selecting Tools Options View in the toolbar.

Now select Tools Customize Toolbars Forms to display the special form toolbar. You can add different controls, such as buttons, and use them to switch between different worksheets. Place for example three buttons on your sheet.






Mark the button using the CTRL-Key, use the right mouse menu und choose the option Format Control. In the Properties Tab-Strip select the option "Don't move or size with cells". This anchors your buttons on refresh of the query results area.





  1. To save your work select the Save Save as new workbook on the BEx toolbar. In the dialog box choose a folder and type in a name for your template.


You can open this workbook by choosing one of the following functions from the Settings menu from the BEx toolbar.


  • New Workbook on embed


With this function, you can determine into which workbook a query is embedded:
        • is selected from list


Select the function is selected from list if you want to choose beforehand, the workbook into which you want to insert the query.
        • is based on permanent template


Select the function is based on permanent template if you want to insert the query into the workbook that you have defined as a permanent workbook template. To do so, use the function Permanent Workbook Template. You can then insert new queries into this kind of permanent workbook template. If you select the function New workbook on embed Is based on permanent template, then all queries are inserted automatically into a new workbook that is based on the permanent workbook template.

3Using VBA for interactivity

3.1Adding VBA for Drilldown



The API (Application Programming Interface) offers us various VBA routines of the BEx add-in, with which you can control the Business Explorer Analyzer functions. In our first example we will add some coding behind the buttons we created in the previous section for a simple drilldown.

The Function SAPBEXsetDrillState changes the drilldown status. The Syntax of the statement is:

Function SAPBEXsetDrillState(newState As Integer, Optional atCell As Range) As Integer

newState = 0” means “do not drilldown anymore”

newState = 1” means “drilldown vertically””

newState = 2” means “drilldown horizontally”

The Cell refers to a filter cell from our navigation block within Excel.
We can determine the drilldown status of a characteristic with the function:

Function SAPBEXgetDrillState(currentState As Integer, Optional atCell As Range) As Integer

The result is returned in the parameter currentState, that must subsequently be transferred to “byRef”. As no byRef parameter transfer is supported when calling up this function with the Excel method "Run", the value can, alternatively, be queried after the call-up of SAPBEXgetDrillState using the help function SAPBEXgetDrillState_currentState.

This means we have to call two functions in sequence to determine the drilldown status:

Run "SAPBEX.XLA!SAPBEXgetDrillState", myState, myFilterCell

myState = Run("SAPBEX.XLA!SAPBEXgetDrillState_currentState")

myState = 0” means “no drilldown”

myState = 1” means “drilldown is vertical””

myState = 2” means “drilldown is horizontal”
Now we can embed a query into our workbook and add the VBA commands for the drilldown.

  1. Embed a Query


Note the Cells for Distribution Channel (Row 10, Column 3), Material (Row 11, Column 3) and Sold-to party (Row 13, Column 3).

  1. Use the right mouse click on the first button and assign the following macro:

Sub Button1_Click()

Dim myState As Integer

Dim myFilterCell As Range: Set myFilterCell = ActiveSheet.Cells(10, 3)

Run "SAPBEX.XLA!SAPBEXgetDrillState", myState, myFilterCell

myState = Run("SAPBEX.XLA!SAPBEXgetDrillState_currentState")

If myState = 0 Then

Run "SAPBEX.XLA!SAPBEXsetDrillState", 1, myFilterCell

Else

Run "SAPBEX.XLA!SAPBEXsetDrillState", 0, myFilterCell

End If

End Sub


  1. On the second and third button we add the same coding, but we change the reference in ActiveSheet.Cells to the other Characteristics.

Sub Button2_Click()

Dim myState As Integer

Dim myFilterCell As Range: Set myFilterCell = ActiveSheet.Cells(11, 3)

Run "SAPBEX.XLA!SAPBEXgetDrillState", myState, myFilterCell

myState = Run("SAPBEX.XLA!SAPBEXgetDrillState_currentState")

If myState = 0 Then

Run "SAPBEX.XLA!SAPBEXsetDrillState", 1, myFilterCell

Else

Run "SAPBEX.XLA!SAPBEXsetDrillState", 0, myFilterCell

End If

End Sub
Sub Button3_Click()

Dim myState As Integer

Dim myFilterCell As Range: Set myFilterCell = ActiveSheet.Cells(13, 3)

Run "SAPBEX.XLA!SAPBEXgetDrillState", myState, myFilterCell

myState = Run("SAPBEX.XLA!SAPBEXgetDrillState_currentState")

If myState = 0 Then

Run "SAPBEX.XLA!SAPBEXsetDrillState", 1, myFilterCell

Else

Run "SAPBEX.XLA!SAPBEXsetDrillState", 0, myFilterCell

End If

End Sub


  1. By selecting a button with the right mouse key you can change the text of the button. Change them into “Distr. Channel", "Material” and “Sold-to party”.




  1. By clicking on the buttons the system will add and remove the drilldowns.




  1. In the next step you can hide the navigational block using the Hide function from Excel and navigate only via the buttons.




3.2Adding VBA to jump to Views


In the second example we add some coding behind the buttons for a jump to a view.

  1. Save a view for every characteristic drilldown.




  1. We change now the coding behind the buttons. The Command

    Run "SAPBEX.XLA!SAPBEXjump", "v", "myView", myRange

restores the state for a query. "myView" is the name that you gave to the view, and the parameter myRange (of type Range) can be used to identify the query where the jump is due to take place. This can be any cell of your query.

Sub Button1_Click()

Run "SAPBEX.XLA!SAPBEXjump", "v", "Distr Channel", ActiveSheet.Cells(11, 3)

End Sub

Sub Button2_Click()

Run "SAPBEX.XLA!SAPBEXjump", "v", "Material", ActiveSheet.Cells(11, 3)

End Sub

Sub Button3_Click()

Run "SAPBEX.XLA!SAPBEXjump", "v", "Sold to party", ActiveSheet.Cells(11, 3)

End Sub


  1. Now we can add a fourth button for a drillthrough to R/3. Therefore we have to define the jump target.





An internal label is assigned to the Receiver-Queries, called "QURYnnnn", where nnnn is the sequence number. Please note: Assignments for the InfoCube are called "Cubennnn". You can see the label in the column Jump Target ID.


  1. We add the following VBA coding to our fourth button:


Sub Button4_Click()

Run "SAPBEX.XLA!SAPBEXjump", "r", "QURY0001", Selection

End Sub
Please note here the two changes in the coding compared to our coding before. The jump target is no longer a view ("v"), here we call an R/3 transaction ("r") and there is no Cell at the end of the statement. The Cursorposition determines the value for the Cell.

3.3Adding VBA to Sort a column



In the next section we add two buttons for sorting the data. If you look at the available command set in Chapter 4, you can find no special command for sorting. We can use the command

Function SAPBEXfireCommand(fCode As String, Optional atCell As Range) As Integer

for this purpose.

  1. In the first step we have to find out the fCode for "sort in ascending order" and "sort in descending order" Switch on the Trace function in the BEx toolbar.




  1. Sort now one of your columns ascending and afterwards descending and switch off the trace in the BEx toolbar.

  2. When you look at the trace you will find the corresponding codes for the sort commands

……

08:25:00 RFC function RSAH_ENSURE_GUI_VISIBLE returned ok

08:25:04 calling OLAP function

- function code: SOAV

- array: Sheet1!$B$15:$D$19

08:25:04 RFC function RSAH_ENSURE_GUI_VISIBLE returned ok

……

……

08:25:10 RFC function RSAH_ENSURE_GUI_VISIBLE returned ok

08:25:12 calling OLAP function

- function code: SODV

- array: Sheet1!$B$15:$D$19

08:25:12 RFC function RSAH_ENSURE_GUI_VISIBLE returned ok
SOAV is the fCode for Sort ascending and SODV is the fCode for descending

  1. Add the following code to your buttons:

Sub Button5_Click()

Run "SAPBEX.XLA!SAPBEXfireCommand", "SOAV", Selection

End Sub

Sub Button6_Click()

Run "SAPBEX.XLA!SAPBEXfireCommand", "SODV", Selection

End Sub
The actual highlighted cell can be adressed in VBA by Selection. There is no need to reference here to any specific column.
You can use this technic also for other functions from the Bex toolbar.

3.4Adding Charts



Interactive charts are charts, which are directly combined with control elements. You can use them to circumscribe the data view. For example, consider the query:



You can now create a chart and with a combo box that refers to all Materials.

  1. Insert a chart and combo box

Select the first two rows and insert the chart.



Activate the Forms toolbar and insert a combo box. While selecting the control with the right mouse button, give it the new name “myDropDown”.



By right mousing on the combo box, you can format it. Choose in the Format Control window the Control tab. The input range is the range containing the cells you want to display in the drop down menu, here the materials. You are only able to select cells in one column and these must be linked together. Click OK to save your work.

  1. Create header area and source area

Select the header of your query (ResultArea) and define a name for this. Select Insert Name Define and type HeaderArea. Do the same for the first data row under the header and give it the name SourceArea.



  1. Link combo box and chart

To change the chart by selecting a different country in the combo box, you have to write a macro. Right click on the combo box and choose Assign Macro. In the following window set the Macros In box to This Workbook. Choose the function New and Excel will automatically start the VBA (Visual Basic for Applications) Editor. You see on the left the projects that are open. Excel will generate your new routine in the module code window as follows:

Sub myDropDown_Change()

End Sub

Complete the routine with the following code:

Sub myDropDown_Change()

Dim myChart As Chart

Dim myDrop As Shape

Dim mySource As Range

Set myChart = Worksheets("Sheet1").ChartObjects(1).Chart

Set myDrop = Worksheets("Sheet1").Shapes("myDropDown")

Set mySource = ThisWorkbook.Names("HeaderArea").RefersToRange

Set mySource = Union _

(mySource, mySource.Offset(myDrop.ControlFormat.ListIndex, 0))

myChart.SetSourceData mySource

End Sub

Save your work and test it.

  1. Refresh query and chart

By refreshing the query, the number of materials may change. Therefore you have to write a routine to assign the combo box to the new material column. When you insert the query in your workbook Excel will generate the following VBA routine.

Sub SAPBEXonRefresh(queryID As String, resultArea as Range)

End Sub

The routine contains the parameter queryID and the definition of the result area as an Excel range. You use this routine to start your own routine by refreshing the query. The ID of your query can be displayed by selecting Properties Information in the BW context menu.


To assign the combo box to the refreshed query, namely SAPBEXq0001, insert the following code:

Sub SAPBEXonRefresh(queryID As String, resultArea as Range)

Dim myDrop As Shape

Dim myRange As Range

If queryID = "SAPBEXq0001" then

Set myDrop = Worksheets("Sheet1").Shapes("myDropDown")

myDrop.ControlFormat.ListFillRange = _

Range(resultArea.Columns(1).Cells(2).Address, _

resultArea.Columns(1).Cells(1).Offset( _

resultArea.Rows.Count-1,0).Address).Address

myDrop.ControlFormat.ListIndex = 1

End If

End Sub
This works now in perfect combination with our buttons. We first have a drilldown into materials:


Now we use the buttons and we change the drilldown to Sold-to party.


As you can see the combo box now contains the Sold-to party values.
Note: If you have a simple chart without controls which refers to the whole query, you need only type the following commands in the SAPBEXonRefresh routine.

Sub SAPBEXonRefresh(queryID As String, resultArea as Range)

Dim myChart as chart

Set myChart = Worksheets("Sheet1").ChartObjects(1).Chart

myChart.SetSourceData resultArea

End Sub

4Overview of VBA Functions in SAPBEX.XLA


The following list shows you a complete set of the available functions in SAPBEX.XLA. For a more detailed description of the individual functions please refer to the BEx documentation.


  • Function SAPBEXgetWorkbookID(wbName As String) As String

  • Function SAPBEXreadWorkbook(wbID As String) As String

  • Function SAPBEXgetErrorText() As String

  • Function SAPBEXsetFilterValue(intValue As String, Optional hierValue As String, Optional atCell As Range) As Integer

  • Function SAPBEXgetFilterValue(intValue As String, hierValue As String, Optional atCell As Range) As Integer

  • Function SAPBEXsetDrillState(newState As Integer, Optional atCell As Range) As Integer

  • Function SAPBEXgetDrillState(currentState As Integer, Optional atCell As Range) As Integer

  • Function SAPBEXrefresh(allQueries As Boolean, Optional atCell As Range) As Integer

  • Function SAPBEXjump(jumpType As String, jumpTarget As String, Optional atCell As Range) As Integer

  • Sub SAPBEXpauseOn()

  • Sub SAPBEXpauseOff()

  • Function SAPBEXfireCommand(fCode As String, Optional atCell As Range) As Integer

  • Function SAPBEXcheckContext(fCode As String, Optional atCell As Range) As Integer


2000 SAP America, Inc. and SAP AG

Share in:

Related:

1. 2References sap bw online Documentation 2Creating Workbook Templates in Excel iconExcel Tutorial 2: Formatting a Workbook

1. 2References sap bw online Documentation 2Creating Workbook Templates in Excel iconHp-ux online Documentation

1. 2References sap bw online Documentation 2Creating Workbook Templates in Excel iconAnnexure-i technical Scope, Techno-Commercial Terms and Conditions...

1. 2References sap bw online Documentation 2Creating Workbook Templates in Excel iconAppendix a – outreach materials & templates outreach material and templates

1. 2References sap bw online Documentation 2Creating Workbook Templates in Excel iconUser/Admin Guide, api guide, and other documentation for online tech support application

1. 2References sap bw online Documentation 2Creating Workbook Templates in Excel iconOnline Documentation for ProLiant Servers Best Practices for vmware...

1. 2References sap bw online Documentation 2Creating Workbook Templates in Excel iconOnline Documentation for ProLiant Servers Best Practices for vmware...

1. 2References sap bw online Documentation 2Creating Workbook Templates in Excel iconThis documentation and any related computer software help programs...

1. 2References sap bw online Documentation 2Creating Workbook Templates in Excel iconAn automated coding and documentation improvement system that uses...

1. 2References sap bw online Documentation 2Creating Workbook Templates in Excel iconOnline Payments: To make a rent payment online, contact your Property Manager to get set up

1. 2References sap bw online Documentation 2Creating Workbook Templates in Excel iconAbstract An Ambitious Functional Consultant with 3+ years of experience...

1. 2References sap bw online Documentation 2Creating Workbook Templates in Excel iconXml publisher Templates by Example

1. 2References sap bw online Documentation 2Creating Workbook Templates in Excel iconDss key Label Templates (English us)

1. 2References sap bw online Documentation 2Creating Workbook Templates in Excel iconDirectory Structure Changes 2 Creating new skins through Themes and Templates

1. 2References sap bw online Documentation 2Creating Workbook Templates in Excel icon[image: /templates/s40 restyle 1/images/sapelink png]

1. 2References sap bw online Documentation 2Creating Workbook Templates in Excel iconThis CommerceWest Bank (Bank) Online Security and Privacy Policy...

1. 2References sap bw online Documentation 2Creating Workbook Templates in Excel iconA workbook on the Fourteen Steps

1. 2References sap bw online Documentation 2Creating Workbook Templates in Excel iconCris training Workbook 09. 10

1. 2References sap bw online Documentation 2Creating Workbook Templates in Excel iconImplementation & Procedures Workbook

1. 2References sap bw online Documentation 2Creating Workbook Templates in Excel icon2Vendor Documentation Documentation can be found on the cd shipped...




manual




When copying material provide a link © 2017
contacts
manual-guide.com
search