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.
We really appreciate if some one from telerik team can help on this ASAP.
Thanks,
Kumar Pindiprolu.
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 |
Thanks,
Kumar Pindiprolu.