Use of Variables in BEX Analyzer Queries

Team-Fly

---

Variables make queries flexible and dynamic. With variables, you set selection values at runtime instead of hard coding in the query definition. These variables are defined in the SAP BW instance, via transaction RSZV or from the main SAP BW menu option. From the SAP BW main menu, select Business Explorer and select the option Maintain Variables and define a new variable, for example, ZCUSTMR, associated with characteristic 0CUSTOMER, as shown in Figure 11-18. This means that when you refer to characteristic 0CUSTOMER, the variable ZCUSTMR is accessible. You can include these variables in the query definition to filter data.

Figure 11-18: Defining Variable ZCUSTMR in SAP BW using Transaction RSZV that Will be Used to Dynamically Set Values for a Customer to Select.

Note 

The creation of variables requires special authorizations. Normally, variables are defined and maintained by the SAP BW application developer or the BASIS administrator. Make sure you have the right authorizations for creating variables.

To add a variable in an existing query, you need to edit the query and assign a variable to a characteristic, as shown in Figure 11-19. Save this query and exit the Edit Query window. The BEX Analyzer refreshes the query and prompts for a Customer. Enter a Customer or use the default value; in this example, the default value is DIGITAL. The query then takes this value, sends the variable value to the OLAP server, and uses it to select data (see Figure 11-18).

Figure 11-19: Customizing the BEX Analyzer Query to Select Customer Data Based on User Input using a Variable. At the Query Runtime, the User is Prompted for Variable Value. This Variable Value is Used to Select Data at the Database Level.

You can define variables for Characteristic, Texts, Formulas, Hierarchies, and Hierarchy nodes. Variables play a key role in implementing slowly changing dimensions, the characteristics of other data objects that change in time. For example, the Material structure often changes. To implement this time dependency, the material must also be stored with valid time periods. Then from the query, a variable, for example ZTIMPRD, is sent to the OLAP processor to select master data valid for the selected time period. To transfer this variable from the query to the OLAP processor, you need to edit the Query properties and enter a variable name in the Key date field, as shown in Figure 11-20. To open the Query properties window, open your query in edit mode, and then click the Query Properties icon.

Figure 11-20: Setting up the Query Global Properties for Time-Dependent (Slowly Changing Dimensions) Data Objects.

Note 

The variable names in the Query property must start and end with &. For example, the Time Period variable ZTIMPRD in Figure 11-20 is recorded in the Query properties as &ZTIMPRD&.

Another thing worth noticing in the Query properties dialog box is the Release for OLE DB for OLAP check box. Only those queries with this checked will be visible to a third-party tool that uses OLE DB for OLAP to access SAP BW. For example, to access data from Business Objects, inSight, Cognos, Brio, or another tool, you must enable the query ODBO.

Often, a single query per spreadsheet is not sufficient to build a complex solution. Instead, worksheets are used as a collection of spreadsheets to build a complete solution; for example, you may want to build a reporting application that uses several queries to fetch data from orders, deliveries, and invoices. It saves retrieved data in several sheets with charts, graphs, and VBA programming that enables you to navigate from one sheet to another based on what the user has selected. With VBA programming, you can call all functions available in the BEX menu bar. Table 11-1 lists such VBA callable functions in SAP BW 1.2B.

Table 11-1: VBA Routines in SAPBEX.XLA to Customize Workbooks

Function Name	(Parameters)
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
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

---

Team-Fly