• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • Last Modified:

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
0
gilalig
Asked:
gilalig
1 Solution
 
aflockhartCommented:
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.

0
 
mlmccCommented:
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
0
 
mlmccCommented:
If you have housenumbers that are not purely umeric then the formula can be modified to handle them

mlmcc
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jeffrey CoachmanMIS LiasonCommented:
gilalig,

What platform do you need this in please?

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

JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
gilalig,

Here it is in Access:

JeffCoachman

Access-EEQ23871009GroupByOddAndE.mdb
0
 
gilaligAuthor Commented:
I used a view in SQLServer so the Mod is not available but rather [house] % 2. Thanks for the example!
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now