Proven techniques to infuse more interactivity and formatting ...

Proven techniques to infuse more interactivity and formatting ...

Infuse More Interactivity and Formatting into Your Reports with Your Existing SAP Business Explorer Tools Dr. Bjarne Berg COMERIT 2012 Wellesley Information Services. All rights reserved. In This 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 2 What Well 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 3 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 4 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 5 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

6 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 fields value is also used for Clipping. More about Use Formulas in later sections When unchecked, SAP will relinquish workbook formatting duties to Excel

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 7 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 improves performance 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 8 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 9 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 Step 44 Workbook Workbook display display Step 3 Set read mode 10 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 11 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 5 Step 4 12 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 5 Step 4 13 What Well 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 15 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 Excels 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

15 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 items property beforehand SAP BEx Analyzers 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 16 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 cells 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 17 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 18 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. 19 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 20 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 21 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 22 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

23 What Well 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 26

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 25

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 26 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. 27 Key Figures 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: Querys Planning property Fig. 1: Key Figures Planning Tab

28 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 s tie ry ue Q er op r P

Query Output Query properties pop-up screen Query output now shows a different currency 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 New worksheet for each characteristic value

Each design item and data provider used in the current worksheet is copied to the new worksheet Fig 2. Context menu in the Filter section Workbook Settings shows original data provider duplicated 31 Drill Across Worksheets Query output in each sheet shows the characteristic value Fig 1. Sample worksheet contents after drill across worksheets

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 32 Copy Sheet Feature All design elements in an Excel sheet can be copied to another sheet A time-saving feature Menu location for Copy Sheet function New sheet after invoking copy sheet function

All data providers in the sheet are copied Copied data providers are visible in Workbook Settings 33 Workbook Settings Settings relevant for the current workbook can be made here General functions, themes, settings for variable processing, etc., can be configured here 34

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 Secondary mouse click Change local view in SAP BW 3.x is now embedded in Query properties under the tab Navigational State Fig. 3 Change local query view in SAP BW 3.x 35

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 36

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

What Well 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 40 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 Possible entries button Type a semi-colon (;) for separating individual values Type a number sign (#) for unassigned value Type a hyphen (-) between intervals Direct entry shortcuts ! - ; # 39

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

40 Upload/Download Selections in Selection Screen Upload An easy option for entering large set of selection values Secondary mouse click Invoking Upload selections 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 File contents

Values after file upload 41 Showing Selection Screen Values in the Report The standard template doesnt 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 42 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 43 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 CallBack is Located in DefaultWorkbook module

Varname() has the address of results area 44 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

45 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 Example of a Complex scenario Multiple heading rows

Add the CallBack code to your companys default workbook template 46 What Well 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

49 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 48 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 What Well 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

52 Resources Thanks to Jeevan Ravindran from InfoElixir who delivered this session at Reporting and Analytics 2009 www.bi-expertonline.com/ * Ned Falk, Everything You Need to Know About Formatting Part 1: BEx Analyzer and BEx Query Designer (BI Expert, February 2008). Muke Abdelnaby, A New Way to Change Queries in SAP NetWeaver BI 7.0 BEx Analyzer (BI Expert, February 2008). http://help.sap.com Working in formula mode

http://help.sap.com/saphelp_nw70/helpdata/en/ d3/015369f54f4a0ca19b28c1b45acf00/frameset.htm SAP BEx Analyzer design items http://help.sap.com/saphelp_nw70/helpdata/en/d9/ bfdfb454b4465585cc6334eb89b195/frameset.htm * Requires appropriate credentials to access full text of article 51 Resources (cont.) SAP Community Network Prakash Darji, Setting the Global Workbook Template in SAP NetWeaver 2004s BI or BW 3.x https://weblogs.sdn.sap.com/pub/wlg/4453 Also search in SDN for Prakash Darji You will get very useful articles, blogs and comments

Reporting, Analysis, and Planning section in SCN www.sdn.sap.com/irj/sdn/nw-bi?rid=/webcontent/uuid/ 90ebc563-724a-2a10-408f-87145877992c A portal for accessing good reporting information Requires registration 52 7 Key Points to Take Home

Make use of the new design elements such as check boxes, dropdown lists, etc., to create reports that enhance user experience BExGetData marries Excel formatting and SAP BEx data to produce aesthetically pleasing reports. Make use of this feature. Use shortcuts and favorites in SAP BEx Excel Analyzer for fast entry of selection screen values Use Local formula to enhance the reusability of existing queries Create a company-wide workbook template and save it in a central repository Pay attention to SAPBExonRefreshs code while converting 3.x workbooks to 7.x format Use VB macros and third-party tools as a last resort to solve formatting challenges explore SAP BEx Analyzers capabilities first 53 Your Turn!

How to contact me: Dr. Bjarne Berg [email protected] 54 Disclaimer SAP, R/3, mySAP, mySAP.com, SAP NetWeaver, Duet, PartnerEdge, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and in several other countries all over the world. All other product and service names mentioned are the trademarks of their respective companies. Wellesley Information Services is neither owned nor controlled by SAP. 55

Recently Viewed Presentations

  • Wolf! Becky Bloom passion  If you have passion

    Wolf! Becky Bloom passion If you have passion

    Wolf! Becky Bloom passion If you have passion for something you have a strong feeling for it. admire When you admire a person, you respect or think well of him or her. concentrate When you concentrate, you think very carefully...
  • A Year of Programs Connecting and Customizing Mission-Based

    A Year of Programs Connecting and Customizing Mission-Based

    Our definition of how many members in a small, medium, large branch - arbitrary and each branch decides what programs they can do as branch size is just a guideline, most programs any size branch can implement if they have...
  • Capacitance - UMass D

    Capacitance - UMass D

    Capacitance occurs whenever electrical conductors are separated by a dielectric, or insulating material. Applying a voltage to the conductors can displace the charge within the dielectric. Current does not actually flow through the dielectric.
  • Whole Numbers CHAPTER 1 1.1 Standard Notation; Order

    Whole Numbers CHAPTER 1 1.1 Standard Notation; Order

    Private donations for relief for Hurricanes Katrina and Rita, which struck the Gulf Coast of the United States in 2005, totaled $3,378,185,879. ... 2008, 2004, 2000 Pearson Education, Inc. 1.1. Standard Notation; Order. d. Use < or > for to...
  • Ground Water Resources Commission Wednesday, April 7, 2010

    Ground Water Resources Commission Wednesday, April 7, 2010

    Evolution of the Water Well Driller Program. August 15, 2009 - ACT 437 Statutorily Effective to transfer DOTD well construction program to DNR
  • Gases - Copley

    Gases - Copley

    A plot of the gases showed that all volumes extrapolated to thesame temperature, -273.2 C. This temperature is also known as 0 Kelvin or . absolute zero. The Kelvin scale was devised by English physicist William Thomson, also known as...
  • Industrial Revolution - Deer Creek Public Schools

    Industrial Revolution - Deer Creek Public Schools

    Why England? Agricultural Revolution - increased food production = lower prices = greater purchase power Geo Factors - island nation combined w/ canal building of 1770s - move goods Geo Factors - large deposits of coal and iron - important...
  • Ch 4 Staying alive: systems in action

    Ch 4 Staying alive: systems in action

    Daniel Kerr, Snowboarder Bogong 2015. Other rescues on Bogong 2015. Analyse media repsonses. Media portrayal of a misadventure "Media portrayals of misadventure in outdoor experiences is exaggerated when compared with other mishaps in the community"