How-to Guide SAP NetWeaver ‘04s

How To … Model SAP BI System Hierarchies in Visual ComposerVersion 1.01 – July 2006 Applicable Releases: SAP NetWeaver ’04s SPS07 or higher

© Copyright 2006 SAP AG. All rights reserved. No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. The information contained herein may be changed without prior notice. Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors. Microsoft, Windows, Outlook, and PowerPoint are registered trademarks of Microsoft Corporation. IBM, DB2, DB2 Universal Database, OS/2, Parallel Sysplex, MVS/ESA, AIX, S/390, AS/400, OS/390, OS/400, iSeries, pSeries, xSeries, zSeries, z/OS, AFP, Intelligent Miner, WebSphere, Netfinity, Tivoli, and Informix are trademarks or registered trademarks of IBM Corporation in the United States and/or other countries. Oracle is a registered trademark of Oracle Corporation. UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group. Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame, and MultiWin are trademarks or registered trademarks of Citrix Systems, Inc. HTML, XML, XHTML and W3C are trademarks or registered trademarks of W3C®, World Wide Web Consortium, Massachusetts Institute of Technology. Java is a registered trademark of Sun Microsystems, Inc. JavaScript is a registered trademark of Sun Microsystems, Inc., used under license for technology invented and implemented by Netscape. MaxDB is a trademark of MySQL AB, Sweden.

SAP, R/3, mySAP, mySAP.com, xApps, xApp, SAP NetWeaver, 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. Data contained in this document serves informational purposes only. National product specifications may vary. These materials are subject to change without notice. These materials are provided by SAP AG and its affiliated companies ("SAP Group") for informational purposes only, without representation or warranty of any kind, and SAP Group shall not be liable for errors or omissions with respect to the materials. The only warranties for SAP Group products and services are those that are set forth in the express warranty statements accompanying such products and services, if any. Nothing herein should be construed as constituting an additional warranty. These materials are provided “as is” without a warranty of any kind, either express or implied, including but not limited to, the implied warranties of merchantability, fitness for a particular purpose, or non-infringement. SAP shall not be liable for damages of any kind including without limitation direct, special, indirect, or consequential damages that may result from the use of these materials. SAP does not warrant the accuracy or completeness of the information, text, graphics, links or other items contained within these materials. SAP has no control over the information that you may access through the use of hot links contained in these materials and does not endorse your use of third party web pages nor provide any warranty whatsoever relating to third party web pages. SAP NetWeaver “How-to” Guides are intended to simplify the product implementation. While specific product features and procedures typically are explained in a practical business context, it is not implied that those features and procedures are the only approach in solving a specific business problem using SAP NetWeaver. Should you wish to receive additional information, clarification or support, please refer to SAP Consulting.

1 Business Scenario You have hierarchies enabled in an SAP BI system query, and you’d like to use Visual Composer to model hierarchy display and limited navigation into an interactive application which you deploy into Adobe Flash format on the portal.

2 Introduction This document provides detailed instructions on how to model hierarchies in Visual Composer. In a step-by-step example, we show you how to visualize hierarchies and define simple hierarchy navigation via modeling the elements in Visual Composer. As of the current release of Visual Composer in NetWeaver 2004s SPS07, Visual Composer’s table view element does not support a tree display, which is typically required for interactive hierarchy navigation. We explain a work-around solution in this paper with which you can model limited hierarchy support. Hierarchies are supported only using the SAP BI Connector to access an SAP BI system. SAP BI systems configured on the portal with the BI XMLA Connector or the BI ODBO Connector don’t support this hierarchy workaround.

2.1 Prerequisites All you need to model this scenario is delivered in SAP NetWeaver 2004s SP Stack 07 or greater. In particular:

• SAP NetWeaver Visual Composer • SAP NetWeaver BI System, with a user on this system:

SAP BW 3.5 systems SP15 or greater, or SAP BI systems 7.0 or greater. • SAP BI System is defined with the SAP BI Connector in the Portal System

Landscape • SAP BI query with hierarchy display enabled (see Enabling SAP BI System

Hierarchies, below) Some knowledge of Visual Composer modeling and the SAP BI Web Design API is helpful.

Note:

There are different ways to create hierarchies in SAP NetWeaver BI. This paper assumes an entire hierarchy is constructed from one InfoObject (including the nodes). If the nodes in your hierarchy are from different InfoObjects, are text nodes, or your underlying InfoObject is a referenced InfoObject, then you will need to adjust the Web API commands used in this paper accordingly.

