Solved

Group Header on Report: odd and even house numbers

Posted on 2008-11-03
7
489 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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…
Viewers will learn how the fundamental information of how to create a table.

896 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

18 Experts available now in Live!

Get 1:1 Help Now