St. Louis Business Objects Users’ Group

1. St. Louis Business Objects Users’ Group Business Objects ReportTips and Tricks Paula Ohlendorf September 27, 2002

2. About Me • Paula Ohlendorf • Working with BI tools since 1998: BusinessObjects, Brio • Sense Corp • Consulting practice dedicated to improving the profitability and quality of its clients’ businesses through • Delivery of business intelligence • Data management and warehousing • System integration

3. Tips and Tricks • Scenarios and Solutions… • The Solutions • Show one of many ways to approach a problem • Focus on BusinessObjects report users • Use eFashion and Island Resorts Marketing Universes for demos • Where do solutions come from? • Sense Corp Solutions • User Group websites • Fellow BusinessObjects users • Not the BusinessObjects User Guide…

4. Scenarios and Solutions • Place Measures in Rows • Duplicate Data Providers and Reports • Create an Outline-style Report • Shade Alternate Rows • Create Inner Joins with Multiple Data Providers • Use Dimensions from Multiple Data Providers • Indicate Active Filters • Filter Aggregate Variables

5. Place Measures in Rows Scenario • You have multiple measures in your report. Instead of displaying them in separate columns, you’d like to see them in rows

6. Place Measures in Rows Solution • Insert rows to hold Measures • Insert a column to hold Measure labels • Move Measure labels into new column • Move Measures into one column • Delete blank columns • Type new labels in Column Headings

7. Duplicate Data Providers and Reports Scenario • You want to duplicate a report, data provider(s) or both to the current document, another open document or a new document

8. Duplicate Data Providers and Reports Solution • Download free Add-In: http://www.islink.com/isi_library_downloads_BO_reporting.htm#COPYQRY • Add the Add-In • From the menu bar choose Tools -> Addins ... • Click on the browse button and navigate to the location where you stored the Add-In • Make sure the check box is checked for the RepDupe Add-In • Click OK • From the menu bar choose Insert -> Duplicate Reports …

9. Create an Outline-style Report Scenario • You have a hierarchical report that you’d like to present in an outline format as opposed to a simple table ü

10. Create an Outline-style Report Solution • View -> Formula Bar • Move Break value into Break Header • Delete Break headers for other columns • Resize cells that contained Break value • Resize cells in Footer to match body cells • Format the table so that borders and backgrounds are appropriate

11. Shade Alternate Rows Scenario • You have many rows in a table and want to shade every other (or every 3rd, 4th) row

12. Shade Alternate Rows Solution • Create variable that assigns 1’s and 0’s to every row • RowShading =Mod(LineNumber() , 2) • Add variable into the table • Create alerter that shades rows where RowShading variable = 1 • Hide RowShading variable

13. Create Inner Joins with Multiple Data Providers Scenario • You want to use Multiple Data Providers, but BusinessObjects creates an Outer Join between Data Providers. You need an Inner Join… Create an Inner Join

14. Create Inner Joins with Multiple Data Providers Solution • Create a Global Filter on the table with Multiple Data Providers • = Not IsNull (<Measure from Query 1>) And Not IsNull (<Measure from Query 2>)

15. Use Dimensions from Multiple Data Providers Scenario • You want to use Multiple Data Providers, but BusinessObjects won’t let you use unlinked Dimensions from both Data Providers

16. Use Dimensions from Multiple Data Providers Solution • If one of your Data Providers is a personal data file, free-hand SQL, or a stored procedure… • In the Data Manager, change the object’s qualifications to Detail and associate it with a linked Dimension • If both of your Data Providers are Universes… • Create a local variable equal to the Object whose qualification you want to change • Define the local variable as Detail and associate it with a linked Dimension

17. Indicate Active Filters Scenario • You want to create a way for users of a document to quickly see if there are filters applied on that document ? Does the sum represent ALL of the data

18. Indicate Active Filters Solution • Add a variable called “Filter Indicator” • = Count( Body ) & " of " & NoFilter(Count( Body )) & " rows displayed” • Count(Body) returns the number of rows displayed in the block • NoFilter(Count(Body)) counts the number of rows that would be displayed in the block if there were no filter • Add a row to the bottom of the block and insert the Filter Indicator variable

19. Filter Aggregate Variables Scenario • You have an aggregate variable, like a Sum, in your report and you’d like to filter it Sales revenue > $1,500,000

20. Filter Aggregate Variables Solution • Insert a Condition on the variable you want to filter • Use the In list Operator • Use the Subquery operand • Insert the variable you want to filter as the Result Object • Insert the same conditions as the main Query • Insert a condition on the measure you want to filter

21. Resources • www.businessobjects.com/forms/tipsandtricks_login.asp • www.forumtopics.com • www.techsupport.businessobjects.com • www.islink.com • www.openitx.com/g/businessobjects-l.asp • Other User Groups! • http://www.bonymaug.com/ • http://www.integrasolutions.net/ntboug/ • BusinessObjects User’s Guide (businessobjects51en.pdf)