Crystal Reports - Enhance the speed of Reports preloading

Initially when report is loading it calls all data sources (for e.g. all stored procedures with NULL values for parameters) to get the schema of the data result. Some stored procedures use NULL as parameter for showing all records and this initial call of stored procedures dumps the DB and make report loading slower.

The workaround is adding initial parameter @bIsRealIn by which to determine if the call was made by Crystal Report initialization or by user. Then when Crystal is initializing - return a predefined and typified dataset with one record (only a schema).

Note: It's strongly recommended to select the schema dataset from the real tables (that way all columns will be right typified) or CAST the select to the right data type. Otherwise the data in the report will be truncated or not treated right.

Example 1:

CREATE PROCEDURE [dbo].[Sel_WeeklySchedule]

@nCompanyID INT

, @nPatientID INT

...

, @bIsRealIn BIT

AS

SET NOCOUNT ON

DECLARE @tResult TABLE( WEEK_NO INT

, FULL_NAME NVARCHAR(200)

, D1 NVARCHAR(1000)

, D2 NVARCHAR(1000)

, D3 NVARCHAR(1000)

, D4 NVARCHAR(1000)

, D5 NVARCHAR(1000)

, D6 NVARCHAR(1000)

, D7 NVARCHAR(1000)

)

IF @bIsRealIn = 0

BEGIN

SELECT WEEK_NO

, FULL_NAME

, D1

, D2

, D3

, D4

, D5

, D6

, D7

FROM @tResult

END

ELSE BEGIN

...

...

SELECT WEEK_NO = @nWeekNo

, FULL_NAME = @sFullName

, D1 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D2 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D3 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D4 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D5 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D6 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D7 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

FROM T100_SCHEDULE

END

GO

Example 2:

CREATE PROCEDURE [dbo].[Sel_WeeklySchedule]

@nCompanyID INT

, @nPatientID INT

...

, @bIsRealIn BIT

AS

SET NOCOUNT ON

IF @bIsRealIn = 0

BEGIN

SELECT WEEK_NO = 0

, FULL_NAME = CAST('' AS NVARCHAR(150))

, D1 = CAST('' AS NVARCHAR(1000))

, D2 = CAST('' AS NVARCHAR(1000))

, D3 = CAST('' AS NVARCHAR(1000))

, D4 = CAST('' AS NVARCHAR(1000))

, D5 = CAST('' AS NVARCHAR(1000))

, D6 = CAST('' AS NVARCHAR(1000))

, D7 = CAST('' AS NVARCHAR(1000))

, LAST_UPDATE = GETDATE()

FROM @tResult

END

ELSE BEGIN

...

...

SELECT WEEK_NO = @nWeekNo

, FULL_NAME = @sFullName

, D1 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D2 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D3 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D4 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D5 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D6 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D7 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, LAST_UPDATE

FROM T100_SCHEDULE

END

GO

This enhancement will solve the "Invalid group condition." problem for reports using groups and complicated stored procedures datasource.