Crystal Reports getting number ranges with start and end numbers

Report Design

Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: getting number ranges with start and end numbers

Author	Message
PeterH Newbie Joined: 13 Dec 2012 Location: United Kingdom Online Status: Offline Posts: 5	Topic: getting number ranges with start and end numbers Posted: 29 Oct 2014 at 6:03am
	Hi I have a database with thousands of serial numbers which are not necessarily continuous as they are withdrawn for use e.g. 1-50, then 55-456, then 769-999 What I'm trying to do is produce a report showing, line by line the serial numbers in the various ranges. So, using the data above as an example I need the result to look like this Row No Start Serial End Serial 1 1 50 2 55 456 3 769 999 etc....... Has anyone any ideas on the best approach? Thanks Peter
	IP Logged


DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 29 Oct 2014 at 6:13am
	what is your source data? seems like you can just use split on the '-' but I am not sure what your true starting point is split (field,'-')[1] split (field,'-')[2]
	IP Logged

praveeng Senior Member Joined: 11 Jul 2011 Online Status: Offline Posts: 165	Posted: 29 Oct 2014 at 6:15am
	Hi Peter, If you have Start Serial and End Serial as separate database fields then place those filelds in details section and sort on Start serial field. IF your data is like 1-50 55-456 769-999 Then split the data as below //Start Serial formula Split(database_field,'-')[1] //End Serial formula Split(Database_filed,'-')[2] Then place these fields in details section and sort on Start field. --Praveen G Edited by praveeng - 29 Oct 2014 at 6:15am
	Praveen Guntuka, praveen_guntuka@yahoo.com
	IP Logged

PeterH Newbie Joined: 13 Dec 2012 Location: United Kingdom Online Status: Offline Posts: 5	Posted: 29 Oct 2014 at 6:23am
	The source table is a serial number table containing many thousands of records in serial number sequence. As these serial numbers get used (not necessarily in sequence) they are allocated a code (dozens of different codes depending on type of use) to show that they are in use, leaving the remaining records which have not yet been issued. The trouble is the unused serial numbers won't always be in sequence so there could be unused (serial) numbers 1-50 in sequence, then a range of numbers that have been used, followed again by another range of unused numbers. It's the blocks of unused numbers that I need to identify by getting the first number of the block and the last number of the block and presenting those two numbers in one row on the report etc. Hope that helps to clarify Thanks Peter
	IP Logged

PeterH Newbie Joined: 13 Dec 2012 Location: United Kingdom Online Status: Offline Posts: 5	Posted: 29 Oct 2014 at 6:26am
	Praveen Thanks for your reply Unfortunately the serial numbers are only in one field (see my other reply), so I need to find a way of identifying the start and end numbers of blocks of numbers that haven't been used. The only thing I can think of that may help is that all of the unused records have a control key field of either null or zero Peter
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 29 Oct 2014 at 6:34am
	do not filter on the table order by the asc number use a suppression if next(field)=field+1 should leave you with only the rows where a gap exists two display formulas should give your gap begin and end //start field+1 //end next(field)-1 EDIT: looks like i inverted what you want to see but it gives you an idea of a process Edited by DBlank - 29 Oct 2014 at 6:35am
	IP Logged

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum