Crystal Subreports 2

[AKPolarBear]

In general, are there any methods to speed up subreports in Crystal 7? I am using 7.6.100a and MSSQL.

I am currently trying to use the data in OEPRCFIL, which is a nasty file to work with anyway and am bringing the data in as a subreport. The links are simple, the reports are simple but it sure takes a long time to process.

I should mention that we have a modification that allows use the use of a 9th price code from the stock 1-8. It is our primary price code.

I have other reports that have 6 or 7 subreports in them that run faster.

Crystals with subreports are also resource hogs from what I can see.

 

[dgillz]

Subreports will execute based on where in the report they are placed. So if you have a report with 10,000 records, and the subreport is placed in the details section, it will execute 10,000 times.

If the same report is grouped and has 12 groups, and the subreport is placed in the group header or footer, it will only execute 12 times.

Subreports should be avoided if at all possible.

The best way to avoid subreports is to create a SQL View or Stored Procedure that has the information your subreport is trying to pull in it, and then add the view or SP to your report, thus eliminating the subreport.

Please note that you will need to make sure that Crystal can read the view by clicking on file, options, database tab from within Crystal, and make sure that Views and Stored Procedures are checked. As soon as you do this exit crystal and come back into it, to save the changes for that workstation. This must be done on every workstation that has Crystal Reports.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[kscheeler]

I would definetly use views for a table like that. Here's the views that I use at my clients for the price code tables

Copy and paste all of it into query analyzer and it will be easier to read.

------------------------------------------------------------------------------
--Create OE Price Code link for Price code 1 - Customer Number and Item Number
------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vOE_PC1CusNoItemNo]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vOE_PC1CusNoItemNo]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vOE_PC1CusNoItemNo
AS
SELECT cd_tp AS cd_tp, curr_cd, LEFT(filler_0001, 12) AS cus_no, SUBSTRING(filler_0001, 13, 15) AS item_no, start_dt, end_dt, cd_prc_basis, minimum_qty_1,
prc_or_disc_1, minimum_qty_2, prc_or_disc_2, minimum_qty_3, prc_or_disc_3, minimum_qty_4, prc_or_disc_4, minimum_qty_5, prc_or_disc_5,
minimum_qty_6, prc_or_disc_6, minimum_qty_7, prc_or_disc_7, minimum_qty_8, prc_or_disc_8, minimum_qty_9, prc_or_disc_9, minimum_qty_10,
prc_or_disc_10
FROM dbEPRCFIL_SQL
WHERE (cd_tp = 1)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

-----------------------------------------------------------------------------------
--Create OE Price Code link for Price code 2 - Customer Number and Product Category
-----------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vOE_PC2CusNoProdCat]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vOE_PC2CusNoProdCat]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vOE_PC2CusNoProdCat
AS
SELECT cd_tp AS cd_tp, curr_cd, LEFT(filler_0001, 12) AS cus_no, SUBSTRING(filler_0001, 13, 3) AS prod_cat, start_dt, end_dt, cd_prc_basis, minimum_qty_1,
prc_or_disc_1, minimum_qty_2, prc_or_disc_2, minimum_qty_3, prc_or_disc_3, minimum_qty_4, prc_or_disc_4, minimum_qty_5, prc_or_disc_5,
minimum_qty_6, prc_or_disc_6, minimum_qty_7, prc_or_disc_7, minimum_qty_8, prc_or_disc_8, minimum_qty_9, prc_or_disc_9, minimum_qty_10,
prc_or_disc_10
FROM dbEPRCFIL_SQL
WHERE (cd_tp = 2)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

----------------------------------------------------------------------------
--Create OE Price Code link for Price code 3 - Customer Type and Item Number
----------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vOE_PC3CusTypeItemNo]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vOE_PC3CusTypeItemNo]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vOE_PC3CusTypeItemNo
AS
SELECT cd_tp, curr_cd, LEFT(filler_0001, 5) AS cus_type_cd, SUBSTRING(filler_0001, 6, 15) AS item_no, start_dt, end_dt, cd_prc_basis, minimum_qty_1,
prc_or_disc_1, minimum_qty_2, prc_or_disc_2, minimum_qty_3, prc_or_disc_3, minimum_qty_4, prc_or_disc_4, minimum_qty_5, prc_or_disc_5,
minimum_qty_6, prc_or_disc_6, minimum_qty_7, prc_or_disc_7, minimum_qty_8, prc_or_disc_8, minimum_qty_9, prc_or_disc_9, minimum_qty_10,
prc_or_disc_10
FROM dbEPRCFIL_SQL
WHERE (cd_tp = 3)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

---------------------------------------------------------------------------------
--Create OE Price Code link for Price code 4 - Customer Type and Product Category
---------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vOE_PC4CusTypeProdCat]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vOE_PC4CusTypeProdCat]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vOE_PC4CusTypeProdCat
AS
SELECT cd_tp, curr_cd, LEFT(filler_0001, 5) AS cus_type_cd, SUBSTRING(filler_0001, 6, 3) AS prod_cat, start_dt, end_dt, cd_prc_basis, minimum_qty_1,
prc_or_disc_1, minimum_qty_2, prc_or_disc_2, minimum_qty_3, prc_or_disc_3, minimum_qty_4, prc_or_disc_4, minimum_qty_5, prc_or_disc_5,
minimum_qty_6, prc_or_disc_6, minimum_qty_7, prc_or_disc_7, minimum_qty_8, prc_or_disc_8, minimum_qty_9, prc_or_disc_9, minimum_qty_10,
prc_or_disc_10
FROM dbEPRCFIL_SQL
WHERE (cd_tp = 4)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

