asked on

Group Header on Report: odd and even house numbers

Hello,

I have a report of streets in a city. I have a group header for each street. The group header needs another group heading which will organize the street with odd house numbers first and after the odd numbers I need a new grouping for even house numbers.

How can this be done? The datasource is an sql statement on a table in sql server.

Thanks

aflockhart

Are the numbers all genuinely numeric ?

If so, you can calculate a 'parity' value for each one, and group your report using that parity value: ( odd numbers have Parity=1, evens have parity =0)

select housenumber , housenumber- floor(housenumber/2)*2 AS Parity from addresses

But having done similar things in the past, there are probably other complications waiting to get you ... houses like 10B or 12-3 ... no easy way to deal with these.

Mike McCracken

You can also do it in a Crystal formula

If {HouseNumberField} mod 2 = 1 then
'ODD'
else
'EVEN'

YOu can then group the report on the formula

mlmcc

Mike McCracken

If you have housenumbers that are not purely umeric then the formula can be modified to handle them

mlmcc

Jeffrey Coachman

gilalig,

What platform do you need this in please?

You have listed both the Access Reports and Crystal Reports, zones to this question.

JeffCoachman

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gilalig

ASKER

I used a view in SQLServer so the Mod is not available but rather [house] % 2. Thanks for the example!

Jeffrey Coachman

gilalig,

OK,

If this was an Access question then can I ask why you added the Crystal Reports Zone to the question?

I believe that the first two Experts presumed that this was specifically a Crystal Reports Question.

JeffCoachman