Stored Procedure & Tables 1

[alectek]

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.

 

[synapsevampire]

You misunderstand how Crystal works, you should be using only ONE datasource in Crystal.

Adding in a second SP says I want to report on data, but I'm nto going to tell you which data, so guess...

I've no idea why you would create another SP to create a grouping unless you intend to then join that SP to your current SP, which means that rather than letting the database do database work, you want Crystal to do so, which os course will be slow, it's not a database.

I may be misunderstanding something as "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()" appears to be saying that you run it and it works fine, but then you add it in a second time... Hard to know from this posting.

-k

 

[alectek]

Synapsevampire,
Thank you for the fast response. I have only one stored procedure in my report (the one I created), linked to the tables from the same data partition on the same database.


Thanks.
Alec.

 

[synapsevampire]

Right, so you're telling Crystal NOT to join the data on the database, rather do so internally in Crystal, hence the slow performance and the appropriate warning.

You decided not to explain why you think it valuable to create an SP for grouping.

I suggest that you create a single SP as the data source for the report.

-k

 

[alectek]

Synapsevampire,
I need it to do the separated summary clients with (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')

Thanks,
Alec

Thanks.
Alec.

 

[synapsevampire]

Sorry, it doesn't make any sense, any database related work you can do in Crystal you can do better and faster on the databasein an SP.

I guess you think that separated summary clients is a known technical term. the equivalent to your HAVING clause is the Report->Record Selection Group in Crystal, although in most cases it will simply do a SUPPRESS.

If you use Database->Show SQL Query, then you'll have precisely what is in the current Crystal Report.

Add that as an inline query (derived table) to your SP code and you'll have the same thing, only faster and reusable and easier to maintain.

-k