Crystal report with linked subreport only working on Report Preview

Question

I used stored procedure with sql parameters with my main report which is working.

my stored procedure for my main report

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

then link Subreport field parameters to my Main

I checked the Main Report Preview if subreport are working

Then I run the report but all I see is this it just stuck in this message and kept running, I even wait for hours and check if task manager got the report hang, but nope.. It also doesn't have any error message. But in order to close the running report I have to "End Process" in the Task Manager.

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

it's working but it's useless to have a report without passing values from main report. because I have date range in it. What seems to be the problem? Why it just stuck on loading of report when I link parameters?

for more info I'm using SAP Crystal Report service pack 16, and IDE as Visual Studio 2015

and this is the code I used for get/set values for parameters on 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 report have these properties:

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

So I tried ADDED OPTIMIZE FOR UNKNOWN BOTH ON 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

Same Results, I think the from the main report are not passing through the sub report, because it always work only on Preview, but on runtime just stuck on loading.

I Tried sp_who2 Active to check if blocked.

btw I'm using this connection, on my App.config if this 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 database permissions on master

Answer 1

I had a situation like this one day. The main issue was in incorrect execution plan for different parameters in stored procedure.

It can be fixed by OPTION (OPTIMIZE FOR (@param UNKNOWN). You can read all about in in here.

The second one: there can be some read locks in queries. You can use standard WITH(NOLOCK) hint (READ UNCOMMITTED isolation level), if report data is not so sensitive.

Try to modify 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 main query for same issues.

P.S. Anyway check the SQL Profiler for outgoing queries.

Answer 2

Let's try to find if the issue is with stored procedure.

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

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

After running main report, when you are seeing stuck message, check contains of dbo.subReportParamsTb with nolock.

SELECT *
FROM dbo.subReportParamsTb WITH(NOLOCK);

If you see parameters are successfully passed to the SP, try to run SP independently with those parameters .

If SP doesn't produce output in limited time, you can try to troubleshoot further by looking at execution plan.

If SP runs fine, you can be sure that issue is not with SP.