Dr. Bjarne Berg COMERIT

Infuse More Interactivity and Formatting into Your Reports with Your Existing SAP Business Explorer Tools Dr. Bjarne Berg COMERIT

InThis Session … • Highlights of this session: • Pragmatic tips and tricks for using SAP Business Explorer (SAP BEx) Excel Analyzer • Useful tips and tricks related to SAP BEx Excel Analyzer • Spotlight on obscure and advanced features • Gain insight into “out of sight” features • Get to know how BExGetData really works • Demystified with examples • SAP NetWeaver® BW 7.x GUI upgrade and 3.x workbooks • Understand the pain points during 3.x workbook migration

What We’ll Cover … • Infusing Interactivity in SAP BEx Workbooks • Creating Aesthetically Pleasing SAP BEx Workbooks • Using Advanced Features of SAP BEx Excel Analyzer • Making the Most of SAP BEx Excel Analyzer — Tips and Tricks • Migrating 3.x SAP BEx Workbooks to 7.x Version • Wrap-Up

Creating Interactive Reports Using SAP BEx Analyzer • Topics covered in this sub-section: • Advanced design elements and features in SAP BEx are explained with an example – Sales Analysis Report • Design elements covered: • Analysis Grid – “Out of sight” features explained • Drop-down box • Radio button • Checkbox

Create an Interactive Report — Initial Steps • Step 1 — Click the Analysis Grid icon • This item provides a link to the data provider (Query) • Step 2 — Click the icon that is placed in cell D5 • This will pop up the Analysis Grid properties window • Click the “Create Data Provider” button Create a data provider

Create an Interactive Report — Linking to a Data Provider • Step 3 — Link Data Provider to a Query • Here select a query or an InfoProvider • Other available options in the step are shown below Query/InfoProvider is linked here Provide Results Offline – Mark this indicator to save the query output inside the workbook. Note: Mark this indicator if you are working in Formula mode. This setting restores the original navigational state saved in the query or query view on the server. In other words, the navigational state saved in the workbook will be ignored. Analysis Grid Properties window

Create an Interactive Report — Analysis Grid Item • Step 4 — Set other relevant settings in the General tab • Settings relevant to Planning and Formula mode are set here Range – Marks the location of the Item in Microsoft Excel. This field’s value is also used for “Clipping.” When unchecked, SAP will relinquish workbook formatting duties to Excel More about Use Formulas in later sections Adjusts the Microsoft Excel print area to the size of the analysis grid In input ready (Planning) queries, marking this field will lock non-input ready cells When marked, no new lines can be entered in Input ready (Planning) queries Mark this field to retain Unit of Measure in Formula mode

Create an Interactive Report — Analysis Grid Item (cont.) Step 4 — Set other relevant settings in the General tab (cont.) The GUI Front End Patch 1000 includes a new feature for the Analysis Grid: + and − characters replace hierarchy icons Advantage – Eliminates shape objects and improvesperformance Open/Close is possible only via context menu Analysis Grid Properties window in GUI 7.x FEP 1000 Report with standard hierarchy icons. Report with new hierarchy icons

Create an Interactive Report — Clipping • Step 5 — Set clipping properties in the Analysis Grid item • This optional step can be used to optimize screen real estate • The output will be “clipped” to the values specified in the Range field • If scroll option is set, a scroll bar appears in the output Scroll bar

Create an Interactive Report — Drop-Down Box • Step 6 — Show a list of Product Groups for selection Step 2 Step 1 – Click on Drop-down box icon Step 4 – Workbook display Step 3 – Set read mode

Create an Interactive Report — Drop-Down Box (cont.) • Step 6 — Insert a drop-down box • GUI FEP 1000 features a drop-down box with an option to select query views in the report • All the available query views for the specified data provider will be available as a drop down Fig 2: Query/Workbook result now shows list of query views Fig 1: New option to select list of Query views

Create an Interactive Report — Radio Button • Step 7 — Provide an easy way to select Calendar Year/Quarter • Insert a radio button item Step 2 Step 1 Step 3 Step 4 Step 5

