Primary and secondary sort with directions - Reportnet MR3

[sharu_bs]

Hi all,

I have to generate report with dynamic sorting in Reportnet MR3. Please give help if anybody has done similar reports.

Report design is as given below.

Report criteria page  has following params
1) Order No
2) Order Name
3) Order date
4) Primary sort field (drop down with Order No, Order Name, date field name)
5) Primary Sort direction (drop down with Ascending and descending)
5) secondary sort field (drop down with Order No, Order Name, date field name)
6)  secondary Sort direction (drop down with Ascending and descending)

Depending on criteria selected i need to show report and sorted accordingly.


I tried following solution given in one of the forums..

?SortOn? is the column/field to sort on and has static values for
> the columns you want the user to be able to sort on.
> ?SortDirection? is the direction of the sort and has static
> values of 'Ascending' or 'Descending'.
>
>Create two columns named Ascending and Descending. Set the
> Ascending column to sort ascending and set the Descending column
> to sort descending. Basically, this technique copies the column
> selected in the ?SortOn? prompt into the Ascending or Descending
> column, which then sorts the report on this column in the
> direction chosen in the ?SortDirection? prompt.
>
>Here's some sample code for the Ascending column:
>
>CASE (?SortDirection?)
>
> WHEN ('Ascending') THEN
> CASE (?SortOn?)
>   WHEN 1 THEN [Column1]
>   WHEN 2 THEN [Column2]
>   WHEN 3 THEN [Column3]
>   ELSE (' ')
> END
>ELSE (' ') END


But with above solution I can do with only with same string datatypes.... If i have sorton column with different data types then it wont work.

Any solutions?????

[MFGF]

Hi,

You could try:

CASE (?SortDirection?)
WHEN ('Ascending') THEN
CASE (?SortOn?)
  WHEN 1 THEN cast([Column1],varchar(10))
  WHEN 2 THEN [Column2]
  WHEN 3 THEN cast([Column3],varchar(12))
  ELSE (' ')
END
ELSE (' ') END

Not sure whether this would impact your sort order - you may end up having to convert your date to a YYYYMMDD integer then casting this as varchar(8).

Regards,

MF.

[larsonr]

I have gotten this code to work with Javascript.  Its not fully functional as I have not modified all my regular expressions.  The values have all been hardcoded as numeric.  So it works just fine with numbers.  Once I have all the regular expressions lined up it will work with any datatype as I defined within C8.

The code was actually taken from a google search of Cognos and Dynamic Sort. Cognos Support apparently has an item posted that made it to the ittoolkit website.

To get a head start, do a google search on sorttable.js.  This code will require modifications to perform within Cognos8. 

If interested in report spec, let me know and I can post this as well.