introbwguide

7/30/2019 IntroBWGuide

http://slidepdf.com/reader/full/introbwguide 1/56

Introduction to Business Warehouse Guide – Duke University

Original: 7/06/2005 Revised: 4/26/2011 4:14 PM

Introduction to Business Warehouse Guidefor Duke University (Company Code 0010)

Table of Contents

About SAP Business Warehouse (BW) at Duke ............................................... 2 Logging Into SAP BW ......................................................................................... 3 The BEx Toolbar Funct ions ............................................................................... 7 BW Terms and Definit ions ................................................................................. 8 Using an Existing Query – Fund Trial Balance (FTB) .................................... 10

To locate and select the Fund Trial Balance query................................................................. 10 To enter or chose selection criteria for the Fund Trial Balance query ..................................... 12 To check the selection values entered before executing the query ......................................... 16 To save variant of the selection values to use later and save steps ........................................ 17 To use or get a saved variant of the selection values ............................................................. 18 To execute the query once all selections are made and review the results ............................. 19

Saving a Formatted Query into a Workbook in the Favori tes Folder ........... 22 To save the formatted query results for into a new workbook for use in the future .................. 22 To save the formatted query results for into an existing workbook.......................................... 24

Opening a Workbook and Access ing a Saved Query at a Later Date .......... 25 Other Navigation Too ls for Queries ................................................................ 27

To navigate between BW/BEx and other applications using Alt +Tab Keys ........................... 27 To undo an action performed or go back to prior results ........................................................ 28 To prevent column sizes from adjusting to the default width ................................................... 28 To eliminate the technical description of the Duke Cost Object .............................................. 30 To expand the hierarchy and see all levels and cost objects .................................................. 31 To view the hierarchy as a straight list of Duke Cost Objects ................................................. 32 To filter and drill-down on one Cost Object by Commitment Item ........................................... 34 To add the drill-down for Commitment Item to all Duke Cost Objects displayed...................... 36 To swap the view from Duke Cost Object to Commitment Item (G/L Account) ........................ 38 To drill-down and further analyze a specific Commitment Item (G/L Account) ........................ 40 To display balances by period across the report .................................................................... 42 To add a Total (results) row in a query if not displayed .......................................................... 44 To move the placement of the Grand Total row in a query ..................................................... 46 To suppress rows or columns that contain a zero amount ...................................................... 48

List of Commonly Used Queries ...................................................................... 50





About SAP Business Warehouse (BW) atDuke

In addition to the financial reporting functions available in SAP R/3,the SAP Business Warehouse (BW) product is available as a tool formore advanced reporting options at Duke. BW is a data warehouseand contains tables of data that can be used for ad hoc reporting,either to view existing standard queries or to build queries. Financialand procurement data are loaded into BW tables from the real-timeSAP R/3 system on a nightly basis (SAP R/3 is an On-line

Transaction Processing software – OLTP). So while the data in BW isnot updated real-time, there is only a one-day lag (SAP BW is an On-line Analytical Processing software – OLAP).

The BW tool uses an Excel Add-in, known as Business Explorer(BEx) to view the results of the query and manipulate the report toanalyze the data in various ways that are not available in R/3. Inaddition, BW allows departments to view all their cost objects (CostCenters, Profit Centers, and WBS Elements/Projects) in one report.Most reports for the University and School of Medicine (CompanyCode 0010) in R/3 allow for reporting on only one kind of cost objectat the time.





Logging Into SAP BW

1. Use the Authentic Log in icon to enter your User Name and password and obtain your green security ticket, just asyou would to log into SAP R/3.

To log into BW:

2. Once you have a green ticket, double click on the SAPlogon icon.

In the resul ting SAP Logon 620 window:

3. Double click on the option listed as BWP.

Note: MAC users should log into SAP per the usualsteps, then select and open BWP on the Open:SAPGUI screen.

3





Logging into SAP BW (cont.)

4. If the above Password Change Request window appears (only

the first time you log into BW), then click on the Deactivate /Delete button to deactivate this additional password.

Note: Do not attempt to change your password usingthis screen. This additional password in BW isnot needed since Duke uses the Kerberospassword authentication.






On the SAP Easy Access - User menu… screen in BW, to openthe BEx (Excel add-in):

5. Enter the transaction code RRMX in the Command field.

Note: To avoid any issues with BW, ensure that allother open Excel files are closed.

6. Click on the Enter button to open an Excel session.

7. In the Excel session, to display the Business Explorer (BEx)toolbar at the top, click on the Add-Ins option on the toolbar

Note: If using an older version of Excel, the BusinessExplorer (BEx) toolbar automatically displays inthe body of the spreadsheet. Click and drag tothe top as a toolbar to avoid covering up in data.

5

6

7






8. Review the icons on the Add-Ins toolbar now displayed (see

next section for descriptions) and use the toolbar to openqueries and workbooks, save workbooks, and perform otherBusiness Explorer (BEx) functions as outlined in the rest of thisGuide.





The BEx Toolbar Functions

The Business Explorer (BEx) toolbar contains buttons that performfunctions which help you analyze the data. A brief description of

each button is shown below and the functions will be discussed in theGuide as the steps for opening workbooks or existing queries areoutlined.

Open

Save

Refreshquery

Back

Changequery

Goto

OLAPfunctions for active cell

Format

Layout

Tools

Settings

Help





BW Terms and Definit ions

The following table outlines some key terms relating to SAP BusinessWarehouse (BW):

Key Terms

InfoCube

A table which collects and stores characteristics(attributes) and key figures (dollars, hours,workdays, etc.) in BW – Example of InfoCube forfinancials = G/L Account Balances

ODSOperational Data Storage – a table that containstransaction data stored at the document level.