-------------------------------------------------------------------
--Create OE Price Code link for Price code 5 - Customer Number Only
-------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vOE_PC5CusNo]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vOE_PC5CusNo]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vOE_PC5CusNo
AS
SELECT cd_tp AS cd_tp, curr_cd, LEFT(filler_0001, 12) AS cus_no, start_dt, end_dt, cd_prc_basis, minimum_qty_1, prc_or_disc_1, minimum_qty_2,
prc_or_disc_2, minimum_qty_3, prc_or_disc_3, minimum_qty_4, prc_or_disc_4, minimum_qty_5, prc_or_disc_5, minimum_qty_6, prc_or_disc_6,
minimum_qty_7, prc_or_disc_7, minimum_qty_8, prc_or_disc_8, minimum_qty_9, prc_or_disc_9, minimum_qty_10, prc_or_disc_10
FROM dbEPRCFIL_SQL
WHERE (cd_tp = 5)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

---------------------------------------------------------------
--Create OE Price Code link for Price code 6 - Item Number Only
---------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vOE_PC6ItemNo]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vOE_PC6ItemNo]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vOE_PC6ItemNo
AS
SELECT cd_tp AS cd_tp, curr_cd, LEFT(filler_0001, 15) AS item_no, start_dt, end_dt, cd_prc_basis, minimum_qty_1, prc_or_disc_1, minimum_qty_2,
prc_or_disc_2, minimum_qty_3, prc_or_disc_3, minimum_qty_4, prc_or_disc_4, minimum_qty_5, prc_or_disc_5, minimum_qty_6, prc_or_disc_6,
minimum_qty_7, prc_or_disc_7, minimum_qty_8, prc_or_disc_8, minimum_qty_9, prc_or_disc_9, minimum_qty_10, prc_or_disc_10
FROM dbEPRCFIL_SQL
WHERE (cd_tp = 6)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

-----------------------------------------------------------------
--Create OE Price Code link for Price code 7 - Customer Type Only
-----------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vOE_PC7CusType]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vOE_PC7CusType]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vOE_PC7CusType
AS
SELECT cd_tp AS cd_tp, curr_cd, LEFT(filler_0001, 5) AS cus_type_cd, start_dt, end_dt, cd_prc_basis, minimum_qty_1, prc_or_disc_1, minimum_qty_2,
prc_or_disc_2, minimum_qty_3, prc_or_disc_3, minimum_qty_4, prc_or_disc_4, minimum_qty_5, prc_or_disc_5, minimum_qty_6, prc_or_disc_6,
minimum_qty_7, prc_or_disc_7, minimum_qty_8, prc_or_disc_8, minimum_qty_9, prc_or_disc_9, minimum_qty_10, prc_or_disc_10
FROM dbEPRCFIL_SQL
WHERE (cd_tp = 7)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

--------------------------------------------------------------------
--Create OE Price Code link for Price code 8 - Product Category Only
--------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vOE_PC8ProdCat]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vOE_PC8ProdCat]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vOE_PC8ProdCat
AS
SELECT cd_tp AS cd_tp, curr_cd, LEFT(filler_0001, 3) AS prod_cat, start_dt, end_dt, cd_prc_basis, minimum_qty_1, prc_or_disc_1, minimum_qty_2,
prc_or_disc_2, minimum_qty_3, prc_or_disc_3, minimum_qty_4, prc_or_disc_4, minimum_qty_5, prc_or_disc_5, minimum_qty_6, prc_or_disc_6,
minimum_qty_7, prc_or_disc_7, minimum_qty_8, prc_or_disc_8, minimum_qty_9, prc_or_disc_9, minimum_qty_10, prc_or_disc_10
FROM dbEPRCFIL_SQL
WHERE (cd_tp = 8)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



Kevin Scheeler

 

[crystalreporting]

When Crystal runs a subreport, it uses the links established in the main report to read the data tables in the subreport. Step 1 therefore is to try and create links that use the indexes to the tables in the subreport.

Crystal doesn't handle not being able to find data based on the subreport's tables indexes very well. If it cannot find the data based on the subreports tables indexes, it moves into a sequential read. You can see this sometimes when moving from one page to another in a main report - one page will read fine, the next will take forever. If you're printing the report, the whole report will appear to be slow.

Step 2 would be to stop long, sequential subreport reads if possible.

An example would be a sales history report with a subreport on IMORDHST to show feature/option components. For an order/line/item that is a feature option item, the subreport will run quickly. On a non-feature option item, the subreport will still run, but it will be slow as it will read through the entire table looking for a match that it will not find. Best option in this scenario is to conditionally supress the subreport so that it only looks for components for feature option items and not for non feature option items.

Peter Shirley

http://www.linkcorp.com

 

[markajem]

I believe that you can not use a stored procedure with a table or view within the same main report. You are not able to link fields to/from a stored procedure. You can create a view and use with tables or use one stored procedure. Just a note for the future.

 

[PhilPPhillips]

I also created a view to use for price codes. Here is the one I created and use.

create view price_code as
select cd_tp,start_dt,end_dt,cd_prc_basis,prc_or_disc_1,
case when cd_tp in (1,2,5) then substring(filler_0001,1,12) end as customer,
case when cd_tp in (3,4,7) then substring(filler_0001,1,5) end as cus_type,
case when cd_tp = 1 then substring(filler_0001,13,27)
when cd_tp = 3 then substring(filler_0001,6,20)
when cd_tp = 6 then substring(filler_0001,1,15) end as item_no,
case when cd_tp = 2 then substring(filler_0001,13,15)
when cd_tp = 4 then substring(filler_0001,6,8)
when cd_tp = 8 then substring(filler_0001,1,3) end as prod_cat
from oeprcfil_sql