Print Page | Close Window

Sort order - blanks last

Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Writing Code
Forum Discription: .NET programming API, report integration
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=1009
Printed Date: 02 May 2025 at 12:15pm


Topic: Sort order - blanks last
Posted By: FreeriderUK
Subject: Sort order - blanks last
Date Posted: 16 Jul 2007 at 2:30am
Hi,
I have a customer report I want to generate. I have a field for each customer called "sequence" in which I put a number (although some customers will have this field blank)

Using the SQL ORDER BY statement, how can I get the report to sort by "sequence" first, then have those without this value afterwards?

When using "ORDER BY Sequence, Address" I get:

AAA -
BBB -
CCC -
FFF - 1
EEE - 2

But how do I get:

FFF - 1
EEE - 2
AAA -
BBB -
CCC -


Replies:
Posted By: hilfy
Date Posted: 16 Jul 2007 at 11:42am
Create a formula that looks something like this:
 
If IsNull({table.Sequence}) then 99999 else {table.Sequence})
 
For '99999' use a value larger than your sequence number could ever possibly be. 
 
Sort on this formula instead of on the field, but use the field itself in the report.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Print Page | Close Window