Crystal report with linked subreports only works with preview reports

Question

I used a stored procedure with sql parameters with my main report, which works.

my stored procedure for my main report

then I tried to add a subreport to the working report with the following stored procedure

then bind the parameters of the Subreport field to my main

I checked the preview of the main report if the subreport is working

Then I run the report, but all I see is it just got stuck in this message and continued to work, I even waited for hours and checked that the task manager received the message, but no. It also has no error message. But to close the current report, I need to "End the process" in the task manager.

so I tried to remove the field parameters from the Sub report as well as the links and re-run the report.

it works, but it is useless to have a report without passing values ​​from the main report. because it has a date range. What is the problem? Why does it just get stuck when loading a report when I bind parameters?

for more information I am using Crystal Reports Service Pack 16 and the IDE is like Visual Studio 2015

and this is the code that I used to get / set the parameter values ​​in the main report

private void SalesByRangeReport_Load(object sender, EventArgs e) { FormBorderStyle = FormBorderStyle.Sizable; WindowState = FormWindowState.Maximized; TopMost = true; DataTable dtSalesByRangeReport = GetData(); showReport(dtSalesByRangeReport); } private void showReport(DataTable dtSalesByRangeReport) { ReportDocument rdoc = new ReportDocument(); //rdoc.Load(@"Report\SalesByRangeReport.rpt"); rdoc.Load(AppDomain.CurrentDomain.BaseDirectory + @"Report\SalesByRangeReport.rpt"); rdoc.SetDataSource(dtSalesByRangeReport); TextObject txt; if (rdoc.ReportDefinition.ReportObjects["test"] != null) { txt = (TextObject)rdoc.ReportDefinition.ReportObjects["test"]; txt.Text = "From :" + StartDate.ToString(" MMMM dd yyyy hh :mm") + " To :" + EndDate.ToString(" MMMM dd yyyy hh :mm"); } SalesByRangeCystalReport.ReportSource = rdoc; } private DataTable GetData() { DataTable dtData = new DataTable(); using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CSPOSWare.Reports.Properties.Settings.fpos_chaplinConnectionString"].ConnectionString)) { //TODO Mark and Reni : Create a Stored Procedure, Saved in SalesByRangeReport.txt using (SqlCommand cmd = new SqlCommand("usp_ReportShowSalesRangeDateTime", conn)) { cmd.CommandType = CommandType.StoredProcedure; conn.Open(); //cmd.Parameters.AddWithValue("@TopInt", this.TopInt); cmd.Parameters.AddWithValue("@SortType", this.SortType); cmd.Parameters.AddWithValue("@StartDate", this.StartDate); cmd.Parameters.AddWithValue("@EndDate", this.EndDate); cmd.Parameters.AddWithValue("@DeptGroup", this.DeptGroup); cmd.Parameters.AddWithValue("@DateType", this.DateType); //Console.WriteLine("Start Date" + StartDate); SqlDataReader rdr = cmd.ExecuteReader(); dtData.Load(rdr); } } return dtData; } 

and both of my reports have the following properties:

 Build Action: Compile Copy to Output Directory: Copy Always Custom Tool: Custom Tool Name: 

So, I tried ADDED OPTIMIZE for UNKNOWN AND OF MY MAIN SP AND SUB SP MAIN:

  ALTER PROCEDURE[dbo].[usp_ReportShowSalesRangeDateTime] ( @SortType Varchar(50), @StartDate DATETIME, @EndDate DATETIME, @DeptGroup Varchar(50), @DateType Varchar(50) ) AS BEGIN Declare @SQLQuery NVARCHAR(max) Declare @ReportCriteria NVARCHAR(max) If (LEN(@DeptGroup) > 0) Set @ReportCriteria = ' AND B.Department = ''' + @DeptGroup + ''''; If (LEN(@DeptGroup) = 0) Set @ReportCriteria = ' '; WITH SalesRange AS( Select A.EndDate as [Log Date], A.StoreDate as [Store Date], B.Department as [Department], B.Quantity as [Quantity], isnull(C.Amount,0) as [Discount], B.AmountDue as [AmountDue],round(B.BasePrice*1.12,4) as [Gross Sales], B.BasePrice + isnull(vsa.Tax,0) as [BasePrice], case when vsa.type = 0 then isnull(vsa.Amount,0) else 0.0000 end As [VAT Sales Amount], case when vsa.type = 1 then isnull(vsa.Amount,0) else 0.0000 end As [VAT Exempt Sales Amount], B.ServiceCharge as [ServiceCharge], isnull(vsa.Tax,0) As [VAT Sales Tax], case when D.[Type] = 0 Then D.Tax Else 0 End As [Tax], case when T.MediaIndex = 4 then T.Amount else 0 End As [GiftCert], case when T.MediaIndex = 4 then 1 else 0 End As [GCCount] FROM CSSaleItem B WITH(NOLOCK) LEFT JOIN CSSaleItemDiscount C WITH(NOLOCK) ON B.CSSaleItemID = C.CSSaleItemID LEFT JOIN CSSale A WITH(NOLOCK) ON A.CSSaleID = B.CSSaleID LEFT JOIN CSSaleItemTax D WITH(NOLOCK) ON B.CSSaleItemID = D.CSSaleItemID LEFT JOIN (Select CSSaleItemID, Amount, Tax, [Type] From CSSaleItemTax WITH(NOLOCK) Where [Type] = 0) As vsa ON vsa.CSSaleItemID = B.CSSaleItemID LEFT JOIN CSSaleTender T WITH(NOLOCK) ON T.CSSaleID = A.CSSaleID Where StoreDate BETWEEN convert(VARCHAR,@StartDate) AND convert(VARCHAR,@EndDate) and a.RefundStoreDate IS NULL ) SELECT [Department], sum([Quantity]) as [Quantity], SUM([Tax]) as [Tax] , sum([Discount]) as [Discount], sum(([Gross Sales]+[ServiceCharge])) as [Gross Sales], sum(([BasePrice]+[ServiceCharge]-[Discount])) As [Net Sales], sum(([BasePrice]+[ServiceCharge]-[Discount]))/ (Select sum(NetSales) FROM CSSale B LEFT JOIN (select csSaleID, Department from CSSaleItem WITH(NOLOCK) group by Department, CSSALEID ) AS A On A.CSSaleID = B. CSSaleID Where StoreDate BETWEEN convert(VARCHAR,@StartDate) AND convert(VARCHAR,@EndDate) and RefundStoreDate IS NULL )*100 as [% Total], sum([GiftCert]) as [Gift Cert Total], sum([ServiceCharge]) as [Service Charge], sum([GCCount]) as [GCCountTotal] From SalesRange Group By [Department] Order By [Department] desc OPTION (OPTIMIZE FOR (@StartDate UNKNOWN, @EndDate UNKNOWN)) END 

SubReport:

  ALTER PROCEDURE[dbo].[usp_ReportShowMedia]( @StartDate2 DATETIME, @EndDate2 DATETIME ) As BEGIN Select isnull(M.MediaName,'Other') As [Media], COUNT(T.MediaIndex) As [Count], isnull(sum(T.Amount),0) As [Sales Amount], isnull(sum(case when S.EndDate IS NOT NULL and DateRefunded IS NULL Then S.NetSales Else 0 End),0) As [Total Sales], isnull(sum(case when S.EndDate IS NULL then S.NetSales else 0 end),0) as [Cancelled Sales], isnull(sum(case when S.DateRefunded IS NOT NULL then S.NetSales else 0 end),0) as [Refunded Sales] FROM CSSale S WITH(NOLOCK) LEFT JOIN (Select CSSaleID, Department from CSSaleItem WITH(NOLOCK) group by CSSaleID,Department) As I ON I.CSSaleID = S.CSSaleID LEFT JOIN CSSaleTender T WITH(NOLOCK) On S.CSSaleID = T.CSSaleID LEFT JOIN Media M WITH(NOLOCK) ON M.MediaIndex = T.MediaIndex Where StoreDate BETWEEN convert(VARCHAR,@StartDate2) AND convert(VARCHAR,@EndDate2) group by M.MediaName order by M.MediaName OPTION (OPTIMIZE FOR (@StartDate2 UNKNOWN, @EndDate2 UNKNOWN)) END 

The same results, I think that the main report does not go through the auxiliary report, because it always works only on Preview, but at runtime it just gets stuck on loading.

I Tried sp_who2 To actively check if it is blocked.

btw I use this connection on my App.config if that helps.

  <?xml version="1.0" encoding="utf-8" ?> <configuration> <startup useLegacyV2RuntimeActivationPolicy="true"> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" /> </startup> <runtime> <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"> <probing privatePath="lib" /> </assemblyBinding> </runtime> <connectionStrings> <add name="CSPOS.Reports.Properties.Settings.chaplinConnectionString" connectionString="Data Source=RENZ\SQLEXPRESS;Initial Catalog=erika;Integrated Security=True" providerName="System.Data.SqlClient"/> </connectionStrings> </configuration> 

I checked the database permissions on master

Answer 1

I solved the problem by using two sqlcommand instead and excluding the use of the stored procedure .. thanks btw.

Answer 2

I had a situation like this day. The main problem was an incorrect execution plan for different parameters of the stored procedure.

It can be set using OPTION (OPTIMIZE FOR (@param UNKNOWN)). Here you can read everything here .

Second: in requests there can be some read locks. You can use the standard WITH (NOLOCK) (READ UNCOMMITTED) tooltip if the report data is not so sensitive.

Try changing your query to look like this and check the result.

 SELECT ... FROM CSSale S WITH(NOLOCK) LEFT JOIN (... FROM CSSaleItem WITH(NOLOCK) ...) LEFT JOIN CSSaleTender T WITH(NOLOCK) ... LEFT JOIN Media M WITH(NOLOCK) ORDER BY M.MediaName OPTION (OPTIMIZE FOR (@StartDate2 UNKNOWN, @EndDate2 UNKNOWN)) 

You also need to check the stored procedure that you use for the main request for the same problems.

PS In any case, check the SQL Profiler for outgoing queries.

Answer 3

Try to find out if there is a problem with the stored procedure.

Try to capture the parameters passed to the stored procedure ( dbo.usp_ReportShowMedia ) in the subreport by inserting them into some dummy table ( dbo.subReportParamsTb ) as the first SP statement.

 INSERT INTO dbo.subReportParamsTb( StartDate2, EndDate2 ) VALUES ( @StartDate2, @EndDate2 ); 

After running the main report, when you see a stuck message, check for dbo.subReportParamsTb with nolock.

 SELECT * FROM dbo.subReportParamsTb WITH(NOLOCK); 

If you see that the parameters were successfully transferred to the SP, try running SP yourself with these parameters.

If the SP does not release the output for a limited time, you can try to fix the problem by looking at the execution plan.

If the SP is working fine, you can be sure that the problem is not with the SP.

Answer 4

I think this is a data transfer problem between both reports. pls ensure that the main report and the sub report have a unique identifier and are correctly linked.