Create an Interactive Report — Check Boxes • Step 8 — Customize the display of key figures • Use the Check box item Step 2 Step 3 Step 1 Step 4 Step 5

Convert to Formula — Introduction • What is “Convert to Formula”? • This option converts a Key Figure cell of the Analysis Grid Item into an Excel formula • Why do you use “Convert to Formula” option? • To leverage Microsoft Excel’s formatting capabilities • How to switch on “Convert to Formula” in SAP BEx Analyzer? • Inside the Analysis Grid item, get the context menu • Choose the option “Convert to Formula”

Convert to Formula — Features • What happens when “Convert to Formula” is enabled? • Analysis Grid item will be automatically deleted • Context menu of Analysis Grid item disappears • Number format will be set to standard • You can keep the SAP number format by switching on the Analysis Grid item’s property beforehand • SAP BEx Analyzer’s responsibility is to bring in the data • Onus is on MS Excel to format the query output • Once converted, there is no turning back • You have to reinsert the Analysis Grid item and link to Data provider

Data Flow of a Cell with BExGetData Function • Data cell in the workbook contains a business value • The cell has an Excel formula named BExGetData • Each BExGetData cell is connected to a Data Provider DP_1, as shown in the below example • Example: BExGetData("DP_1",$J11,$F12,$G12,$H12) • Each cell’s data fetch is independent of other cells • Data Provider is connected to a query • Example: Z0D_SD_C03_DEMO_Q005 • Query is connected to an InfoProvider • Example: 0D_SD_C03 Zoom cell Excel Formula InfoProvider Cell Query Workbook

Syntax of BExGetData • Mainly three parameters are passed in the sequence • {Data Provider, Column Name, Row Name(s)} • An easy way to represent BExGetData syntax is: • {Data Provider, Key Figure, Characteristic(s)} • Supply the characteristics in sequence separated by a comma • If the characteristic shows as “Text” not “Key” value, then pass the characteristic value in “Text” form • Be cautious while typing in the values. They are case sensitive. • Any typos will result in #NV (No Value) in the output • Three different types of BExGetData syntax

Convert to Formula — Capabilities • What else can “Convert to Formula” do? • You can cut and paste rows of report into different sections • When refreshed, SAP will not reassemble your report • Overwrite and Fetch capability • It gives you dynamic data selection capability • You can overwrite values of a characteristic to another value and get the result of that characteristic • In the above example, copy line 16 (Q1 2003) to line 17. Change value in line 17 from Q1 2003 to Q2 2003. You will notice the change in key figure values.

Understanding Overwriting Capability • Boundaries of data access • The overwriting value must exist within the data selection • Consider this scenario: A report is run for 4 quarters in 2003. It is then converted to formula. • In the output, if you change the cell value from “Q2 2003” to “Q2 2004,” the output will show #NV • However, in the output, if you change the cell value from “Q2 2003” to “Q3 2003,” the output will show the correct value • To extend the boundaries of data access: • Refresh the workbook with selection criteria that covers the data range that you are looking for • For the above example, refresh the report for 2003 and 2004

Convert to Formula — Miscellaneous • To see results offline • When you open a workbook that is saved after the converting to formula, you may find the key figures show #NV (No value) • To avoid this error, check the data provider property – “Provide the Result Offline” • Can I use any query for “Convert to Formula”? • The answer is No • The query should have elements in both row and column

What Is “Use Formulas” • Use Formulas is another method to generate BExGetData in the data cells • It is set as a property of Analysis Grid item • Unlike “Convert to Formula,” “Use Formulas” does not delete the Analysis Grid design item • Access is still available to the context menu functions • Standard formatting is retained • For generating formatted reports, “Convert to Formula” is a better option than “Use Formulas”

Use Formulas — Other Points • Before you check the “Use Formulas” box, remove the “Suppress Repeated Key Values” option • Otherwise, you will see #NV in most data cells • “Use Formulas” will convert data cells to BExGetData mini-queries

Local Formula — To Add a New Key Figure Column • Add a Local Calculation — e.g., Add “Plan vs. Actual %” • Use the option “Local Formula” in the query result section • Use the secondary mouse button to select the “Add Local Formula” option • Enter your formula • You can perform simple arithmetic and some scientific functions • A new column will be appended to the end of the table

Local Formula — Features • How to retain the Local Formula? • In the query output, save the Local Formula as a “view” (query view) • The Local formula will be saved along with the query • Use the saved view (query view) in your workbook

How Can I Create an “Input-Ready” Query (Planning Layout)? • What is an “input-ready query?” • The key figure column(s) in the output of an “input-ready” query can be made available for entering data • Input-ready queries form the basis of manual planning layout in SAP NetWeaver BI Integrated Planning • Manually entered data can be saved back to the cube • This type of query is created on a real-time InfoCube (transactional cube) or at an aggregation Level • A regular query is converted into an “input-ready query” by setting parameters in the Planning tab • Other prerequisites apply for query use in planning. They are beyond the scope of this presentation.

Key Figure’s Planning Tab Settings • Step 1: Set planning options for a key figure (KF) • In “Change data” section of key figure's properties tab • Option #2 or #3 makes KF or Restricted KF (RKF) input-ready • During planning, a lock will be set on this KF or RKF • Disaggregation and types of distribution • Used for “top-down” distribution • Step 2: Set planning options at a query level • For input-readiness, this flag (Fig. 2) must be set • Set in “Query-level” properties Fig. 2: Query’s Planning property Fig. 1: Key Figure’s Planning Tab

Currency Translation • In SAP BEx, you can specify currency translation in 2 places: • During query definition for individual key figure • At run time in SAP BEx Analyzer, using context menu • Currency selection in Query Designer • Select the properties of an amount/price field • Then choose the tab name “Conversions” • Select Conversion type • Target Currency • You can introduce a variable for Target Currency

Currency Translation in SAP BEx Analyzer • In the query output, get the context menu of any amount field • Select Query properties option • Then, choose “Currency Conversion” tab • Select Target currency • Select Translation type Query Properties Query Output Query output now shows a different currency Query properties pop-up screen

Create Worksheets Based on Characteristic Values • In the Filter section, access the context menu using the secondary mouse button • Select  Add drilldown in new worksheets (See Fig 2.) • New worksheets will be created for each value of the characteristics • Each design item and data provider used in the current worksheet is copied to the new worksheet New worksheet for each characteristic value Fig 2. Context menu in the Filter section Workbook Settings shows original data provider duplicated

Drill Across Worksheets • Query output in each sheet shows the characteristic value • Prerequisites • Selected characteristic must not exist in rows or columns • No display hierarchy is active on that characteristic • A maximum of 200 characteristic values will be used to create sheets Fig 1. Sample worksheet contents after drill across worksheets

Copy Sheet Feature • All design elements in an Excel sheet can be copied to another sheet • A time-saving feature • All data providers in the sheet are copied Menu location for Copy Sheet function New sheet after invoking copy sheet function Copied data providers are visible in Workbook Settings

Workbook Settings • Settings relevant for the current workbook can be made here • General functions, themes, settings for variable processing, etc., can be configured here

Query Properties in SAP BEx Analyzer • Query properties — One-stop shop to maintain all settings pertaining to a query • Access Query Properties from the context menu of query output • “Change local view” in SAP BW 3.x is now embedded in Query properties under the tab “Navigational State” Secondary mouse click Fig. 3 – Change local query view in SAP BW 3.x

Properties in the SAP BEx Analyzer Context Menu • The property as shown in Fig. 1 corresponds to the property of that individual object • The result row suppression, result value selection, and display format are set here • Sort by attributes value is possible • Suppressing result rows for all characteristics at once is not supported Secondary mouse click Fig. 1 – Property of 0CALQUARTER

Global Settings • Configure global workbook template, the trace file, local history, SAP GUI, etc. • See “Resources section” for Global workbook template details • You can also temporarily switch on BW statistics for the workbook Behavior tab in Global Settings Configuring Global Workbook Template Result after pressing “Display Statistics” button

