title: how to show sales summary in rdlc from four sql table using sql
body: <p>I am having bad times to generate a rdlc report to show report in predefined format given by my client.</p>
  
  <p><strong>Client given format</strong>
  <a href="https://i.stack.imgur.com/iBioH.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/iBioH.png" alt="Output format"></a>
  </p>
  
  <p>As you can notice RED background is my problem area, Item "A" price has been changed thrice. And accordingly their sales, breakages and refund quantity is shown. I have 4 tables Products, SalesLog, Breakages, SalesReturn. Product table consists latest(current) price of an item but SalesLog, Breakages and SalesReturn tables consists price at the time of Sales, Breakages and Sales Return. I have written queries for SalesLog, Breakges and SalesReturn which shows rows according to ProductCode, Price and Quantity. But i don't understand how I can merge their (4 tables output) output to show below result.</p>
  
  <p><strong>Query for SalesLog</strong></p>
  
  <pre><code>SELECT  [Products].pCode AS Code,
      [Products].pName AS Item,
      SalesLog.[Price] AS Price ,
      COUNT(*)AS Quantity ,
      SUM(SalesLog.[Price]) AS Total
  FROM    SalesLog
      INNER JOIN [Products] ON [Products].pCode = SalesLog.ProductCode
  WHERE   BillDate = '07/01/2010'
  GROUP BY [Products].pCode,[Products].pName ,
      SalesLog.[Price]
  </code></pre>
  
  <p><strong>Query for Breakages</strong></p>
  
  <pre><code>SELECT  [Products].pCode AS Code,
      [Products].pName AS Item,
      Breakages.[Price] AS Price ,
      COUNT(*)AS Quantity ,
      SUM(Breakages.[Price]) AS Total
  FROM    Breakages
      INNER JOIN [Products] ON [Products].pCode = Breakages.ProductCode
  WHERE   BillDate = '07/01/2010'
  GROUP BY [Products].pCode,[Products].pName ,
      Breakages.[Price]
  </code></pre>
  
  <p><strong>Query for SalesReturn</strong></p>
  
  <pre><code>SELECT  [Products].pCode AS Code,
      [Products].pName AS Item,
      Breakages.[Price] AS Price ,
      COUNT(*)AS Quantity ,
      SUM(Breakages.[Price]) AS Total
  FROM    Breakages
      INNER JOIN [Products] ON [Products].pCode = Breakages.ProductCode
  WHERE   BillDate = '07/01/2010'
  GROUP BY [Products].pCode,[Products].pName ,
      Breakages.[Price]
  </code></pre>
  
  <p><strong>Product Table</strong></p>
  
  <pre><code>CREATE TABLE [dbo].[Products](
      [ProductId] [int] IDENTITY(1,1) NOT NULL,
      [pName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [pSize] [int] NULL,
      [pPrice] [decimal](10, 2) NULL,
      [pPackQty] [int] NULL,
      [pGroup] [int] NULL,
      [pCode] [int] NULL,
      [OpenStock] [int] NULL,
      [CloseStock] [int] NULL,
      [YrlyOpenStock] [int] NULL,
  CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
  (    
      [ProductId] ASC
  )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
  ) ON [PRIMARY]
  </code></pre>
  
  <p>Can anyone help me, or suggest any trick to accomplish this.</p>
  
  <p>thanks in advance.....</p>