Sorting executed in DB2 or in Cognos?

[stancho]

Hi,

When I sort the data in List report from Advanced Sorting (Grouping and Sorting) in Report Studio where the sorting will be done:
in the database server (it's DB2) or in the Cognos server (8.4.1).

Does the sorting depends on the setting: Query Processing (Database Only or Limited Local)?

Thanks and Regards,
Stancho

[MFGF]

The answer is... it depends. Where possible the sort will be passed to the database as part of the SQL query, but if you are sorting on a locally calculated or aggregated item, then obviously this cannot be done. The Limited Local vs Database Only setting will not really have an influence on this. The easy way to chexk is to look at the query being generated by your report - does it have an order by clause?

Regards,

MF.

[stancho]

Hi MFGF,

thanks for your reply!

Basically I'm trying to achieve dynamic Cognos sorting using this article:
http://www.stthomas.edu/irt/support/enterprise/cognos/documentation/Cognos_8-_One_Click_.pdf

It is generating the following query in Native and Cognos format:

-- Native SQL
select "BRAND_DIM"."BRAND_CD", "BRAND_DIM"."SUBBRAND_CD"
from "REPORTING"."BRAND_DIM" "BRAND_DIM"



-- IBM Cognos SQL
select distinct
       BRAND_DIM.BRAND_CD  as  Brand,
       '[Test Package].[Brand].[Brand]'  as  Brand1,
       (-1)  as  Direction,
       BRAND_DIM.SUBBRAND_CD  as  Subbrand,
       '[Test Package].[Brand].[Subbrand]'  as  Subbrand,
       BRAND_DIM.BRAND_CD  as  Sort_item_asc,
       case when (1 = (-1)) then BRAND_DIM.BRAND_CD else '0' end   as  Sort_item_desc,
       '[Test Package].[Brand].[Brand]'  as  Brand18,
       (-1)  as  Direction9,
       '[Test Package].[Brand].[Subbrand]'  as  Subbrand110
from
       INSIGHT..REPORTING.BRAND_DIM BRAND_DIM
order by
       Sort_item_asc asc,
       Sort_item_desc desc

--------------------
Why is it diffrent in Native and Cognos format so much?

In Cognos format it is generating:
order by
       Sort_item_asc asc,
       Sort_item_desc desc
So it seems that the sorting is done in the database (DB2)?

Thanks a lot,
Stancho

[MFGF]

Hi,

The Cognos SQL gets translated to native SQL, and I don't see an orderby clause in the native query, so I would assume in this case that the sorting is being done locally on the Cognos server. It's probably because you are sorting on an item derived within the report.

MF.

[stancho]

MFGF thanks for you answer!

So after every click to sort dynamically the Cognos server will execute this simple SELECT query to the database and will sort the data locally.

Stancho

[MFGF]

MFGF thanks for you answer!

So after every click to sort dynamically the Cognos server will execute this simple SELECT query to the database and will sort the data locally.

Stancho

In this case yes - exactly right!

MF.