Hi,
I'm new in using SP as a data source.
I'm running CR10 against SQL Server 2000.
The SP I created very simple and I think is working fine:
CREATE PROCEDURE mfs_Clients_with_Medication_Monitoring_LofC
@StartTime as datetime,
@EndTime as datetime
AS
SELECT TOP 100 PERCENT Client.OID as Client_OID, Client.ID as Client_ID, dbo.LevelofCare.Code as LevelofCare_Code, dbo.COST_CENTER_LEVEL_OF_CARE.ExpDate,
dbo.COST_CENTER_LEVEL_OF_CARE.EffDate, dbo.LevelofCare.FullName as LevelofCare_FullName
FROM dbo.COSTCTRGRP_TO_COST_CENTER_LEVEL_OF_CARE_COLLECTION FULL OUTER JOIN
dbo.COST_CENTER_LEVEL_OF_CARE ON
dbo.COSTCTRGRP_TO_COST_CENTER_LEVEL_OF_CARE_COLLECTION.OID_LINK = dbo.COST_CENTER_LEVEL_OF_CARE.OID FULL OUTER JOIN
dbo.LevelofCare ON dbo.COST_CENTER_LEVEL_OF_CARE.LEVELOFCARE_MONIKER = dbo.LevelofCare.OID FULL OUTER JOIN
dbo.CostCtrGrp CostCtrGrp ON dbo.COSTCTRGRP_TO_COST_CENTER_LEVEL_OF_CARE_COLLECTION.OID = CostCtrGrp.OID FULL OUTER JOIN
dbo.AGENCY_COST_CENTER AGENCY_COST_CENTER ON CostCtrGrp.CostCenter = AGENCY_COST_CENTER.OID FULL OUTER JOIN
dbo.EPISODE_TO_COST_CENTER_GROUP_COLLECTION EPISODE_TO_COST_CENTER_GROUP_COLLECTION FULL OUTER JOIN
dbo.Client Client FULL OUTER JOIN
dbo.CLIENT_TO_CLIENTAGENCY_COLLECTION CLIENT_TO_CLIENTAGENCY_COLLECTION ON
Client.OID = CLIENT_TO_CLIENTAGENCY_COLLECTION.OID FULL OUTER JOIN
dbo.ClientAgency ClientAgency ON CLIENT_TO_CLIENTAGENCY_COLLECTION.OID_LINK = ClientAgency.OID FULL OUTER JOIN
dbo.CLIENTAGENCY_TO_EPISODE_COLLECTION CLIENTAGENCY_TO_EPISODE_COLLECTION ON
ClientAgency.OID = CLIENTAGENCY_TO_EPISODE_COLLECTION.OID FULL OUTER JOIN
dbo.Episode Episode ON CLIENTAGENCY_TO_EPISODE_COLLECTION.OID_LINK = Episode.OID ON
EPISODE_TO_COST_CENTER_GROUP_COLLECTION.OID = Episode.OID ON
CostCtrGrp.OID = EPISODE_TO_COST_CENTER_GROUP_COLLECTION.OID_LINK
GROUP BY Client.OID, Client.ID, dbo.LevelofCare.Code, dbo.COST_CENTER_LEVEL_OF_CARE.ExpDate, dbo.COST_CENTER_LEVEL_OF_CARE.EffDate,
dbo.LevelofCare.FullName
HAVING
(COST_CENTER_LEVEL_OF_CARE.ExpDate IS NULL OR
COST_CENTER_LEVEL_OF_CARE.ExpDate >= @StartTime) AND
(COST_CENTER_LEVEL_OF_CARE.EffDate < @EndTime) AND (LevelofCare.Code = '012')
GO
When I run report just using this SP, reports runs instant and look very good. However, when I added this SP to existing report to create a group base on (isnull() or not isnull()mfs_Clients_with_Medication_Monitoring_LofC.Client_OID) the report looks like running forever and shows the Database error:
More than one datasource or a stored procedure has been used in this report.
Please make sure that on SQL Expression is added and no server-side group-by is performed.
Please help with advice.
Thanks.
Alec.
Thanks.
Alec.
I'm new in using SP as a data source.
I'm running CR10 against SQL Server 2000.
The SP I created very simple and I think is working fine:
CREATE PROCEDURE mfs_Clients_with_Medication_Monitoring_LofC
@StartTime as datetime,
@EndTime as datetime
AS
SELECT TOP 100 PERCENT Client.OID as Client_OID, Client.ID as Client_ID, dbo.LevelofCare.Code as LevelofCare_Code, dbo.COST_CENTER_LEVEL_OF_CARE.ExpDate,
dbo.COST_CENTER_LEVEL_OF_CARE.EffDate, dbo.LevelofCare.FullName as LevelofCare_FullName
FROM dbo.COSTCTRGRP_TO_COST_CENTER_LEVEL_OF_CARE_COLLECTION FULL OUTER JOIN
dbo.COST_CENTER_LEVEL_OF_CARE ON
dbo.COSTCTRGRP_TO_COST_CENTER_LEVEL_OF_CARE_COLLECTION.OID_LINK = dbo.COST_CENTER_LEVEL_OF_CARE.OID FULL OUTER JOIN
dbo.LevelofCare ON dbo.COST_CENTER_LEVEL_OF_CARE.LEVELOFCARE_MONIKER = dbo.LevelofCare.OID FULL OUTER JOIN
dbo.CostCtrGrp CostCtrGrp ON dbo.COSTCTRGRP_TO_COST_CENTER_LEVEL_OF_CARE_COLLECTION.OID = CostCtrGrp.OID FULL OUTER JOIN
dbo.AGENCY_COST_CENTER AGENCY_COST_CENTER ON CostCtrGrp.CostCenter = AGENCY_COST_CENTER.OID FULL OUTER JOIN
dbo.EPISODE_TO_COST_CENTER_GROUP_COLLECTION EPISODE_TO_COST_CENTER_GROUP_COLLECTION FULL OUTER JOIN
dbo.Client Client FULL OUTER JOIN
dbo.CLIENT_TO_CLIENTAGENCY_COLLECTION CLIENT_TO_CLIENTAGENCY_COLLECTION ON
Client.OID = CLIENT_TO_CLIENTAGENCY_COLLECTION.OID FULL OUTER JOIN
dbo.ClientAgency ClientAgency ON CLIENT_TO_CLIENTAGENCY_COLLECTION.OID_LINK = ClientAgency.OID FULL OUTER JOIN
dbo.CLIENTAGENCY_TO_EPISODE_COLLECTION CLIENTAGENCY_TO_EPISODE_COLLECTION ON
ClientAgency.OID = CLIENTAGENCY_TO_EPISODE_COLLECTION.OID FULL OUTER JOIN
dbo.Episode Episode ON CLIENTAGENCY_TO_EPISODE_COLLECTION.OID_LINK = Episode.OID ON
EPISODE_TO_COST_CENTER_GROUP_COLLECTION.OID = Episode.OID ON
CostCtrGrp.OID = EPISODE_TO_COST_CENTER_GROUP_COLLECTION.OID_LINK
GROUP BY Client.OID, Client.ID, dbo.LevelofCare.Code, dbo.COST_CENTER_LEVEL_OF_CARE.ExpDate, dbo.COST_CENTER_LEVEL_OF_CARE.EffDate,
dbo.LevelofCare.FullName
HAVING
(COST_CENTER_LEVEL_OF_CARE.ExpDate IS NULL OR
COST_CENTER_LEVEL_OF_CARE.ExpDate >= @StartTime) AND
(COST_CENTER_LEVEL_OF_CARE.EffDate < @EndTime) AND (LevelofCare.Code = '012')
GO
When I run report just using this SP, reports runs instant and look very good. However, when I added this SP to existing report to create a group base on (isnull() or not isnull()mfs_Clients_with_Medication_Monitoring_LofC.Client_OID) the report looks like running forever and shows the Database error:
More than one datasource or a stored procedure has been used in this report.
Please make sure that on SQL Expression is added and no server-side group-by is performed.
Please help with advice.
Thanks.
Alec.
Thanks.
Alec.