title: Querying with SQL Server for SSRS
body: <p>I am developing a SSRS report and need help with the dataset queries. I am not sure how to write the query to achieve the desired data.</p>
  
  <p>Query1:</p>
  
  <pre><code>SELECT DISTINCT FullDate,
    SUM([Gross]) AS Gross
    ,StoreName
  
  FROM [T1] inner join [T2]
  on T1.StoreKey = T2.StoreKey
  
  inner join T3
  on T1.DateKey = T3.DateKey
  
  where [Store] = 'B'
  and FullDate = '2016-10-24'
  
  group by FullDate,
  StoreName
  </code></pre>
  
  <p>The result for this is:</p>
  
  <pre><code> FullDate     Gross      StoreName
  2016-10-24   2621.89          B
  </code></pre>
  
  <p>Query2:</p>
  
  <pre><code>SELECT DISTINCT FullDate,SUM(TotalCost) AS TotalCost,
    SUM([Gross]) AS Gross
    ,StoreName
  
  FROM [T1] inner join [T2]
  on T1.StoreKey = T2.Storekey
  
  inner join T3
  on T1.DateKey = T3.DateKey
  
  inner join T4
  on T1.DateKey = T4.DateKey
  and T1.StoreKey = T4.StoreKey
  
  
  where [Store] = 'B'
  and FullDate = '2016-10-24'
  
  group by FullDate,
  StoreName
  </code></pre>
  
  <p>The result for this is:</p>
  
  <pre><code>FullDate      TotalCost     Gross        StoreName
  2016-10-24       5         20060.12         B
  </code></pre>
  
  <p>I want to achieve 2621.89 with query2 so that I can use it in one table for SSRS.</p>