sorting columns by clicking headers

[faris]

hi,
i want to sort columns in ascending/descendin order by clicking headers.thanks in advance.

[Venu_Gollapudi]

You can achieve this by using JSP coding with the help of a HTML object

[faris]

Can u explain it in detail?

[Venu_Gollapudi]

Hi Faris,

When you want to sort the columns by clicking the headers you can insert a html object over there and can put some code [JSP] as how you want, please find the attached zip folder which contains XML code with the same scenario, open the same from clip board in Report studio, also note that you can use the same html object [which is there in that report] for your report.


Regards,
Venu.

[cognosjon]

I downloaded this Cognos Supportlink document some time ago (see attached file) I tried it at the time and it worked ok and all with no Java coding.


hope it helps

Jonathan

[Venu_Gollapudi]

We are unable to download the attached document, pelase find the error message.

[cognosjon]

see what you mean about the error code, very odd, I can download it with no probs. Other
than that i'll try and paste document though you will obviously lose all the screenshots.

so here goes......................................(if you let me have your email address I can mail the attachment to you if you wish)

One Click Dynamic Column Sorting in a Report

by Adrian Pocovnicu, ISA Consulting

This technique allows you to dynamically sort a report by column by clicking on the column header plus it also enables you to change the sort order by repeating the click. This technique works with ReportNet as well as Cognos 8.1 and 8.2 reports. No Java Script or SDK is required to achieve this.
           
To create a list report that allows you to dynamically sort the data ascending or descending by pressing each column’s header you will need:
•   A parameter that provides the column used for sorting;
•   A parameter that provides the sort order (ascending/descending);
•   A drill through on each column title that will be sorted and pass the full name of that column. Also a pass sort order to change it each time a drill through occurs.
Steps:
1.   Create a new list report using Go Sales and Retailers package.
2.   From Insertable Objects Pane, expand Products query subject and drag Product line and Product type into the list.
3.   In Query Explorer go to Query1.
4.   Click on Product line data item and press CTRL+C and then CTRL+V to duplicate Product line. Repeat step 4 for Product type.
5.   Double-click on Product line1 (created at step 4) to edit it, and add an apostrophe to the beginning and to the end of the expression so it looks like this:
'[gosales_goretailers].[Products].[Product line]' and press OK.
Repeat step 5 for Product type1.
6.   From the Toolbox drag a Data Item underneath Product type1. In the Expression Editor that pops-up type:
#prompt('p_SortColumn','token','[gosales_goretailers].[Products].[Product line]')#
Click OK. (Column that we will sort on, will be the value of this parameter.)
7.   Rename newly created data item to: Sort_item_asc.
8.   In Page Explorer, click on Page 1 and then click anywhere in the list. From the ReportStudio men, click on Data and then Advanced Sorting...
9.   In the Grouping & Sorting dialog that pops-up drag Sort_item_asc on top of Detail Sort List. Click OK.

Figure 1: Grouping and Sorting dialogue
10.   Save the report and call it Cognos Sorting.
11.   Click on the header of the column Product line (the Properties pane should display List Column Title next to Ancestor Selector).
12.   In the Properties pane double click Drill Throughs. Click New Drill Through. For Report choose Cognos Sorting, for action choose Run the report, click to edit parameters, choose Pass data item values as Method and for Value select Product line1, see Figure 2:

Figure 2: Editing Parameters
13.   Click OK and then OK again.
Repeat steps 11 to 13 for each Product type column, but selecting Product type1 as value for the drill through parameter. (Depending on the column title where the drill through is initiated from - we will assign the according value for the parameter that gives us the column to sort.)
14.   Save the report and from Run->Run Options... menu deselect Prompt and click OK.
15.   Run the report and test sorting by clicking on column headers.
To improve the report to allow dynamic ascending or descending sorting, you can follow these additional steps:
16.   In Query Explorer go to Query1.
17.   Double-click Sort_item_asc to edit it and type:
if (#prompt('p_Direction','integer','1')#=1)
then(
#prompt('p_SortColumn','token','[gosales_goretailers].[Products].[Product line]')#)
else ('0')
(1 means ascending and -1 means descending. We will have a data item that changes from 1 to -1 and vice-versa at each drill through.)

Click OK.

Figure 3: Create ascending sort
18.   Create a copy for Sort_item_asc (using CTRL+C and CTRL+V). Rename the copy to Sort_item_desc.
19.   Double-click Sort_item_desc to edit it and change =1 to =-1. Click OK.

Figure 4: Create Descending sort
20.   Go to Page1 and click anywhere in the list. From the menu select Data and then Advanced Sorting...
21.   Drag Sort_item_desc underneath Sort_item_asc in Detail Sort List folder and click on the Sort Order button to change it to descending.

Figure 5: Change sort order to descending
22.   Click OK.
23.   From Query Explorer click Query1 and add a Data Item from the toolbox. Type this in the expression editor:
-(#prompt('p_Direction','integer','1')#)
Click OK. (This data item is the future value of the sort order on the next drill through.)

Figure 6: Create expression definition
23.   Rename Data Item1 to Direction.
24.   Save the report.
25.   Change the drill through properties for Product line and Product type list column title to pass a data item value for p_Direction parameter, and for the Value select Direction.

Figure 7: Setting the parameters
26.   Click OK and then OK.
27.   Save the report and run. For each click on the column header the order changes from ascending to descending and vice-versa.
XML Report Specification
Attached is the XML Report Specification for this solution. Remember to save the report under the name Cognos Sorting or otherwise change the drill through property to point to the correct name.

[faris]

I tried HTML method. It is working well for one page. My report has 25 rows. in single page i can view 20 rows. the HTML method is working well for this 20 rows. How to implement it for all 25 rows?

[faris]

I tried the method (without HTML and javascript). I am getting the following error..
"An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'."

[MFGF]

Hi,

I tried HTML method. It is working well for one page. My report has 25 rows. in single page i can view 20 rows. the HTML method is working well for this 20 rows. How to implement it for all 25 rows?

Select the list object in your report, and set the number of rows per page to be 25.

Regards,

MF.

[faris]

I tried it.. it is working fine if i give the number of rows per page is 25..But unfortunately the requirement is, if the report contains 2 pages,clicking on column header should sort rows in both pages. Any idea?

[faris]

I tried the method of sorting columns without HTML object and javascript.It is working well for non-numeric columns.But for numeric columns it is throwing " 'sqlPrepareWithOptions' status='-16' " error.

[faris]

Problem solved.. just replaced ['0'] by [null].. now it is working for both numeric and non-numeric columns.

[JO]

Hi,

I have tried this solution in the list report . It is working fine. But my requirement in the cross tab report. Can some one help me on this?

Thanks
Jo

[fasi]

Did any one have solution for this.

Sorting columns dynamically for cross tab.

Thanks,
Fasi.

[satys]

I tried the method without java script but i am not able to do it for Numeric Columns Can some one help me

[raj.cognos3@gmail.com]

Hi,
Can you please send me the XML.. I need it now please.... Thanks in Adv.

Ty