Report Studio : Dynamic Sorting Controls from IBM Cognos

Report Studio : Dynamic Sorting Controls from IBM Cognos

Cognos seems to have come up with new controls / method for dynamic column sorting that doesn't involve the drill through methods.

Here's the link to the article.

http://www.ibm.com/developerworks/data/library/cognos/reporting/scripting_techniques/page515.html?ca=drs-

Sounds cool!! Hope this ends the report re-run issues we have while using the drill-through method for sorting.

I will check this method out and post my comments later.

Let me know your thoughts on this method though.

Here's an update from PaulM about applying this method on Cognos 10 environment:

[User comments: PaulM] 

There are some differences between the XML files from Cognos 8 to Cognos 10. The general structure of the files are the same, so making the correct updates is not difficult.

First you need to use a decent text editor, one with comparison capabilities. Notepad++ is excellent for this.

Open both the C10 version and the version from the download. They should be in different tabs in Notepad++. Click on the plugins menu and select Compare --> Compare.

Find and copy the differences related to this technique only. Don't try to copy deprecated objects over. All of the related fragments have "orderCol" somewhere in the name.

Introduction

Purpose

This document describes a technique which applies dynamic column sorting on a list using custom IBM Cognos 8 Report Studio toolbox items.

Applicability

The technique outlined in this document was validated using:

• IBM Cognos 8.4.102-18-0 using the Go Sales (query) package
• IBM Cognos 10.1.1 using the Go Sales(query) package
• IBM Cognos 10.2.1 using the Go Sales(query) package

The steps outlined in this document apply only when sorting is desired for a single list within an IBM Cognos Report Studio report with a HTML output type using an English run locale.

The 8.4.1 ,10.1.1 and 10.2.1 files are not compatible between versions. Ensure that the file with the version suffix that matches your existing IBM Cognos BI version is renamed, before being copied.

Undocumented and Unsupported Capabilities Used

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

Back to top

Applying Dynamic Sorting on a List Report

Updating the Toolbox Files

Before launching IBM Cognos 8 Report Studio, a few of the IBM Cognos 8 install files need to be updated with the attached files which contain the new toolbox items that can be dropped over the canvas to add the sorting logic. The custom toolbox items allow the report author to quickly add all the necessary components to the report, instead of having to do everything manually.

1. Locate the <install_dir>/webcontent/pat/res directory, where <install_dir> is the root directory of your IBM Cognos 8 instance.
2. Backup the following files:
   • reportstudio_en.xml
   • Toolbox.xml
   • ToolboxControls.xml
3. Replace the aforementioned files (not the backups) with the files of the same name that are attached to this technique.

Applying Sorting on a List Report

In this example, we will create a new report from the GO Sales (query) sample data. If you have an existing report, you can also apply sorting to it by following steps 3 onward.

1. Open a new Report Studio instance, and when prompted, create a new List report.
2. Drag and drop the data items that you want to show up in your list onto the list object already on the canvas. For this example, the entire Sales (query)\Retailer Query Subject was used.

   Figure 1 Screen Capture of IBM Cognos 8 Report Studio with the Retailer Query Subject dragged into a List Report.

   
3. The next step is to add the ordering logic to each column. In order to do that, we first need to unlock the page objects. To unlock the page objects, go to the Structure menu, and click on the Lock Page Objects menu item so that it is unchecked.

   Figure 2 Image of the Structure Menu with the Lock Page Objects setting unchecked.

   
   Alternatively, you can click on the lock icon in the toolbar to unlock them (the lock icon will then change to the unlocked icon).
4. Once the page objects have been unlocked, go to the left-hand side pane, and click on the toolbox tab.
5. In the Insertable Objects panel, scroll down to the very bottom. You should see the Column Sorting Function and Sortable Column Title toolbox objects as illustrated below.

   Figure 3 IBM Cognos 8 Report Studio toolbox image displaying the custom Column Sorting Function and Sortable Column Title as new toolbox objects.

   
6. Drag and drop the "Sortable Column Title" to the title cell of a column that you want to have sorting capabilities. Your canvas should then represent the following image.

   Figure 4 Image of a List column with the original Column Title highlighted as Item 2 and the new column title highlighted as Item 1.

   
7. Edit the text so that it contains the desired column header, and delete to original content of the cell. When completed, your screen should now represent the following screen capture.

   Figure 5 Image of the List Column with a specified Sortable Column Title and the original column title removed.

   