2.2 Enabling SAP BI System Hierarchies Visual Composer relies on a hierarchy setting saved in an SAP BI query to know that a hierarchy is enabled for a characteristic. You can switch the display of hierarchies on or off in Visual Composer by enabling or disabling this setting. Hence, before modeling the hierarchy solution in Visual Composer, we explain how hierarchies are defined in the SAP BI query.

1. Launch the BEx Query Designer and open a query that contains a characteristic that has a hierarchy associated with it. In our case, we start with SAP Demo query DalSegno Company Revenue Analysis (0D_DX_M01_Q0002) and move the Industry Sector DB SIC characteristic (0D_DBSIC1) to the rows.

2. Evoke the Properties dialog box for a characteristic (BW 3.5 systems) or display the Hierarchy tab on the Properties pane (BI 7.0 and above):

Figure 1 – Properties Hierarchy Pane in Query Designer 7.0

3. To enable hierarchy display, select the Activate Hierarchy Display checkbox (BI 7.0) or the Active checkbox in the Display Hierarchy section (BW 3.5).

4. Save your query with the hierarchy active. Note the technical name of the characteristic, in this case 0D_DBSIC1. We will use this while modeling the hierarchy in Visual Composer.

2.3 For More Information

• For more information about the BEx Query Designer, see the online documentation at: http://help.sap.com/ → SAP NetWeaver → SAP NetWeaver 2004s → SAP NetWeaver by Key Capability → Information Integration by Key Capability → Business Intelligence → BI Suite: Business Explorer → Query Design: BEx Query Designer

• For more information about the Web Design API for hierarchies, see the online

documentation at: http://help.sap.com/ → SAP NetWeaver → SAP NetWeaver 2004 → Information Integration → SAP Business Information Warehouse → BI Suite: Business Explorer → BEx Web → Web Application Design: BEx Web Application Designer → Command URLs → Data Provider - Commands → Operations for Display Hierarchies

3 The Step By Step Solution To model hierarchies in Visual Composer, we perform these steps:

• Model the basic elements • Add drill buttons to the table view • Program the drill down action • Program the drill up action • Deploy the model and interact with the application in the portal

3.1 Model the Basic Elements 1. Launch Visual Composer in the

portal in which your hierarchy-enabled query is saved.

2. Open an existing model in which to model the hierarchy, or create a new model.

3. Navigate into an existing iView or create a new one and navigate into it.

4. From the Find Data Services panel, select the SAP BI system in which your query is located, and locate the query that contains the hierarchy-enabled characteristic.

For background, note that for each hierarchy-enabled characteristic, two additional fields are created in the Result folder for an SAP BI query. In our example, the fields Industry_Sector_DB_SIC_node_level and Industry_Sector_DB_SIC_node_status are generated because the Industry_Sector_DB_SIC characteristic has hierarchy display enabled. The fields provide the following hierarchy functionality:

• Industry_Sector_DB_SIC_node_level provides the value for the hierarchy node level of the selected row. The root node is level 1.

• Industry_Sector_DB_SIC_node_status provides the value that corresponds to the hierarchy node drill state:

o E – expanded o C – collapsed o L – leaf node

5. Drag the query to the Visual Composer storyboard.

6. Connect a start point to its Input port.

7. Connect a table view to its Output port, and select the fields you want to display in the table. Make sure that your hierarchy-enabled characteristic is selected as one of the fields (you do NOT need to select the special *_node_level and *_node_status fields).

3.2 Add Drill Buttons to the Table View 8. Double-click the table view to

access the Configure Element task panel.

9. Select Show in the Toolbar dropdown list to enable toolbar display for the table.

Next, we model two buttons that will perform hierarchy navigation. 10. Click Edit to edit the toolbar. 11. In the Create Toolbar Buttons

dialog box, click the Add Button button to create two buttons with the following settings: Button name=Drill Down Action type=Custom action Custom action=DRILLDOWN Button name=Drill Up Action type=Custom Action Custom action=DRILLUP

12. Click OK to save the toolbar with the new buttons and close the dialog box.

3.3 Program the Drill Down Action 13. On the Storyboard, drag a

line to connect the Out port of the table view to the Input port of the query.

14. Select this line, and in the Configure Element task panel, select drilldown from the Event name dropdown list.

15. At the bottom of the task panel, select the Assigned Value column next to the WEBAPI field, and in the dropdown list, select Enter formula:

16. In the Assign Value dialog box, enter the Web Design API commands that will drill to the second hierarchy level and at the same time filter by the selected hierarchy node. Start with the formula to the right as a template:

'FILTER_NODE_IOBJNM=CHAR_TECH_NAME;FILTER_VALUE='&CHAR_key&';FILTER_COLLAPSE= ;FILTER_IOBJNM=CHAR_TECH_NAME;CMD_1=CMD%3DDRILL_TO_LEVEL%26LEVEL%3D2%26IOBJNM%3DCHAR_TECH_NAME%26DATA_PROVIDER%3DDP;'

(Note: There is a space after FILTER_COLLAPSE) See Prerequisites section above for notes, and Appendix: SAP BI Web Design API Commands for more Web API information.

17. Replace the three instances of CHAR_TECH_NAME with the technical name for your characteristic (in our case, 0D_DBSIC1 – see Enabling SAP BI System Hierarchies, above).

CHAR_key represents the key of the currently selected characteristic member. You replace this with your value, prefixed with the ID of the table view, by using the data fields provided: 18. Expand the Data Fields

node, then expand the Output Table node.

19. Drag the key field of your hierarchy-enabled characteristic into the text editor and place it in the correct location in the formula.

At this point, your formula looks something like what’s to the right. 20. Click Check to validate the

formula, and if valid, click OK to save the changes and close the dialog box.

3.4 Program the Drill Up Action 21. Repeat steps 13-20 to create

a drill up event. At step 16, use the Web Design API command to drill to the level of the currently selected hierarchy node, without filtering. Start with the formula to the right as a template:

22. Just as with the drill down commands, replace CHAR_TECH_NAME with the technical name for your characteristic (it appears in one place).

'CMD_1=CMD%3DDRILL_TO_LEVEL%26LEVEL%3D'&NSTR(CHAR_node_level,'B')&'%26IOBJNM%3DCHAR_TECH_NAME%26DATA_PROVIDER%3DDP;'

CHAR_node_level represents the node level of the currently selected characteristic member. Replace it with the right node level field of your table view: 23. Expand the Data Fields node,

then expand the Output Table node.

24. Drag the node level field of your hierarchy-enabled characteristic into the text

editor and place it in the correct location in the formula.

At this point, your formula looks something like this:

25. Save your model. When your model is complete, with the commands modeled for both drill down and drill up, the Storyboard looks something like this:

3.5 Deploy the Model and Interact With the Application in the Portal 26. Deploy the model (to Adobe Flash format). 27. Launch the iView:

In runtime on the portal, hierarchy nodes are displayed using indentation and special flags for drill states:

• Root nodes: Aligned fully left • Subnodes: Indented • Nodes that are expanded: Prefaced with "-" • Nodes that are collapsed: Prefaced with "+"

In our case, the root SIC Hierarchy node is expanded by default.

28. Select a node that is collapsed (prefaced with "+"), and click the Drill Down button:

The node expands and its subnodes are displayed beneath it:

You can continue drilling down into collapsed subnodes until they are fully expanded:

29. Select an expanded node (prefaced by "-") and click the Drill Up button to drill back up. You can continue drilling up until all nodes are collapsed:

Using SAP BI Web Design API commands in procedures similar to the above, you can build additional hierarchy support into your model. For example, you can add a button that drills to level "X" (where you provide an input field for "X").

4 Appendix: SAP BI Web Design API Commands The most relevant Web Design API commands for hierarchy support are outlined below. For more details, see the SAP BI Web Design API documentation (see For More Information, above).

• Use the DRILL_TO_LEVEL command to expand the display hierarchy up to

the specified hierarchy level: Parameter Description IOBJNM Name of characteristic

Characteristic, to which an active display hierarchy is assigned.

LEVEL Hierarchy level Level to which you want to expand the hierarchy. Level 1 is the root level. If a hierarchy node is filtered at the same time, make sure that the level is higher than the level of the filtered node.

• Use the following Web Design API commands to filter hierarchy nodes: Parameter Description FILTER_IOBJNM Name of the characteristic /

structure by which the system is to filter

FILTER_COLLAPSE ‘X’ = Yes, ‘ ’ = No

Removing filter characteristics from the drilldown The characteristics that are specified under FILTER_IOBJNM are taken from the axes after the filter has been applied. If you do not specify the parameters, the system assumes you want to use the value ‘X’ = Yes.

FILTER_NODE_IOBJNM Node characteristic name This parameter must be specified if the system filters by hierarchy nodes.

FILTER_VALUE Hierarchy node name