IBM Cognos Proven Practices: IBM Cognos BI – Freezing Crosstab Row Headers

Nature of Document: Tip or Technique; Product(s): IBM Cognos Report Studio; Area of Interest: Reporting

This document describes a technique which applies column freeze functionality similar to Microsoft Excel on Crosstab row headers using IBM Cognos BI Report Studio toolbox items and JavaScript.

Thaker Prit, Consultant, Cognizant Technology Solutions

Thaker Prit, Consultant, Cognizant Technology Solutions, has close to 21 months of experience in the Information Technology field. As part of his assignments, he has been in Requirement Analysis, Functional Studies, Solution Development, Quality Reviews and Testing. He has worked for clients in the Life Sciences industry with extensive focus on Sales and Marketing.



Mohit Rangwani, Consultant, Cognizant Technology Solutions

Mohit Rangwani, Consultant, Cognizant Technology Solutions, has close to 20 months of experience in the Information Technology field. As part of his assignments, he has been in Requirement Analysis, Application Maintenance, Functional Studies, Quality Reviews and Testing. He has worked for clients in Life Sciences industry with extensive focus on Sales and Marketing.



19 June 2012

Also available in Chinese Russian

Introduction

Purpose

This document describes a technique which applies column freeze functionality similar to Microsoft Excel on Crosstab row headers using IBM Cognos BI Report Studio toolbox items and JavaScript.

Applicability

The technique outlined in this document was validated using IBM Cognos BI 10.1.1 using the GO Data Warehouse (query) sample package.

Assumptions

This document assumes experience with IBM Cognos BI Report Studio.

Exclusions and Exceptions

The steps outlined in this document are applicable only in the HTML output version of the IBM Cognos Report Studio report.

The width of the row headers to be frozen, collectively do not exceed the screen width.

This technique requires the use of undocumented and unsupported capabilities in IBM Cognos BI. There may be a risk associated with this technique in that support for these capabilities may change or be dropped entirely in some future release.

This technique was created as a proof of concept and does not cover all usage scenarios. This technique should be thoroughly tested before being used on a live IBM Cognos BI system.


Overview

In some cases, crosstab report data is so extensive that it becomes difficult to analyze measures along with their respective row headers. There may also be instances where you are working with limited real estate in dashboard-style reports. The approach described in this document can aid with this problem by freezing row headers for the data while allowing users to scroll through the columns of measures.

Another approach to achieve similar results can be found in the document titled Report Studio Freeze Panes With Scripting which is available on IBM developerWorks at the following URL,
http://www.ibm.com/developerworks/data/library/cognos/page242.html

That approach requires the hosting of a .JS file while this approach is self contained within the IBM Cognos Report Studio report.


Apply Row Freeze Functionality in a Crosstab

