Displaying Broken Ranges

[driaspoc]

I'm doing a vehicle report. The report groups by model id. Each model has a bunch of years. It ends up looking like this:
Toyota Corolla 1992
1993
1994
2000
2005
2006

Acura Integra 1999

I want to be able to list the Toyota like this:
Toyota Corolla 1992-1994,2000,2005-2006

This is just an example. The year range could possibly have no breaks or it could possible have more.
Any ideas on how to do this? I'm stuck on this one.

 

[lbass]

First group on {table.model} and sort by year. Then place the following formula in the detail section:

whileprintingrecords;
stringvar x;

if onlastrecord and
{table.model} <> previous({table.model}) then
x := x + totext({table.year},0,"") + "," else

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

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

x := x + totext({table.year},0,"") + "," ;

Place the following reset formula in the group header:
whileprintingrecords;
stringvar x := "";

Place the following display formula in the group footer:
whileprintingrecords;
stringvar x;
left(x,len(x)-1);

Drag the groupname into the group footer and then suppress the group header and details section.

-LB

 

[synapsevampire]

Might simplify that a bit to:

Group Header formula:
whileprintingrecords;
stringvar x := "";

Details formula:
whileprintingrecords;
stringvar x := x & totext({table.year},0,"") & ","

Group Footer formula:
whileprintingrecords;
stringvar x;
left(x,len(x)-1)

If the years repeat, create an additional group by year within the model grouping and move the details formula to the group footer of the year.

Suppress all sections except the model group footer.

-k

 

[driaspoc]

Thanks for the help. Unless I misread the posts the first reply comes closest to solving my problem. However, for a range of years such as 1992-2002, 2005-2006 it ends up as: 2002,2005. For a range such as 1993-1999 it seems to work right. This was definetely the help I needed though. Thanks.

 

[lbass]

I would be glad to troubleshoot this for you, but I would need to see a sample of your detail level data, along with what the formula is displaying. Also, are you sure you created the formula exactly as posted? While I tested it, I didn't necessarily test all variations of data.

-LB

 

[driaspoc]

Here's what seems to work on the details formula:

whileprintingrecords;
stringvar x;
if(OnFirstRecord) then x := totext({TABLE.YEAR}) + "-" else

if onlastrecord then
x := x + totext({TABLE.YEAR},0,"") + "," else

if {TABLE.YEAR} = next({TABLE.YEAR})-1 and
(
{TABLE.MODEL} <> previous({TABLE.MODEL}) or
{TABLE.YEAR} <> previous({TABLE.YEAR})+1
) then
x := x + totext({TABLE.YEAR},0,"") + "-" else

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

x := x + totext({TABLE.YEAR},0,"") + "," ;

As you can see, all I did was add the "on first record" line and also got rid of the model comparison in the "on last record" line. It seems to work right, but the data is so varied that I'll need to test it some more to be sure. I really appreciate your help.

 

[lbass]

Please try the following. I don't think your first line would work correctly in the case where the first record is not part of a range.

whileprintingrecords;
stringvar x ;

if onlastrecord then
x := x + totext({@year},0,"") + "," else

if {@year} = next({@year})-1 and
(onfirstrecord or
{Orders.Ship Via} <> previous({Orders.Ship Via}) or
{@year} <> previous({@year})+1
) then
x := x + totext({@year},0,"") + "-" else

if {@year} = next({@year})-1 and
{@year} = previous({@year})+1 then
x := x else

x := x + totext({@year},0,"") + "," ;

-LB

 

[lbass]

Oops, posted my test formula without adapting it. Try this:

whileprintingrecords;
stringvar x ;

if onlastrecord then
x := x + totext({table.year},0,"") + "," else

if {table.year} = next({table.year})-1 and
(onfirstrecord or
{table.model} <> previous({table.model}) or
{table.year} <> previous({table.year})+1
) then
x := x + totext({table.year},0,"") + "-" else

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

x := x + totext({table.year},0,"") + "," ;

-LB

 

[driaspoc]

Yep, you're right. My post took a year range like 1998, 2000-2005 and made it look like 1998-2000-2005. Thanks for the correction. It seems to work.