Skip to Content
avatar image
Former Member

Stored Procedure Parameters in a Crystal object command

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’

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

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

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

    Add comment
    10|10000 characters needed characters exceeded

  • Aug 01, 2017 at 09:47 PM

    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

    Add comment
    10|10000 characters needed characters exceeded