Solved

Group Header on Report: odd and even house numbers

Posted on 2008-11-03
7
487 Views
Last Modified: 2013-11-28
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
Comment
Question by:gilalig
7 Comments
 
LVL 17

Expert Comment

by:aflockhart
ID: 22868083
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 22869570
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 22869573
If you have housenumbers that are not purely umeric then the formula can be modified to handle them

mlmcc
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22871644
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 22881498
gilalig,

Here it is in Access:

JeffCoachman

Access-EEQ23871009GroupByOddAndE.mdb
0
 

Author Closing Comment

by:gilalig
ID: 31512725
I used a view in SQLServer so the Mod is not available but rather [house] % 2. Thanks for the example!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22900172
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now