Control Type Select Multiple Facilities & won't pull multiple is I select more than one

[bpxmas]

Crystal v10

I have a report & I need to be able to select multiple facilities.
I created a Control Type 'Select Facility' Name 'Facility' and checked 'Allow Multiple Selection' as well as 'Enabled'

When I chose multiple facilities it says 'No data for report'. If I select one facility at a time there is data. Below is my code, what do I need to do to be able to select multiple facilities and see all the data? If anyone can help me that would be great.


SET NOCOUNT ON
DECLARE @Aging TABLE( PatientVisitId int NULL,
InsDeposit money NULL,
PatDeposit money NULL,
InsBalance0 money NULL,
PatBalance0 money NULL,
InsBalance30 money NULL,
PatBalance30 money NULL,
InsBalance60 money NULL,
PatBalance60 money NULL,
InsBalance90 money NULL,
PatBalance90 money NULL,
InsBalance120 money NULL,
PatBalance120 money NULL,
InsBalance money NULL,
PatBalance money NULL
)

DECLARE @DB varchar(128), @EndDate datetime, @AgingDateMode int

SELECT @DB = DB_Name(), @AgingDateMode = CASE WHEN ?DATETYPE.VALUE? = '1' THEN 2 ELSE 1 END

IF ?RESPPROV.ITEMDATA? is NULL
INSERT INTO @Aging exec mbcxp_AgingVisit @DB, ?ENDDATE.DATE?, @AgingDateMode, 0, ?RESPPROV.ITEMDATA.U?, ?FACILITY.ITEMDATA.U?, ?COMPANY.ITEMDATA.U?

ELSE
DECLARE @resprovid int
DECLARE resprov_cursor CURSOR FOR
SELECT DoctorFacilityId from DoctorFacility where DoctorFacilityId in ( ?RESPPROV.ITEMDATA.U? )
OPEN resprov_cursor
FETCH NEXT FROM resprov_cursor
INTO @resprovid
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @Aging exec mbcxp_AgingVisit @DB, ?ENDDATE.DATE?, @AgingDateMode, 0, @resprovid, ?FACILITY.ITEMDATA.U?, ?COMPANY.ITEMDATA.U?
FETCH NEXT FROM resprov_cursor into @resprovid
END
close resprov_cursor
DEALLOCATE resprov_cursor


IF ?AGE.ITEMDATA.U? = 1
DELETE @Aging
FROM @Aging A
INNER JOIN Patientvisit pv1 ON pv1.PatientvisitId= A.PatientvisitId
where ISNULL(pv1.DoctorId,0) IN
(SELECT ISNULL(pv.DoctorId,0) DoctorId
FROM @Aging ta INNER JOIN PatientVisit pv ON ta.PatientVisitId = pv.PatientVisitId
INNER JOIN DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId
Group by PV.DoctorId
having Sum(TA.InsDeposit) =0 and Sum(TA.PatDeposit) =0)

ELSE IF ?AGE.ITEMDATA.U? = 2
DELETE @Aging
FROM @Aging A
INNER JOIN Patientvisit pv1 ON pv1.PatientvisitId= A.PatientvisitId
where ISNULL(pv1.DoctorId,0) IN
(SELECT ISNULL(pv.DoctorId,0) DoctorId
FROM @Aging ta INNER JOIN PatientVisit pv ON ta.PatientVisitId = pv.PatientVisitId
INNER JOIN DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId
Group by PV.DoctorId
having Sum(TA.InsBalance0) =0 and Sum(TA.PatBalance0) =0)

ELSE IF ?AGE.ITEMDATA.U? = 3
DELETE @Aging
FROM @Aging A
INNER JOIN Patientvisit pv1 ON pv1.PatientvisitId= A.PatientvisitId
where ISNULL(pv1.DoctorId,0) IN
(SELECT ISNULL(pv.DoctorId,0) DoctorId
FROM @Aging ta INNER JOIN PatientVisit pv ON ta.PatientVisitId = pv.PatientVisitId
INNER JOIN DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId
Group by PV.DoctorId
having Sum(TA.InsBalance30) =0 and Sum(TA.PatBalance30) =0)

