asug 2001 reporting tips & techniques

7/30/2019 Asug 2001 Reporting Tips & Techniques

1/59

Reporting Tips& Techniques

John MadrenBen ArmstrongPhilip Morris USA

Session Code: [3307]


2/59

Agenda

Reporting Using a BW Hierarchy withR/3 Hierarchies as Sub-Trees

Customizing Workbooks with VBA


3/59

BW at Philip Morris, USA

R/3 SD,MM,COPA,CCA,PCA,OPA,FIGL Legacy File

Custom

Cubes

Standard

Cubes

CustomStandard

Content

Finance = 2 Sales and Inventory = 64

Finance =88

Multi-

Cubes

Finance = 8Sales and Inventory = 8

Finance = 2

Sales and Inventory = 5

Finance = 2

Queries

InfoCubes

Source

Systems

Total

154

Total

25


4/59

BW at Philip Morris, USA

BW initial rollout - October 2, 2000 FCS 2.0B (Currently Support Pack 11) Infrastructure - Oracle (8.1.6), IBM SP2, AIX

Source Systems -

R/3 4.5B (SD, MM, COPA, CCA, PCA, OPA, FIGL)

Flat Files (Legacy systems)

Business Areas Sales & Inventory Finance Total

# of Cubes Standard Business Content 0 8 8

Custom 8 2 10

MultiCubes 5 2 7

# of Queries

Standard Business Content 0 2 2

Custom 64 88 152

Current Data Size - 50+ GB (including aggregates)

Daily Record Volume - 500,000 Records

Team Size during Implementation - 10 PM, 5 Deloitte, 1 SAP

Number of BW Users - 280


5/59

Cost CenterHierarchies


6/59

Agenda

Reporting Requirement

Issue We Encountered

How We Resolved It

Creating Hierarchy with Sub-trees

Using in Queries

Subsequent Hierarchy Reload AnotherIssue

Workaround Solution


7/59

Business Requirement

Reporting Scenario:Ability to use a variable to select a cost

center hierarchy and a variable to thenselect node from that same hierarchy

BW 2.0b does not support use of both

variables together. Node variables can only be pointed to onehierarchy.


8/59




Demonstration of Issue

Query Design Using Hierarchy and NodeVariables

Query Execution to Highlight Issue


9/59

Query Design ( Bex Analyzer)

Add Variables toCost Center


10/59

Add Hierarchy Variable

Right Click

Select Properties


11/59


Values

Button


12/59


Check

Box


13/59


Hierarchy

Variable


14/59

Add Node Variable

Right Click

Select Restrict


15/59

Add Node Variable

Cost Center

Variables


16/59

Add Node Variable

Select

Hierarchy


17/59

Add Node Variable

Select Hierarchy

Variable


18/59

Execute Query

Variable

Selection


19/59

Execute Query

Hierarchies

Available

Hierarchy

Selection


20/59

Execute Query

Drop Down

for Node

Hierarchy

Selected


21/59

Execute Query

Alternate

Hierarchy

Selected

Nodes From

Standard Hierarchy


22/59



How We Resolved It


23/59


24/59



How We Resolved It


Create Root Node

Add Text Nodes

Set-up Infopackages


25/59

How to Create Hierarchy

We created a new root cost centerhierarchy in BW named Cost Center

Hierarchy Then added text nodes to the hierarchy.

These nodes have the sametechnical

name and description as the R/3hierarchies that will be assigned to it.


26/59

Create Root Hierarchy

In the Administrator Workbench, click on

theMaintain Hierarchies button


27/59

Create Root Hierarchy

1) Input 0costcenter

for the characteristic

2) Click on the createbutton


28/59

Add Hierarchy Name

1. Enter a Hierarchy Name

2. Enter Descriptions


29/59

Add Text Nodes

Right Click on Hierarchy Name andselect Create text node(s)


30/59

Add Text Node Names

In pop-up box enter technical name

for the node and descriptions. These must be the same as the R/3

hierarchy to be loaded as the subtree.


31/59

Add Additional Nodes

Repeat create text node until all

nodes needed have been entered.Then save and activate hierarchy.


32/59

Set-up Infopackages

Create an infopackage for eachhierarchy in R/3 to be loaded as a

sub-tree

Run infopackages


