...

Image Indicators in a Crosstab Tip or Technique Product(s): IBM Cognos 8

by user

on
Category: Documents
1

views

Report

Comments

Transcript

Image Indicators in a Crosstab Tip or Technique Product(s): IBM Cognos 8
Tip or Technique
Image Indicators in a Crosstab
Product(s): IBM Cognos 8
Area of Interest: Report Design
Image Indicators in a Crosstab
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
Image Indicators in a Crosstab
3
Contents
1
INTRODUCTION ............................................................................................ 4
1.1
1.2
1.3
PURPOSE ............................................................................................................4
APPLICABILITY .....................................................................................................4
EXCLUSIONS AND EXCEPTIONS ..................................................................................4
2
CONDITIONAL CROSSTAB IMAGES .............................................................. 4
2.1
2.2
CREATING THE INITIAL REPORT .................................................................................4
CREATING THE CONDITIONAL IMAGE ...........................................................................5
3
CONCLUSION ................................................................................................ 9
IBM Cognos Proprietary Information
Image Indicators in a Crosstab
4
1 Introduction
1.1
Purpose
Conditional Formatting can be used to format individual objects and cells
within a crosstab based on measure values. For more pleasing layouts a
graphic is often a more attractive choice than cell formatting. This document
outlines the approach and techniques required to insert a conditional image
into the crosstab cells.
1.2
Applicability
This document applies to IBM Cognos 8.
1.3
Exclusions and Exceptions
Measure context in this document is obtained from the default crosstab
measure property. If multiple measures are in use then the measures will
need to be nested within the crosstab rows or columns to provide context.
2 Conditional Crosstab Images
2.1
Creating the initial report
The following examples use the Great Outdoors Company sample package
and PowerCube. These samples are provided with IBM Cognos 8. To start
with, we will open Report Studio using the Great Outdoors Company package.
Create
a new crosstab report with the following layout:
Rows: / Products / Products / Product line
Columns: / Years / Years / Year
Measures: / Measures / Revenue
The result should look like the following:
From the Toolbox drag a Query Calculation into the right-hand drop zone of
the Columns. Name the calculation “All Years” and use the following
calculation expression1:
total( currentMeasure within set [Year] )
1
In this particular example it is possible that instead of this calculation expression the root
member from the Years hierarchy could be used to achieve the same result. A calculation is
used in this case as an introduction to the expression that will allow for more complicated
aggregates to be calculated.
IBM Cognos Proprietary Information
Image Indicators in a Crosstab
5
The total function will determine the currentMeasure based on the context. In
this case because there are no measures defined on the opposite edge (on
the rows) the calculation will use the default measure of the crosstab which is
Revenue. The total will be calculated using the individual values returned
from the “within set” portion of the expression. In this case the set is defined
by [Year] and will include all the years from the Year level (2004, 2005, and
2006). So, the revenue values for each year will be determined and then
aggregated to produce a grand total called “All Years”.
The “All Years” calculation is a rollup of the individual year values and will be
considered as part of the same dimension as the previous members on the
edge. This means that the rollup calculation will be considered as part of the
Years hierarchy.
2.2
Creating the Conditional Image
To create the conditional image we need to have a column which will hold
the image object. There are two approaches to this requirement: using an
existing column or creating a new column to hold the image.
We will start with using an existing column from our layout. Select the All
Years fact cells. The properties pane should indicate that a Crosstab
Intersection object is selected.
IBM Cognos Proprietary Information
Image Indicators in a Crosstab
6
Change the “Define Contents” property to “Yes”. This will override the
contents of the All Years calculation cells. Click the
button on the toolbar
to unlock the layout. The button should change to look like
From the Data Items tab, drag Revenue into the empty measure cell of All
Years.
From the Toolbox drag a “Conditional Blocks” object into the All Years
measure cell. Click the ellipsis next to the Block Variable property of the
Conditional Block.
Create a new Boolean variable with the following expression:
[Query1].[All Years] > 5000000
This should enable two options for the “Current Block” property of the
Conditional Block object: No and Yes. Drag an image into the No block and
set the image reference to:
http://<server name>/cognos8/samples/images/red.jpg
Replace <server name> with the appropriate gateway server for your
environment.
For the Yes block drag in another image and use the following image
reference:
http://<server name>/cognos8/samples/images/green.jpg
Run the report.
IBM Cognos Proprietary Information
Image Indicators in a Crosstab
7
The error above should be the result of our Boolean variable expression. The
reason that this occurs is that [All Years] is a rollup of the Years and even
though it uses a calculated expression the result is considered part of the
Years hierarchy. What is really required is a Measure expression in the
Boolean variable.
Use the Conditional Explorer to access the properties of the Boolean variable.
Edit the expression and change it to use:
[Query1].[Revenue] > 5000000
Now the variable will be evaluated as the Revenue measure defined within
the context of the All Years time rollup (i.e. the combination of 2004, 2005,
and 2006 rolled up using the total function).
Run the report again to get the following output:
We can see that the Conditional Block is evaluated for each cell within the
context of the All Years rollup. The appropriate image is displayed for each
value of the total Revenue.
IBM Cognos Proprietary Information
Image Indicators in a Crosstab
8
It is now possible to apply formatting to the image and add additional layout
objects such as a table to make the output look more attractive:
To revisit the initial requirements we could have also created a new column
as a container for the images. The key to this approach is to define a new
column within the layout with the appropriate context. In the above example
we used an existing column to specify the context so that the Revenue values
reflected the rollup over all the years. If we define a new column the same
context must be defined. This can be accomplished by dragging the “All
Years” calculation from the Data Items tab into the report as another
column.2
Once again, use “Yes” setting of the “Define Contents” property for the
measure cells of the new All Years column and unlock the layout. Either
follow the steps above to create the conditional block and images within the
new column or copy the conditional block from the first instance of the “All
Years” column. If desired, the text item in the column title can be removed.
2
If you do not see the drop zone when dragging in the All Years data item then check that
the layout is not unlocked. If the layout is unlocked then click the button on the toolbar to
lock the layout and the column drop zones should be easier to locate.
IBM Cognos Proprietary Information
Image Indicators in a Crosstab
By using the “All Years” data item to define the column we ensure that the
values used to evaluate the Boolean variable are the total Revenue for all of
the years for each Product line.
Defining a second column is not explicitly necessary for this technique to
work as we saw above using the first instance of “All Years”. However, the
second column allows us the freedom of positioning the images wherever
they are required within the crosstab layout rather than being tied to the
existing column used to display the numeric results of the Revenue rollup.
3 Conclusion
The above technique allows a report author to generate complex metric and
dashboard style reports that provide an at-a-glance evaluation of core
business measures. The use of images instead of background formatting
creates presentation quality reports that can be used to extend or
complement existing metrics authored in IBM Cognos 8.
IBM Cognos Proprietary Information
9
Fly UP