Hello Experts
i have to produce sorting logic with the help of macro and rank function plus running count if needed . How do i achieve that ? my desired output is as follow,
Grade Date Score
03 03/05 86
03 03/05 741
04 03/06 1718
04 03/06 1906
05 03/07 1185
03 03/08 687
04 03/09 295
04 03/09 1484
04 03/09 1619
05 03/09 1968
05 03/10 1465
07 03/10 2274
06 04/11 833
06 04/11 1304
06 04/11 1393
06 04/11 1687
06 04/11 2059
07 04/11 1572
07 04/11 1614
so on , basically as year(ascending in this example) changes , grade(ascending) also changes with year and score in this example sorted ascending.
I would use a SQL ORDER BY clause, and make sure Cognos is not re-sorting. Some versions of cognos do that be default.
Basic SQL would look like this:
Assuming your 3 fields are pulled from a table and you're just sorting them .. do this:
SELECT [Grade], [Date], [Score]
FROM YourTable
ORDER BY [Date], [Grade]
You can also rank these values, if you're looking to generate the grade # for example .. then do something like this:
SELECT Rank() OVER (ORDER BY [Date]) as [Grade], [Date], [Score]
FROM YourTable
cheers,
Dave