...

Multiple Aligned Column Headers in Lists and Crosstabs Tip or Technique

by user

on
Category: Documents
1

views

Report

Comments

Transcript

Multiple Aligned Column Headers in Lists and Crosstabs Tip or Technique
Tip or Technique
Multiple Aligned Column Headers
in Lists and Crosstabs
Product(s): IBM Cognos 8
Area of Interest: Report Design
Multiple Aligned Column Headers in Lists and Crosstabs
2
Copyright
Copyright © 2008 Cognos ULC (formerly Cognos Incorporated). Cognos ULC
is an IBM Company. While every attempt has been made to ensure that the
information in this document is accurate and complete, some typographical
errors or technical inaccuracies may exist. Cognos does not accept
responsibility for any kind of loss resulting from the use of information
contained in this document. This document shows the publication date. The
information contained in this document is subject to change without notice.
Any improvements or changes to the information contained in this document
will be documented in subsequent editions. This document contains
proprietary information of Cognos. All rights are reserved. No part of this
document may be copied, photocopied, reproduced, stored in a retrieval
system, transmitted in any form or by any means, or translated into another
language without the prior written consent of Cognos. Cognos and the
Cognos logo are trademarks of Cognos ULC (formerly Cognos Incorporated)
in the United States and/or other countries. IBM and the IBM logo are
trademarks of International Business Machines Corporation in the United
States, or other countries, or both. All other names are trademarks or
registered trademarks of their respective companies. Information about
Cognos products can be found at www.cognos.com
This document is maintained by the Best Practices, Product and Technology
team. You can send comments, suggestions, and additions to
[email protected] .
IBM Cognos Proprietary Information
Multiple Aligned Column Headers in Lists and Crosstabs
3
Contents
1
INTRODUCTION ............................................................................................ 4
1.1
1.2
1.3
PURPOSE ............................................................................................................4
APPLICABILITY .....................................................................................................4
EXCLUSIONS AND EXCEPTIONS ..................................................................................4
2
REPORT REQUIREMENTS.............................................................................. 4
3
LAYOUT AND DESIGN ................................................................................... 5
3.1
3.2
LIST REPORT .......................................................................................................5
CROSS TAB REPORT ...............................................................................................8
IBM Cognos Proprietary Information
Multiple Aligned Column Headers in Lists and Crosstabs
4
1 Introduction
1.1
Purpose
This document will show step-by-step how to create list and crosstab reports
in Report Studio that contain multiple aligned header rows to ‘group’ column
information together and display additional information about the items
displayed in the report.
1.2
Applicability
The following examples were built using IBM Cognos 8.1 MR2 Report Studio
against the GO Sales and Retailers package. The list report technique can be
applied to Report Studio reports created in ReportNet as well as in IBM
Cognos 8. The crosstab technique has been available since the introduction of
the ‘Crosstab Space (with Text)’ insertable toolbox object in IBM Cognos 8
MR1.
1.3
Exclusions and Exceptions
These techniques should work with Relational, Dimensionally Modeled
Relational (DMR), and OLAP data sources. However, they have not been
explicitly tested in these scenarios.
2 Report Requirements
Often the metadata available from a data source does not provide some of
the grouping information necessary for quick evaluation by consumers. While
the information can be added to a report, there are some techniques which
are less ideal than others. The best approach is to incorporate layout
elements without adding to the query overhead.
The issue with list reports is that the existing column titles does not allow for
additional rows to be added.
The techniques described below will allow a report author to add header
items such as “Product Per Unit” and “Product Sales” to list reports as in the
following:
IBM Cognos Proprietary Information
Multiple Aligned Column Headers in Lists and Crosstabs
A similar technique will be presented for crosstabs to incorporate elements
into the crosstab structure such as “Order Method Quantity” and “Product
Sales” as in the crosstab image below:
3 Layout and Design
3.1
List Report
Open Report Studio using the ‘GO Sales and Retailers’ sample package and
create a new list report.
Drag in
right:
•
•
•
•
•
•
•
•
•
the following columns into the list in the order below from left to
[gosales_goretailers].[Products].[Product line]
[gosales_goretailers].[Products].[Product type]
[gosales_goretailers].[Products].[Product name]
[gosales_goretailers].[Orders].[Unit cost]
[gosales_goretailers].[Orders].[Unit price]
[gosales_goretailers].[Orders].[Unit sale price]
[gosales_goretailers].[Orders].[Quantity]
[gosales_goretailers].[Orders].[Revenue]
[gosales_goretailers].[Orders].[Gross profit]
Group the [Product line] and [Product type] columns.
Click on the ‘List Headers & Footers’ icon
‘List page header’.
IBM Cognos Proprietary Information
in the toolbar and turn on the
5
Multiple Aligned Column Headers in Lists and Crosstabs
6
Unlock the report layout and remove the ‘List Page Header’ text item found in
the List Page Header row.
With the List Page Header row selected (see above image), change the Class
property to ‘List column title cell’.
With the List Page Header row selected (see above image), select ‘Insert List
Row Cells Above’ from the Structure menu. Accept the default of ‘1’ and click
‘OK’.
With the List Page Header row still selected (see above image), select ‘Split
List Row Cell’ from the Structure menu.
Select all of the cells in the newly created List Page Header row and change
the Class property to Global Class ‘List column title cell’.
With the report specification ‘unlocked’, drag each of the text items from the
original Column Title row to the bottom of the two List Page Header rows in
the same column.
IBM Cognos Proprietary Information
Multiple Aligned Column Headers in Lists and Crosstabs
7
Select anywhere in the List object and using the properties pane hierarchy
drop-down list, select the overall list. Change the Column Titles property to
‘Hide’.
Multi-select the left three cells in the first List Page Header row (see above)
and change the Class property to ‘None’.
Multi-select the next three cells in the first List Page Header row (see above)
and select ‘Merge List Row Cells’ from the Structure menu.
Multi-select the last three cells in the first List Page Header row and select
‘Merge List Row Cells’ from the Structure menu.
Add a text item for the ‘Product Per Unit’ header in the first merged cell and a
text item in the second merged cell for the ‘Product Sales’ header in the
second merged cell.
The report is complete and the output appears as in the introductory list
example:
IBM Cognos Proprietary Information
Multiple Aligned Column Headers in Lists and Crosstabs
8
Using this technique any number of aligned column header rows can be
added to a list report.
3.2
Crosstab Report
Open Report Studio using the ‘GO Sales and Retailers’ sample package and
create a new crosstab report. (If you are reusing your previous Report Studio
session from the List example above then ensure that you lock the layout to
perform the initial crosstab creation steps.)
Create the crosstab layout by dragging the following model items to the
indicated locations:
• Rows: [gosales_goretailers].[Products].[Product name]
• Measures: [gosales_goretailers].[Orders].[Quantity]
Drag a ‘Crosstab Space (with Text)’ object from the ‘Toolbox’ onto the
Column edge. The result will look like the following.
Drag a second ‘Crosstab Space (with Text)’ object from the ‘Toolbox’ onto the
Column edge directly to the right of the first ‘Crosstab Space (with Text)’
object. (Without defined text, the layout with two crosstab space objects will
look the same as the layout with only one crosstab space.)
Edit the text of the first column to read ‘Order Method Quantity’ and then set
the second column to read ‘Product Sales’.
Drag ‘Order method’ under the ‘Order Method Quantity’ column as shown
below.
IBM Cognos Proprietary Information
Multiple Aligned Column Headers in Lists and Crosstabs
Drag ‘Revenue’ under the ‘Product Sales’ column as shown below.
Drag ‘Gross Profit’ under the ‘Product Sales’ column, along side ‘Revenue’ as
shown below.
The completed crosstab report that will look like this:
The output of this report reproduces the sample shown in the introduction:
This method adds additional structure to a crosstab layout without incurring
any query processing overhead by including unnecessary data items into the
query itself.
IBM Cognos Proprietary Information
9
Fly UP