IBM Business Analytics Proven Practices: IBM Cognos BI - Freezing Headers in Report Studio Crosstabs and Lists

Product(s): IBM Cognos Report Studio; Area of Interest: Reporting

This document describes how to achieve freezing on Crosstab and List row or column headers in IBM Cognos Report Studio using Cascading Style Sheets (CSS).

Sridhar Jayanthi, Consultant, Cognizant Technology Solutions

Sridhar Jayanthi is a consultant for Cognizant Technology Solutions with over 2 years of experience in the IT industry and IBM cognos and certified in IBM cognos reporting.



Pratik Kumar Mankar, Consultant, Cognizant Technology Solutions

Pratik Kumar Mankar is a consultant for Cognizant Technology Solutions with over two of experience in the IT industry and IBM Cognos.



11 December 2012

Introduction

Purpose of Document

This document describes how to achieve freezing on Crosstab and List row or column headers in IBM Cognos Report Studio using Cascading Style Sheets (CSS).

Applicability

The method described in this document has been tested with Microsoft Internet Explorer against IBM Cognos 8.4.1, 10.1, and 10.1.1 using the GO Data Warehouse (query) sample package. The same technique can be applied to both crosstabs and lists to freeze both column and row headers. IBM Cognos 10.2 introduced a freeze column and row headers feature in IBM Cognos Viewer. Therefore this technique only benefits users of earlier versions of IBM Cognos BI.

Exclusions and Exceptions

This method works only for the HTML output of IBM Cognos Report Studio.

This method has not been tested for all scenarios. The contents within are provided “as is” and require thorough testing for your environment and scenario.

Assumptions

This document requires experience authoring IBM Cognos Report Studio reports.


Overview

Many times we are faced with reports where we need to scroll down vertically or scroll horizontally to the extent that we have no idea which data belongs to which column or row as the column or row headers are no longer visible. The technique described in this document allows you to achieve freezing of the headers. Unlike other methods which use JavaScript or multiple report objects which must be aligned and formatted to give the impression of one object, this method uses CSS.

There are several advantages to the CSS approach,

  • Plenty of manual effort saved in aligning crosstabs/lists
  • Same code works for both crosstabs and lists
  • Code is simple, consists of very few lines and can be modified to suit one’s needs

Freeze Headers Using CSS

