can i have data of two datasets into a single table

In Report, I need to display PO Details such as pono,item names and
their item qty,item rate, po amount, dcno., dcdate.
PO Details TenderName,pono,item name,item qty, item rate available in a
table tblPurchaseOrder.
PO amount=sum(item qty*item rate) + sum(charge)+ sum(item tax amount).
charge amount, tax amount may/may not exist for PO.
Each PO can have more than one DC.
using single query if i am trying to retreive pono,item name,po amount,
dc date,dcno then i am getting po amount no. of times dc
occurs*original po amount.so i splitted po details and dc details into
two. now the data i am getting is correct. but using two tables the
data is not corresponding to each other. dc details getting ups and
downs.
Kindly give me solution ...
My query to retreive PO Details is as follows :
SELECT viewPurchaseOrderComponents.TenderName,
viewPurchaseOrderComponents.PONo, viewPurchaseOrderComponents.PODate,
viewPurchaseOrderComponents.ComponentName,
viewPurchaseOrderComponents.ComponentQty,
viewPurchaseOrderComponents.Rate,
viewPurchaseOrderComponents.ComponentQty *
viewPurchaseOrderComponents.Rate AS CompAmount,
viewPurchaseOrderComponents.UnitName,
B.TotalTaxAmount, A.Charge,
viewPurchaseOrderComponents.ProjectComponentId,
viewPurchaseOrderComponents.ComponentId,
viewPurchaseOrderComponents.IndentNo, tblPurchaseOrder.PaymentTerms,
tblPurchaseOrder.IndentDate,
tblPurchaseOrder.DeliveryPeriod, tblPurchaseOrder.Status,
tblPurchaseOrder.HasAnyAmendment,
tblPurchaseOrder.IsRegularize,
viewPurchaseOrderComponents.Type, viewPurchaseOrderComponents.Supplier
FROM viewPurchaseOrderComponents INNER JOIN
tblPurchaseOrder ON
viewPurchaseOrderComponents.PONo = tblPurchaseOrder.PONo LEFT OUTER
JOIN
(SELECT PONo, SUM(Amount) AS Charge
FROM tblPOCharges
GROUP BY PONo) AS A ON A.PONo = viewPurchaseOrderComponents.PONo LEFT OUTER JOIN
(SELECT POComponentId,
SUM(TaxValue) AS TotalTaxAmount
FROM tblPOComponentTaxes
GROUP BY POComponentId) AS B ON
B.POComponentId = viewPurchaseOrderComponents.ComponentId
WHERE (viewPurchaseOrderComponents.PODate >= @.StartDate) AND
(viewPurchaseOrderComponents.PODate < DATEADD(d, 1, @.EndDate))
For DC Details :
SELECT viewPurchaseOrderComponents.PONo,
viewPurchaseOrderComponents.TenderName,
viewPurchaseOrderComponents.PODate,
viewPurchaseOrderComponents.ComponentName,
tblPurchaseOrder.Status, tblPurchaseOrder.HasAnyAmendment,
tblPurchaseOrder.IsRegularize,
viewPurchaseOrderComponents.Type, viewPurchaseOrderComponents.Supplier,
tblComponentsOfDc.Qty AS DCQty,
tblDeliveryChallan.DCId, CONVERT(varchar(11),
tblDeliveryChallan.ChallanDate, 106) AS ChallanDate
FROM viewPurchaseOrderComponents LEFT OUTER JOIN
tblDeliveryChallan ON tblDeliveryChallan.PONo
= viewPurchaseOrderComponents.PONo INNER JOIN
tblPurchaseOrder ON
viewPurchaseOrderComponents.PONo = tblPurchaseOrder.PONo LEFT OUTER
JOIN
tblComponentsOfDc ON tblDeliveryChallan.DCId = tblComponentsOfDc.DCId
WHERE (viewPurchaseOrderComponents.PODate >= @.StartDate) AND
(viewPurchaseOrderComponents.PODate < DATEADD(d, 1, @.EndDate))
In a single table i need TenderName,PONO,item details name,qty,rate
POAmount, DCDate...
Waiting for solution ...Try looking at subreports. You can embed a subreport into a detail line of a
table. Add an extra detail line and then drag and drop your subreport onto
the cell. Then right mouse click, properties and set the parameters to the
field values that link the two together.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sarada" <sarada_devi_i@.yahoo.com> wrote in message
news:1155904374.494550.59260@.i3g2000cwc.googlegroups.com...
> In Report, I need to display PO Details such as pono,item names and
> their item qty,item rate, po amount, dcno., dcdate.
> PO Details TenderName,pono,item name,item qty, item rate available in a
> table tblPurchaseOrder.
> PO amount=sum(item qty*item rate) + sum(charge)+ sum(item tax amount).
> charge amount, tax amount may/may not exist for PO.
> Each PO can have more than one DC.
> using single query if i am trying to retreive pono,item name,po amount,
> dc date,dcno then i am getting po amount no. of times dc
> occurs*original po amount.so i splitted po details and dc details into
> two. now the data i am getting is correct. but using two tables the
> data is not corresponding to each other. dc details getting ups and
> downs.
> Kindly give me solution ...
> My query to retreive PO Details is as follows :
> SELECT viewPurchaseOrderComponents.TenderName,
> viewPurchaseOrderComponents.PONo, viewPurchaseOrderComponents.PODate,
> viewPurchaseOrderComponents.ComponentName,
> viewPurchaseOrderComponents.ComponentQty,
> viewPurchaseOrderComponents.Rate,
> viewPurchaseOrderComponents.ComponentQty *
> viewPurchaseOrderComponents.Rate AS CompAmount,
> viewPurchaseOrderComponents.UnitName,
> B.TotalTaxAmount, A.Charge,
> viewPurchaseOrderComponents.ProjectComponentId,
> viewPurchaseOrderComponents.ComponentId,
> viewPurchaseOrderComponents.IndentNo, tblPurchaseOrder.PaymentTerms,
> tblPurchaseOrder.IndentDate,
> tblPurchaseOrder.DeliveryPeriod, tblPurchaseOrder.Status,
> tblPurchaseOrder.HasAnyAmendment,
> tblPurchaseOrder.IsRegularize,
> viewPurchaseOrderComponents.Type, viewPurchaseOrderComponents.Supplier
> FROM viewPurchaseOrderComponents INNER JOIN
> tblPurchaseOrder ON
> viewPurchaseOrderComponents.PONo = tblPurchaseOrder.PONo LEFT OUTER
> JOIN
> (SELECT PONo, SUM(Amount) AS Charge
> FROM tblPOCharges
> GROUP BY PONo) AS A ON A.PONo => viewPurchaseOrderComponents.PONo LEFT OUTER JOIN
> (SELECT POComponentId,
> SUM(TaxValue) AS TotalTaxAmount
> FROM tblPOComponentTaxes
> GROUP BY POComponentId) AS B ON
> B.POComponentId = viewPurchaseOrderComponents.ComponentId
> WHERE (viewPurchaseOrderComponents.PODate >= @.StartDate) AND
> (viewPurchaseOrderComponents.PODate < DATEADD(d, 1, @.EndDate))
> For DC Details :
> SELECT viewPurchaseOrderComponents.PONo,
> viewPurchaseOrderComponents.TenderName,
> viewPurchaseOrderComponents.PODate,
> viewPurchaseOrderComponents.ComponentName,
> tblPurchaseOrder.Status, tblPurchaseOrder.HasAnyAmendment,
> tblPurchaseOrder.IsRegularize,
> viewPurchaseOrderComponents.Type, viewPurchaseOrderComponents.Supplier,
> tblComponentsOfDc.Qty AS DCQty,
> tblDeliveryChallan.DCId, CONVERT(varchar(11),
> tblDeliveryChallan.ChallanDate, 106) AS ChallanDate
> FROM viewPurchaseOrderComponents LEFT OUTER JOIN
> tblDeliveryChallan ON tblDeliveryChallan.PONo
> = viewPurchaseOrderComponents.PONo INNER JOIN
> tblPurchaseOrder ON
> viewPurchaseOrderComponents.PONo = tblPurchaseOrder.PONo LEFT OUTER
> JOIN
> tblComponentsOfDc ON tblDeliveryChallan.DCId => tblComponentsOfDc.DCId
> WHERE (viewPurchaseOrderComponents.PODate >= @.StartDate) AND
> (viewPurchaseOrderComponents.PODate < DATEADD(d, 1, @.EndDate))
> In a single table i need TenderName,PONO,item details name,qty,rate
> POAmount, DCDate...
> Waiting for solution ...
>