ELSE IF ?AGE.ITEMDATA.U? = 4
DELETE @Aging
FROM @Aging A
INNER JOIN Patientvisit pv1 ON pv1.PatientvisitId= A.PatientvisitId
where ISNULL(pv1.DoctorId,0) IN
(SELECT ISNULL(pv.DoctorId,0) DoctorId
FROM @Aging ta INNER JOIN PatientVisit pv ON ta.PatientVisitId = pv.PatientVisitId
INNER JOIN DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId
Group by PV.DoctorId
having Sum(TA.InsBalance60) =0 and Sum(TA.PatBalance60) =0)

ELSE IF ?AGE.ITEMDATA.U? = 5
DELETE @Aging
FROM @Aging A
INNER JOIN Patientvisit pv1 ON pv1.PatientvisitId= A.PatientvisitId
where ISNULL(pv1.DoctorId,0) IN
(SELECT ISNULL(pv.DoctorId,0) DoctorId
FROM @Aging ta INNER JOIN PatientVisit pv ON ta.PatientVisitId = pv.PatientVisitId
INNER JOIN DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId
Group by PV.DoctorId
having Sum(TA.InsBalance90) =0 and Sum(TA.PatBalance90) =0)

ELSE IF ?AGE.ITEMDATA.U? = 6
DELETE @Aging
FROM @Aging A
INNER JOIN Patientvisit pv1 ON pv1.PatientvisitId= A.PatientvisitId
where ISNULL(pv1.DoctorId,0) IN
(SELECT ISNULL(pv.DoctorId,0) DoctorId
FROM @Aging ta INNER JOIN PatientVisit pv ON ta.PatientVisitId = pv.PatientVisitId
INNER JOIN DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId
Group by PV.DoctorId
having Sum(TA.InsBalance120) =0 and Sum(TA.PatBalance120) =0)


SELECT pv.DoctorId AS Id,
ISNULL(d.ListName, 'Unknown') AS Description,
pv.DoctorId, d.ListName AS DoctorName,
pv.FacilityId, f.ListName AS FacilityName,
pv.CompanyId, c.ListName AS CompanyName,
-ta.InsDeposit AS InsDeposit, -ta.PatDeposit AS PatDeposit, ta.InsBalance0, ta.PatBalance0,
ta.InsBalance30, ta.PatBalance30, ta.InsBalance60, ta.PatBalance60,
ta.InsBalance90, ta.PatBalance90, ta.InsBalance120, ta.PatBalance120,
ta.InsBalance, ta.PatBalance,
EndDate = convert(datetime,?ENDDATE.DATE?),
DOType = CASE WHEN ?DATETYPE.VALUE? = '1' THEN 'Service' ELSE 'Entry' END,
Flag=convert(varchar(50),?GROUPBY.TEXT?), 'test' AS Test

FROM @Aging ta INNER JOIN PatientVisit pv ON ta.PatientVisitId = pv.PatientVisitId
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
INNER JOIN DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId
INNER JOIN DoctorFacility f ON pv.FacilityId = f.DoctorFacilityId
INNER JOIN DoctorFacility c ON pv.CompanyId = c.DoctorFacilityId

Where --Filter on facility
(
(?FACILITY.ITEMDATA? IS NULL AND pv.FacilityID IN (?FACILITY.ITEMDATA.U?)) OR
(?FACILITY.ITEMDATA? IS NOT NULL)
)

 

[bpxmas]

Can anyone please give me an idea what I need to do?

Thanks.

 

[IanWaterman]

This looks like MS SQL code. You will probably better off posting your question in a SQL Server forum

Ian

 

[Madawc]

A quick-and-diry fix would be to add a selection in the Crystal records statement. It would treat the product of the SQL code as input and select from that.

Not as efficient as changing the SQL code, but unless you know some SQL and also can run it and check output on your machine, it would be the best way.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP