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
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.
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
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.
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
Quote from: stancho on 25 Nov 2011 05:38:39 AM
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.