Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: How to find ranges of numbers Post Reply Post New Topic
Author Message
smarshall
Newbie
Newbie


Joined: 06 Aug 2012
Online Status: Offline
Posts: 1
Quote smarshall Replybullet Topic: How to find ranges of numbers
     Posted: 06 Aug 2012 at 1:48pm
I have a report that needs to show ranges of tickets the grouping is by User, then date of service. what I need to show is the ranges of tickets turned in by each user every day. The report is complete except for the ability to display/fomat the ranges.
Method1:
I have tried creating two formula fields (columnA (TicketRangeMinimum) and columnB (Ticket Range Maximum). the issue with this is not being able to dispaly the TicketRangeMinimum to TicketRangeMaximum on the same detail line. they always alternate.

Method2:

I tried concatenating the formulas from method1. I then checked the box to 'Format with Multiple Columns'. with this method I got close to what I wanted but was able to only display two columns if I stated the detail was 3". the gap is too big when displayed on the report. If I shorten the detail the numbers look better (in terms of distance) but I then get more than two columns which screws up the range columns.

Any ideas how I can display this correctly?
 
here are my Formula fields:
 
//TicketRangeMinimum
if previous ({@created tickets}) <> {@created tickets}-1 then tonumber ({@created tickets})
else
if previous ({@created tickets}) = {@created tickets}-1 and {@DateRcvd-DateOnly} <> previous ({@DateRcvd-DateOnly}) then tonumber ({@created tickets})
else
if PreviousIsNull ({@created tickets}) then tonumber ({@created tickets})
else
0
 
//TicketRangeMaximum
if next ({@created tickets}) <> {@created tickets}+1 then tonumber({@created tickets})
else
if next ({@created tickets}) = {@created tickets}+1 and {@DateRcvd-DateOnly} <> next ({@DateRcvd-DateOnly}) then tonumber ({@created tickets})
else
if NextIsNull ({@created tickets}) then tonumber ({@created tickets})
else
0
 
 
 
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 07 Aug 2012 at 4:39am
well, my first thought would be to group by user then by date. In the date group footer I would use the aggregate function MIN and MAX and just format them...
if you want to do it all in a formula it would look something like:
Minimum({table.field}, {table.datefield}) + " - " + Maximum({table.field}, {table.datefield})
 
you might need to convert to string using toText for each max/min if they are numbers.
 
HTH
IP IP Logged
Printable version 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