Revised 7/26/2016
BW Tips and Tricks
Sections:
A.Helpful Hints
B.Variables Entry Options for Entering Selections
C.Variables Entry Screen Variants
D.Bookmarks
E.View/Print in PDF
F.Export to Excel
G.Return to the Variables Entry Screen
H.Add Columns Within the Report
I.Remove Columns from the Report
J.Change Column Order
K.Key & Text Options
L.Adjust the Sort Order of Any Column
M.Add and Remove Results Rows (Totals and Subtotals)
N.Move Results Rows to Top of Data
O.Activate & Deactivate Hierarchies and Levels
P.Other Hierarchy Adjustments
Q.Go To R/3 Transaction or Detailed BW Report
R.Display Repeated Text in Columns
S.Export to CSV
T.Conditions
U.Exceptions
A.Helpful Hints
1.It is usually best to use Internet Explorer.
2.If you get messages to keep logging in, call the UC Helpdesk for assistance.
3.It is never a good idea to be among the first to upgrade to the latest Internet Explorer and Excel versions when using BW.
4.Always make sure you log into UCFlex/BW before executing bookmarks. Keep a separate internet window open & execute bookmarks from there.
5.Maintain a list of frequently used variable lists of hierarchy nodes, ranges and multiple singles values that can be quickly used as copy/paste variable entries.
6.NEVER use the Personalizationfeature.
7.Save sets of common variables as Variants for individual reports – they’ll work for the report and bookmarks of the individual report.
8.An error message on Variables Entry that says a characteristic has no master data “or you have no authorization” is ALWAYS a user data entry error. All users who have access to a report have authorization for the data so it is never true that the mistake is based on no authorization.
9.If you encounter a “500 Server” error, it is best to log completely out of UCFlex and back into BW.
10.Check for and use Go To drilldowns to RRI detailed reports and jumps to R/3 detailed data. They’re faster in 7.0 BW.
11.To quickly & easily move the location of report columns, open the Navigation Block and drag columns around within the block including moving them between Rows and Free Characteristics. But, you normally can only swap the locations of the Key Figures columns by dragging them around within the report.
12.As in 3.5 BW, it is faster and easier to totally reformat a report if you filter the report to just a few rows first. Then add/subtract/re-sort columns, add/remove subtotals, change key/text, etc. When formatting is complete, run the report for the complete listing which is needed. Save the format as a bookmark that report style may be needed again.
13.Blank columns may be available in the Key Figures filter. They are identified by a long technical name of alpha-numeric characters such as
14.If you print from PDF, keep the Orientation setting on “Auto portrait/landscape” so it will follow the choice that you made on the Export Dialog box when moving to PDF. The setting you choose in the initial “Export Dialog” box before moving to PDF will control how the data fits on the page when you print, regardless of whether you change between portrait and landscape inside PDF.
B.Variables Entry Options for Entering Selections
1.Type one Key directly in the box (cannot type text here)
2.Type a range if the name of the variable says “Range” including spaces on both sides of the hyphen
3.Type an exclamation point “!” in front of a single item or range to EXCLUDE
4.Click the Current Selection drop-down box to the right for other entry options:
a.Hierarchies – click open the carats, highlight and Add; OK when complete
b.Some variables allow many selection options inside.
A.Choose “Single Values” to select values individually from the list or to type them yourself
B.Enter the key if you already know it and then click Add and OK
C.Highlight one or multiples of choices from the list and click add to select from the list
Change the Maximum Records and click Refresh to access values when there are more than 1000 available
D.Highlight any selections you’ve made on the right and click the red box to EXCLUDE
5.Choose Value ranges in order to get this drop-down with choices: (Note: “Contains pattern” is the star wildcard choice*)
Note: You can use the drop-down and above screen to select value ranges or type values directly into the Variables Entry screen. You MUST use the drop-down to identify multiple groups such as three ranges of cost centers or one range of cost centers with one exclusion.
Between
Greater
Greater or Equal to
Less Than
Less Than or Equal to
Contains Pattern (all Fund Owners that start with 626 in the example below)
6.Some variables ONLY offer the opportunity to enter Multiple SINGLE Values and not the full set of value ranges above. These have [MSV] in their name so users can easily identify the difference.
Users can list choices on the screen with a semi-colon and space between the choices: OR use the drop-down to select individual items.
OR users can utilize the drop-down to select individual items.
7.Search for Variables Using Text Instead of Key:
a.Click the “Show View” box and select Search
b.The screen will change and will show the “Text” option automatically. (If you decide to change back to Key you can do so with the drop-down. so everything is included in your search (such as changing the 1000 to 5000) and then click Refresh
c.Complete the empty box, use the Wildcard (*) and increase the Maximum Records to make sure you don’t miss something in your search. Then click the Search button. The search is Case Sensitive!!
C.Variables Entry Screen Personalization and Screen Variants
1.Selection Variants – Save Selection Variants for INDIVIDUAL reports if you regularly want to easily access a grouping of variables for a report. One OR MORE variables may be customized in the Screen Variant. Variants also work for bookmarks of the main report.
2.Variables Personalization –DO NOT USE!!
D.Bookmarks
1. Bookmarks for personal use
a.Click the Bookmark icon
b.Type the name of the new bookmark
c.Select the Favorites folder to save it in or create a new folder for it
d.Click Add
e.Close the window
2.Bookmarks to Share with Others
a.Select “Copy to Clipboard” if you have built a bookmark you want to send to someone else who has BW access.
b.Use the Office “paste” functionality in an email or word document to paste the bookmark into the document.
c.Electronically send the document to the intended bookmark recipient. He/she must first log into BW and then double-click on the bookmark to execute it.
E.View/Print in PDF
a.Click the PDF Icon at the top of the Report
b.Adjust any print/view settings as desired. (The default on the page will usually be right but can be adjusted by the user.)
c.Click OK
d.Once inside PDF you can utilize any of the view settings as desired.
e.If you print from PDF, keep the Orientation setting on “Auto portrait/landscape” so it will follow the choice that you made on the Export Dialog box below when moving to PDF.
NOTE: The setting you choose in the initial “Export Dialog” box before moving to PDF will control how the data fits on the page when you print, regardless of whether you change between portrait and landscape inside PDF.
F.Export to Excel
a.Click the Export to Excel icon at the top of the report
b.Click the Open or Save button and follow instructions to open and name
c.Adjust the column widths.
G.Return to the Variables Entry Screen
1.To Save all the formatting, filters and variables, click the Variable Screen button
2.To Close Out and return to Variables Entry to start completely over, click the Window’s red X to close.
H.Add Columns Within the Report
Users have many options for adding columns to the report. They can be added from the right click context menu or they can be selected from the Navigation Block.
1.Adding Columns via use of the Right Click Context Menu:
Right click on an item in the column that should be on the RIGHT on the new column. Click Change Drilldown, Drill Down By and then select the new column.
NOTE: Your cursor must be on the column heading of a Key Figure in order to access the right click menu to change the filter and add a column.
2.Adding Columns via use of the Navigation Block:
Click the box to the right on the Navigation Block section. A list appears with all the available objects in the report. Add columns and Key Figures from here, if desired. Click on Key Figures to get a list of options or click on one of the Free Characteristics and drag it into the report.
NOTE – Drag columns from the “Characteristics” section to the “Rows” sections to quickly add new columns.
To access the Key Figures, right click on the word Key Figures. If there are no EXTRA Key Figures available, clicking on “Key Figures” won’t do anything. If at least one extra key figure column is available a box will open and it can be utilized like the variables entry box
NOTE – even though it looks like you may be able to reposition the order of the Key Figure columns, sometimes you can’t. This is controlled by the query the report is based on.
I.Remove Columns
Users have three methods to choose from to remove columns from the report. They can be dragged out of the report, they can be removed via the right click menu or they can be removed via the Navigation Block. Key Figures columns can ONLY be removed via the Navigation Block.
1.To drag out columns to remove them, click on any data in a regular column and then drag it out to the open white space area.
2.Right click on the column to be removed, select Change Drilldown and then Remove Drilldown
3.To remove Key Figures, open the Navigation Block, and then Click Filter and Select Filter Value. Then highlight the column to be removed from the report and click Remove OR IF ALL THE COLUMNS ARE ON THE LEFT, Add back only the ones to be viewed in the report. (NOTE: If you select Remove Filter Value, it puts ALL THE KEY FIGURES BACK INTO THE REPORT.)
J.Change Column Order
Users have two methods for changing column order of regular rows and two methods to change the order of Key Figures. Normally the easiest method is to drag columns to their new location. To swap regular column locations, utilize the right click menu. Key Figures may be rearranged via the Navigation Block too..
1.CAREFULLY drag columns. Sometimes users get unexpected results. When this happens, right click and choose to Back> Back One Navigation Step.
2.Right Click on one the the columns involved in the swap and click Change Drilldown > Swap xxxx with > then select the column to swap with.
3.The Key Figures can be moved around once the Navigation Block is open and Key Figures >Filter > Select Filter Value has been selected
K.Key &Text Options
Users can choose to change the Key and Text view on the report via the Right Click Menu or the Navigation Block.
1.Right click on the desired column and choose Properties > Characteristic. Then select from the usually very long list on the General tab, Display selection. Click OK to choose the option.
2. If the Navigation Block is already open, then the same choices as above can be accessed by right clicking on the row to be changed, then click Properties > Characteristic and make a selection form the Properties Box.
L.Adjust the Sort Order of Any Column
Often the easiest way to adjust the sort order of a column is to click the sort triangle to the right of any of the column headings. BUT, if the column includes key and text and you want to have one of them first but sort by the other, the Right Click menu has to be utilized.
1.Use the sort triangle to change from ascending to descending and vice versa.
2.Use the Right Click Menu instead by right clicking on the object and selecting the desired result.
M.Add and Remove Results Rows (Totals and Subtotals)
Like changing key and text above, users can change what subtotals are available in the report from the right click menu or from the Navigation Block.
N.Move Results Rows to Top of Data
Users may choose to display yellow results rows (totals and sub-totals) at the top of data instead of at the end of the data. (This is especially useful if only overall totals are being displayed.)
Right Click on data, select Properties > Axis and click the Data Formatting tab. Adjust the Result Position to Top.
O.Activate and Deactivate Hierarchies and Levels
Users can use the Properties/Hierarchy tab of the column or use the Right Click option to make changes.
1.To Activate a Hierarchy that isn’t currently turned on, Right Click on the column’s heading or one of the individual objects and choose Hierarchy > Hierarchy Active
2.To Deactivate a Hierarchy that is on, Right Click on the columns’ heading and choose Hierarchy > Hierarchy Active which has the check mark next to it (which is odd, but it’ll turn the hierarchy off)
3.To CHANGE the levels of the ENTIRE hierarchy, Right Click on the Column’s Heading and choose Hierarchy > Expand Hierarchy and then choose the level. Depending on the level of the hierarchy already in your report when you selected the variables, your level options will vary.
4.To View MORE LEVELS of the hierarchy for an INDIVIDUAL NODE, Right click on the object in the column and then make a hierarchy selection as desired.
P.Other Hierarchy Adjustments
The Properties menu of a data column provides many hierarchy adjustments on the Hierarchy tab. Users can make multiple selections as needed and check OK to change the data column.
Q.Go To R/3 Transaction or Detailed BW Report
Some Financial reports and many HR reports provide a “Go To” option that allows users to jump to R/3 for more detailed information or to a detailed BW report.
For example, an HR “Go To” selection can jump to PA20 in R/3 to reflect more HR data about the employee selected.
For example, an FI “Go To” selection can jump from an individual amount for a commitment item on a budget report to a version of the FM865 detailed report for just that dollar total selected.
Right click on the employee, dollar amount, FI document number, etc. based on the report and select the Go To Option and the jump option.
R.Display Repeated Text in Columns
Users may wish to see all the columns and rows filled in the report while viewing it on the web. (When exported to Excel this will automatically happen anyway in 7.0 BW.) To show all repeated text in the web view of a report, Right Click on any data and select Properties > Web Item. Then click the Data-Specific Settings tab, check the Display Repeated Texts box and then click OK.
S.Export to CSV
Export to Excel if all that is needed is the rows and columns filled. Export to CSV if a special file is needed for downloading in another system.
1. Click the Export to CSV button.
2. Click the Open or Save button and follow instructions to open or save
3. Convert the file as needed to CSV settings.
T.Conditions
Establish Conditions to temporarily filter and display the data in the report that meets certain conditions. For example, users often what to limit a Fund Balance report view to only those funds whose ending fund balance is less than or equal to $0.
To build the condition to display only Ending Fund Balances with totals less than or equal to $0:
Right click on any column heading (or data in the left section of the report), select Filter > Conditions > Create New Condition
1. Set the Condition Type: select “Threshold value condition” and click Next
2. Set the Condition Parameter: Choose the Key Figure to set the condition on (Ending Expendable Fund Balance), the Operator (<=) and the Value (0.00) and click Next
3. Set the Characteristic Assignment: Select “Detailed characteristic along the” and choose “Rows.” Then click Next