In this example we will create a new report using the GO Data Warehouse (query) sample package.

  1. Open IBM Cognos BI Report Studio, select the GO Data Warehouse (query) package, and then create a new blank report.
  2. From the Toolbox tab, add a table with two columns and one row to the report.
  3. Insert a Block object in both cells of the newly created table.
  4. Insert a Crosstab object within the Block object of the left table cell.
  5. From the Source tab, navigate to Sales and Marketing (query) > Sales (query), and then, from Products, add Product line to the Rows drop zone.
  6. Nest Product type to the right of Product line.
  7. From Time, add Year to the Columns drop zone, and then from Order method, nest Order method type under Year.
  8. From Sales fact, add Quantity to the Measures drop zone.
    The crosstab appears as shown below.
    Illustration 1: Crosstab report inside Block object in the left table cell
    Illustration 1: Crosstab report inside Block object in the left table cell
  9. In the Properties pane, in the Name property, change the name of the crosstab to leftCrosstab.
  10. Copy the crosstab into the Block object of the right table cell, and then name this crosstab rightCrosstab.
  11. In the leftCrosstab object, multi-select the fact cells and crosstab node members above the fact cells. In this example, select the Quantity fact cells and the Year and Order method type crosstab node members, and the as shown in Illustration 2.
    Illustration 2: leftCrosstab object with columns and fact cells selected
    Illustration 2: leftCrosstab object with columns and fact cells selected
  12. In the Properties pane change the Box Type property to None. This setting will hide those objects. To see the objects in the design UI again, in the View menu, navigate to Visual Aids and then select Show Hidden Objects.
  13. In the leftCrosstab object, select the Product line and Product type crosstab node members , and then in the Properties pane, change the White Space property to No wrap as shown in Illustration 3.
    Illustration 3: leftCrosstab object with White Space property set to No Wrap
    Illustration 3: leftCrosstab object with White Space property set to No Wrap
  14. In the rightCrosstab object, select the row crosstab node members and crosstab corner and change the Box Type property to None. In this example select the Product line and Product type crosstab node members, and the Quantity crosstab corner as shown in Illustration 4.
    Illustration 4: rightCrosstab object with rows and crosstab corner selected
    Illustration 4: rightCrosstab object with rows and crosstab corner selected
    The two crosstabs appear as in Illustration 5.
    Illustration 5: leftCrosstab and rightCrosstab objects after hiding respective columns, row and crosstab corner
    Illustration 5: leftCrosstab and rightCrosstab objects after hiding respective columns, row and crosstab corner
    Now, we need to set the size of the scrollable block for the the right side crosstab.
  15. From the Toolbox tab, add an HTML Item above and below the right Block object. If you have difficulty placing the HTML Items, you can use the Page Structure option under the View menu to navigate to the correct locations.
    Illustration 6: HTML Items added above and below the right side Block object
    Illustration 6: HTML Items added above and below the right side Block object
  16. Double-click the top HTML Item, and then copy the following HTML code into the dialog box.
    <div id="freeze"  style=" overflow:auto;  overflow-y: hidden; 
     white-space:nowrap; width:375px;">

    You need to replace the number 375 with the width in pixels you want. Try to set the width such that the whole crosstab fits the width of the desired area.
  17. Double-click the bottom HTML Item, and then copy the following HTML code into the dialog box.
    </div>
  18. Select the right Block object, in the Properties pane, open the Padding dialog, and set the bottom padding to 14 px. This will ensure the scrollbar under the crosstab does not obstruct any of the data.
  19. Add an HTML Item below the table as shown below in Illustration 7.
    Illustration 7: HTML Item added below the table
    Illustration 7: HTML Item added below the table
    The next step will add JavaScript to account for changes in screen resolution for consumers' machines. In the first HTML code added in this example, the width of the right block was set to a specific size in pixels in a <div> tag. To adjust this width based on the user's screen resolution, the following JavaScript code is used.
  20. Double-click the HTML Item below the table, and then add the following JavaScript code.
    <script>
    var original_width = 1280;	// Original screen width in which report was developed
    var current_width = screen.width;   // Detects width of the screen
    if(current_width != original_width)
    {
    //Get width of the ‘right’ block
    var set_width = parseInt(document.getElementById("freeze").style.pixelWidth);  
    if ((set_width + current_width – original_width) > 0)
    {
    //Add the difference of current screen width and
    // original screen width to adjust the block size
    set_width = (set_width + current_width – original_width) +"px";	 
    document.getElementById("freeze").style.width = set_width;
    }
    }
    
    </script>

    You need to change the number ‘1280’ with width of your design screen resolution.
  21. Select the left table cell, in the Properties pane, open the Size & Overflow dialog and set the width to 1%.
  22. You will need to change the height of the crosstab corner of the leftCrosstab object to set the alignment of both the crosstabs. In this example, 43 px was used.
  23. Run the report. It appears with a scroll bar below the crosstab columns, as shown in Illustration 8, which allows the user to vertically scroll through the columns without affecting the position of the crosstab rows.
    Illustration 8: Crosstab report with scroll bar below columns
    Illustration 8: Crosstab report with scroll bar below columns

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 Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=821733
ArticleTitle=IBM Cognos Proven Practices: IBM Cognos BI – Freezing Crosstab Row Headers
publish-date=06192012