Thread: Dataset to a table

I created a store procedure(2 queries with an Union) and now I have to show the results in my web app. I was able to show the result exactly as it shows on sql. But now they want me to show the results in a different way, like manipulate the data and show the table differently. I need help with that



USE [MPData]
GO
/****** Object: StoredProcedure [dbo].[Invoices_Total] Script Date: 05/08/2012 10:41:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Invoices_Total]
@PartnerID INT
AS
BEGIN
SELECT * FROM (
select COUNT As 'Count', DATEPART(YYYY, InvoiceDt) AS 'Year', DATEPART(MM, InvoiceDt) AS 'Month1', SUM(invoiceAmount)AS 'Invoice Amount',
InvoiceType AS 'Type', InvoiceStatus AS 'Status'
from Invoice i
inner join ProductLine pl on i.ProductLineId = pl.ID
inner join Person p on pl.PersonID = p.ID
where InvoiceStatus = 'C' and p.PartnerID = @PartnerID
group by InvoiceType, InvoiceStatus, DATEPART(YYYY, InvoiceDt), DATEPART(MM, InvoiceDt)
UNION
select COUNT As 'Count', NULL AS 'Year', NULL AS 'Month1', SUM(invoiceAmount)AS 'Invoice Amount', InvoiceType AS 'Type', InvoiceStatus AS 'Status'
from Invoice i
inner join ProductLine pl on i.ProductLineId = pl.ID
inner join Person p on pl.PersonID = p.ID
where InvoiceStatus = 'C' and p.PartnerID = @PartnerID
group by InvoiceType, InvoiceStatus) AS A
ORDER BY A.Year, A.Month1
END

Store procedure is returning the following results:
Count Year Month1 Inv.Amount Type Status
1 NULL NULL 25.00 Recharge C
2 NULL NULL 0.00 Activation C
10 NULL NULL 0.00 History C
17 NULL NULL 89.6296AccountCredit C
1 2012 3 25.00 Recharge C
5 2012 3 11.75 AccountCredit C
2 2012 4 0.00 Activation C
8 2012 4 47.4796AccountCredit C
4 2012 5 30.40 AccountCredit C
10 2012 5 0.00 History C

I create my dataset and now I am returninig the data from the way it is showing here, the problem is that I need to display the data in a different way, as you can see the first 4 lines are the totals of the next 6 lines.

well based on this data I need to return something like this, basically the totals for each month depending of the type

Total Mar 2012 Apr 2012 May 2012
Recharge 25.00 25.00 0.00 0.00
AccountCredit 89.6296 11.75 47.4796 30.40
History 0.00 0.00 0.00 0.00
Activation 0.00 0.00 0.00 0.00

this is how I am getting the data into my web app so far

<table class="formTbl">
<tr><th colspan="2" class="formHeader">Invoice Summary</th></tr>
<asp:Repeater runat="server" ID="repInvoiceSummary">
<HeaderTemplate>
<tr>
<td></td>
<td>Total</td>
<td>Mar 2012</td>
<td>Apr 2012</td>
<td>May 2012</td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<th>
<%# ((DataRowView)Container.DataItem)[4] %>
</th>
<th>
<%# ((DataRowView)Container.DataItem)[0] %>
</th>
<td>
<%# ((DataRowView)Container.DataItem)[1]%>
</td>
<th>
<%# ((DataRowView)Container.DataItem)[2] %>
</th>
<td>
<%# ((DataRowView)Container.DataItem)[3] %>
</td>
<th>
<%# ((DataRowView)Container.DataItem)[5] %>
</th>
</tr>
</ItemTemplate>
</asp:Repeater>
</table>

==================
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
var reportsSvc = new Shared.Core.Reports.ReportSvc();

// Invoice Summary
var dashboardInfoInvoices = reportsSvc.DashboardParnterInvoices();
repInvoiceSummary.DataSource = dashboardInfo.InvoicesTotal;
repInvoiceSummary.DataBind();

//


}
}