Printing information from many tables

[EBECK]

I work in a homeless shelter and have developed a VB.NET database (SQL Server database) application. The application is a Guest Registration system, collecting all kinds of information on the homeless people (called ‘guests’) who stay here. Additionally, the application has several Untyped Crystal Reports. Crystal Reports is fairly new to me and I’ve not been able to get one of them to work correctly (an EMT Information report – a report given to EMT personnel when called to our facility). Here’s the background.

There are several tables in the SQL Server database related to this report:
tblAddress
AddressID int Primary key
GuestMID int
Address varchar(30)
City varchar(20)
StateID varchar(2)
CountryID int
ZipCode varchar(10)


tblCountry
CountryID int Primary key
Country varchar(40)


tblDiagnosedCondition
DiagnosedConditionID int Primary key
DiagnosedCondition varchar(40)


tblGuestM
GuestMID int Primary key
LastName varchar(30)
FirstName varchar(20)
MiddleInitial char(1)
(more fields)

tblHealthHistory
HealthHistoryID int Primary key
GuestMID int
DiagnosedConditionID int
DateDiagnosed Date
Comment varchar (80)


tblMedication
MedicationID int
Medication varchar(60)


tblMedicationHistory
MedicationHistoryID int Primary key
GuestMID int
HealthHistoryID int Associate this med w/a health history record
MedicationID int
Dosage varchar(20)
TimesPerDay int
StillTakingMedicationID int
Comment varchar(40)


tblState
StateID char(2) Primary key
StateName varchar(20)


tblYesNo
YesNoID int Primary key
YesNo varchar(3)


The EMT Information Crystal Report has four Stored Procedures associated with it as follows:
CREATE PROCEDURE dbo.spInformationEMTReportAddress
(
@Original_GuestMID int
)
AS
SET NOCOUNT ON;
SELECT A.AddressID, A.GuestMID, A.Address, A.City, S.StateName, C.Country, A.ZipCode FROM (tblAddress A LEFT OUTER JOIN tblState S ON A.StateID=S.StateID) LEFT OUTER JOIN tblCountry C ON A.CountryID=C.CountryID WHERE A.GuestMID=@Original_GuestMID ORDER BY AddressID DESC
GO


CREATE PROCEDURE dbo.spInformationEMTReportGuestM
(
@Original_GuestMID int
)
AS
SET NOCOUNT ON;
SELECT G.GuestMID, G.LastName, G.FirstName, G.MiddleInitial, G.AliasLastName, G.AliasFirstName, G.AliasMiddleInitial, G.SSNumber, G.BirthDate, G.Height, G.Weight, E.EyeColor, H.HairColor, G.EmergencyName, R.Relation, G.EmergencyAddress, G.EmergencyCity, G.EmergencyStateID, C.Country, G.EmergencyZipCode, G.EmergencyPhone FROM (((tblGuestM G INNER JOIN tblEyeColor E ON G.EyeColorID=E.EyeColorID) INNER JOIN tblHairColor H ON G.HairColorID=H.HairColorID) INNER JOIN tblRelation R ON G.RelationID=R.RelationID) LEFT OUTER JOIN tblCountry C ON G.EmergencyCountryID=C.CountryID WHERE G.GuestMID=@Original_GuestMID
GO


CREATE PROCEDURE dbo.spInformationEMTReportHealthHistory
(
@Original_GuestMID int
)
AS
SET NOCOUNT ON;
SELECT HH.HealthHistoryID, HH.GuestMID, DC.DiagnosedCondition, HH.DateDiagnosed, HH.Comment FROM tblHealthHistory HH LEFT OUTER JOIN tblDiagnosedCondition DC ON HH.DiagnosedConditionID=DC.DiagnosedConditionID WHERE HH.GuestMID=@Original_GuestMID
GO


CREATE PROCEDURE dbo.spInformationEMTReportMedicationHistory
(
@Original_GuestMID int
)
AS
SET NOCOUNT ON;
SELECT MH.HealthHistoryID, M.Medication, MH.Dosage, MH.TimesPerDay, YN.YesNo FROM (tblMedicationHistory MH LEFT OUTER JOIN tblMedication M ON MH.MedicationID=M.MedicationID) INNER JOIN tblYesNo YN ON MH.StillTakingMedicationID=YN.YesNoID WHERE MH.GuestMID=@Original_GuestMID
GO

The EMT Information report prints the guest’s
Personal information (name, height, weight, emergency contact, etc.)
Address information (most recent address)
Health and Medication information

In the Health and Medication information area of the report, if a Health History record doesn’t have an associated Medication History record (if the guest has a diagnosed condition, but isn’t taking any medication for it) that Health History record doesn’t print. For example
Arthritis (Diagnosed Condition – this line prints)
Abacavir (Medication – this line prints)
Abarelix (Medication – this line prints)
Heart Disease Diagnosed Condition – no medication, this line doesn’t print)
High Blood Pressure (Diagnosed Condition – this line prints)
Accutane (Medication – this line prints)

Additionally, if the Address record is missing, no information is printed on the report. There must be a better way to get this information on the report, perhaps even structuring it differently, but I’m stuck.

Please let me know what you think. Thank you in advance for your time and consideration.

Ed

 

[synapsevampire]

Some additional information would be useful, such as your Crystal version, and how these SPs are being called from the report, such as by subreports, or?

For optimal performance, one would have a single recordset returned by the database, as it stands you are probably using a main report and 3 subreports.

I would change this to a main report which pulls everyone, and 4 subreports to not have a failure of nothing to link to the subreports.

Of course I'm just guessing as you haven't exxplained the report layout, but you did an excellent job on the data acquisition.

-k

 

[EBECK]

Thank you, synapsevampire, for your feedback!

I'm using Visual Studio .NET 2003, which I believe uses Crystal Reports 8.5. As I think I mentioned, I'm fairly new to CR. I believe the Stored Procedures are called from the main report -- I don't believe I have any subreports.

Maybe I should have subreports or maybe, as you suggested, I combine the Stored Procedures to return one recordset. Do you have a suggestion on what that Stored Procedure would look like?

Ed

 

[synapsevampire]

Right, Crystal only allows ONE datasource for each report, so adding in multiples will only get weird results (though I've seen people cheat this, but...).

Combining the SP would create a large recordset, but entirely doable, however it would take a bit longer than I have to offer up here.

Basically it would be something like:

CREATE PROCEDURE dbo.All
(
@Original_GuestMID int
)
AS
-- create a temp table
create table #blah as
(
field list for all returned fields
)
SET NOCOUNT ON;
insert into blah
SELECT A.AddressID, A.GuestMID, A.Address, A.City, S.StateName, C.Country, A.ZipCode intop #blah
FROM (tblAddress A LEFT OUTER JOIN tblState S ON A.StateID=S.StateID) LEFT OUTER JOIN tblCountry C ON A.CountryID=C.CountryID WHERE A.GuestMID=@Original_GuestMID ORDER BY AddressID DESC

Then you'd use update statements for the other SPs to add in the additional data for each field they return.

Subreports will work as well, but it will be slower. If your sql isn't good, go the subreport route, but this sql isn't difficult, just time consuming.

-k

 

[Turkbear]

Hi,
Synapse, can you explain what you meant by:
Right, Crystal only allows ONE datasource for each report

Crystal can use multiple datasources ( especially those of the same type ( all Oracle native, etc))..There are some caveats, but it does work fine..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

Turk: I meant combinatively, I should have stuck with the recordset term I used later.

-k