Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]Sorting fields
Go
New
Search
Notify
Tools
Reply
  
[CLOSED]Sorting fields
 Login/Join
 
Member
posted
I used the BY command combined with the LOWEST and HIGHEST prefix to vertically sort fields for my report whose output is in Excel. However, when I sort the data using Excel, the order is different. How do I sort in Web Focus so the order of the data is the same I get by sorting in Excel. See examples below:

BY LOWEST ITEM_NO
A18-46672-000
A22-67400-000
HUN145138997730
03-38548-002
06-75227-000
22-66582-010

BY HIGHEST ITEM_NO
22-66582-010
06-75227-000
03-38548-002
HUN145138997730
A22-67400-000
A18-46672-000

ITEM_NO (Using Excel A to Z)
03-38548-002
06-75227-000
22-66582-010
A18-46672-000
A22-67400-000
HUN145138997730

ITEM_NO (Using Excel Z to A)
HUN145138997730
A22-67400-000
A18-46672-000
22-66582-010
06-75227-000
03-38548-002

Thank you for the help!

This message has been edited. Last edited by: <Emily McAllister>,


WebFocus 7703
Windows, All Outputs
 
Posts: 11 | Registered: November 09, 2012Reply With QuoteReport This Post
Expert
posted Hide Post
Petter, when I run the following code, the order is as you described for Excel A to Z:

DEFINE FILE CAR
MY_ALPHA_NUM_COLUMN/A15 =
     IF CAR EQ 'AUDI'   THEN 'A18-46672-000'
ELSE IF CAR EQ 'BMW'    THEN 'A22-67400-000'
ELSE IF CAR EQ 'JENSEN' THEN '06-75227-000'
ELSE IF CAR EQ 'DATSUN' THEN 'HUN145138997730'
ELSE IF CAR EQ 'JAGUAR' THEN '03-38548-002'
ELSE '22-66582-010';
END

TABLE FILE CAR
SUM
SALES
BY LOWEST MY_ALPHA_NUM_COLUMN
END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
Perhaps the issue is platform related - is your reporting server on UNIX?

Perhaps this will work - add a number to the beginning of columns where the data starts with an alpha character:

DEFINE FILE CAR
MY_ALPHA_NUM_COLUMN/A15 =
     IF CAR EQ 'AUDI'   THEN 'A18-46672-000'
ELSE IF CAR EQ 'BMW'    THEN 'A22-67400-000'
ELSE IF CAR EQ 'JENSEN' THEN '06-75227-000'
ELSE IF CAR EQ 'DATSUN' THEN 'HUN145138997730'
ELSE IF CAR EQ 'JAGUAR' THEN '03-38548-002'
ELSE '22-66582-010';

POS1/A1 = SUBSTR(15, MY_ALPHA_NUM_COLUMN, 1, 1, 1, 'A1');
-*TEST/I6 = CHKFMT(1, POS1, '9', 'I6');
MY_ALPHA_NUM_COLUMN_SORT/A16 = IF CHKFMT(1, POS1, 'A', 'I6') EQ 0 THEN '9' | MY_ALPHA_NUM_COLUMN ELSE MY_ALPHA_NUM_COLUMN;
END

TABLE FILE CAR
SUM
-*POS1
-*TEST
MY_ALPHA_NUM_COLUMN
SALES
MY_ALPHA_NUM_COLUMN_SORT
BY LOWEST MY_ALPHA_NUM_COLUMN_SORT NOPRINT
END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
Your database is sorting in EBCDIC while Excel is sorting in ASCII. To fix this you will need to set your database to sort in ASCII.

Regards,
Anatess


WF 8.1.05 Windows
 
Posts: 332 | Location: Orlando, FL | Registered: October 17, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Is it the database? Couldn't WebFOCUS intervene? By creating a HOLD file, and then sorting the HOLD file, which would be DB independent.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
quote:
Originally posted by Francis Mariani:
Is it the database? Couldn't WebFOCUS intervene? By creating a HOLD file, and then sorting the HOLD file, which would be DB independent.


Ahh yeah. In any case, whoever is sorting that on the Webfocus side is sorting in EBCDIC.


WF 8.1.05 Windows
 
Posts: 332 | Location: Orlando, FL | Registered: October 17, 2006Reply With QuoteReport This Post
Member
posted Hide Post
Thank you for all the answers. I will explore all the options. I have a follow up question: Is there any SET command I can use in Web Focus so it sorts in ASCII instead of EBCDIC? or alternatively, how do I set up my work environment to sort in ASCII just for me?


WebFocus 7703
Windows, All Outputs
 
Posts: 11 | Registered: November 09, 2012Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I'm skeptical of the notion that the data is coming out in EBCDIC seeing as Petter's signature shows his environment as Windows.

The first question to ask is "Where is the data stored?" Is it on a DB2 database on a mainframe? If that is the case, then perhaps we do have an EBCDIC vs ASCII issue.

But if the data is stored on Unix or Windows, then how is it possible that we're getting an EBCDIC sort?

I'd recommend looking at the raw data (where ever it is) and do a native query. For example, if the data is on SQL Server, do a native SQL Server query and see what you get back.

The other place to look would be your WebFOCUS server language setting. It's unlikely, but perhaps there is some server language setting that is causing this sort. You can find out your setting by either going to the Server Console and look at your NLS configuration - OR - you can do a ? LANG from the command line. It should look something like this:

>>? lang
        NATIONAL LANGUAGE INFORMATION

Language                001/AMENGLISH   (   ,en)
Code Page               1252
Client Code Page        1252
Dollar                  24($)
Lowcase alphabet        YES
Decimal notation        OFF(.)
Currency symbol         $
Date/Time format        EDA
NLS sort                NO
NLS upcase/lowcase      NO
NLS Control Characters  OFF
DBCS Flag               OFF(SBCS)
  
 
Posts: 164 | Registered: March 26, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Wouldn't a hub-sub setup, with the sub being in EBCDIC encoding and the hub in DOS/Windows encoding, explain that?


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1650 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]Sorting fields

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.