InfoProvider InfoCubes (balances) or ODS (transactions)

Query

An evaluation of data per the selection of attributesand key figures from InfoProviders that can beanalyzed in an Excel worksheet – users can defineand customize queries based on how they want toview and navigate data (e.g. Fund Trial Balance).

Workbook Collection of Excel spreadsheets with embeddedqueries composed of one or more related worksheets.

InfoArea

Comprised of folders of InfoProviders that contain

existing queries and workbooks .

Continued on the next page.





BW Terms and Definit ions (cont.)

InfoProviders are folders that contain the existing queries, such

as the Fund Trial Balance. These InfoProviders are identified in BWby symbols, which are used to expand the folders and open thequeries.

More on InfoProviders

Below is legend of these symbols and what they represent in BW.

Identifies this InfoProvider as an InfoCubeFor financial analysis, cubes contain balances

Identifies this InfoProvider as an Operational Data Store (ODS)For financial analysis, ODS contain transactions

Identifies this InfoProvider as a Multi-cube =combination of two or more InfoCubes and/or ODS

For financial analysis, cubes contain a combination, suchas balances and transactions

Identifies a Query within an InfoProvider (folder).





Using an Existing Query – Fund TrialBalance (FTB)

To locate and select the Fund Trial Balance query:In the Excel worksheet with the Business Explorer (BEx) add-in:

1. Click on the Open button on the BEx toolbar .2. Click on the Queries option from the drop-down list.

In the SAP BEx: Select query box:

Note: The History button is the default selectionand lists the last queries opened on the right sideof the screen.

3. Click on the InfoAreas button to display the folderscontaining the InfoProviders with lists of InfoAreas (folders) onthe right side of the screen (see screen next page).

3

1

2





Using an Existing Query – Fund TrialBalance (cont.)

4. Click to expand the Financials Management &Controlling InfoArea folder (on the + symbol).

5. Click to expand the Duke Custom Financial ObjectsInfoArea folder (on the + symbol).

6. Click to expand the Commitments/actuals and budget inFunds Management (Duke) InfoCube folder (on the + symbol).

Note: As a reminder, the term Infocube = balances.

7. Double-click on the Fund Trial Balance (No

Commitments) with Hierarchy to select that query.

Note: If double-clicking in the step above does notwork, click once on the Fund Trial Balance todisplay a text box, then click the OK button.

4

5

6

7





Using an Existing Query – FTB (cont.)

To enter or chose selection cr iteria for the Fund Trial Balancequery:

Note: The selection dialog box contains the selectionfields and the fields may vary by query. Thefields denoted with an asterisk are required.Some fields are defaulted to assist in running thisreport the proper way.

1. As you enter values, DO NOT USE the Personalize Querybutton for each selection field as it will make the field disappear.

2. Enter the fiscal year (e.g. 2011) in the Fiscal Year field.

3. Ensure the values of # to 16 are displayed in the Posting

Period Range From and To fields (# = balance forward neededfor certain codes to determine a fund balance).

4. Ensure the values of 290000 to 999999 are displayed in theCommitment Item Range From and To fields (commitmentitem = G/L Account in SAP BW; using the range of G/LAccounts 290000 to 999999 provides a fund trial balanceversus a complete trial balance).

2

34

5-14

1 NEVER USE THIS BUTTON FOR ANY FIELD.






5. Use the next steps to select the desired level of BFR Code /Org. Unit for the Duke Cost Object Hierarchy Node field.

6. If the BFR Code / Org. unit number is known, the 10 digitnumber may be keyed directly into the field using the value of OU at the beginning (default is SOM top level BFR Code nodeof OU6860000000 – change this to your BFR Code).

7. To search and find the BFR Code value, click on the Drop-down for the Duke Cost Object Hierarchy Node field andfollow the next steps to find a value (SOM defaults and shouldbe changed).

8. In the Fund Trial Balance… CODUKEORG_ALL_NODES window (see above), note the following:

•

On the left side, the Entire Hierarchy can be displayed andthe BFR Code/Org. Unit needed may be found and selected.

• On the right side, the Selected Hierarchy Nodes displaysthe current selected BFR Code/Org. Unit (the default for thisquery is School of Medicine and needs to be changed toyour BFR Code/Org Unit).

• Use the twistees to open the “trees” within the EntireHierarchy (on the left) for the Duke organization and locatethe desired level of BFR Code/ Org. Unit (see next steps).

• Only one BFR Code/Org. Unit may be selected (multipleselections are not provided on this particular query).

9. To open/expand the Entire Hierarchy and begin to locate the

BFR Code / Org Unit, click on the Twistee button besidethe DUKE tree (note the Twistee button will point down).

8

9






10. Click on the Twistee button beside of Duke University toopen that node.

Note: Duke University includes School of Medicine andProvost areas, as well as the DCRI).

11. Continue to click on the Twistee buttons to open and locatethe desired level of the organization hierarchy to be selected forthe query (i.e., the department, division, or school) per below:

• To find schools or departments within the School of Medicine, School of Nursing, or Centers, follow the path:Chancellor , Health Affairs Chancellor, Health Af fairs(again) choose appropriate area from here.

• To find schools or departments within the Provost area, follow the path: Provost Provost (again) chooseappropr iate area from here.

10

11






12. Once the desired BFR Code / Org. Unit node is found, double-click on the node (on left side) to display the node underSelected Hierarchy Nodes (on the right side – replaces thedefaulted SOM node).

Note: The recommended selection is a mid or lower level node (BFR Code/Org. Unit) for your organizational hierarchy. All BFR Code/Org. Unit

levels are indicated with the symbol. Thesymbol displays beside the 7 digit cost object