8. Repeat steps 5 to 7 for each column for which you want sorting capabilities.
9. At this point, we should lock the page objects again. To do this, go to the Structure menu, and click on the Lock Page Objects menu item so that it is checked. Alternatively, you can click on the lock icon in the toolbar to lock them (the icon will then change to locked).
10. These new column headers require certain render variables to be set in order to be displayed correctly in the report. To set those variables, hover your mouse over the Condition Explorer tab (with the Conditional Explorer icon, between the Insertable Objects panel and the canvas), and when it expands, click on the Variables folder.
    The canvas will be replaced by the variables view illustrated below.

    Figure 6 IBM Cognos 8 Report Studio Conditional Explorer View

    
11. From the Insertable Objects panel on the left, drag and drop the three “Column Sort” variables (“Column Sort – Sorting Controls Rendering”, “Column Sort – Sorting Column Rendering” and “Column Sort – PDF Image Rendering”).
12. Return to the canvas by hovering your mouse over the Page Explorer tab (at the same location as the Condition Explorer), and when it expands, click on the page where your list is located. For this example the page will be Page1.
13. Next, we will add the sorting columns. If you haven’t already done so, you should lock the page objects again (see step 9). Two alternatives are described below, so you can choose the one that better suits your needs.
    
    Method A: One ascending column, one descending column
    The first method is probably the simplest and most efficient one, but it may behave unexpectedly depending on the database type that you are using. In this method, we will create two columns: one for ascending ordering, the other for descending ordering.
    1. Go to the left-hand side pane, and click on the toolbox tab.
    2. In the Insertable Objects panel, locate “Query Calculation”. Drag and drop it to the end of your list (make sure that it is dropped on the list, and not after it).
    3. You will be presented with the Create Calculation dialog box.
    4. In the Create Calculation dialog, enter a meaningful name, such as “Order By ASC”, and click OK. The Data Item Expression dialog appears.

       Figure 7 IBM Cognos 8 Report Studio Data Item Expression Editor

       
    5. This is where we will define which column will be used for sorting.
    6. In the Data Item Expression dialog, click on the functions tab. The Available Components box will be refreshed. Click on the “Constructs” folder, and drag and drop the “if then else” item in the Expression Definition box.
    7. In the Expression Definition box, replace <condition> by the following:
       ?OrderBy? = '1'
    8. Click on the data items tab. Drag and drop the item representing the first column in place of the first <expression> in the Expression Definitionbox (you will need to manually remove the <expression> text):

       Figure 8 IBM Cognos 8 Report Studio Data Item Expression dialog box

       
       Note that if you are using an OLAP data source and have levels showing up in the Available Components box, you will not be able to successfully use them in the expression. In such cases, you will need to go in the source tab and pick the data item that you wish to use for sorting those particular columns.
    9. Replace the remaining <expression> by null or by any static value (e.g. 'ASC'). At this point, your expression should look like this:

        IF ( ?OrderBy? = '1' ) THEN
            ( [Retailer type code] )
        ELSE
            ( null )

       
       This essentially means that if the “OrderBy” parameter has a value of “1”, this sorting column will be populated with the content of the first column, “Retailer type code”. Otherwise, it will be populated with null/empty values.
       Note that when using OLAP data sources, you will not be able to use null in the else statement, and will need to use a static value.
    10. In order to apply the ordering on all the columns, we will need to add them to the if-then-else statement. You can either repeat the previous steps to drag and drop items, or you can type in the expression manually. In the end, your expression should look something like this:

         IF (?OrderBy? = '1') THEN
             ([Retailer type code])
         ELSE IF (?OrderBy? = '2') THEN
             ([Retailer type])
         ELSE IF (?OrderBy? = '3') THEN
             ([Retailer code])
         ELSE IF (?OrderBy? = '4') THEN
             ([Retailer name])
         ELSE IF (?OrderBy? = '5') THEN
             ([Retailer name (multiscript)])
         ELSE IF (?OrderBy? = '6') THEN
             ([Retailer start date])
         ELSE (null)

    11. Validate your expression using the Validate Expression button at the top-right of the dialog (you may be prompted to enter a value for the “OrderBy” parameter, in which case you can enter zero and click OK).
        If you get “No errors” in the Information box, click OK on the dialog.
        There may be certain situations where you can get a message that looks like this:

         QE-DEF-0459 CCLException
         QE-DEF-0478 Invalid coercion from 'value' to 'string'
           for '<data item>' in  '<expression>'

        Figure 9 IBM Cognos 8 Report Studio Data Item Expression dialog box with the completed calculation and the coercion error in the Information pane

        
        If this happens, try to reorder your conditions in your expression, grouping items of similar data types (numbers, strings, dates, etc.) together. For example, in the previous expression, by putting both integer data items together (“Retailer type code” and “Retailer code”), the error message disappears.

        Figure 10 IBM Cognos 8 Report Studio Data Item Expression dialog box with the re ordered caclulation to avoid the error

        
        Notice how the (?OrderBy? = ‘2’) and (?OrderBy? = ‘3’) conditions have switched places. If this doesn’t solve the problem, you may have to use the second sorting alternative.
    12. Once all errors have been resolved, click OK to close the dialog. You will notice that a new column was added to the list.
    13. Select either the title or the body of that new column.
    14. In the Report Studio toolbar, click on the sort icon, and select Sort Ascending from the drop-down menu.
    15. Notice the icon that now appears in the column, indicating that it will be sorted in ascending fashion.
    16. Repeat steps 13.A.1 to 13.A.15 for a descending sorting column, with the following changes:
        • Give the column a different name (e.g. “Order By DESC”)
        • Change the conditions in the Expression Definition to use negative values, e.g.

            IF (?OrderBy? = '-1') THEN
                ([Retailer type code])
            ELSE IF (?OrderBy? = '-3') THEN
                ([Retailer code])
            ELSE IF (?OrderBy? = '-2') THEN
                ([Retailer type])
            ELSE IF (?OrderBy? = '-4') THEN
                ([Retailer name])
            ELSE IF (?OrderBy? = '-5') THEN
                ([Retailer name (multiscript)])
            ELSE IF (?OrderBy? = '-6') THEN
                ([Retailer start date])
            ELSE (null)

          
          You can copy and paste the expression from the ASC column, and only have to replace the values in the conditions from positive to negative.
        • Select Sort Descending from the sort menu.
    17. At this point, you should have two new columns at the end of your list, like so:

        Figure 11 The IBM Cognos 8 Report Studio List with the two created calculations displayed as the final two columns

        
    Method B: One ascending column per data type, one descending column per data type
    The second method requires a bit more work and may not be quite as straightforward, but should be a bit safer to use. In this method, we will create two columns per data type in the query: one for ascending ordering, the other for descending ordering.
    1. Since the idea is the same regardless of the method you choose, follow steps 13.A.1 to 13.A.15 to create an ascending sorting column for one of the data types in your list. You will need to consider the following differences:
       • Give the column a meaningful name. Something like “Order By Int ASC” would be good practice for an ascending integer sorting column.
       • Change the expression so that it only contains items of the same type. For example, for integers, the expression would be:

           IF (?OrderBy? = '1') THEN
               ([Retailer type code])
           ELSE IF (?OrderBy? = '3') THEN
               ([Retailer code])
           ELSE (null)

         
         Notice how the other four data items are not listed here.
    2. Once you have created the ascending sorting column for the data type, create the descending column for that same data type, as per step 13.A.16.
    3. Repeat steps 13.B.1 to 13.B.2 for all data types in your list. In our example, we ended up adding 6 sorting columns:

       Sorting columns
       Order By Int ASC Data type: Integer Direction: Ascending Expression: IF (?OrderBy? = '1') THEN ([Retailer type code]) ELSE IF (?OrderBy? = '3') THEN ([Retailer code]) ELSE (null)	Order By Int DESC Data type: Integer Direction: Descending Expression: IF (?OrderBy? = '-1') THEN ([Retailer type code]) ELSE IF (?OrderBy? = '-3') THEN ([Retailer code]) ELSE (null)
       Order By Varchar ASC Data type: Varchar/String Direction: Ascending Expression: IF (?OrderBy? = '2') THEN ([Retailer type]) ELSE IF (?OrderBy? = '4') THEN ([Retailer name]) ELSE IF (?OrderBy? = '5') THEN ([Retailer name (multiscript)]) ELSE (null)	Order By Varchar DESC Data type: Varchar/String Direction: Descending Expression: IF (?OrderBy? = '-2') THEN ([Retailer type]) ELSE IF (?OrderBy? = '-4') THEN ([Retailer name]) ELSE IF (?OrderBy? = '-5') THEN ([Retailer name (multiscript)]) ELSE (null)
       Order By Date ASC Data type: Date Direction: Ascending Expression: IF (?OrderBy? = '6') THEN ([Retailer start date]) ELSE (null)	Order By Date DESC Data type: Date Direction: Descending Expression: IF (?OrderBy? = '-6') THEN ([Retailer start date]) ELSE (null)

