Sql and Crystal Report

[lothos12345]

I wrote this for a visual basic.net application it is a sql query that a crystal report is using the query I wrote is as follows:

--------------------------------------------------------------------------
SELECT TOP 100 PERCENT dbo.tblPtProgReportMain.PtProgHeader,
dbo.tblPtProgReportMain.PtProgFooter,
dbo.tblPtProgReportMain.PtProgSalutation,
dbo.tblPtProgReportMain.PtProgSigBlock, dbo.tblPtProgReportMain.PatientName, dbo.tblPtProgReportMain.PatientSex,
dbo.tblPtProgReportMain.PatientProfileId, dbo.tblPtProgReportMain.PatientSSN, dbo.tblPtProgReportMain.PatientDOB,
dbo.tblPtProgReportMain.PatientDOI, dbo.tblPtProgReportMain.nextvisitdate, dbo.tblPtProgReportMain.TreatingDoc,
dbo.tblPtProgReportMain.TreatmentsNum, dbo.tblPtProgReportMain.TreatmentDates, dbo.tblPtProgReportMain.OtherRx,
dbo.tblPtProgReportMain.Medication, dbo.tblPtProgReportMain.PhysicianComment, dbo.tblPtProgReportMain.PriorHx, '' AS DiagDesc, dbo.tblPtProgReportDetail.NoteValue,
dbo.tblPtProgReportDetail.sortorder AS Detailsortorder,
case dbo.tblPtProgReportMain.initeval
when 0 then tblPTProgLists.Listtext
WHEN 1 then tblPTProgLists.Listtext2
WHEN 2 then tblPTProgLists.Listtext3
end AS GroupName,
tblPTProgLists.sortorder AS Groupsortorder,
dbo.tblPtProgReportMain.ChiefComplaint, dbo.tblPtProgReportMain.initeval AS SupressCC, dbo.tblPtProgReportMain.Diagnosis,
case dbo.tblPtProgReportMain.initeval
when 0 then 'Physical Therapy Progress Note'
when 1 then 'Physical Therapy Initial Evaluation'
when 2 then 'Physical Therapy Daily Note'
end AS LtrType, dbo.tblPtProgReportMain.printsig, ' ' AS docSigPic
FROM dbo.tblPTProgLists WITH (NOLOCK) INNER JOIN
dbo.tblPtProgReportDetail WITH (NOLOCK) ON dbo.tblPTProgLists.PtProgListid = dbo.tblPtProgReportDetail.PtProgGrpId Right outer JOIN
dbo.tblPTProgReportMain WITH (NOLOCK) ON dbo.tblPtProgReportDetail.PtProgReportId = dbo.tblPTProgReportMain.PtProgReportId
WHERE (dbo.tblPTProgReportMain.PtProgReportId = @rptid)
UNION ALL
SELECT '' as PtProgHeader, '' as PtProgFooter, '' as PtProgSalutation,
'' as PtProgSigBlock, '' as PatientName, '' as PatientSex,
'' as PatientProfileId, '' as PatientSSN, '' as PatientDOB,
'' as PatientDOI, '' as nextvisitdate, '' as TreatingDoc,
'' as TreatmentsNum, '' as TreatmentDates, '' as OtherRx,
'' as Medication, '' as PhysicianComment, '' as PriorHx, '' AS DiagDesc, '' as NoteValue,
'' as Detailsortorder,
'' as GroupName,
'' as Groupsortorder,
'' as ChiefComplaint, '' as SupressCC, '' as Diagnosis,
'' AS LtrType, '' as printsig, practicemanager.dbo.tblDocSigs.DocSigPic
FROM Practicemanager.dbo.tblDocSigs INNER JOIN
Practicemanager.dbo.PatientVisit ON Practicemanager.dbo.PatientVisit.DoctorId = Practicemanager.dbo.tblDocSigs.DoctorFacilityId INNER JOIN
dbo.tblPtProgReportMain ON Practicemanager.dbo.PatientVisit.PatientVisitId = dbo.tblPtProgReportMain.PVID
WHERE (dbo.tblPtProgReportMain.PtProgReportId = @rptid)
--------------------------------------------------------------------------

The crystal report is the groupname to group by, my problem is that the information that is static such PatientName does not display. It is blank in the report I browsed the data in the field in the Field Explorer and the information is there it is just on the second line. And my signature does not come out at all it is the item in the second query of the union. I am not sure what to do the only thing that does work right is the displaying of the details using the group by. Please help and as always any help given is greatly appreciated.

 

[djj55]

You might try the Microsoft SQL Server forum.
djj