once you’ve reached the bottom BFR Code /Org. Unit level, and may not be the best choicefor this type of query. In other words, the reportis better for the BFR Code/Org. Unit levelsversus single 7 digit cost objects.

12

14

13






13. Review the BFR Code/Org Unit level now displayed andselected on the right side under the Selected Hierarchy Nodessection (the level selected has replaced the defaulted SOM toplevel node).

14. Once the desired BFR Code/Org. Unit is selected, click on the

Execute button at the bottom of the window to return to theselection window.

To check the selection values entered before executing thequery:

1. Once you’ve returned to the Fund Trial Balance… selectionscreen (see above), review the values selected.

2. To check and see if the values used for the selection are valid

in SAP BW, click the Check button (especially if theBFR Code/ Org. Unit was manually entered).

2

1






To save variant of the selection values to use later and savesteps:

Note: The Save All Variant function allows the selectionvalues entered or chosen in each field to besaved as a named variant. The variant can thenbe used the next time to populate the fields andsave keystrokes.

1. Ensure the values desired are entered in the selection fields.

2. To save a variant of the selection, click on the Save AllVariant button.

3. On the Save All Variants window, enter a name in the Variantfield, and a brief description in the Description field.

4. Review the Yes values listed for each selection field andchange any to No as desired (Yes = values entered in thesefields will be saved in the variant).

5. Click on the Save All Variants button (middle of thescreen) to save the named variant and return to the selectionscreen.

2

1

3

4

5






To use or get a saved variant of the selection values:

Note: If a named variant was saved for the selection

values, then each time the query is used again,the Load Variant function may be used to find /select the variant. The variant will populate thefields with the saved values and save keystrokes.

1. On the selection screen, before entering or choosing selectionvalues in the fields, click on the Load Variant button

2. In the Restrictions window, double click on the desiredvariant to select it and populate the fields.

Note: The list is a universal list of all saved variants byany user for this specific query.

1

2






3. Review the selection values now populated in the fields fromthe variant selected.

To execute the query once all selections are made and reviewthe results:

1. When ready to run the query, click on the Execute button(or press F8).

1

3






In the Fund Trial Balance query results:

2. Review the query results for the BFR Code/ Org. Unit nodethat was selected:

• The top bolded section contains the items you can use todisplay the results in a different view (see Navigation Tips).

• The middle section contains the criteria for the current viewof the results (i.e., what was chosen to build the results).

• The bottom section contains the results/data for the query.

3. Note the value listed for Duke Cost Object in the middlesection (Cell B25 in this example - B column, cell may vary)providing the name of the BFR/Org Unit chosen.

2

4

3






4. Note the following about the actual results (bottom section) forthis Fund Trial Balance query:

•

This query uses the Duke Cost Object table (CODUKEORG) which combines all four major cost objects in one place forreporting - Cost Centers, Profit Centers, Projects/WBSElements, and Internal Orders.

• The cost objects are displayed in one “super hierarchy”,which provides a consolidated view of the entireorganization’s financial activity.

• The hierarchy levels are displayed in Column A and B with

the Twistee buttons and some may already beexpanded to display the Duke Cost Object level.

• Do not use the Twistee buttons to open the hierarchylevels individually as this is time consuming - the hierarchycan be easily expanded or removed, and other formattingcan be adjusted (see Other Navigation Tools for Queries).

• The format for the lowest level Duke Cost Objects in ColumnA is displayed as the “technical name”, is not useful, and canbe removed (see Other Navigation Tools for Queries). For

example, a cost center such as 1573147 is listed asKSDUKE0001573147, and a Project/ WBS Element such as7473100 is listed as nothing meaningful, like PD0001157.

• The meaningful 7 digit value for Duke Cost Object is listed inthe Cost Object column – in the example used above – thevalues of 1573147 or 7473100 (WBS Plant fund) are shown.

• See the Cost Object column for the meaningful 7 digit CostObject (fund code) value.

Note: Other options for analyzing data are alsoavailable, such as the ability to drill-down by theitems listed in Column A at the top of thescreen, such as Commitment item (term for Cost Element or G/L Account), Fiscal Period,Fund Group, Fund Categories, etc. See theOther Navigation Tools for Queries section of this Guide for more details.





Saving a Formatted Query into aWorkbook in the Favorites Folder

Before saving the query:1. Filter and format the query as needed per the steps covered in

the Other Navigation Tools for Queries section of the Guide.

To save the formatted query results for into a new workbook for use in the future:

Note: Use the Save as new workbook option to savethe query results as a new workbook at anytime

with a new unique name, especially the first timea query is formatted and saved. The Save asexisting workbook option is useful if youcontinue to make changes and just want to saveover an already named workbook.

2. Click on the Save button on the BEx Add-Ins toolbar.3. Click on the Save as new workbook option from the drop-

down list (see next page and steps).

Note: The Save as exiting workbook option will notbe highlighted if this is the first time you aresaving a query into a workbook.

2

3





Saving a Formatted Query (cont.)

On the SAP BEx: Save in document store window:

4. Ensure the Favorites button is selected (left side of

the screen –note that the Roles opt ion cannot be used).

5. If not defaulted, click on the Favorites folder button (on the + symbol) to expand and highlight.

6. Enter a description of the query in the Description field.

7. Click on the Save button.

Note: The queries may also be saved as an Excel file

as well. This might be beneficial if you’d like tosave a particular view such as monthly. TheExcel files can then be easily organized on your network drive.

4

5

6

7





Saving a Formatted Query (cont.)

To save the formatted query results for into an existingworkbook:

Note: The Save as existing workbook… is useful if you continue to make changes while in a queryand just want to save over an already namedworkbook.

1. Click on the Save button on the BEx Add-Ins toolbar.

2. Click on the Save as existing workbook option from the drop-down list (Caution: this will save over the currentworkbook).

1

2





Opening a Workbook and Accessing aSaved Query at a Later Date

Note: To avoid any issues with BW, ensure that allother open Excel files are closed.

1. Log into BW and use the transaction code RRMX to open Exceland click on the Add-Ins option to display the BEx toolbar.

2. Click on the Open button on the BEx toolbar .

3. Click on the Workbooks option from the drop-down list.

On the resulting SAP BEx Open… window:

4. Click on the Favorites button (left side of the screen).

Note: The Roles folder can be opened as another option and contains workbooks of saved queriesused primarily by central finance areas withinDuke. The Roles folder contains one folder for the School of Medicine for Tiered reports.

2

3

4

5

6





Opening a Workbook (cont.)

5. Click to open various folders (click on the + symbol) on the rightto locate the workbook desired (like the Favorites folder).

6. Double click on a workbook (i.e., the named query, likeFinancial Services Fund Trial Balance in this example).

Note: The workbook MUST be refreshed before anyother functions can be used.

7. IMPORTANT: Immediately after retrieval, use the Refreshbutton on the BEx Add-In toolbar to update the results of oneor all existing queries in the Workbook as follows:

• To refresh only the query displayed, click into an active cell

(any shaded cell) and click on the Refresh button (orright mouse click in any active cell and choose Refresh).

• To refresh all queries at once if applicable (if multiple queriesare included in a workbook), click into a “global” cell (any cell

shaded in white), click on the Refresh button, and clickon the Yes button to refresh all queries (not shown).

8. In the resulting selection screen window for the query, enter the

updated selection values and click on the Execute button.

7

8





Other Navigation Tools for Queries

To navigate between BW/BEx and other applications using Alt +Tab Keys:

When using the BW/BEx tool, along with other applications, BEx mayappear to “lock up” in windows as you are moving between differentapplications. In other words, you may not be able to use theWindows Start Bar to move between or minimize/maximize the BExscreens on your desktop. This usually occurs when a query orworkbook selection window is open, like when you are enteringselections for the Fund Trial Balance report and then navigate toanother application. When you return via the Start Bar, the selectionbox may just be a white blank box on the screen. This section covers

steps on how to work around this situation.

1. Depress the Alt key with your thumb and hold while doing thenext step.

2. Use another finger to click on the Tab key once to view a menuof all open window applications.

3. While continuing to depress the Al t key, tap on the Tab keywith another finger and scroll through the open windows.

4. Release both keys at once to select and go to a window(example the icon that has the name of the query or workbook,like Fund Trial Balance…).

When using BEx, if you are opening a query or workbook, theselection screen box is open, and you navigate away to another application, you may not be able to get to the selection screen boxagain via the Start bar. The box will be a blank white box. Use Alt +Tab keys to find (see below).





Other Navigation Tools (cont.)

To undo an action performed or go back to prior results:

1. Use the Back button on the BEx Add-Ins toolbar (similarto the Undo function in Excel).

To prevent column s izes from adjusting to the default width:

Note: When navigating in a query and performing

analysis, any column sizes that were manuallyadjusted will change back to the default width for that column. Use the steps below to prevent theautomatic sizing of columns back to defaultcolumn widths.

1. Right click once in an active shaded cell within the BExworksheet to display a drop-down list.

2. Click on the Properties option from the list (or highlight withmouse and release mouse).

1

2

1






On the resulting SAP BEx Query: Properties screen:

3. Select the colWidth (Column Width) tab.

4. Click in the radio button to choose the Do not adjust columnwidth option.

5. Click the OK button to close the window.

Note: Once the query reappears after the OK button isused, then the columns will stay at the width thatwas last set (by the click and drag feature).

3

4

5






To eliminate the technical descr ipt ion of the Duke Cost Object:

The technical description contains the longer different format for thecost object, such as KSDUKE0001573070. The steps below are tochange the display of the Duke Cost Object column from thetechnical “key” (number) description to the name (text) description.

1. Right click once on the column heading Duke Cost Object inColumn A (cell may vary) to display a drop-down list.

2. Select the options in the resulting pop-up windows via path:Duke Cost Object Display as Duke Cost Object Name

3. Review the results and note the Duke Cost Object column nowcontains the name of the Cost Object with the 7 digit numberstill displayed in the in the Cost Object column.

1

2

3






To expand the hierarchy and see all levels and cost objects:

Note: This example is done from the Duke Cost Object

view (original view) of the query. Use theExpand Hierarchy to open the hierarchy (closed

Twistee buttons) to the level chosen.

