Report Studio : Dynamic Rows Per Page

Report Studio : Dynamic Rows Per Page

In continuation with the last article where we discussed achieving dynamic rows per page using Database functions, in this article we will discuss the same using JavaScripting where the processing happens on the client side. As discussed in the previous article report developers should weigh their options before opting for either of these approaches. 

Dynamic Rows Per Page - Approach 1

I have been working for the last couple of weeks on developing reports that can be integrated into websites and portals. Reports that need to be integrated into such sites need to be able to support a lot of interactive requirements. And one such requirement is the dynamic rows per page concept.

There are 2 ways to implement the same, one where the entire processing is pushed to the DB side and the other where the processing happens on the client side using JavaScript.

Such interactive requirements should only be supported when the number of rows retrieved is less else report rendering is going to be slow and in cases if the client side approach is used and the client environment is not powerful enough reports could result in hung screens. Some databases do not support running functions and when used these are executed on the Cognos server impacting performance. And with each click on the report re-executes the report query causing more queries to be fired against the DB.

Hence report developers should weigh their options before opting for either of these approaches.

Approach 1: DB only

Solution:

Step 1: Create a Rows Per Page value prompt with static choices of 5,10, 15.. and a default selection of 5. Set the name of the prompt to Rows.

Step 2: Create a Page text box prompt with default value of 1 indicating that the report should display Page 1 when run for the first time. Set the Visible property of the parameter to No.

Step 3: Create a Query say Query 1 with the required data items.

Step 4: Create the following Data Items:

RunningCount: running-count(1)

Page: ceil([RunningCount]/?RowsPerPage?)

Next Page: ?Page? + 1

Prev Page: ?Page? - 1

First Page: 1

Last Page: maximum([Page] for report)

Step 5: Create Query 2 with Query 1 as the referenced query. Drag all items from Query 1. Include a filter [Page] = ?Page?

Step 6: Create a simple list report based on Query 2

Step 7: Create the Paginate components of First Page, Prev Page, Next Page and Last Page text items in the Page Footer with drill through properties set on them.

Choose the "Pass Parameter Value" for the RowsPerPage drill through parameter and for the Page parameter set the below options for the various items.

First Page: Pass Data Item / [First Page]

Prev Page: Pass Data Item / [Prev Page]

Next Page: Pass Data Item / [Next Page]

Last Page: Pass Data Item / [Last Page]

Step 8: Create conditional styles to hide the First Page, Prev Page components when on Page 1.

Conditional Style 1: ParamValue('Page') = '1'

Step 9: Create conditional styles to hide the Next Page, Last Page components when on Last Page.

Conditional Style 2: string2double(ParamValue('Page')) = [Query2].[Last Page]

 

Dynamic Rows Per Page - Approach 2

Approach 2: Client Side Processing

Solution: 

Step 1: Create a Rows Per Page value prompt with static choices of 5,10, 15.. and a default selection of 5. Set the name of the prompt to Rows.

Step 2: Create a simple list report and set the Rows Per Page property to a value higher than the total number of rows returned

Step 3: Insert HTML items before and after the list report

    HTML Item 1: <div id="ListTbl">

    HTML Item 2: </div>

Step 4: Now to create a Pagination component, create a table in the Page Footer with 4 columns. Insert HTML Items before and after the table.

    HTML Item 3: <div id="Paginate">

    HTML Item 4: </div>

Step 5: Create First Page, Prev Page, Next Page, Last Page components in the 4 columns as shown below:



    HTML Item 5: <a href="#" onClick="paginate( 1);">

    HTML Item 7: <a href="#" onClick="paginate(currentPage - 1);">

    HTML Item 9: <a href="#" onClick="paginate(currentPage + 1);">

    HTML Item 11: <a href="#" onClick="paginate(lastPage);">

    HTML Item 6, HTML Item 8, HTML Item 10, HTML Item 12: </a>

Step 6: Insert an HTML Item and include the below JavaScript code:

<script>
    //get the List Table object
    var ListTbl = document.getElementById("ListTbl");

    var Tbl = ListTbl.getElementsByTagName('table')[0];

    //get the rows per page prompt value selected

    var fW = getFormWarpRequest();

    var rowsPerPg = fW._oLstChoicesRows.value;

    //get the total number of rows in the list

    var rows = Tbl.rows.length;

    var currentPage = 1;

    //get the last page value

    var lastPage = Math.ceil(rows/rowsPerPg);

    var rowStart;

    var rowStop;

    function paginate(currentPage)
    {
        window.currentPage = currentPage;
        rowStart = (currentPage * rowsPerPg) - rowsPerPg + 1;
        rowStop = (currentPage * rowsPerPg) + 1;
        if(currentPage==lastPage)
        rowStop = rows;

        for(i=1; i < rows ;i++)
        {
            //hide the display of all rows
            Tbl.rows(i).style.display='none';
        }

        for(i = rowStart ; i< rowStop ; i++)
        {
            //set only the required rows to display
            Tbl.rows(i).style.display='block';
        }

        var Paginate = document.getElementById("Paginate");
        var PaginateTbl = Paginate.getElementsByTagName('table')[0];

        if(currentPage==1)
        {
            //hide First Page and Prev Page components when on Page 1
            PaginateTbl.rows(0).cells(0).style.display = 'none';
            PaginateTbl.rows(0).cells(1).style.display='none';
        }
        else
        {
            PaginateTbl.rows(0).cells(0).style.display='block';
            PaginateTbl.rows(0).cells(1).style.display='block';
        }

        if(currentPage==lastPage)
        {
            //hide Last Page and Next Page components when on Last Page
            PaginateTbl.rows(0).cells(2).style.display='none';
            PaginateTbl.rows(0).cells(3).style.display='none';
        }
        else
        {
            PaginateTbl.rows(0).cells(2).style.display='block';
            PaginateTbl.rows(0).cells(3).style.display='block';
        }

    }

paginate(currentPage);

</script>

Note: This technique uses JavaScript against underlying report objects in an IBM Cognos 8 BI report. For this reason, there is no guarantee that reports created using this technique will migrate or upgrade successfully to future versions without requiring modifications. Any such modifications are the responsibility of the report designer.