Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Is there a way to trick CR to print blank lines Post Reply Post New Topic
<< Prev  Page  of 2
Author Message
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 14 Dec 2007 at 6:05am
As a simpler solution....

You have created a Calendar table in Oracle (this is the recommended solution for your problem, incidentally).  Now, make your query look like:

SELECT Calendar.Time, MyAppt.Time, MyAppt.Details
FROM Calendar
LEFT JOIN MyAppt
ON Calendar.Time = MyAppt.Time

The left join will show all the results from the Calendar table, plus any matching appointments.  (There is a tricky bit in getting the join condition between the tables set up right, that depends on how you set up the Calendar table.)

You can also do this in Crystal, using the link tab when setting up your datasource.  But, when at all possible, I recommend doing it in the source database.  It is more powerful, more flexible, and more efficient.

I presume you have a DBA for this database?  Talk to him/her, and get some assistance.  After all, it's in their best interests for you to get this done right, too.


IP IP Logged
cmpgeek
Newbie
Newbie
Avatar

Joined: 11 Dec 2007
Online Status: Offline
Posts: 39
Quote cmpgeek Replybullet Posted: 14 Dec 2007 at 9:52am
I am the one who maintains the database tables, but we have an IS Analyst that helps with interfaces and other "background" stuff that I imagine includes some database things as well.
 
I did not actually create a new table within the database, but used one that was not being used so i dont know if that counts as creating it or not.
 
I did try and do a left outer join, but it would not work.  I dont know if it is because my information is not contained within two tables, but six.  (The relavent date is one, the time in another, all the patient demographics is in yet another.  The surgeon ID # is in that table, but to actually attach his/her name, i have to bring in another table, etc.)    No matter how I linked the tables together, I could not get them to give me the result I wanted.
 
I dont mean to annoy or ask dumb questions, but why is doing it with the left outer join the way you both suggested (which I would have been happy to do had I gotten it to work) preferable to using the subreport?  I would just like to understand so that the knowledge will be there if and when I need it at a later date.
 
Thank you for your patience.
Nomi   
CR 10    
Oracle 9i
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 14 Dec 2007 at 11:52am
The appointment date is in one table, and the time is in another?  That's, well, goofy.  Given that Oracle doesn't store date or time values, but datetime values, what is the date on those time values?

OK, if you haven't created the Calendar table yet, then this is a good time to do so.  Actually, just make it a Numbers table, as that's more flexible.  For your purposes, make it a two-column table.  The first column contains the integers 0 through, say, 400.  The second column is equal to the first column times 15.

In order to do your join, it should look like:

PROCEDURE ApptProc (startdate IN DATETIME, enddate IN DATETIME)

IS
BEGIN

SELECT
DATEADD("mi", Numbers.ID15, TRUNC(startdate)) AS ApptDate,
ApptDetails.*
FROM Numbers
LEFT JOIN ({{insert your existing SQL here}}) ApptDetails
ON Numbers.ID15 = DATEDIFF("mi",TRUNC(ApptDetails.ApptTime),ApptDetails.ApptTime)

END


Well, you'll actually need to add a WHERE clause to only show the times you want.  But, that should give you the starting point.

The reason to use the left join is twofold.  One, it is significantly more efficient, from a processing point of view.  You are only processing the query once, and you are processing everything on the server.  Two, it is significantly easier from a management point of view down the road.  Even if the SQL above looks complicated to you, it's a lot easier to come back and modify a year from now than a jury-rigged collection of subreports will be.  Take it from someone who's been there.


IP IP Logged
cmpgeek
Newbie
Newbie
Avatar

Joined: 11 Dec 2007
Online Status: Offline
Posts: 39
Quote cmpgeek Replybullet Posted: 14 Dec 2007 at 12:26pm
Originally posted by Lugh

The appointment date is in one table, and the time is in another?  That's, well, goofy.  Given that Oracle doesn't store date or time values, but datetime values, what is the date on those time values?
 
Actually, the Date field is a datetime field but the start time is in a string field.  I dont know why they did it that way, but that is how it has been since we bought the system.
 
I have been online looking for Oracle training books so hopefully I can get more comfortable with the idea of creating tables from scratch.  (If this was something I was playing with just to learn I wouldnt care, but since this is the backbone of our entire system, I am rather nervous about potentially screwing up something.
 
I was looking at the "boot camp" training as well, but I know our company wont pay for something like that right now and I definitely can't afford to pay it out of my pocket...    I have taught myself some basic VB coding through training books so hopefully I will be able to do the same with DBA books.
 
Thanks again for your help & suggestions.  They are very much appreciated.
Nomi   
CR 10    
Oracle 9i
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 17 Dec 2007 at 4:33am
One quick note about "boot camp" training.  Nine times out of ten, those classes won't actually teach you much.  They are really designed to take already experienced (especially self-taught) programmers and/or admins, fill in any gaps in their knowledge, and prep them for a certification exam.  They are so quick, because they really zip through a lot of the material, without going in depth.


IP IP Logged
<< Prev  Page  of 2
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum