Skip to Content
0

Stored Procedure Parameters in a Crystal object command

Jul 31, 2017 at 06:13 PM

54

avatar image
Former Member

I have a stored procedure that is in a command object inside a crystal report. I need to pass it a date. I defined startdate and enddate in the command parameter window.

It does not error out but does run forever. Please help me figure out why and what I need to change.

This is the code inside the command object in Crystal:

DECLARE @StatisticStartDate as datetime = EPIC_UTIL.EFN_DIN({?startdate});

DECLARE @StatisticEndDate as datetime = dateadd(s, 86399, EPIC_UTIL.EFN_DIN({?enddate}));

DECLARE

--@StatisticStartDate nvarchar(20), -- Change all of these to the data types of the specific columns...

--@StatisticEndDate nvarchar(20),

@CostCenterList nvarchar(200),

@ProcedureCodeList nvarchar(100),

@Statistic nvarchar(100),

@StatisticDescription VARCHAR(255),

@RVValue numeric(18,0),

@Fiscalyear int,

@PatientTypeIndicator nvarchar(100),

@PatientServiceList nvarchar(100),

@PatientClassList nvarchar(100),

@PatientStatusList nvarchar(100);

DECLARE @Output TABLE (

[Facility ID] INT,

[Stat Date] DATETIME,

[DEPARTMENT] VARCHAR(200),

[Statistic Name] VARCHAR (200),

[Statistic Description] VARCHAR (200) ,

[Patient Type Indicator] VARCHAR (2),

[Daily Quantity] DECIMAL(18,2) ,

[Daily Amount] DECIMAL(18,2),

COST_CENTER_NAME VARCHAR(200) ,

[Fiscal Year] INT

)

DECLARE Setting_cur CURSOR FOR

SELECT StatisticStartDate,

StatisticEndDate,

CostCenterList,

ProcedureCodeList,

Statistic,

StatisticDescription,

PatientTypeIndicator,

RVThreshold,

PatientServiceList,

PatientClassList,

PatientStatusList,

FiscalYear

FROM ssisconfiguration.dbo.BalladHealthDayOneStats

--WHERE

--FiscalYear = 2017

;

OPEN Setting_cur

FETCH NEXT FROM Setting_cur INTO@StatisticStartDate,

@StatisticEndDate,

@CostCenterList,

@ProcedureCodeList,

@Statistic,

@StatisticDescription,

@PatientTypeIndicator,

@RVValue,

@PatientServiceList,

@PatientClassList,

@PatientStatusList,

@FiscalYear

WHILE @@FETCH_STATUS = 0 BEGIN

INSERT @Output

EXEC [SSIS].[sp_BalladDayOneMaster] --@RCout = Clarity.ssis.sp_GetFinancialStatsByFinParams

@SD = @StatisticStartDate,

@ED = @StatisticEndDate,

@CostCenterList = @CostCenterList,

@ProcedureCodeList = @ProcedureCodeList,

@Statname = @Statistic,

@StatDescription = @StatisticDescription,

@PatientTypeIndicator = @PatientTypeIndicator,

@RVvalue = @RVValue,

@FiscalYear = @FiscalYear,

@PatientClassList = @PatientClassList ,

@PatientStatusList = @PatientStatusList,

@PatientServiceList = @PatientServiceList

FETCH NEXT FROMSetting_cur INTO@StatisticStartDate,

@StatisticEndDate,

@CostCenterList,

@ProcedureCodeList,

@Statistic,

@StatisticDescription,

@PatientTypeIndicator,

@RVValue,

@PatientServiceList,

@PatientClassList,

@PatientStatusList,

@FiscalYear

END

CLOSE Setting_cur

DEALLOCATE Setting_cur

SELECT * FROM @Output

This is how I defined the object parameters in Crystal:

Startdate – string . default value = ‘t-1’

Enddate - string. default value = ‘t-1’

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

avatar image
Former Member Aug 02, 2017 at 12:16 AM
0

Suppose the vendor database does not allow? that's what we have.

Share
10 |10000 characters needed characters left characters exceeded
Don Williams
Aug 01, 2017 at 09:47 PM
0

I've never heard of anyone creating a SP within a Command object. Why don't you just create the SP on the DB Server and then use the SP in CRD? CR will automatically prompt for the dates.

Don

Share
10 |10000 characters needed characters left characters exceeded