Crystal Reports
I am working on a report in Crystal Report,
I have a stored Procedure like this
ALTER PROCEDURE [dbo].[usp_cust_by_activity]
@carrier as VARCHAR(10)
AS
--drop table #custA
--drop table #custPA
--drop table #cdrall
--drop table #cdrPrevMonth
--SET @carrier = 'PR' --'PR', 'RG'
SELECT DISTINCT fld_cust_id INTO #custA FROM tbl_cust_ld WHERE fld_ld_status = 'active' and fld_cust_id in (SELECT fld_cust_id FROM tbl_cust_ld_phone WHERE fld_carrier = @carrier)
SELECT fld_cust_id, fld_btn INTO #custPA FROM dbo.tbl_cust_ld_phone WHERE fld_status = 'active' and fld_cust_id in (SELECT fld_cust_id FROM #custA) and fld_carrier = @carrier
SELECT DISTINCT fld_btn INTO #cdrall FROM tbl_cdr
SELECT DISTINCT fld_btn INTO #cdrPrevMonth FROM tbl_cdr WHERE (DATEDIFF(m, fld_call_date, Convert(nvarchar,DATEADD("Month", -1,GETDATE()),101)) = 0)
--reports
SELECT 'Total Cust' as custStatus, COUNT(*) as total FROM tbl_cust_ld WHERE fld_cust_id in (SELECT fld_cust_id FROM tbl_cust_ld_phone WHERE fld_carrier = @carrier)
UNION ALL
SELECT 'Active Cust' as custStatus,COUNT(*) as total FROM #custA
UNION ALL
SELECT 'Active Cust with Activity' as custStatus,COUNT( DISTINCT fld_cust_id) AS total FROM #custPA WHERE fld_btn IN (SELECT fld_btn FROM #cdrall)
UNION ALL
SELECT 'Active Cust with Activity in Previous Month' as custStatus, COUNT( DISTINCT fld_cust_id) AS total_cust_active_with_activity_previous_month FROM #custPA WHERE fld_btn IN (SELECT fld_btn FROM #cdrPrevMonth)
-
I want to be able to drop the #custPA ....and other temporary tables each time I execute this procedure.
First time it goes thru, but next time it gives errors that it can't delete the temp tables.
--
I also want to be able to use this StoredProc in my Crystal Report.
When I say new item and select DataSet, then when I drag and Drop my Stored Proc
I get this error
"The XML Schema could not be interpretted from this object"
Please could some body help me resolve this problem.
I want to be able to use Stored Procedures with parameters in Crystal Report.
Thank you,
