display non sequential ids

[biformulas]

I am new to Crystal Reports 2011. I have one group called ORDERID. Within that group are varying non sequential ids.

ORDERID ID
1 1
1 2
1 4
1 5

I am looking to add a formula at the Group Level that would display the following:

1-2, 4-5

Obvioulsy, the data can vary in the dataset. I am not sure how to determine if the records are sequential, then show 1-2, skip, then 4-5 etc. It could also look like this:

ORDERID ID
1 5
1 6
1 10
1 11

Desired Result:
5-6, 10-11

Any help on this would be appricated. I have VBA background for this type of programming but trying to figure out how CR 2011 would work the issue.

Thanks

 

[RTag]

This is an "islands and gaps" scenario. You can see a SQLServer example how to handle it here :

http://www.sqlservercentral.com/art...icientsetbasedsolutionforislandsandgaps/1619/

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[biformulas]

Thanks for the post. I do not have access to the underlying database and will have to program it in Crystal. Any thoughts on that? There has to be a way within crystal to do this.

 

[biformulas]

would i use an array of some kind?

 

[lbass]

If you don't have repeating IDs within the Order ID group then the following should work. Insert a group on Order ID, sort ascending on ID, and then create these formulas:

//{@reset} for the group header:
whileprintingrecords;
stringvar x;
if not inrepeatedgroupheader then
x := "";

//{@accum} for the detail section:
whileprintingrecords;
stringvar x;

if onlastrecord and
{table.OrderID} <> previous({table.OrderID}) then
x := x + totext({table.ID},"00") + "," else

if {table.ID} = next({table.ID})-1 and
(
{table.OrderID} <> previous({table.OrderID}) or
{table.ID} <> previous({table.ID})+1
) then
x := x + totext({table.ID},"00") + "-" else

if {table.ID} = next({table.ID})-1 and
{table.ID} = previous({table.ID})+1 then
x := x else

x := x + totext({table.ID},"00") + "," ;

//{@display} for the group footer section:
whileprintingrecords;
stringvar x;
left(x,len(x)-1);

-LB

 

[biformulas]

LB,
I must comment on your body of work throughout the forums, you are a brilliant!
The data some times has all sequential IDS mixed in the group:
ORDERID ID
1 5
1 6
1 10
1 11
2 1
2 2
2 3
2 4
Would it be possible to have the display formula return a min - max when that occurs?
Order ID 1
5-6, 10-11
Order ID 2
1-4

Please let me know if that is possible. Again, your solution is nothing short of brilliant!