1. Right click once on the column heading Duke Cost Object inColumn A (cell # may vary) to display a drop-down list.

2. Select the options from the resulting pop-up windows per thispath: Expand Hierarchy Level # (# = choose the highestlevel listed at bottom of last lis t in order to expand fully).

3. Review the results that now show the entire depth of the

hierarchy (all Twistee buttons now open).

2

1

3






To view the hierarchy as a straight list of Duke Cost Objects:

Note: This example is done from the Duke Cost Object

view (original view) of the query. Use theHierarchy Active function to completely removethe hierarchy (BFR/Org. Unit levels) and displaythe results by Duke Cost Objects. Once removedthe function can be used again later to add backthe hierarchy (turn on and off like a light switch).


5. Click on the option Hierarchy Active (which has a checkmarkas the default) to uncheck and deselect that item.

6. Review the results that now show the entire depth of thehierarchy listed by Duke Cost Object and sorted by CostCenters, Profi t Centers, and then WBS Elements/Projects.

Note: Sort for this view defaults to by Cost Centers,Profit Centers, and WBS Elements/Projects, soto sort numerically by Duke Cost Object, see thenext step.

4

5

6






7. Optional: To sort the Duke Cost Objects numerically fromlowest to highest number:

• Right click on the Cost Object column heading (for columnthat contains the 7 digit cost objects).

• Click on Sort option and then click on Sort Ascending According to Key (key = number).

• Review the results that now show the entire depth of thehierarchy listed by Duke Cost Object and sorted numerically

in a straight line from lowest to highest cost object.Note: Remember to use the Save button to save

this view as the existing workbook or as a new

workbook for future use, or to use the Backbutton to return to a previous view as needed.

7






To fil ter and dr ill-down on one Cost Object by Commitment Item:

Note: This example is done from the Duke Cost Objectview shown as a straight list with no hierarchy,but may be done in any view. Use the Filter anddrilldown according to function to filter for thechosen cell and see the details per the valuechosen (like Commitment item / G/L Account).

1. Right click once in the cell that lists the Cost Object 7 digi t

number to display a drop-down list.2. Select the options from the resulting pop-up windows per this

path: Filter and drilldown according to Commitment Item.

Note: Choose from one of the many other Filter anddrilldown according to values, like Fund Group,if preferred.

2

1






3. Review the results and note the following:• The name of the one filtered Duke Cost Object is displayed

in column B (B12 in this example / cell # may vary) at thetop of the report.

• The balances are displayed for the filtered Duke Cost Objectin the report below with the drilldown by Commitment Item

• If desired, remove the hierarchy per earlier steps if desired(right mouse click in Cell A24 for Commitment Item anduncheck the Hierarchy Active option).

Note: Remember to use the Save button to savethis view as an existing or new workbook for future use

4. Use the Back button on the BEx Add-Ins toolbar to returnto a previous view (in this case to remove the filter) as needed.

3

4






To add the dril l-down for Commitment Item to all Duke CostObjects displayed:

Note: This example is done from the Duke Cost Objectview shown as a straight list with no hierarchy,but may be done in any view. Use the AddDrilldown According to function to add the drill-down to ALL Cost Objects for the value chosen(like Commitment item / G/L Account).

1. Right click once in the Cost Object column heading (columncontaining the 7 digit number) to display a drop-down list.

2. Select the options from the resulting pop-up windows per this

path: Add Drilldown According to Commitment item.

Note: Choose from other Add Dril ldown According toother values, like Fund Group, if preferred.

2

1






3. Review the results and note the following:

• The B Cell (B4 in this example/cell# may vary) for theCommitment item field at the top of the screen is nowshaded (striped).

• The balances are displayed for ALL Duke Cost Objects withthe added drilldown by Commitment Item (G/L Account).

Note: Remember to use the Save button to savethis view as an existing or new workbook for future use.

4. Use the Back button on the BEx Add-Ins toolbar to returnto a previous view as needed.

3

4






To swap the view from Duke Cost Object to Commitment Item(G/L Account):

Note: This example is done from the Duke Cost Objectview shown as a straight list with no hierarchy,but may be done in any view, such as the originalview for this query. Use the Swap function tochange the query results from one type of view toanother, for example from a Cost Object view toa Commitment Item (G/L Account) view.


2. Select the options from the resulting pop-up windows per thispath: Swap Duke CostObject wi th Commitment item.

1

2






Note: The first column now contains and is sorted byG/L Account, also known as Commitment Item

for this type of report.


• The entire view of the report has now changed from aCost Object (7 digi t number) view to a Commitment Item(G/L Account) view for the entire BFR Code/Org. Unithierarchy chosen for the original query.

• Column A now has the column heading of CommitmentItems and the query results are listed by 6 digit G/L Account

versus the 7 digit Cost Object.• The balances are displayed for Commitment Items (G/L

Accounts) listed.



Note: Another option is to right click once onCommitment Item in the A column heading and select the options: Swap Commitment Itemwith Duke Cost Object.

3

4






To drill-down and further analyze a specif ic Commitment Item(G/L Account):

1. Right click once on the Commitment item in question inColumn A (this example 647000) to display a drop-down list.

2. Select the options from the resulting pop-up windows per thispath: Filter and drilldown according to Duke Cost Object.

2

1







• SCROLL TO THE TOP of the new drill-down view, as theview will be positioned wherever the cursor was previously.

• The number and description of the Commitment Item (G/L Account) chosen for the drill-down are displayed in Bcell beside Commitment Item at the top.

• The amounts for the Commitment Item chosen are listed byDuke Cost Object in the bottom area.



3

4






To disp lay balances by period across the report:

Note: This example is done from the Duke Cost Objectview shown as a straight list with no hierarchy.Use the Drilldown Across (or Down) function toadd the drill-down using the characteristic valuesat the top of the query to provide additionaldetail, such as to display balances with the drill-down across the posting periods.

1. Right click once in the B cell (top section of the screen /Cell B4 in this example/ cell # may vary) that correspondsto the Posting Period to display a drop-down list.

2. Select the options from the resulting pop-up windows per thispath: Drilldown Across (or Drilldown Down if preferred).

2

1







• The B Cell beside the Posting Period is now shaded.

• The # character in the column heading of the report indicates

the column contains Period 0 - Balance Carry Forward.• Balances for each period will appear across the next

columns with the period listed in each column heading (or if “down” was chosen, the balances by period are in the rows).

• There are no totals displayed as default on this view, buttotals may be added – see next section of guide.

Note: Remember to use the Save button to savethis view as an existing or new workbook for

future use.


Note: Another option to undo this action is to rightclick once on B Cell beside of Posting Period atthe top and select the options: Drilldown Remove Drilldown.

3

4






To add a Total (results ) row in a query i f not displayed:

Note: The display of totals in a query depends on the

query chosen. In this example, the Fund TrialBalance query has the drill-down across periodadded for the balances, which does not displayTotals (known as results rows). The totals are“suppressed”. Use the Al l Charactist ics featureto never suppress the results row, which willdisplay a Total results row if applicable.


2. Select the options from the resulting pop-up windows per thispath: Al l Character ist ics Suppress Results Rows Never .

2

1







• The Overall Resul t row has been added to total the postingperiod columns across the query results for each Duke CostObject row.

• There may also be a total added for each column at thebottom of the report to total each posting period down thereport for all Duke Cost Objects.

• The position of the results (Total) rows can be changed (see next section of the Guide).



Note: Another option to undo this action is to right clickonce on the A Column Heading for Duke CostObject and select options: Al l Character ist ics Suppress Results Row Always.

3

4






To move the placement of the Grand Total row in a query:

Note: The original placement of the Total results row

depends on the query chosen. Use the Al lCharacteristics function to move the position of the Total results column/row. This function canbe used on any view of a query.

In the example below, the query results have thePosting Period drilldown added across theresults. In this example, the Total column for allthe posting periods is displayed to the right as

the last column, and the Total row for all columnsis displayed at the bottom of the query results(not shown here for space).


2. Select the options from the resulting pop-up windows per thispath: Al l Character ist ics Position of Results Rows Results Rows Top Left (or Bottom Right if Top Left is checkedas original position).

Note: The function phrase” Results Rows Top Left”means that the Total for columns across thereport will display to the far left and the Total for all rows in a column will display at the top of thequery results (the two are not used in conjunctionto put a total in the top left corner, etc.).

2

1







• For totals originally displayed in a column to the far right (totaling across the results like this example wherethere was a total for the posting period drilldown across theresults), then the total will now appear on the far left (thefirst column before the Posting Period drilldown columns).

• For totals originally displayed at the bottom of the

results(totaling down the results like this example where thePosting Period column totals were at the bottom), then thetotal will now appear at the top of the results.


4. Use the Back button on the BEx Add-Ins toolbar to returnto a previous view as needed (or repeat the above steps andchoose the other option to change the results rows).

3






To suppress rows or columns that contain a zero amount:

Note: The original results of some queries will allowrows or columns that contain zero amounts todisplay. Use the Al l Character is tics function tosuppress those rows or columns as desired.


2. Select the options from the resulting pop-up windows per thispath: Al l Character ist ics Suppress Zero Columns/Rows.

2

1

Note that the1573147 codehas a zerobalance(indicated by

just the $ sign).

$







• The rows and/or columns containing any zero amountshave now been suppressed or hidden from view(example: 1573147 had a 0.00 amount indicated with the $sign, and is now no longer displayed as a row in the queryresults).



3

Note that the 1573147 codewith a zero balance is nowsuppressed and NO longer displays on the query results.





List of Commonly Used Queries

The table below contains the most commonly used queries that havebeen created in SAP BW for your use and a brief description of each

query.

For any SOM queries, please contact your SOMMC representativefor any additional training materials that may be available.

QueryName

InfoProvider (Cube or ODS)

Input Fields AvailableCharacteristics

Description

Fund TrialBalance (NoCommitments)with Hierarchy

FinancialManagement &Controlling

Duke CustomFinancial Objects

Commitments/actualand budget inFunds Management(Duke) (FMCFIFM)

• Fiscal Year• Posting

Period Range• Commitment

Item Range• Duke Cost

ObjectHierarchyNode (BFR)

• Duke Cost Object,hierarchy

• Link to Fund Center(BFR)

• Commitment item• Posting period• Fund Tier• Fund Category• Fund Group• Fund Class• Fund Programmatic

Class• Board Reporting

Mimics Fund Trial Balancefound in R/3. Provides actualbalances depending oncommitment item range - toget fund balances runcommitment item range

290000 to 999999.

Fund TrialBalance (NoCommitments)



Commitments/actualand budget inFunds Management(Duke) (FMCFIFM)


Period Range• Commitment

Item Range• Fund Range

and List

• Organizational Unit(BFR)

• Commitment item• Posting period• Fund Tier• Fund Group• Fund Class• Fund Programmatic

Class• Key Figures• Fund• Board Reporting

Mimics Fund Trial Balancefound in R/3. Provides actualbalances depending oncommitment item range - toget fund balances runcommitment item range290000 to 999999.

Provides a straight list of cost objects for the rangeselected (no Org. Unithierarchy levels).

ZF107,BeginningBalance withHierarchy



Commitments/actualand budget in

Funds Management(Duke) (FMCFIFM)


Period• Organizational

Unit (BFR)

• Fund• Fund Category• Fund Class• Fund Group• Fund Programmatic

Class• Commitment item• Organizational Unit

(BFR)• Key Figures• Posting Period• Board Reporting

Mimics ZF107 found in R/3.Provides actual beginningand ending balances as wellas FYTD revenues andexpenses.

TABLE CONTINUED ON NEXT PAGES





List of Commonly Used Queries (cont.)

QueryName



Description

SupportSummaryAwardStatement



Project Systems –Controlling/Dates

•

Duke CostObjecthierarchy

• Cost Object(fund code)

• Responsibleperson

• CalendarMonth/Year

•

Cost Object• PI• Project Definition• Account

Description (costelement hierarchy)

Used in Faculty Accessapplication by support staff.Provides Project’s Inceptionto Date plan versus actualexpenditures as well ascurrent month and fiscalyear to date actuals forselected cost objects. Costelements (object codes)presented using SponsoredProjects Award versusExpenditure (SPAWEXP)hierarchy.

Supt Transaction

Stmt

FinancialManagement &

Controlling


MultiProvider – FIand CO only Transactions

• Duke CostObject

hierarchy• Cost Object

(fund code)• Responsible

person• Calendar

Month/Year

• Cost Object• PI

• Account• Doc. Date• Fiscal Month• FY• Line Item Text• Offset Acct Desc

Used in Faculty Accessapplication by support staff.

Provides list of transactionsfor selected period of timefor selected cost objects.Can also select by PI.Selection by hierarchy (BFR)available but notrecommended. Drilldown todocuments not provided.

SOM BasicOperations

FinancialManagement &Controlling>DukeCustom FinancialObjects>Commitments/actualand budget in Funds

Management (Duke)(FMCFIFM)

• Fiscal Year• Posting Period• Org Unit

• Fund ProgrammaticClass

• Fund Category• Commitment item• Organizational Unit

(BFR)

The report allows users tomonitor actual SOM incomeand expenses, by broadcategories. The categories(columns) of the reportcorrespond to specific FundProgrammatic Classes, and

consist of Clinical Services, Total Education, TotalResearch, Administrationand Other, and a Grand Total.

Sum of All Tiers

FinancialManagement &Controlling>DukeCustom FinancialObjects>Commitments/actualand budget in FundsManagement (Duke)(FMCFIFM)

• Fiscal Year• Posting Period• Org Unit


(BFR)

The report allows users tomonitor actual SOM incomeand expenses, by broadcategories. The categories(columns) of the reportcorrespond to specific FundCategories, and consist of Basic Operations (Tier 1),Chief Controllable Reserves(Tier 2), Chair ControllableReserves (Tier 3), Subtotal,Faculty Discretionary, Start-Up and Other InternallyRestricted (Tier 4),Externally Restricted (Tier5), Endowment, Plan & LoadFunds (Tier 6), and Grand Total.






QueryName



Description

SOM Current Yr Actual VPrior YearActual

FinancialManagement &Controlling>DukeCustom FinancialObjects>Commitments/actual andbudget in FundsManagement (Duke)(FMCFIFM)

•

Fiscal Year• Posting Period• Fund Group• Fund• Organizational

Unit (BFR)

•

Fund Category• Commitment item• Organizational Unit

(BFR)

No Description Availble

SOM BudgetV Actual YTD

FinancialManagement &Controlling>DukeCustom FinancialObjects>Commitments/actual and

budget in FundsManagement (Duke)(FMCFIFM)

• Fiscal Year• Posting Period• Organizational

Unit (BFR)


(BFR)

No Description Availble

DepartmentalAdministratorReport

NA • OrganizationalUnit (BFR)

• NA This is a report of Departmental Administratorsby BFR.

SOM BudgetVs Actual


• Fiscal Year• Posting Period• Fund Group• Fund• Organizational

Unit (BFR)

• Fiscal Year• Fund• Fund Category• Board Reporting• Fund Programmatic

Class• Fund Tier• Fund Group•

Duke Cost Objecthierarchy• Fund Class• Posting period• Organizational Unit

(BFR)• Commitment item

This report shows the Boardof Trustees version of thecost elements in the rows.Departments should befamiliar with this view toanalyze variances.

YE11A Yearto DateBudget vs Year to DateActuals

FinancialManagement &Controlling>DukeCustom FinancialObjects>Commitments/actual andbudget in FundsManagement (Duke)

(FMCFIFM)

• Fiscal Year• Posting Period,• Fund Group• Organizational

Unit (BFR)

• Fiscal Year• Organizational Unit

(BFR)• Duke Cost Object

hierarchy• Fund• Fund Category• Board Reporting• Fund Programmatic

Class• Fund Tier• Fund Group• Fund Class• Posting period• Commitment item

The report allows users tomonitor year to to dateactual activity in comparisonto year to date originalbudget. Data is presented inthe Board format, sponsoredactivity is not included.






QueryName



Description

ZF107, AllFunds OneLine Summary


•

Fiscal Year• Posting Period

•

Posting period• Fund• Fund Category• Fund Class• Fund Group• Commitment item• Board Reporting• Fund Endowment

Estab.• Person responsible• Fund Endowment

Expen ResponsiblePerson

• Person responsible2

• Duke Cost Objecthierarchy

Shows Beginning balance, YTD Revenue and Expense,Ending balance and theAnnual Plan. If there is anoverdraft in the endingbalance, then the field willbe highlighted in red.

ZF107, AllFunds - OneLIne Summarywith Hierarchy



Unit (BFR)

• Posting period• Fund Category• Fund Programmatic

Class• Board Reporting• Commitment item• Organizational Unit• Fund• Fund Group• Fund Class

This report displays actualbalance by BFR and byfund. Columns includebeginning balance, year todate actual revenues, yearto date actual expenses,ending balance and revisedannual plan. Selection is byorganizational unit.

Fund Attribute Cross ApplicationComponents>(0FUND)

• Funds Center• Fund Range &

List

• Fund Unit• Fund Other

Sponsored• Fund Programmatic

Class• Fund Class• Fund Category• Funds Center• Fund• Person responsible• Fund Valid-

to,Board Reporting

This report provides a list of attributes assigned to funds- primary sort by fundcategory.

SOM G&ABS/C/I

FinancialManagement &Controlling>DukeCustom Financial

Objects>Commitments/actual andbudget in FundsManagement (Duke)(FMCFIFM)

• Fiscal Year• Posting Period• Duke Cost

Object

• Fund Class• Commitment item• Fund Group• Fund Category• Fund Programmatic

Class• Duke Cost Object

Provides an estimatecalculation of the G&Aexpense that will post on thefund.






QueryName



Description

SOM G&AClinical FinancialManagement &Controlling>DukeCustom FinancialObjects>Commitments/actual andbudget in FundsManagement (Duke)(FMCFIFM)

•

Fiscal Year• Posting Period• Duke Cost

Object

•

Fund Class• Commitment item• Fund Group• Fund Category• Fund Programmatic

Class• Duke Cost Object

Provides an estimatecalculation of the G&Aexpense that will post on thefund.

ZF109,SponsoredPrograms

Award OneLine Summary

FinancialManagement &Controlling>DukeCustom FinancialObjects>ProjectSystem -

COntrolling/Dates(0PS_C05)

• Duke CostObject

• Cost Object(Fund Code)

• ResponsiblePerson


• Duke Cost Object• Cost Object (Fund

Code)• P.I.• Project Start Date• Project Finish Date• Parent• SPS Proposal ID

ZF109, SponsoredPrograms Award One LineSummary. This uses theCalendar Month/Year asopposed to FiscalPeriod/Year.

ZF109,SponsoredProgramsNon-Award One LineSummary

FinancialManagement &Controlling>DukeCustom FinancialObjects>ProjectSystem -COntrolling/Dates(0PS_C05)

• Duke CostObject

• Cost Object(Fund Code)

• ResponsiblePerson


• Duke Cost Object• Cost Object (Fund

Code)• P.I.• Project Start Date• Project Finish Date

ZF109, SponsoredPrograms Non-Award OneLine Summary. This usesCalendar Month/Year asopposed to FiscalPeriod/Year.

Fund Balance(NoCommitments)with Hierarchy

FinancialManagement &Controlling>DukeCustom Financial

Objects>Commitments/actualand budget in FundsManagement (Duke)(FMCFIFM)

• Fiscal Year• Posting Period• Commitment

Item

• Duke CostObject

• Commitment item• Posting period• Board Reporting• Fund Category

• Link to Fund Center• Fund Tier• Fund Group• Fund Class• Fund Programmatic

Class• Fund• Duke Cost Object

This report displays actualbalance by BFR and byfund, beginning balancesare posted to period 0.

Selection is by cost objecthierarchy.

Fund Balance(NoCommitments)

FinancialManagement &Controlling>DukeCustom FinancialObjects>Commitments/actual and

budget in FundsManagement (Duke)(FMCFIFM)

• Fiscal Year• Posting Period• Commitment

Item• Fund

• Organizational Unit• Fund Tier• Posting period• Fund Group• Fund Class• Fund Programmatic

Class• Board Reporting• Commitment item• Fund

This report displays actualbalance by fund, beginningbalances are posted toperiod 0. Selection is byfund or range of funds.






QueryName



Description

ZF127,Plan/ActualOne LineSummary withHierarchy


•

Fiscal Year• Posting Period• Organizational

Unit (BFR)

•

Organizational Unit• Fund Center• Fund Class• Fund Category• Commitment item• Fund Group• Fund

This report displays budgetto actual by BFR and byfund. Columns includeannual budget, currentperiod plan, current periodactual, current periodvariance, year to date plan,year to date actual and yearto date variance. Selection isby organizational unit.

MCProgrammaticReports, Tiers1-6 andDrilldowns

FinancialManagement &Controlling>DukeCustom FinancialObjects>Commitme

nts/actual andbudget in FundsManagement (Duke)(FMCFIFM)


Unit (BFR)• Fund Category

• Fund• Fund Group• Fund Category• Duke Cost Object• Commitment item

The report allows users tomonitor actual SOM incomeand expenses, by broadcategories. The categories(columns) of the report

correspond to specificProgrammatic Codes. Thisreport can be filtered manydifferent ways and allowsusers to perform analysis onthe Sum of All Tiers andBasic Operations data.

MCProgrammaticReports,Fiscal Period Trend

FinancialManagement &Controlling>DukeCustom FinancialObjects>Commitments/actual andbudget in FundsManagement (Duke)

(FMCFIFM)

• Fiscal Year• Posting Period• Fund Category

• Fund Group• Fund Category• Fund Programmatic

Class• Duke Cost Object• Commitment item

A trend report on actuals byperiod.

ZF107, AllFunds - OneLine Summary(no Hierarchy)



Unit (BFR)

• Posting period• Fund Category• Fund Programmatic

Class• Board Reporting• Commitment Item• Fund• Organizational Unit

(BFR)• Fund Group• Fund Class

This report displays actualbalance by fund only (noBFR display). Columnsinclude beginning balance,year to date actualrevenues, year to dateactual expenses, endingbalance and revised annualplan. Selection is byorganizational unit.





QueryName



Description

EndowmentReport FinancialManagement &Controlling>DukeEndowmentInvestmentAccounting>EIAInvestmentMultiprovider(EIAMNVST)

•

Fiscal Year• Posting Period• EIA Investment

ID• EIA Division• Fund Category• Fund Class• Cost Object

Familiar• Number of

RemainingQuarters

• EIA Customer• Market

Valuation Date• Company

Code• Cost Object

•

Pool Value Per Unit• Distrib Rate

(Spend)• Supplemental Rate• Recipient Code• EIA Investment ID• EIA Fiscal Period• EIA Subledger FY• EIA Management

Center• EIA Process Type• EIA Customer

Entity• Cost Object• Organizational Unit• Valuation Date

Long Term Pool ParticipantBalances and ProjectedDistributions

Book ValueRollforward

FinancialManagement &Controlling>DukeEndowmentInvestmentAccounting>EIAInvestmentMultiprovider(EIAMNVST)

• Fiscal Year• Posting Period• EIA Investment

ID• EIA Division• Fund Category• Fund Class• Cost Object

Familiar• EIA Customer• Market

Valuation Date• Company

Code• Cost Object

• Pool• Fiscal Year• UMV• EIA Management

Center• EIA Fiscal Period• Cost Object• Organizational Unit• Valuation Date

Long Term Pool ParticipantActivity

introbwguide

Documents