Selection Shortcuts — Direct Entry Using Special Characters • Type an exclamation point (!) in the selection screen for entering “NOT EQUAL TO” • Saves 5 to 7 clicks as compared to clicking the Possible entries button • Type a semi-colon (;) for separating individual values • Type a number sign (#) for “unassigned” value • Type a hyphen (-) between intervals Possible entries button Direct entry shortcuts ! - ; #

Selection Shortcuts — Direct Entry — No F4 Button • Type an asterisk (*) as a wildcard character to show a pattern • Allowed only for “select-option” characteristic variables • Type “>=” for “greater than or equal to” • Value Range field — How to enter values directly • Standard behavior — Direct entry is not permitted • Solution — Place cursor in field and press Ctrl+K • Alternative solution • Select Key from the menu Ctrl + K

Upload/Download Selections in Selection Screen • Upload – An easy option for entering large set of selection values • The selections can be uploaded from a text file • Individual selections are separated by semicolon (;) or new line • The selection screen shortcuts such as !, *, etc., can be used • Download of a selection is also possible after entering values Secondary mouse click Invoking Upload selections Values after file upload File contents

Showing Selection Screen Values in the Report • The standard template doesn’t show selection screen values used • 3.x SAP BEx Analyzer showed this by default in the first few rows • Showing report selection helps to interpret the results • Report selection can be shown in columns C and D below Filter • Filter button hides/un-hides columns C and D Fig. 1 – 3.x SAP BEx Analyzer shows selection screen values Fig. 2 – 7.x SAP BEx Excel Analyzer workbook showing selection screen values

Showing Selection Screen Values in the Report (cont.) In 7.x SAP BEx Excel Analyzer, insert two design elements Text Elements  Static Filters Filter Section in Query Designer Navigation Pane  Dimensions Can be used to display Dynamic Filters Default values section in Query Designer Text Element Navigation Pane 42

How to Populate Blank Column Headings in the Report • Challenge: Pivot tables cannot be created if the header is blank • Solution: Write VBA code to copy header title • Code in CallBack macro located in DefaultWorkbook module • Use “Developer” tab (Excel 2007) or press Alt+F11 to access the VBA code Varname() has the address of results area CallBack is Located in DefaultWorkbook module

How to Populate Blank Column Headings in the Report (cont.) • Callback macro coding considerations • Simple scenario — Only one header row • Objective: All the columns in the workbook should have column headings • Sample code for simple scenario is on the take-home CD Before execution of custom code in CallBack macro After execution of custom code in CallBack macro

How to Populate Blank Column Headings in the Report (cont.) • Callback macro coding considerations (cont.) • Complex scenario — More than one header row • Only Key Figures (KF) are present in rows, Characteristics (Char) in columns • Nothing exists in rows; Char and KF are in columns • Complex scenario will require additional coding to check cell styles • Add the CallBack code to your company’s default workbook template Example of a Complex scenario – Multiple heading rows

Conversion of Existing 3.x Workbooks to 7.x • Workbooks are automatically converted when opened in the SAP BEx Analyzer 7.x GUI • Manual intervention is required for workbooks with: • Custom VBA coding on SAPBEXonRefresh • Any hardcoded reference to query ID – “SAPBEXq0001,” “SAPBEXq0002,” etc. • Any hardcoded reference to “SAPBEXqueries” sheet • If SAPBExonRefresh is already present in the 3.x workbook • Add the macro (Fig 2) SAPBExonRefresh to 7.x workbook • Via Workbook Settings Invoke Workbook Setting from 7.x SAP BEx Analyzer toolbar Click on Add Macros to add SAPBEXonRefresh to 7.x workbook

Conversion of Existing 3.x Workbooks to 7.x (cont.) SAPBExonRefresh code changes 1) Add extra parameter (see SAP Note 1258169) 3.x definition Sub SAPBEXonRefresh(queryID As String, resultArea As Range) 7.x definition Sub SAPBEXonRefresh(queryID As String, resultArea As Range, ParamArray varname()) 2) If 'queryID = "SAPBEXq0001"' check is present, then replace with 'queryID = "DP_1"' For MS Office SP2 support Install SAP NetWeaver BW 7.x Front End Patch 902 or 1000 FEP 1000 Patch Data from the ‘About’ screen

Dr. Bjarne Berg COMERIT