BW and Excel Features How-To Guide B I

BW AND EXCEL FEATURES
BW HOW-TO GUIDE
BW and Excel Features
How-To Guide
BUSINESS INFORMATION WAREHOUSE
Document Version 2.0
2000 SAP AMERICA, INC. AND SAP AG
1
Table of Contents
BW AND EXCEL FEATURES ................................................................................................................ 1
TABLE OF CONTENTS ......................................................................................................................... 2
1
INTRODUCTION ............................................................................................................................. 3
1.1
1.2
ORGANIZATION OF MATERIAL ...................................................................................................... 3
REFERENCES ............................................................................................................................. 3
2
CREATING WORKBOOK TEMPLATES IN EXCEL ...................................................................... 3
3
USING VBA FOR INTERACTIVITY ................................................................................................ 6
3.1
3.2
3.3
3.4
4
ADDING VBA FOR DRILLDOWN .................................................................................................... 6
ADDING VBA TO JUMP TO VIEWS ................................................................................................ 9
ADDING VBA TO SORT A COLUMN ............................................................................................. 11
ADDING CHARTS ...................................................................................................................... 13
OVERVIEW OF VBA FUNCTIONS IN SAPBEX.XLA .................................................................. 17
2000 SAP AMERICA, INC. AND SAP AG
2
1 Introduction
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.1
Organization 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.2
References
SAP BW Online Documentation
2 Creating 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.
2000 SAP AMERICA, INC. AND SAP AG
3
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.
2000 SAP AMERICA, INC. AND SAP AG
4
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.
2. 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:
2000 SAP AMERICA, INC. AND SAP AG
5

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.
3 Using VBA for interactivity
3.1
Adding 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”
2000 SAP AMERICA, INC. AND SAP AG
6
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).
2. 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
3. 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)
2000 SAP AMERICA, INC. AND SAP AG
7
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
4. 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”.
5. By clicking on the buttons the system will add and remove the drilldowns.
6. In the next step you can hide the navigational block using the Hide function from Excel and
navigate only via the buttons.
2000 SAP AMERICA, INC. AND SAP AG
8
3.2
Adding 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.
2000 SAP AMERICA, INC. AND SAP AG
9
2. 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
3. Now we can add a fourth button for a drillthrough to R/3. Therefore we have to define the
jump target.
2000 SAP AMERICA, INC. AND SAP AG
10
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.
4. 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.3
Adding 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.
2000 SAP AMERICA, INC. AND SAP AG
11
2. Sort now one of your columns ascending and afterwards descending and switch off the trace
in the BEx toolbar.
3. 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
4. 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.
2000 SAP AMERICA, INC. AND SAP AG
12
You can use this technic also for other functions from the Bex toolbar.
3.4
Adding 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”.
2000 SAP AMERICA, INC. AND SAP AG
13
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.
2. 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.
3. 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))
2000 SAP AMERICA, INC. AND SAP AG
14
myChart.SetSourceData mySource
End Sub
Save your work and test it.
4. 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:
2000 SAP AMERICA, INC. AND SAP AG
15
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.
2000 SAP AMERICA, INC. AND SAP AG
16
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
4 Overview 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
17