14. Regardless of the selected ordering method, we will hide the sorting columns: select one of the sorting columns.
15. In the Properties panel in the left pane, in the title area, click on the ancestors icon.
16. From the context menu, select the List Column object.
17. In the Properties panel, under the Conditional section, double-click on Render Variable.
18. In the Variable drop-down, select “Sorting Columns Rendering”, and click OK.
19. Repeat steps 15 to 19 for any other sorting column.
20. Finally, we can add the last piece of the puzzle, the logic that allows the list to be sorted on the fly. Go to the left-hand side pane, and click on the toolbox tab.
21. In the Insertable Objects panel, scroll down to the very bottom. You should see the Column Sorting Function and Sortable Column Title custom toolbox items.
22. Drag and drop the “Column Sorting Function” right above the list. Your canvas should now display as follows.

    Figure 12 Image depicting the list object with the sorted columns and the additional html item above the list

    
23. In order to use the sorting capabilities, the report needs to be saved. Save the report.
24. Go back in the Cognos Connection portal (do not close Report Studio), and navigate to the report.
25. Under the Actions column, select the Set properties icon.
26. In the Set properties window, under the General tab, on the far right, click on the link that reads View the search path, ID and URL. A new dialog should open.
27. Select the text in the Default action URL box and copy it to the clipboard.
28. Go back into Report Studio, and double-click on the text item that reads “Copy the Default action URL for the report here. Only the Default action URL should exist between these two HTML items.” This should bring up the Text dialog.
29. Replace the existing string with the default action URL for the report, and close the Text dialog by clicking OK. Your report should now display the following.

    Figure 13 IBM Cognos 8 Report Studio List report with the sorts in place and the query calculations as the last two columns of the list

    
    Ensure that the host in the URL is not “localhost”, otherwise other users may have problems with the sorting.
    Also note that if you rename the report, or move it to a different location, you will need to update the URL inside the report.