In this example we will create a new report with a list and a crosstab on it using the GO Data Warehouse (query) sample package. This report will demonstrate the ability to freeze headers in both a list and a crosstab.

  1. Open IBM Cognos Report Studio, select the GO Data Warehouse (query) package, and then create a new List report.
  2. From the Toolbox tab, add a Table object to the report that has 2 columns and 1 row.
  3. Add a Block object to each of the table cells in the report.
  4. Select the List object on the report and drag it into the left Block object.
  5. From the Toolbox tab, drag a Crosstab object into the right Block object.
    Select both Block objects and in the Properties pane, open the Padding dialog, specify 1 px for the top margin, and then click OK. This will prevent the top of the report object headers from being cut off.
  6. From the Source tab, navigate to Sales and Marketing (query) > Sales (query), and then, from Products, add Product line, Product type and Product to the List object. Do the same for the Crosstab’s Rows drop zone.
  7. From Time, add Year and Month to the List object. Do the same for the Crosstab’s Columns drop zone nesting Month under Year.
  8. From Sales fact, add Revenue to the List object as well as the Measures drop zone of the Crosstab object.
    The report appears as shown in Figure 1.
    Figure 1: List and Crosstab report with items added as described in the steps
    Figure 1: List and Crosstab report with items added as described in the steps
  9. Select the List object and set the Rows Per Page property to 6000 so that all the data is returned on a single page. Do the same for the Crosstab object setting the property to 1000.
  10. From the Toolbox tab, add an HTML Item above the Table object, double-click the HTML Item to open its expression dialog, and then paste the following CSS/HTML code into the dialog box:
    <style>
    .freezeCorner{
    font-weight: bold;
    vertical-align: top;
    text-align: center;
    padding: 4px 5px 4px 6px;
    background-color: #FFFFFF;
    color: #444444;
    border: none;
    top: expression(parentNode.parentNode.parentNode.parentNode.scrollTop-1); 
    left: expression(parentNode.parentNode.parentNode.parentNode.scrollLeft);
    position: relative;
    z-index:10;
    }
    
    .freezeRows{
    vertical-align: top;
    padding: 4px 5px 4px 6px;
    background-image: url(../reportstyles/images/silver_grad.png);
    background-position: left top;
    background-repeat: repeat-x;
    background-color: #E7E5E5;
    color: #333333;
    border: 1px solid silver;
    left: expression(parentNode.parentNode.parentNode.parentNode.scrollLeft);
    position: relative;
    }
    
    .freezeColumns{
    vertical-align: top;
    padding: 4px 5px 4px 6px;
    background-image: url(../reportstyles/images/silver_grad.png);
    background-position: left top;
    background-repeat: repeat-x;
    background-color: #E7E5E5;
    color: #333333;
    border: 1px solid silver;
    top: expression(parentNode.parentNode.parentNode.parentNode.scrollTop-1);
    position: relative;
    }
    </style>

    This code includes the default header styles for IBM Cognos 10.x. It can be altered to meet your requirements.
    The report appears as shown in Figure 2.
    Figure 2: List and Crosstab report with HTML Item in the report header
    Figure 2: List and Crosstab report with HTML Item in the report header
  11. Navigate to Page Explorer > Classes, copy the code below to your clipboard, and then from the Edit menu, click Paste.
    <RSClipboardFragment version="2.0">
    <classStyle name="freezeColumns" label="freezeColumns"/>
    <classStyle name="freezeRows" label="freezeRows"/>
    <classStyle name="freezeCorner" label="freezeCorner"/>
    </RSClipboardFragment>

    You should see three new classes, freezeColumns, freezeRows, and freezeCorner in the Local Classes pane as shown in Figure 3. The RSClipboardFragment tag is used to allow IBM Cognos Report Studio to recognize this as a class on the system clipboard that can be pasted in as a local class.
    Figure 3: Local Classes pane with three new classes pasted in from the system clipboard
    Figure 3: Local Classes pane with three new classes pasted in from the system clipboard
  12. In Page Explorer, navigate back to Page1, select the left Block object, and then in the Properties pane, open the Size & Overflow property.
  13. Set Height to 12 cm, Width to 8 cm, and then select Use scrollbars only when necessary as shown in Figure 4.
    Figure 4: Size & Overflow dialog with settings implemented as described in the steps
    Figure 4: Size & Overflow dialog with settings implemented as described in the steps
  14. Click OK, and do the same for the right Block object setting the height to 12 cm and the width to 18 cm.
  15. Select each of the List object’s column headers, and then in the Properties pane, under Miscellaneous, open the Classes dialog.
  16. Remove the existing class, and then insert the freezeColumns class from the Local Classes pane as shown in Figure 5.
    Figure 5: Classes dialog with new freezeColumns local class added to the Selected classes pane
    Figure 5: Classes dialog with new freezeColumns local class added to the Selected classes pane
  17. Similarly remove the existing class for the Crosstab object’s corner cell and column and row headers and apply the freezeCorner, freezeColumns, and freezeRows local classes respectively.
    The formatting in the report design appears lost for the columns, rows and corner but the formatting specified in the CSS code will be applied when the report is run.
  18. Run the report.
    As shown in Figure 6, the report appears with a scrollbar to the right of the list allowing the user to scroll through the list data without affecting the position of the headers. There is also a scrollbar below the crosstab and to the right of the crosstab, which allows the user to horizontally scroll through the columns without affecting the position of the crosstab rows and vertically scroll through the rows without affecting the position of the crosstab columns.
    Figure 6: List and Crosstab report in IBM Cognos Viewer with scrollbars allowing scrolling without losing sight of the column or row headers
    Figure 6: List and Crosstab report in IBM Cognos Viewer with scrollbars allowing scrolling without losing sight of the column or row headers

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Big data and analytics
ArticleID=850662
ArticleTitle=IBM Business Analytics Proven Practices: IBM Cognos BI - Freezing Headers in Report Studio Crosstabs and Lists
publish-date=12112012