Skip to Content

Dear All,

I have developed one Crystal Report… but takes to much time to open. It takes around 40 to 60 Sec.. I want to open it into max 10 Seconts and not more. I have created one SQL Server 2008 view and its data comes from AR Invoice Header and Footer Level, Delivery its batches, Sales Oder No. Delivery No. and Series.  AR Invoice View code as follows…kindly pls give me some feasible solution….

Thanks and Regards,

Babu Terve

ALTER VIEW [dbo].[VEPL_VIEW_AR_TAX_INVOICE]

AS

Select OINV.Docentry,  OINV.docnum,

NM.SERIESNAME+’ / ‘+cast( OINV.docnum AS char(20)) AS docnumber1,

NM.SERIESNAME,   OINV.Serial , OINV.U_Delivery,

OINV.docdate, OINV.Numatcard,

(select top 1 isnull(n1.seriesname+’ ‘+ convert (varchar (10),n1.[Beginstr]),”) +’-‘+ convert (varchar (10),DOCNUM )

from ORDR t10 inner join RDR1 t12 on t10.docentry=t12.docentry Left OUTER  JOIN NNM1 n1 ON t10.Series = n1.Series

where t12.trgetentry =(select top 1 t13.docentry from odln t13 inner join dln1 t14 on t13.docentry=t14.docentry 

where t14.trgetentry=INV1.docentry)) as ‘Sales Order’, INV1.Baseref as ‘challan Number’,

 

—  (select (dbo.SODetail(OINV.DocEntry)))’Sales Order’,

–(select (dbo.ChallanDetail(OINV.DocEntry)))’challan Number’,

 

 

Left(OINV.DocTime,2) + ‘:’ + Right(OINV.DocTime,2) AS TimeOfIssueOfInv,

OINV.ShipToCode, OINV.PayToCode, OINV.Address2, OINV.Address,  OINV.[DocTotal]’Document Total’,

OINV.U_LRN, OINV.U_LRD , OINV.U_TRANS_1 , OINV.U_MOT , OINV.U_pn, OINV.U_FN, OINV.U_DESPATCHDT, OINV.U_VE ,

INV1.Itemcode, INV1.Dscription, Inv1.DiscPrcnt, inv1.PriceBefDi ,

CRD1.U_BECC,     CRD1.[U_BVAT] ,    CRD1.[U_BCST] ,    CRD1.[U_BTAN] ,

CRD1.[U_CERange] ,    CRD1.[U_CEDivis] ,    CRD1.[U_CEComRate] ,    OIBT.[U_SID], 

OIBT.[U_MIN], OIBT.[U_MID], OIBT.[U_MIQ], OIBT.[U_RGN], OIBT.[U_UAV],

OIBT.[U_STAV], OIBT.[U_MTAV], OIBT.[U_ED], OIBT.[U_EC], OIBT.[U_HS],  OIBT.[U_STED], OIBT.[U_SUED],

OIBT.[U_SUEC],  OIBT.[U_SUHS], OIBT.[U_STCVD],

OIBT.[U_MTCVD],  OIBT.[U_CHID],

OIBT.[U_RG_No], OIBT.[U_SOS],  OIBT.[U_GRPOQty],

OIBT.BatchNum AS ‘Batch’, OIBT.WhsCode, ibt1.Quantity as ‘Batch Qty’, 

INV1.U_ITMGRP , obtn.LotNumber, obtn.LotNumber ‘Lot No’,oibt.U_SID ‘SID’–, SuppFAcode.U_FACode

, isNull(OINV.U_EDActual,0) AS EDActual, isNull(OINV.U_EDTax,0) as EDTax ,isNull(OINV.U_EDTotal,0) as EDTotal

, isNull(OINV.U_SC5Tax,0) as SC5Tax, OCTG.PymntGroup

from OINV

INNER JOIN INV1 on OINV.docentry=INV1.docentry

INNER JOIN  NNM1 NM on OINV.series=NM.series

INNER JOIN  OCRD on OINV.cardcode=OCRD.cardcode

—LEFT OUTER JOIN took more time to run…

INNER JOIN CRD1 on CRD1.cardcode=OCRD.cardcode   and crd1.LineNum=1

INNER JOIN (select * from IBT1 where BaseType = 15) IBT1 ON  INV1.baseentry=IBT1.baseentry and INV1.itemcode=IBT1.itemcode and  IBT1.WhsCode =INV1.WhsCode

INNER JOIN dbo.OIBT ON OIBT.ItemCode = IBT1.ItemCode AND OIBT.BatchNum = IBT1.BatchNum AND OIBT.WhsCode = IBT1.WhsCode      and OIBT.ItemCode=inv1.ItemCode

INNER JOIN OBTN  ON OBTN.ItemCode = IBT1.ItemCode AND OBTN.DistNumber = IBT1.BatchNum

INNER JOIN OCTG ON OINV.GroupNum=OCTG.GroupNum

Thanks and Regards,

Babu Terve

Cell: 09867577894

Mumbai, India

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Jimmy Michael

    Hi,

    Try to use ‘distinct’. Check if you are using wrong parameter syntax. What about database source location ? are you using server IP or your server standalone IP ?

    Rgds,

    JimM

    (0) 

Leave a Reply