33/59

Infopackage Settings

Click RenameHierarchy after loading

to and enter technicalname

Check Store hierarchyas sub-tree


34/59



How We Resolved It


Using in Queries


35/59


36/59

Using Hierarchy in Query

1. Select new

hierarchy from

drop down list

2. Add node

selection

variable


37/59

Query Execution

When executing query selection box for

hierarchy variabledisplays new hierarchywith subtrees


38/59



How We Resolved It


Using in Queries

Issues with Subsequent HierarchyReload


39/59

Reloading Hierarchy

Subsequent attempts to reload thehierarchies results in errors -

Node Already Exists

Multiple Occurrences of Cost Center Exist

While a solution is not available in BW

2.0b, SAP was able to provide aworkaround.


40/59

Reporting Requirement Issue We Encountered

How We Resolved It


Using in Queries

Issues with Subsequent HierarchyReload

Workaround Solution


41/59

Workaround Solution

Create a flat file which contains anempty hierarchy with same name andnodes as hierarchy in BW

Has same root name and sub-trees Data is NOT loaded to sub-trees

Load flat file before reloading

hierarchies as sub-trees from R/3


42/59

Empty Hierarchy

Empty Hierarchy

Created as CSV File


43/59

Empty Hierarchy

Empty

Hierarchy


44/59

Visual Basic for

Applications(VBA) Add-ins &

Toolbars


45/59

VBA Add-ins and Toolbars

l h d f b


46/59

Goals & Methods of Distribution

The Need for VBA

Simplify workbook developmentImprove/Simplify tasks for End-Users

Distribution

Workbook Code vs. External Add-In

Windows Scripting Host (WSH)

h l lb


47/59

The Developers Toolbar

TheDevelopers Toolbaris used to format a BWworkbook with a single click. It imports the

PMUSA stylesheet, adds PMUSA graphics, lays

out the report data on the screen and inserts the

necessary VBA modules and functions to correctly

activate and de-activate thePMUSA BW Tools

toolbar.

PMUSA BW R Vi


48/59

PMUSA BW Report View

Th E d U T lb


49/59

The End-User Toolbar

The End-User Toolbar was created to enhance thenavigational and viewing capabilities of the BEx

Analyzer. Utilizing a generic add-in also provides

a singular location for generic VBA functions and

modules.

Th E d U T lb


50/59


The ShowCharacteristicsbuttonis used to display thelist of characteristics

available for drilldownand filtering in eachreport. After selection,this button will changeto the HideCharacteristicsbutton.

Th E d U T lb


51/59


The Hide Characteristicsbutton is used to hide thelist of characteristicsavailable for drilldown and

filtering. This allows formore data to be displayedon the screen. Afterselection, it will change tothe Show Characteristics

button.

Th E d U T lb


52/59


The Select TextElementsbutton is usedto change the list ofdisplayed text elements.

Text elements containstatistics and valuesregarding the reportand its data as well as

selection criteriaentered when thereport is executed.

Th E d U T lb


53/59


The Standard Excel Viewbutton is used to return to astandard Microsoft Excelscreen presentation. Row

and column headings will bedisplayed, and the Standardand Formattingtoolbars willagain be visible. Afterselection, it will change tothe PMUSA BW Viewbutton.

Th E d U T lb


54/59


The PMUSA BW Viewbutton is used to return tothe PMUSA BW reportlayout. This will hide the

Excel toolbars and removerow and column headings.

After selection, this buttonwill change to theStandard Excel Viewbutton.

Th E d U T lb


55/59

The E-mail to BW Supportbuttonwill electronically send a copy of thecurrent workbook to the BWsupport team. The workbook is

attached to an e-mail messagecontaining a tracking number.


Th E d U T lb


56/59

The Print BW Reportbutton is usedto create a printout of the report.The user has the option ofdisplaying all available text

elements or just those that arecurrently being displayed. Usingthis option eliminates the problemof printing extra blank pages.



57/59

Questions?


58/59

Thank you for attending!

Please remember to complete andreturn your evaluation form followingthis session.

Session Code: [3307]


59/59

Reporting Requirement How We Satisfied It

Steps to Creating Hierarchy with Sub-

trees

Using in Queries

asug 2001 reporting tips & techniques

Documents