...

Using a Crosstab to Produce Gantt Charts Proven Practice

by user

on
Category: Documents
3

views

Report

Comments

Transcript

Using a Crosstab to Produce Gantt Charts Proven Practice
Proven Practice
Using a Crosstab to Produce Gantt
Charts
Product(s): IBM Cognos 8, IBM Cognos ReportNet
Area of Interest: Report Design
Using a Crosstab to Produce Gantt Charts
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
Using a Crosstab to Produce Gantt Charts
3
Contents
1
INTRODUCTION ............................................................................................ 4
1.1
1.2
PURPOSE ............................................................................................................4
APPLICABILITY .....................................................................................................4
2
WHAT IS A GANTT CHART? ........................................................................... 4
3
DATA AND LAYOUT CONSIDERATIONS ........................................................ 4
4
STEPS TO CREATE A GANTT CHART .............................................................. 5
5
APPLYING THE CONCEPTS TO REPORTNET ................................................ 12
6
SUMMARY ................................................................................................... 16
7
APPENDIX A – SAMPLE REPORT SPECIFIATION FOR COGNOS 8............... 17
IBM Cognos Proprietary Information
Using a Crosstab to Produce Gantt Charts
4
1 Introduction
1.1
Purpose
IBM Cognos 8 and IBM Cognos ReportNet do not include a Gantt chart as an
available option in chart dialogs. The technique below outlines the method to
create a Gantt chart from other objects available within the product
functionality.
1.2
Applicability
While the discussion and examples will target IBM Cognos 8, the technique is
also applicable to IBM Cognos ReportNet.
2 What is a Gantt Chart?
A Gantt chart is a tool typically used for project planning to display date
ranges graphically for comparison to other items in a project plan. The
following is a sample of what such Gantt charts look like
Additional complexity can be added to the chart by colouring the bars to
display status such as red to denote a project phase that may be behind
schedule or partially colouring a bar to show current progress towards a
milestone.
3 Data and Layout Considerations
The layout of a Crosstab lends itself to the Gantt chart format where the task
items would populate the rows while the date series would populate the
columns of the crosstab.
There are a few considerations for the following technique. The first
consideration is how the data is to be derived for the time series in the
columns.
A typical modelling approach for time data is to use an inner join to establish
a direct correlation between a time dimension and the detail fact information
for each project to ensure good performance for most queries. This is
commonly represented as a time dimension for a project start date and/or
the project end date. This will result in gaps in the time series for periods that
fall within the range defined by a start date and an end date as the inner
equi-join will not match to these records.
IBM Cognos Proprietary Information
Using a Crosstab to Produce Gantt Charts
5
There are two approaches that can be taken within the model to resolve the
all the dates required for a Gantt chart.
The first approach is to create a new date dimension that will return a range
of dates based on a start date and an end date for a task. This can be
accomplished with a between clause and an inner join to return the matching
date dimension records for the entire range. However, this approach will miss
date periods that are not with any range defined by a set of project tasks. For
example, if one project ends in September and the next project starts in
December then October and November will not be returned in the columns of
the Gantt chart.
The second approach for resolving the date ranges is to use an outer join
using a between clause to return all the records for the date table even if
there are no matching values in the set of task dates. Keep in mind that an
outer join may have more negative performance impact than an inner join
using a between clause.
The same techniques can be approached with a report instead of within the
model. In Cognos ReportNet a child tabular model can be used to return a
series of dates and these can be related to the parent tabular model using a
filter object to create the join options discussed above. In Cognos 8 this is
much more straight-forward and can be accomplished using a join object
between two queries.
4 Steps to Create a Gantt Chart
The steps below will discuss creating the outer join using a between clause in
Report Studio to satisfy the Gantt chart. The IBM Cognos 8 sample GO Data
Warehouse model will be used to create the report.
1. Create a new crosstab report using the GO Data Warehouse sample
model.
2. Expand the “Sales” namespace to display the “Sales order” dimension.
Expand the “Sales order” dimension until the “Order number” level is
shown and then drag “Order number” into the rows of the crosstab
layout.
Since the sample data does not include project details the “Order
number” will play the role of a project task. The ship date and the close
date will serve as the start and end dates of each project for the Gantt
chart. You may rename the data items to reflect their roles in this report if
you wish.
3. In the “Sales” namespace expand the “Time (ship date)” dimension and
from the properties of the lowest level, “Day (ship date)”, drag the “Ship
date” property into the crosstab and nest it in the rows beneath the
“Order number”.
IBM Cognos Proprietary Information
Using a Crosstab to Produce Gantt Charts
6
4. In the “Sales” namespace expand the “Time (close date)” dimension and
from the properties of the lowest level, “Day key (close date)”, drag the
“Close date” property into the crosstab and nest it in the rows beneath
the “Ship date”.
5. To get meaningful results we must filter the sample data to return records
where the ship date and the close date are not the same. As we are
synthesizing project type data the “projects” with a zero length duration
will not be of use to us here. So, select the crosstab and click the filter
button on the toolbar to create a new detail filter. Enter or create the
following expression and click OK twice to return to the report layout.
[Ship date]<>[Close date]
6. The report should now look like the following image. This is the basis for
one of the queries we will need for this report.
7. The second query will be created manually. Expand the query explorer
and click Queries.
8. Drag a new query object into the query explorer pane. Double click the
new query object to access the query properties.
9. Expand the “Sales” namespace and insert the “Date” property from the
lowest level of the “Time” dimension. This is the second query that will
serve our Gantt chart and should look like the following image.
IBM Cognos Proprietary Information
Using a Crosstab to Produce Gantt Charts
10. Return to the query explorer pane and drag a new query object into the
workspace. Drag a “Join” object beneath this new query and in the two
placeholders for the join drag in Query2 (the date query) to the first
placeholder and Query1 (the order number query) to the second
placeholder. The result should look like the following image.
IBM Cognos Proprietary Information
7
Using a Crosstab to Produce Gantt Charts
8
11. Select the join object in the query explorer and click the ellipses in the
“Join Relationships” property to edit the join definition. Create the join
with the following links (notice the operator used for each link to define a
between clause and the cardinality to define the outer join)
IBM Cognos Proprietary Information
Using a Crosstab to Produce Gantt Charts
9
Note that if no gaps between project tasks are expected then the join
cardinality can be set to define an inner join instead of an outer join.
12. Access the properties of the last query “Query3” and drag in all the data
items from both Query1 and Query2. to get the following query definition
13. Add a new data item from the toolbox to “Query3”. Since the outer join
returns more records than are defined for the given ranges this new
calculation will assist in identifying which cells of the crosstab report
should be highlighted for the Gantt chart. Enter the following expression
for the new data item. Name the data item “Plot Range”
if ([Query2].[Date] between [Query1].[Ship date] and
[Query1].[Close date]) then (1) else (0)
The resulting query should look like the following image.
IBM Cognos Proprietary Information
Using a Crosstab to Produce Gantt Charts
10
Note that if no date gaps are expected between project tasks and an
inner join was defined in step 12 then this expression could be changed
to use a constant value of 1.
14. Return to the page layout and select the crosstab object on the page. In
the properties of the crosstab change the Query to use “Query3” (the
result of the union of “Query1” and “Query2”).
15. Drag the “Date” data item from “Query3” into the columns of the crosstab
and drag “Plot Range” into the measures of the crosstab. Select the
“Date” column and set it to sort in ascending order.
16. Select the “Plot Range” measure and set the aggregate properties to use
Total for both the Aggregate Function and the Rollup Aggregate Function.
The resulting layout should look as follows.
IBM Cognos Proprietary Information
Using a Crosstab to Produce Gantt Charts
11
17. Unlock the layout and select the text item for the Plot Range in the
crosstab fact cells. Set the “Visible” property for the text item to “No” to
hide the number from view.
18. Select the Crosstab Fact Cells object in the crosstab. Click the ellipses in
the “Style Variable” property to bring up the “Style Variable” dialog. Select
“<new boolean variable>” in the drop-down box and in the resulting
expression editor define the following condition.
[Query3].[Plot Range]>0
Click OK twice to return to the page layout.
19. From the Condition Explorer select the “Yes” condition for the new
Boolean variable.
20. The Crosstab Fact Cells should still be selected. From the properties of the
Crosstab Fact Cells define a Navy Background colour.
21. Run the report and view the results. Note that the execution time with an
outer join will be slower than the inner join approach due to the extra
processing required at the database. If you apply an ascending sort to the
Order number rows then the results should look similar to the following
although the crosstab has been truncated below to show the portion of
the time series that has the given project ranges highlighted.
IBM Cognos Proprietary Information
Using a Crosstab to Produce Gantt Charts
12
5 Applying the concepts to ReportNet
The steps below will discuss creating the outer join using tabular models and
filter expressions in Report Studio to satisfy the Gantt chart. The IBM Cognos
ReportNet sample GO Sales and Retailers model will be used to create the
report.
1. Create a new crosstab report using the GO Sales and Retailers sample
model.
2. Expand the “Orders” query subject and drag “Order number” into the
rows of the crosstab layout.
Since the sample data does not include project details the “Order
number” will play the role of a project task. The order date and the return
date will serve as the start and end dates of each project for the Gantt
chart. You may rename the data items to reflect their roles in this report if
you wish.
3. From the “Orders” query subject drag “Order date” and nest it in the rows
beneath “Order number”.
4. Expand the “Returned items” folder in the “Orders” query subject and
drag “Return date” to the crosstab rows and nest it beneath “Order date”.
5. To get meaningful results we must filter the sample data to return records
where the order date and the return date both exist and are not the
same. As we are synthesizing project type data the “projects” with a zero
length duration will not be of use to us here. So, select the crosstab and
click the filter button on the toolbar to create a new detail filter. Enter or
create the following expression and click OK twice to return to the report
layout.
[Order date]<>[Return date] AND [Return date] is not null
6. The report should now look like the following image. This is the basis for
one of the queries we will need for this report.
IBM Cognos Proprietary Information
Using a Crosstab to Produce Gantt Charts
13
7. The second query will be created manually. Expand the query explorer
and click on the Tabular model for Query1.
8. Drag a new Tabular model object from the Toolbox into the Children area
of the existing tabular model for Query1.
9. Double click the new tabular model to access the properties and, in the
properties pane, set the name of the tabular model to “Date Sequence”
10. Drag the Order date query item from the Orders query subject into the
Data Items area of the new Tabular model. Note that the sample data we
are working with is transactional and is sparse. This means that the entire
date sequence will not be represented and there may be gaps in the
resulting Gantt chart. Normally a time series from a full date dimension
would be the appropriate choice instead of a date derived from
transactional records.
IBM Cognos Proprietary Information
Using a Crosstab to Produce Gantt Charts
14
11. Since we are working with transactional data we will modify the date
values to remove the timestamp portion of the dates. Double-click on the
“Order date” data item to modify the expression as follows1:
convert({datetime}, convert({varchar},
[gosales_goretailers].[Orders].[Order date], 104), 104)
12. Return to the query explorer pane and select the “Query1” object.
13. In the properties of the query object set the “Cross Product Allowed”
property to Allow.
14. Return to the query explorer pane and select the original "Tabular model"
object.
15. Drag a new filter object into the Filters area of the Tabular model. Set the
filter expression to:
[Date Sequence].[Order date] between [Order date] and [Return
date]
This will populate the date columns between the start and end date to fill
in the length of the bars for the Gantt chart without having to perform a
full cross product of all the dates in the data warehouse.
16. From the Query Items tab of the Insertable Objects pane, drag “Order
date” into the original Tabular model Data Items pane.
17. From the Toolbox of the Insertable Objects pane, drag a new Data Item
into the Data Items pane and set the expression to2
if ([Order date1] between [Order date] and [Return date]) then (1)
else (0)
18. Select the new Data Item and set the Aggregate Function property to
Total.
19. For the sake of illustration and to lower the data volume being processed,
drag a new filter from the Toolbox into the Filters area of the Tabular
model and set the expression to:
[Order number] between 1000 and 1500
The resulting query should look like the following image.
1
Note that the convert function is native to SQL Server and is used here to strip the time
portion from a datetime type. Refer to your database vendor documentation for appropriate
function calls to accomplish this if you are using the samples databases in a different
environment.
2
Note that due to the filter condition above not including a full cross join of the two data sets
it is also possible to use a constant numeric value for the data item expression.
IBM Cognos Proprietary Information
Using a Crosstab to Produce Gantt Charts
15
20. Using the Query Explorer, select Query1.
21. Drag “Order date1” into the Dimensions pane to create a new dimension
and drag “Data Item” into the Facts as shown in the following image.
22. Use the Page Explorer to return to the page layout.
23. From the Query Items tab of the Insertable Objects pane, drag “Order
date1” to the crosstab columns and drag “Data Item” to the measures
section of the crosstab.
24. Select the “Order date1” column and apply an ascending sort using the
Sort button on the toolbar.
25. Unlock the layout and select the text item for the Data Item measure in
the crosstab fact cells. Set the “Visible” property for the text item to “No”
to hide the number from view.
26. Select the Crosstab Cells object in the crosstab. Click the ellipses in the
“Conditional Style” property to bring up the “Conditional Style” dialog.
IBM Cognos Proprietary Information
Using a Crosstab to Produce Gantt Charts
16
27. Click the Variables button and in the new Variables dialog click the New
button at the bottom left to create a new Boolean variable (leave the
name as the default).
28. Set the new Boolean variable expression to:
[Data Item] > 0
29. Click the OK button three times to return to the page layout.
30. From the Condition Explorer select the “Yes” condition for the new
Boolean variable.
31. The Crosstab Fact Cells should still be selected. From the properties of the
Crosstab Fact Cells define a Navy Background colour.
32. Run the report and view the results. The crosstab below has been
truncated to show the portion of the time series that has project ranges
highlighted. The rows with multiple return dates are due to the
transactional nature of the data. True project-related data will likely not
encounter such a scenario where multiple project end dates have been
defined.
6 Summary
The steps above should provide a starting point for developing your own
Gantt charts. By filtering the result set for the time dimension it is possible to
build reports that target the listed projects and provide a graphical
representation for timelines and project milestones.
Note that not all data will follow the same structure as in the sample GO Data
Warehouse tables. You may need to implement your own changes and
customization of the above technique to account for these differences in data
structure.
IBM Cognos Proprietary Information
Using a Crosstab to Produce Gantt Charts
17
7 Appendix A – Sample Report Specifiation for IBM
Cognos 8
The following report specification can be copied to the clipboard and opened
in Report Studio using the “Open Report from Clipboard” option available in
the Tools menu. Note that the GO Data Warehouse samples must be
deployed to execute and validate this report.
<report xmlns="http://developer.cognos.com/schemas/report/2.0/"
expressionLocale="en"><!--RS:8.1-->
<modelPath>/content/package[@name='GO Data
Warehouse']/model[@name='model']</modelPath>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Order number"
aggregate="none"><expression>[Sales].[Sales order].[Sales
order].[Order number]</expression></dataItem><dataItem name="Ship
date" aggregate="none"><expression>[Sales].[Time (ship date)].[Time
(ship date)].[Day (ship date)].[Ship
date]</expression></dataItem><dataItem name="Close date"
aggregate="none"><expression>[Sales].[Time (close date)].[Time (close
date)].[Day key (close date)].[Close
date]</expression></dataItem></selection>
<detailFilters><detailFilter><filterExpression>[Ship
date]&lt;&gt;[Close
date]</filterExpression></detailFilter></detailFilters></query>
<query name="Query2">
<source>
<model/>
</source>
<selection><dataItem name="Date"
aggregate="none"><expression>[Sales].[Time].[Time].[Day].[Date]</expre
ssion></dataItem></selection>
</query>
<query name="Query3">
<source>
<joinOperation>
<joinOperands>
<joinOperand cardinality="1:N"><queryRef
refQuery="Query2"/></joinOperand>
<joinOperand cardinality="0:N"><queryRef
refQuery="Query1"/></joinOperand>
IBM Cognos Proprietary Information
Using a Crosstab to Produce Gantt Charts
18
</joinOperands>
<joinFilter>
<filterExpression>[Query2].[Date] &gt;= [Query1].[Ship
date] and
[Query2].[Date] &lt;= [Query1].[Close date]</filterExpression>
</joinFilter>
</joinOperation>
</source>
<selection><dataItem name="Order
number"><expression>[Query1].[Order
number]</expression></dataItem><dataItem name="Ship
date"><expression>[Query1].[Ship
date]</expression></dataItem><dataItem name="Close
date"><expression>[Query1].[Close
date]</expression></dataItem><dataItem
name="Date"><expression>[Query2].[Date]</expression></dataItem><dataIt
em name="Plot Range" aggregate="total"
rollupAggregate="total"><expression>if ([Query2].[Date] between
[Query1].[Ship date] and [Query1].[Close date]) then (1) else
(0)</expression></dataItem></selection>
</query>
</queries>
<layouts>
<layout>
<reportPages>
<page class="pg" name="Page1">
<pageBody class="pb">
<contents>
<crosstab class="xt" refQuery="Query3">
<crosstabCorner
class="xm"><contents><textItem><dataSource><dataItemLabel
refDataItem="Plot
Range"/></dataSource></textItem></contents></crosstabCorner>
<style>
<CSS value="border-collapse:collapse"/>
</style>
IBM Cognos Proprietary Information
Using a Crosstab to Produce Gantt Charts
19
<crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><cros
stabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember
refDataItem="Close date"
class="ml"><contents><textItem><dataSource><memberCaption/></dataSourc
e></textItem></contents></crosstabNodeMember></crosstabNodeMembers></c
rosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMe
mber refDataItem="Ship date"
class="ml"><contents><textItem><dataSource><memberCaption/></dataSourc
e></textItem></contents></crosstabNodeMember></crosstabNodeMembers></c
rosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMe
mber refDataItem="Order number"
class="ml"><contents><textItem><dataSource><memberCaption/></dataSourc
e></textItem></contents></crosstabNodeMember></crosstabNodeMembers></c
rosstabNode></crosstabRows><crosstabColumns><crosstabNode><crosstabNod
eMembers><crosstabNodeMember refDataItem="Date"
class="ml"><contents><textItem><dataSource><memberCaption/></dataSourc
e></textItem></contents></crosstabNodeMember></crosstabNodeMembers></c
rosstabNode></crosstabColumns><defaultMeasure refDataItem="Plot
Range"/><crosstabFactCell
class="mv"><contents><textItem><dataSource><cellValue/></dataSource><s
tyle><CSS
value="visibility:hidden"/></style></textItem></contents><conditionalS
tyles><conditionalStyleCases refVariable="Boolean1"><conditionalStyle
refVariableValue="1"><CSS value="backgroundcolor:navy"/></conditionalStyle></conditionalStyleCases><conditionalSt
yleDefault/></conditionalStyles></crosstabFactCell></crosstab>
</contents>
</pageBody>
<pageHeader class="ph">
<contents>
<block class="ta">
<contents>
<textItem class="tt">
<dataSource>
<staticValue/>
</dataSource>
</textItem>
</contents>
</block>
</contents>
<style>
<CSS value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageFooter class="pf">
IBM Cognos Proprietary Information
Using a Crosstab to Produce Gantt Charts
20
<contents>
<table class="tb">
<tableRows>
<tableRow>
<tableCells>
<tableCell>
<contents>
<textItem>
<dataSource>
<reportExpression>AsOfDate()</reportExpression>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="verticalalign:top;text-align:left;width:25%"/>
</style>
</tableCell>
<tableCell>
<contents>
<textItem>
<dataSource>
<staticValue>- </staticValue>
</dataSource>
</textItem>
<textItem>
<dataSource>
<reportExpression>PageNumber()</reportExpression>
</dataSource>
</textItem>
<textItem>
<dataSource>
<staticValue> -</staticValue>
</dataSource>
</textItem>
</contents>
<style>
IBM Cognos Proprietary Information
Using a Crosstab to Produce Gantt Charts
21
<CSS value="vertical-
align:top;text-align:center;width:50%"/>
</style>
</tableCell>
<tableCell>
<contents>
<textItem>
<dataSource>
<reportExpression>AsOfTime()</reportExpression>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="verticalalign:top;text-align:right;width:25%"/>
</style>
</tableCell>
</tableCells>
</tableRow>
</tableRows>
<style>
<CSS value="bordercollapse:collapse;width:100%"/>
</style>
</table>
</contents>
<style>
<CSS value="padding-top:10px"/>
</style>
</pageFooter>
</page>
</reportPages>
</layout>
</layouts>
<reportVariables><reportVariable type="boolean" name="Boolean1">
<reportExpression>[Query3].[Plot Range]&gt;0</reportExpression>
<variableValues>
<variableValue value="1"/>
</variableValues>
</reportVariable>
</reportVariables></report>
IBM Cognos Proprietary Information
Using a Crosstab to Produce Gantt Charts
IBM Cognos Proprietary Information
22
Fly UP