| Author |
Message |
premnathk.chn
Newbie
Joined: 17 Jan 2008
Online Status: Offline
Posts: 19
|
   Topic: Multiple grouping in a report
Posted: 28 Jan 2008 at 7:58pm |
|
Hi,
I need to develop a report with the following requirement.
I have to display all the cities and the list of hospitals ,colleges ,hotels etc.This has to grouped by the cities and then by respective categories.
City1
hospital1
hospital2
hospital3
College1
college2
college3
hotel1
hotel2
hotel3
City2
.
.
.
Thanks ,
prem
Edited by premnathk.chn - 28 Jan 2008 at 7:58pm
|
 IP Logged |
|
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 28 Jan 2008 at 9:44pm |
|
Use subreports. Create a subreport for each group (one for hospitals, one for colleges, etc.)
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
|
IP Logged |
|
premnathk.chn
Newbie
Joined: 17 Jan 2008
Online Status: Offline
Posts: 19
|
Posted: 28 Jan 2008 at 10:48pm |
|
Thanks brain
I thought of that but there is a problem in it. i have to include this report as a subreport to my main report.
Any thoughts on this?
cheers,
prem
|
|
IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 28 Jan 2008 at 10:57pm |
You can't do it then. Sorry. The one way you could do it, but you need to know SQL is to create a Command object and write a custom SQL query. In the query use a UNION statement to join the different tables together into a single table. I discuss UNION in detail in my book, but the basic idea is to create SELECT statements that retrieve the data you need from each table. But each SELECT statement must return the same number of fields and using the same data types. Then put a UNION between them to make them all appear as a single table.
SELECT City, HospitalName as Col1 FROM table
UNION
SELECT City, CollegeName as Col1 FROM table That should give you the idea. You can find out a lot more about optimizing SQL in chapter 11 of my book Crystal Reports Encyclopedia.
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
|
IP Logged |
|
premnathk.chn
Newbie
Joined: 17 Jan 2008
Online Status: Offline
Posts: 19
|
Posted: 28 Jan 2008 at 11:05pm |
|
Brain,
The number of columns for each section would be different in my case.So is there is any other way?
Cheers,
Prem
Edited by premnathk.chn - 28 Jan 2008 at 11:25pm
|
|
IP Logged |
|
Savan
Senior Member
Joined: 14 Dec 2007
Location: India
Online Status: Offline
Posts: 162
|
Posted: 28 Jan 2008 at 11:51pm |
Hi Prem
do u have any field which identify wheter the record is college, hospital or hotel. if yes then u can do a group by on the city field and record type field i.e (college, hospital, hotel). if u dont have a field to identify, please how the data is stored, i can let u know some other way.
|
|
Thanks
Savan
|
|
IP Logged |
|
premnathk.chn
Newbie
Joined: 17 Jan 2008
Online Status: Offline
Posts: 19
|
Posted: 29 Jan 2008 at 12:00am |
|
Thanks Savan
No i dont have any such identifier.
The Database structure looks like this.
For City:
CityId
CityName
CityCode
StateId(FK)
For College:
CollegeId
CityId(fk)
CollegeName
NoOfStaff
NoOfDept
For Hospital:
HospitalId
CityId(fk)
HospitalName
Nearest Landmark
specialization
...
the report is pulled for a particular State.
Thanks for your efforts
prem
Edited by premnathk.chn - 29 Jan 2008 at 12:01am
|
|
IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 29 Jan 2008 at 9:21am |
|
For tables which don't have any data, just fill it with an empty string. For example, if you need three fields but the table on has two:
SELECT CityName, HospitalName as Field1, "" as Field2 FROM table
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
|
IP Logged |
|
Savan
Senior Member
Joined: 14 Dec 2007
Location: India
Online Status: Offline
Posts: 162
|
Posted: 29 Jan 2008 at 10:00am |
i think there is one more way to do by using subreport.
in main report take the table city and group it on city id. take to subreports for college and hospital. link the college subreport and hospital subreport with city id. in the main report. place the cityname field in group footer and put the college subreport below it. after that put the hospital subreport. so in this way u will get the city name and all the colleges of that city and all the hospitals and then comes the next city.
i have generated this report with dummy data. please check and let me know if this will solve ur problem.
|
|
Thanks
Savan
|
|
IP Logged |
|
premnathk.chn
Newbie
Joined: 17 Jan 2008
Online Status: Offline
Posts: 19
|
Posted: 29 Jan 2008 at 7:39pm |
thanks guys
savan - as i have already told this reports is pulled against a particular state so the main report will have information other than city for eg
main report Layout:
1.Introduction
2.Awards
3.Popular Cities
4.Cutural Heritage & People
.
.
.
So now Popular Cities is the one creating problems for me since its has multiple grouping in it like colleges,hospitals etc.So now if i put a group by cityId in the main report will it give me the correct output? Any way i ll check it and let you know.
Now i have a new problem there is another section in the main report which has multiple grouping like the Popular cities 
Thanks,
prem
Edited by premnathk.chn - 29 Jan 2008 at 7:41pm
|
|
IP Logged |
|
|
|