30. Notice that there are two HTML Item objects following the default action URL. If you have any existing parameters/prompts for your report that you want to carry over when you sort the list, you will need to define them here as follows:
    1. In the Insertable Objects panel (in the toolbox tab), locate the Layout Calculation object, and drag and drop it between the two aforementioned HTML Item objects. The Report Expression dialog appears.
    2. Type in (or insert using the Available Components pane) the following for the first parameter that you wish to carry over:

       ParamName('<parameter name>')+ '=' + ParamValue('<parameter name>')

    3. Repeat steps 1-2 for each additional parameter that you wish to carry over. Note that you do not need to include the OrderBy parameter here.
    4. In the end, your expression for each Layout Calculation object should look similar to what appears in Figure 14. Furthermore, the Layout Calculation objects should appear between the two HTML Item objects, also as shown in Figure 14.

       Figure 14 The parameter specification for the column sort, as it should appear from IBM Cognos 8 Report Studio. Circled is put on the location of the Layout Calculation object represented by the depicted Report Expression.

       
       These values will then be added to the URL by the sorting JavaScript function.
       Important: A few assumptions have been made when handling the parameter values in the JavaScript code to cover the most common cases:
       • Parameter values are comma-delimited. This is meant to accommodate multi-select parameters, whose values are represented as “value1, value2, value3”. This means that if you have a single parameter value that contains a comma, it would be treated as if it consisted of two values.
       • Parameter values starting with “Between” represent a range of values. This is meant to accommodate parameters such as date ranges. This means that if you have a single parameter value that starts with “Between”, it would be parsed in a way to extract a range of values.
       If these assumptions are inaccurate for the data being retrieved in a given report, special handling may need to be implemented in the JavaScript code by the report author (see the HTML Item object that appears before the default action URL).
    If you don't have any parameter values that you wish to carry over, you do not have to do anything here.
31. Save the report.

Using the Dynamic Sorting Capabilities

This section will give you a brief overview of how the sorting capabilities that were put into the report work.

1. From IBM Cognos Connection or from IBM Cognos 8 Report Studio, run the report as HTML.
2. A prompt should come up asking for the “OrderBy” parameter value. Enter zero and click OK.
   Note that you can set a default value for this parameter in the report properties page and tell the Cognos Viewer not to prompt for values if you want.
3. Your report content should look like this (note that only the first page of the output is shown here):

   Figure 15 IBM Cognos 8 Report Viewer output displaying the list in HTML format

   
   Notice that the two sorting columns are not visible.
4. Hover over any column header, and you will see the sorting icon appear.
5. Click on the current sorting icon. After the report has finished running, it will be sorted ascending on the selected column (note that only the first page of the output is shown here):

   Figure 16 IBM Cognos 8 Report Viewer output of the list in HTML format with the custom sort icon displayed on the selected column

   
   Notice that the sorting icon has changed to the ascending sorting icon, indicating the sort direction.
6. Click on the current sorting icon. After the report has finished running, it will be sorted descending on the selected column.
   Notice that the sorting icon has changed to the descending sorting icon, indicating the sort direction.
7. Click on the current sorting icon. After the report has finished running, it will no longer be sorted based on the selected column.
   Notice that the sorting icon has disappeared, indicating that no sorting is taking place.