advanced crosstabs
DESCRIPTION
This was discussed by Michael Ward at the Spring 2010 Seattle Area Business Objects User Group meeting. This is available for personal/professional use by those that attended the meeting. However, any use as training material by anyone other than Michael, without his express consent, is in violation of copyright laws.TRANSCRIPT
56132 ParkviewShelby Twp, MI 48316586.677.8300 Phone586.677.8301 Faxhttp://www.cttsbi.com
Advanced Crosstabs Features and Functions WebIntelligence™ 3.1
Last Revised: 2/1/2010
© 2010 – Creative Technology and Training Solutions, LLC 2
Advanced Crosstabs Features and Functions 3.1
Trademarks & Copyrights
The Business Objects logo, Business Miner, Business Query, and WebIntelligence are registered trademarks of Business Objects S.A. The Business Objects tagline, Broadcast Agent, Business Objects, Personal Trainer, Rapid Deployment Templates, and Set Analyzer are trademarks of Business Objects S.A. All screen shots from Business Objects software products are copyrighted by Business Objects, S.A., all rights reserved.
Microsoft, Windows, Windows NT, Access, Microsoft Windows VBA, the Visual Basic Logo, and other names of Microsoft products referenced herein are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
Oracle is a registered trademark of Oracle Corporation. All other names of Oracle products referenced herein are trademarks or registered trademarks of Oracle Corporation.
All other product and company names mentioned herein are the trademarks of their respective owners. CTTS is not an authorized partner of Business Objects S.A.
© 2010 – Creative Technology and Training Solutions, LLC 3
Advanced Crosstabs Features and Functions 3.1
Objective
To provide you with skills to…
Create Crosstab Block reports
Work with Column and Row Headers
Create Summary Cells and Master Cells
Apply Calculations Separately to Rows and Columns
Calculate Variance and Sum of the Variance
© 2010 – Creative Technology and Training Solutions, LLC 4
Advanced Crosstabs Features and Functions 3.1
Blocks
Vertical Table Horizontal TableCrosstabChartForm
© 2010 – Creative Technology and Training Solutions, LLC 5
Advanced Crosstabs Features and Functions 3.1
Blocks
Vertical TableData Appears in Columns Headings Appear on Top
© 2010 – Creative Technology and Training Solutions, LLC 6
Advanced Crosstabs Features and Functions 3.1
Blocks
Horizontal TableData Appears in RowsHeadings Appear on Left
© 2010 – Creative Technology and Training Solutions, LLC 7
Advanced Crosstabs Features and Functions 3.1
Blocks
CrosstabData Appears in Both Columns and RowsHeadings Do Not Appear by Default
© 2010 – Creative Technology and Training Solutions, LLC 8
Advanced Crosstabs Features and Functions 3.1
Blocks
ChartsPie, Column, Line, Area, XY (Scatter)
© 2010 – Creative Technology and Training Solutions, LLC 9
Advanced Crosstabs Features and Functions 3.1
Different Ways to Create a Crosstab Block
From Existing Vertical TableUsing Crosstab TemplateUsing Turn To Option
© 2010 – Creative Technology and Training Solutions, LLC 10
Advanced Crosstabs Features and Functions 3.1
Create a Crosstab
From Existing Vertical Table
3. When VerticalBlue Bar Appears,
Release Mouse Button
1. Select Anywhere in the Year Column
2. Drag Year CellDirectly Above
Top of Block
© 2010 – Creative Technology and Training Solutions, LLC 11
Advanced Crosstabs Features and Functions 3.1
Create a Crosstab
Using Crosstab Template
2. Click and DragCrosstab Template
Over the Block
1. Click Templates Tab
© 2010 – Creative Technology and Training Solutions, LLC 12
Advanced Crosstabs Features and Functions 3.1
CrosstabUsing Turn To Option
2. Select Turn Tofrom Menu
1. Right-Click onBorder of Block
© 2010 – Creative Technology and Training Solutions, LLC 13
Advanced Crosstabs Features and Functions 3.1
CrosstabUsing Turn To Option
1. SelectTables Tab
2. Select Crosstab Picture and Click OK
3. Click OK
© 2010 – Creative Technology and Training Solutions, LLC 14
Advanced Crosstabs Features and Functions 3.1
Crosstab
At Least Two Dimensions and One Measure Use Properties Tab to Edit Dimensions Define . . .
Columns
Row Headers
Measures Define . . .
Body
© 2010 – Creative Technology and Training Solutions, LLC 15
Advanced Crosstabs Features and Functions 3.1
Pivoting Data Pivoting via Headers
3.. When Year CellCompletely Overlays
City Cell, Drop to Swap
2. Drag Year HeadingDirectly Over the
City Value inThe Heading
1. Select Heading Portion
of Year Column
© 2010 – Creative Technology and Training Solutions, LLC 16
Advanced Crosstabs Features and Functions 3.1
Pivoting Data
Pivoting via Structure Button1. Click View Structure
Button on Functions Toolbar
© 2010 – Creative Technology and Training Solutions, LLC 17
Advanced Crosstabs Features and Functions 3.1
Pivoting Data
Pivoting via Structure Button
4.. When Year Cell Completely Overlays
City Cell, Drop to Swap
3. Drag Year ObjectDirectly Over City in Structure
2. Select Year in Structure 5. Click View
Results to Display Crosstab
© 2010 – Creative Technology and Training Solutions, LLC 18
Advanced Crosstabs Features and Functions 3.1
Pivoting Data Setting Alternate Row/Column
Colors
3. Click the Plus-Sign Next to Alternate
Row/Column ColorsOption
1. Click the Properties Tab
2. With Mouse, HoverOver Block Border and
Left-Mouse Click to Select
© 2010 – Creative Technology and Training Solutions, LLC 19
Advanced Crosstabs Features and Functions 3.1
Pivoting Data Setting Alternate Row/Column
Colors
5. Click Drop-Down Arrow to DisplayColor Palette, and
Select Desired Color
4. Click Anywhere on Color Option
© 2010 – Creative Technology and Training Solutions, LLC 20
Advanced Crosstabs Features and Functions 3.1
Crosstabs and Break Function Breaking - Down
2. Click on Break Toolbar Button
1. Select Year Dimension Value
© 2010 – Creative Technology and Training Solutions, LLC 21
Advanced Crosstabs Features and Functions 3.1
Crosstabs and Break Function Breaking - Down
1. Stay Selected on Break Dimension and Click on
Properties Tab 3. Add SUM to Crosstab
2. Select Break Choices such as Center Values
Across Break
© 2010 – Creative Technology and Training Solutions, LLC 22
Advanced Crosstabs Features and Functions 3.1
Crosstabs and Break Function Breaking - Across
1. Click on Show/Hide Formula Toolbar Button
2. Left-Mouse Click on Year Dimension value 3. Click on Break
Toolbar Button
© 2010 – Creative Technology and Training Solutions, LLC 23
Advanced Crosstabs Features and Functions 3.1
Crosstabs and Break Function
Applying a Formula
Build via Typing in Formula Bar
Breaking - Across
4. Click Inside Blank Column
Created by Break, and Type Formula
in Toolbar
© 2010 – Creative Technology and Training Solutions, LLC 24
Advanced Crosstabs Features and Functions 3.1
Crosstabs and Break Function
Applying a Formula
Build via Formula Editor
Breaking - Across
1. Select Variables(s) from
Data Window
2. Select Calculation(s) from Operators Window
3. Formula Built on the Top Window
Panel
© 2010 – Creative Technology and Training Solutions, LLC 25
Advanced Crosstabs Features and Functions 3.1
Crosstabs and Break Function
Break Properties
Breaking - Across
2. Select Break Choices such as Center Values
Across Break
1. Stay Selected on Break Dimension and Click on
Properties Tab
© 2010 – Creative Technology and Training Solutions, LLC 26
Advanced Crosstabs Features and Functions 3.1
Crosstabs and Rank Function
3. Click on Ranking Toolbar Button
1. Add SUM Function to Crosstab
Report
2. Click on Dimension in
Crosstab Header to Apply Ranking
© 2010 – Creative Technology and Training Solutions, LLC 27
Advanced Crosstabs Features and Functions 3.1
Crosstabs and Rank Function
4. Select Top or Bottom Ranking and the Number to Rank
5. Select Measure from Query on
Which to Base the Ranking
6. Select Dimension to be Ranked and
Click OK
© 2010 – Creative Technology and Training Solutions, LLC 28
Advanced Crosstabs Features and Functions 3.1
Crosstabs and Rank Function
Ranking Displayed Across Dimension From High-to-Low
Measure Values
© 2010 – Creative Technology and Training Solutions, LLC 29
Advanced Crosstabs Features and Functions 3.1
Show Variable Headers
By Default, a Crosstab Report Does Not Display True Variable Headers
© 2010 – Creative Technology and Training Solutions, LLC 30
Advanced Crosstabs Features and Functions 3.1
Show Variable Headers To Display Actual Headers
4. Check theShow
Object NameBox
3. Click thePlus-Sign
Next to DisplayOption
1. Click the Properties Tab2. With Mouse, HoverOver Block Border and
Left-Mouse Click to Select
© 2010 – Creative Technology and Training Solutions, LLC 31
Advanced Crosstabs Features and Functions 3.1
Show Variable Headers Actual Variable Headers now Display
© 2010 – Creative Technology and Training Solutions, LLC 32
Advanced Crosstabs Features and Functions 3.1
Crosstabs and Master Cells Reconfigure Crosstab from either
Report Manager or from Report itselfDrag/Drop Object From Report
Manager Window into area Above
Report, creating a Master Cell
© 2010 – Creative Technology and Training Solutions, LLC 33
Advanced Crosstabs Features and Functions 3.1
Crosstabs and Master Cells Reconfigure Crosstab from either
Report Manager or from Report itself
Swap between Quarter and Year by clicking on Quarter. Drag and Drop Over Year Value
© 2010 – Creative Technology and Training Solutions, LLC 34
Advanced Crosstabs Features and Functions 3.1
Adding Summary Cells3. Repeat process and drop
Sales Revenue next to Lines Master Cell.
1. Select Sales Revenue.Drag next to Quarter Master Cell and Hold Mouse Button.
2. Press Control Key.Release Mouse.
© 2010 – Creative Technology and Training Solutions, LLC 35
Advanced Crosstabs Features and Functions 3.1
Adding Summary CellsSummed Sales Revenue for Accessories Block Only and
For All 2006
© 2010 – Creative Technology and Training Solutions, LLC 36
Advanced Crosstabs Features and Functions 3.1
Drilling on Crosstab Blocks Create new Query Select Objects from One or More Hierarchies Drill On Dimensions Only
Year, Quarter and Month are from Time Hierarchy;
State, City and Store Name are from Store Hierarchy.
© 2010 – Creative Technology and Training Solutions, LLC 37
Advanced Crosstabs Features and Functions 3.1
Drilling on Crosstab Blocks
2. Drag Block Left, into Report Manager Window and Drop
1. Hover Over Border of Block; Left-Mouse Click Border
© 2010 – Creative Technology and Training Solutions, LLC 38
Advanced Crosstabs Features and Functions 3.1
Drilling on Crosstab Blocks1. Multi-Select using Control
Button, All Top-Level Hierarchy Objects and Drop
in Report Window
2. Create Crosstab with Drag and Drop, Template, or Right- Click Border/Turn To Option.
© 2010 – Creative Technology and Training Solutions, LLC 39
Advanced Crosstabs Features and Functions 3.1
Drilling on Crosstab Blocks Pivot Columns as NeededClick the Drill Mode Button
© 2010 – Creative Technology and Training Solutions, LLC 40
Advanced Crosstabs Features and Functions 3.1
Drilling on Crosstab Blocks
Report Cells Have Become Drillable HyperLinks
Drill Down on Multiple HierarchiesClick Once on a Measure Cell
in Order to Drill Down on Multiple Hierarchies – For Example: 2008-California.
© 2010 – Creative Technology and Training Solutions, LLC 41
Advanced Crosstabs Features and Functions 3.1
Drilling on Crosstab Blocks
Upper Drill Levels Displayed in Drill Filter Toolbar
Drill Down/Up on Multiple Hierarchies
Click One Up-Arrow To Drill Up on That Hierarchy
Report Drills Down to the Header Values at the Intersection of the
Row/Column Selected
© 2010 – Creative Technology and Training Solutions, LLC 42
Advanced Crosstabs Features and Functions 3.1
Drilling on Crosstab BlocksDrill Down/Up on Single Hierarchy
Click Once on a Header Cell in Order to Drill Down On That
Hierarchy.
© 2010 – Creative Technology and Training Solutions, LLC 43
Advanced Crosstabs Features and Functions 3.1
Drilling on Crosstab BlocksWorking with Drill Filters on Drill Toolbar
2. Click on Up-Arrow to Drill Back Up
© 2010 – Creative Technology and Training Solutions, LLC 44
Advanced Crosstabs Features and Functions 3.1
Drilling on Crosstab BlocksWorking with Drill Filters on Drill Toolbar
Drag Additional Report Manager Variables into the Drill Toolbar to Create a Drill
Report Similar to Pivot Table
© 2010 – Creative Technology and Training Solutions, LLC 45
Advanced Crosstabs Features and Functions 3.1
Drilling on Crosstab BlocksWorking with Drill Filters on Drill Toolbar
2. Drag Into the Report Manager, to Remove Variable from Drill Toolbar
1.Click Directly on Dimension Icon in Drill Toolbar
© 2010 – Creative Technology and Training Solutions, LLC 46
Advanced Crosstabs Features and Functions 3.1
Drilling on Crosstab BlocksWorking with Drill Filters on Drill ToolbarDrill Across
Click on Any Drill Toolbar Drop-Down Arrow and Select a Different Value, to Drill Across
© 2010 – Creative Technology and Training Solutions, LLC 47
Advanced Crosstabs Features and Functions 3.1
Drilling on Crosstab BlocksDrill By
2. Select Drill By Option, Select Class, and Dimension within
Class
1. Right-Click Directly on Dimension Icon in Drill Toolbar
© 2010 – Creative Technology and Training Solutions, LLC 48
Advanced Crosstabs Features and Functions 3.1
Drilling on Crosstab BlocksDrill By Drill By Dimension Displays in
Report, and Drilled From Dimension is Moved to Drill
Toolbar
© 2010 – Creative Technology and Training Solutions, LLC 49
Advanced Crosstabs Features and Functions 3.1
Converting Crosstab to Chart
1. Right-click on Crosstab Border, Select Turn To
2. Select Chart Type and Click OK
© 2010 – Creative Technology and Training Solutions, LLC 50
Advanced Crosstabs Features and Functions 3.1
Chart Formatting and Drilling1. To Format,
Click on a Chart Area, Then Click on
Properties Tab
2. To Enlarge the Chart Display, Drag Out the Side and/or
Bottom Chart Borders
© 2010 – Creative Technology and Training Solutions, LLC 51
Advanced Crosstabs Features and Functions 3.1
Chart Formatting and DrillingClick on Axis
Dimensions or Data Columns to Drill
Down or Up
© 2010 – Creative Technology and Training Solutions, LLC 52
Advanced Crosstabs Features and Functions 3.1
Chart Formatting and DrillingResult of Drilling Down From State to City
© 2010 – Creative Technology and Training Solutions, LLC 53
Advanced Crosstabs Features and Functions 3.1
© 2009 – Creative Technology and Training Solutions, LLC
Contact Information: Michael Ward(586) [email protected]
BusinessObjects™ 3.1 Training Classes:Desktop Intelligence™ 3.1 Basic Reporting (2 Days)Desktop Intelligence™ 3.1 Intermediate Reporting (1 Day)Desktop Intelligence™ 3.1 Advanced Reporting (1 Day)Desktop Intelligence™ 3.1 Advanced Crosstabs & Functions (1/2 Day) Desktop Intelligence™ 3.1 Foundations (1 Day) WebIntelligence™ 3.1 Intro To InfoView (1 Day) WebIntelligence™ 3.1 Basic Reporting (1 Day)WebIntelligence™ 3.1 Intermediate Reporting (1 Day)WebIntelligence™ 3.1 Advanced Reporting (1 Day)WebIntelligence™ 3.1 Rich Client (1 Day)BusinessObjects™ 3.1 Designer (2 Days)BusinessObjects™ 3.1 Scheduler (2 Hours) BuisnessObjects™ 3.1 Administration (2 Days)
© 2010 – Creative Technology and Training Solutions, LLC 54
Advanced Crosstabs Features and Functions 3.1
© 2009 – Creative Technology and Training Solutions, LLC
DI 3.1Basic Reporting
2 Days
DI 3.1Interm Reporting
1 Day
Desktop Intelligence3.1 Reporting
BO 3.1Designer
2 Days
BO 3.1Administration
2 Days
WebIntelligence3.1 Reporting
Webi 3.1Intermediate
Reporting1 Day
Webi 3.1Basic Reporting
1 Day
DI 3.1Adv Reporting
1 Day
DI 3.1Adv Crosstabs
1/2 Day
Webi 3.1Advanced Reporting
1 Day
DI 3.1Foundations
1 Day
BO 3.1Rich Client
1 Day