title: Crystal report with linked subreport only working on Report Preview
body: <p>I used stored procedure with sql parameters with my main report which is working.</p>
  
  <p><a href="https://i.stack.imgur.com/brE9N.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/brE9N.jpg" alt="enter image description here"></a></p>
  
  <p>my stored procedure  for my main report 
  <a href="https://i.stack.imgur.com/5U7Bv.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5U7Bv.jpg" alt="enter image description here"></a></p>
  
  <p>then I tried to add subreport inside the working report with the following stored procedure</p>
  
  <p><a href="https://i.stack.imgur.com/NH1AK.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NH1AK.jpg" alt="enter image description here"></a></p>
  
  <p>then link Subreport field parameters to my Main</p>
  
  <p><a href="https://i.stack.imgur.com/bFjea.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bFjea.jpg" alt="enter image description here"></a></p>
  
  <p>I checked the Main Report Preview if subreport are working</p>
  
  <p><a href="https://i.stack.imgur.com/uZ6e9.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/uZ6e9.jpg" alt="enter image description here"></a></p>
  
  <p>Then I run the report but all I see is this
  <a href="https://i.stack.imgur.com/NRvJv.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NRvJv.jpg" alt="enter image description here"></a>
  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.</p>
  
  <p>so I tried removing the field parameters from the Sub Report as well as links and re-run the report. </p>
  
  <p><a href="https://i.stack.imgur.com/R7gb4.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/R7gb4.jpg" alt="enter image description here"></a></p>
  
  <p>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?</p>
  
  <p>for more info I'm using SAP Crystal Report service pack 16, and IDE as Visual Studio 2015 </p>
  
  <p>and this is the code I used for get/set values for parameters on the Main Report</p>
  
  <pre><code> 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;
      }
  </code></pre>
  
  <p>and both of my report have these properties:</p>
  
  <pre><code>Build Action: Compile
  Copy to Output Directory: Copy Always
  Custom Tool: 
  Custom Tool Name: 
  </code></pre>
  
  <p>So I tried ADDED OPTIMIZE FOR UNKNOWN BOTH ON MY MAIN SP AND SUB SP
  MAIN:</p>
  
  <pre><code>  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) &gt; 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
  </code></pre>
  
  <p>SUBREPORT:</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>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.</p>
  
  <p>I Tried sp_who2 Active to check if blocked.
  <a href="https://i.stack.imgur.com/8xHFQ.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8xHFQ.jpg" alt="enter image description here"></a></p>
  
  <p>btw I'm using this connection, on my App.config if this helps..</p>
  
  <pre><code> &lt;?xml version="1.0" encoding="utf-8" ?&gt;
   &lt;configuration&gt;
  &lt;startup useLegacyV2RuntimeActivationPolicy="true"&gt; 
      &lt;supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" /&gt;
  &lt;/startup&gt;
  &lt;runtime&gt;
    &lt;assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"&gt;
      &lt;probing privatePath="lib" /&gt;
    &lt;/assemblyBinding&gt;
  &lt;/runtime&gt;
  &lt;connectionStrings&gt;
    &lt;add name="CSPOS.Reports.Properties.Settings.chaplinConnectionString" connectionString="Data Source=RENZ\SQLEXPRESS;Initial Catalog=erika;Integrated Security=True" providerName="System.Data.SqlClient"/&gt;
   &lt;/connectionStrings&gt;
   &lt;/configuration&gt;
  </code></pre>
  
  <p>I Checked database permissions on master</p>
  
  <p><a href="https://i.stack.imgur.com/Dk6Wu.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Dk6Wu.jpg" alt="enter image description here"></a></p>