I need to create over 40 different reports that pulls data from the same 4 tables. What I have done so far is to create a view for each report that filters the data accordingly. This has left me with 40 Views (SQLSERVER) and 40 reports(Crystal). I know theres an easier way to do this and so far Stored Procedures seems to be the way to go.
What I want is a Stored Procedure(s) that filters the data for each report. If I could run all forty reports from one stored procedure instead of 40 views and 40 reports, mylife would be much easier.
Here is an example of a stored proc I have been working on. I just need a way to consolidate all the seperate SP I would need to run all 40 reports.
----- Stored Procedure 1 ------
CREATE PROCEDURE [AFLU]
AS
Declare @ReportType Varchar(8)
Set @ReportType = 'AFLU'
Select
dbo.claim_details.claimno,
dbo.claim_details.diagcode,
LTRIM(dbo.claim_details.proccode) as 'PROCCODE',
@ReportType as 'TYPE'
FROM
dbo.claim_details
Where
LTRIM(dbo.claim_details.proccode) = '90724'
------ Stored Procdure 2 --------
CREATE PROCEDURE [ESRD]
AS
Declare @ReportType Varchar(8)
Set @ReportType = 'ESRD'
Select
dbo.claim_details.claimno,
dbo.claim_details.diagcode,
LTRIM(dbo.claim_details.proccode) as 'PROCCODE',
@ReportType as 'TYPE'
FROM
dbo.claim_details
Where
LTRIM(dbo.claim_details.proccode) = '90443'and
LTRIM(dbo.claim_details.diagcode) = 'V24.5'
RESULTS:
claimno diagcode PROCCODE TYPE
-------------------- -------- --------------- --------
1999081302400007 719.46 90724 AFLU
1999082302400093 780.4 90724 AFLU
1999083100400177 692. 90724 AFLU
1999090100400028 V06.9 90724 AFLU
1999101102400033 493.9 90724 AFLU
1999151107100087 V24.5 90443 ESRD
2000140805200001 V24.5 90443 ESRD
2000150804200089 V24.5 90443 ESRD
Now, if I could insert this data into a temp table along with the other 40 , I could run all my reports from one SP
using my report writer to sort and group the data for each report.
Thanks
Mark
What I want is a Stored Procedure(s) that filters the data for each report. If I could run all forty reports from one stored procedure instead of 40 views and 40 reports, mylife would be much easier.
Here is an example of a stored proc I have been working on. I just need a way to consolidate all the seperate SP I would need to run all 40 reports.
----- Stored Procedure 1 ------
CREATE PROCEDURE [AFLU]
AS
Declare @ReportType Varchar(8)
Set @ReportType = 'AFLU'
Select
dbo.claim_details.claimno,
dbo.claim_details.diagcode,
LTRIM(dbo.claim_details.proccode) as 'PROCCODE',
@ReportType as 'TYPE'
FROM
dbo.claim_details
Where
LTRIM(dbo.claim_details.proccode) = '90724'
------ Stored Procdure 2 --------
CREATE PROCEDURE [ESRD]
AS
Declare @ReportType Varchar(8)
Set @ReportType = 'ESRD'
Select
dbo.claim_details.claimno,
dbo.claim_details.diagcode,
LTRIM(dbo.claim_details.proccode) as 'PROCCODE',
@ReportType as 'TYPE'
FROM
dbo.claim_details
Where
LTRIM(dbo.claim_details.proccode) = '90443'and
LTRIM(dbo.claim_details.diagcode) = 'V24.5'
RESULTS:
claimno diagcode PROCCODE TYPE
-------------------- -------- --------------- --------
1999081302400007 719.46 90724 AFLU
1999082302400093 780.4 90724 AFLU
1999083100400177 692. 90724 AFLU
1999090100400028 V06.9 90724 AFLU
1999101102400033 493.9 90724 AFLU
1999151107100087 V24.5 90443 ESRD
2000140805200001 V24.5 90443 ESRD
2000150804200089 V24.5 90443 ESRD
Now, if I could insert this data into a temp table along with the other 40 , I could run all my reports from one SP
using my report writer to sort and group the data for each report.
Thanks
Mark