Dataset to a table

Discussion in 'Software' started by jcast, May 8, 2012.

  1. jcast

    jcast Private E-2

    I created a store procedure and now I need to display the dataset into my page.
    The store procedure has two queries in a union


    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

    This is returning the following results:
    Count YearMonth1 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.6296 AccountCredit 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.4796 AccountCredit 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

    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, but I need to fix it.
    <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();


    }
    }

    How can I return my data the way I want it in my app.

    Please help.

    thanks
     

MajorGeeks.Com Menu

Downloads All In One Tweaks \ Android \ Anti-Malware \ Anti-Virus \ Appearance \ Backup \ Browsers \ CD\DVD\Blu-Ray \ Covert Ops \ Drive Utilities \ Drivers \ Graphics \ Internet Tools \ Multimedia \ Networking \ Office Tools \ PC Games \ System Tools \ Mac/Apple/Ipad Downloads

Other News: Top Downloads \ News (Tech) \ Off Base (Other Websites News) \ Way Off Base (Offbeat Stories and Pics)

Social: Facebook \ YouTube \ Twitter \ Tumblr \ Pintrest \ RSS Feeds