[CLOSED] sort the column in sql report based on two criteria

Focal Point

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/263100981

July 23, 2009, 04:45 PM

Maria Tan

[CLOSED] sort the column in sql report based on two criteria

Hi;

I have a sql report and the result looks like the following group by Resource and business role

Resource Business Role Function Role
Oracle FR 1
Oracle FR 2
Oracle FR 3
Oracle AP Accountant FR 4
Oracle AP Manager FR 5

However i want to see Business Role sort first and put all the records with empty business role at the end of report. Is it feasible?

Resource Business Role Function Role
Oracle AP Accountant FR 4
Oracle AP Manager FR 5
Oracle FR 1
Oracle FR 2
Oracle FR 3

Thanks

-Maria

This message has been edited. Last edited by: Kerry,

====================
Production: WebFOCUS 7.1.1 on Window 2003
WebFOCUS 7.1.1 Client on HP-UX on WebLOGIC 8.1 sp4
Dev: WebFOCUS 7.6.4 on Window 2003
WebFOCUS 7.1.1 Client on HP-UX on WebLogic 8.1 sp4

July 23, 2009, 04:48 PM

Maria Tan

Hi;

I have a sql report and the result looks like the following group by Resource and business role

Resource, Business Role , Function Role
Oracle , , , FR 1
Oracle , , , FR 2
Oracle , , , FR 3
Oracle , AP Accountant , FR 4
Oracle , AP Manager , FR 5

However i want to see Business Role sort first and put all the records with empty business role at the end of report. Is it feasible?

Resource Business Role Function Role
Oracle , AP Accountant , FR 4
Oracle , AP Manager , FR 5
Oracle , , FR 1
Oracle , , FR 2
Oracle , , FR 3

Thanks

-Maria

====================
Production: WebFOCUS 7.1.1 on Window 2003
WebFOCUS 7.1.1 Client on HP-UX on WebLOGIC 8.1 sp4
Dev: WebFOCUS 7.6.4 on Window 2003
WebFOCUS 7.1.1 Client on HP-UX on WebLogic 8.1 sp4

July 23, 2009, 05:19 PM

Darin Lee

Not only have you posted the thread twice, you have posted the same message twice in the same thread. You only need to post an issue once - please refer to my response posting on first posting

Regards,

Darin

In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat

July 23, 2009, 11:03 PM

Doug

Maria Tan, Please, while you're at it, delete this post. To do so, edit the initial post and "delete message" (using the little folder with the eraser on it). This will help keep the forum a bit cleaner...

July 24, 2009, 08:20 AM

Danny-SRL

Maria,

Using the CAR file and the SALES field, making it blank when zero, here is a possible solution:

  
-* File MariaTan2.fex
DEFINE FILE CAR
ASALES/A6=IF SALES EQ 0 THEN ' ' ELSE FTOA(SALES,'(D6c)', 'A6');
SORTSALES/A6=IF ASALES EQ ' ' THEN 'ZZZZZZ' ELSE ASALES;
END
SET BYDISPLAY=ON
TABLE FILE CAR
PRINT 
MODEL
BY COUNTRY
BY SORTSALES NOPRINT
BY ASALES
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
GRID=OFF, $
ENDSTYLE
END

July 24, 2009, 07:26 PM

Doug

Marie Tan,

Please combine these two posts into a single post. It seems that they're both getting replies.

1) sort the column in sql report
2) sort the column in sql report based on two criteria