Telerik Reporting Problems.

[kumar pindiprolu]

Hi

  We are using Telerik reporting to generate reports and we are facing this problem that one particular report which brings out around 8000 records is not getting exported to any format. When I try to export, it just giving out blank page in pdf ,excel etc. on webserver.
  This behaviour is very different when I try to reproduce it in my local development machine and it didnt even generate a report in the report viewer and ofcourse the pdf , excel are blank again.
We know this particular report and its code are working fine because it is generating report for other input parameters which are generating around 200 to 500 records. We are not getting any errors and it just simply not displaying any data, this is really frustrating.

We are making some complex calculations in the stored procedure for this report , the code for which is I am attaching below. When I execute this particular stored procedure it is generating 8000 records in around 2 mins and everything seems to be working fine at the database end. I even checked extending connection string time out period but its of no use.

USE [GBE_GBSDB]
GO
/****** Object:  StoredProcedure [dbo].[usp_Report_DistributorInvoiceSummary]    Script Date: 01/07/2010 11:08:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_Report_DistributorInvoiceSummary]
@ManufacturerId int,
@InvoiceId int,
@StartDate datetime,
@EndDate datetime
AS
BEGIN
SELECT  max(dbo.CustomerSpend.InvoiceNumber) as InvoiceNumber,
   convert(varchar(12),max(dbo.CustomerSpend.InvoiceDate),101) as [Date],
   max(dbo.CustomerSpend.SellerProductNumber) as Item,
   case when isnull(ltrim(rtrim(max(dbo.Product.PackSize))),'')=''
   then max(PackSizeAlias.PackSize) else max(dbo.Product.PackSize) end as PackSize,
   max(CustomerSpend.SellerMfgCustomerName) as Manufacturer,
   max(CustomerSpend.SellerProductBrandedLabel) as DistributorBrand,
   max(dbo.Product.ProductDescription) as [Description],
   max(dbo.CustomerSpend.SellerMfgCustomerPN) as MFC,
            max(dbo.SubmissionCouponSpendRelation.ActualCalculatedCases - CAST(isnull(SplitQty,'0') AS FLOAT)/ProductQtyPerCase) as CaseQty,
   isnull(max(dbo.CustomerSpend.TotalItemPrice),0) as TotalCost,
   MAX(dbo.CustomerSpend.SellerCustomerId) as AccountNum,
   MAX(CustomerSpend.BuyerActualCustomerId) as RestaurantId,
   MAX(CustomerSpend.SellerActualCustomerId) as DistributorId
into  #tb
FROM        dbo.SubmissionInvoice
INNER JOIN dbo.Coupon
ON   SubmissionInvoice.CouponId=Coupon.CouponId
INNER JOIN   dbo.CouponGroupRelation
 ON   dbo.Coupon.CouponId = dbo.CouponGroupRelation.CouponId
INNER JOIN   dbo.CouponProductGroup
 ON      dbo.CouponProductGroup.CouponProductGroupId = dbo.CouponGroupRelation.ProductGroupId
INNER JOIN   dbo.ProductGroupRelation
 ON      dbo.CouponProductGroup.CouponProductGroupId = dbo.ProductGroupRelation.ProductGroupId
INNER JOIN   dbo.SubmissionCoupon
 ON   dbo.SubmissionInvoice.SubmissionInvoiceId = dbo.SubmissionCoupon.InvoiceId
inner join dbo.SubmissionCouponSpendRelation
 ON      SubmissionCouponSpendRelation.SubmissionCouponId = dbo.SubmissionCoupon.CouponSubmissionId
inner JOIN   dbo.CustomerSpend
 ON  SubmissionCouponSpendRelation.CustomerSpendId = dbo.CustomerSpend.CustomerSpendId
inner JOIN dbo.Product
 ON  dbo.CustomerSpend.ActualProductId=dbo.Product.ProductId
 and  dbo.Product.ProductId=ProductGroupRelation.ProductId
left join  (select  ProductPackSizeAlias.ProductId,
       MAX(ProductPackSizeAlias.PackSize) as PackSize
    from  ProductPackSizeAlias  group by ProductId
    ) as PackSizeAlias
on    Product.ProductId=PackSizeAlias.ProductId
WHERE  (DATEDIFF(S, dbo.Coupon.StartDate, CustomerSpend.InvoiceDate) >= 0)
AND   (DATEDIFF(S, dbo.Coupon.EndDate,CustomerSpend.InvoiceDate) <= 0)
and   CouponTypeId=1
and   CustomerSpend.SrcMfgActualCustomerId=@ManufacturerId
and   SubmissionInvoice.SubmissionInvoiceId=@InvoiceId
group by CustomerSpend.CustomerSpendId,Coupon.CouponId
select  #tb.*,
   Restaurant.CustomerName as CustomerName,
   Distributor.CustomerName as DistributorName,
   Distributor.CustomerLogoBinary as DistributorLogo,
   CL_C.Address1 as Address1_c,
   CL_C.Address2 as Address2_c,
   CL_C.City AS City_c,
   USState_C.USStateName as state_c,
   CL_C.Zipcode AS Zipcode_c,
   CL_C.PrimaryPhoneNumber AS Phone_c,
   CL_D.Address1 as Address1_D,
   CL_D.Address2 as Address2_D,
   CL_D.City AS City_D,
   USState_D.USStateName as state_D,
   CL_D.Zipcode AS Zipcode_D,
   CL_D.PrimaryPhoneNumber AS Phone_D
from  #tb
left join Customer as Restaurant
on   #tb.RestaurantId=Restaurant.CustomerId
left join Customer as Distributor
on   #tb.DistributorId=Distributor.CustomerId
left join (select * from CustomerLocation where CustomerLocation.CustomerLocationId in (select MAX(CustomerLocation.CustomerLocationId) from CustomerLocation group by CustomerLocation.CustomerId)
   )as CL_C
ON   CL_C.CustomerId=#tb.RestaurantId
left join USState as USState_C
on   CL_C.USStateId=USState_C.USStateId
left join (select * from CustomerLocation where CustomerLocation.CustomerLocationId in (select MAX(CustomerLocation.CustomerLocationId) from CustomerLocation group by CustomerLocation.CustomerId)
   )as CL_D
ON   CL_D.CustomerId=#tb.DistributorId
left join USState as USState_D
on   CL_D.USStateId=USState_D.USStateId
END
GO

We really appreciate if some one from telerik team can help on this ASAP.

Thanks,
Kumar Pindiprolu.