group total in a report

Discussion in 'ASP General' started by Rajani, Feb 26, 2005.

  1. Rajani

    Rajani Guest

    Hello,

    I have a table(msaccess) with the structure...
    job_code text 6
    style text 10
    qty number
    fabrication text 65
    ship_date date/time


    I want to show a report like...

    job_code style qty fabrication ship_date
    ------------------------------------------------------
    05A001 2464 75000 AAAAAAA 5-Jan-2005
    05A002 3800 93430 BBBBBBB 5-Jan-2005
    05B001 2845 4900 CCCCCCC 5-Jan-2005

    ----------
    173330
    05A003 2564 45000 DDDDDDD 5-Feb-2005
    05B002 2800 65000 EEEEEEE 15-Feb-2005
    05C001 P357G 5500 FFFFFFF 15-Feb-2005
    ------------
    115500

    .....

    What could be the query and how can i show an HTML report. I am using ADO
    with ASP and VBScript.


    thanx in advance
    Rajani, Feb 26, 2005
    #1
    1. Advertising

  2. Rajani wrote:
    > Hello,
    >
    > I have a table(msaccess) with the structure...
    > job_code text 6
    > style text 10
    > qty number
    > fabrication text 65
    > ship_date date/time
    >
    >
    > I want to show a report like...
    >
    > job_code style qty fabrication ship_date
    > ------------------------------------------------------
    > 05A001 2464 75000 AAAAAAA 5-Jan-2005
    > 05A002 3800 93430 BBBBBBB 5-Jan-2005
    > 05B001 2845 4900 CCCCCCC 5-Jan-2005
    >
    > ----------
    > 173330
    > 05A003 2564 45000 DDDDDDD 5-Feb-2005
    > 05B002 2800 65000 EEEEEEE 15-Feb-2005
    > 05C001 P357G 5500 FFFFFFF 15-Feb-2005
    > ------------
    > 115500
    >
    > ....
    >
    > What could be the query and how can i show an HTML report. I am using
    > ADO with ASP and VBScript.
    >
    >
    > thanx in advance


    You can use a UNION query inside a subquery for this:

    Select job_code, style, qty, fabrication,
    ship_date FROM
    (
    Select 'Detail' As RowType, job_code, style, qty, fabrication,
    ship_date, Year(ship_date) As ShipYear,
    Month(ship_date) As ShipMonth
    FROM Shipments
    UNION ALL
    Select 'Total' As RowType,'Total','',Sum(qty),'',Null,Year(ship_date),
    Month(ship_date)
    FROM Shipments
    GROUP BY Year(ship_date),Month(ship_date)
    ) As q
    ORDER BY ShipYear, ShipMonth, RowType, ship_date



    I would create a saved query with this SQL and save it as qShipmentReport.
    If you need to limit the time period used for the report, then you can add
    parameters, like this:

    Select job_code, style, qty, fabrication,
    ship_date FROM
    (
    Select 'Detail' As RowType, job_code, style, qty, fabrication,
    ship_date, Year(ship_date) As ShipYear,
    Month(ship_date) As ShipMonth
    FROM Shipments
    WHERE ship_date BETWEEN [pStart] AND [pYear]
    UNION ALL
    Select 'Total' As RowType,'Total','',Sum(qty),'',Null,
    Year(ship_date),Month(ship_date)
    FROM Shipments
    WHERE ship_date BETWEEN [pStart] AND [pYear]
    GROUP BY Year(ship_date),Month(ship_date)
    ) As q
    ORDER BY ShipYear, ShipMonth, RowType, ship_date


    The ASP code to display the report would look like this (tested in IE6 - you
    may need to tweak it for other browsers. If so, follow up in a client-side
    newsgroup):

    <%@ Language=VBScript %>
    <%
    dim sHTML, dStart, dEnd, cn, rs, arData, sCellTagSt, sCellTagEnd
    dim iRow, iCol
    dStart=Request.Form("StartDate")
    dEnd=Request.Form("EndDate")
    if len(dStart) > 0 AND len(dEnd)>0 Then
    if IsDate(dStart) AND IsDate(dEnd) then
    dStart=cdate(dStart)
    dEnd=cdate(dEnd)
    Set cn=CreateObject("adodb.connection")
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=P:\ath\to\database.mdb"
    set rs=CreateObject("adodb.recordset")
    cn.qShipmentReport dStart,dEnd, rs
    if not rs.EOF then arData=rs.GetRows
    rs.Close:set rs=nothing
    cn.Close:set cn=nothing
    if IsArray(arData) then
    for iRow = 0 to UBound(arData,2)
    if len(sHTML) = 0 then
    sHTML= "<TR>"
    else
    sHTML=sHTML & "<TR>"
    end if
    if arData(0,iRow)="Total" then
    sCellTagSt="<TH>"
    sCellTagEnd="</TH>"
    else
    sCellTagSt="<TD>"
    sCellTagEnd="</TD>"
    end if
    for iCol=0 to UBound(arData,1)
    sHTML=sHTML & sCellTagSt & arData(iCol,iRow) & _
    sCellTagEnd
    next
    sHTML=sHTML & "</TR>"
    next
    else
    sHTML="<tr><td colspan=5>No data was found in this " & _
    "date range</td></tr>"
    end if
    else
    sHTML="<tr><td colspan=5>Invalid Date Entered." & _
    "Date must be in YYYY-MM-DD format. Ex. 2004-12-01</td></tr>"
    end if
    End if
    %>
    <HTML>
    <HEAD>
    <TITLE>Shipment Report</TITLE>
    </HEAD>
    <BODY>
    <H1>Shipment Report</H1>
    <form method="post">
    <span
    style="margin-right:20px;font: normal normal bold x-small Arial">
    Start Date (YYYY-MM-DD): <input name="StartDate"
    value="<%=Request.Form("StartDate")%>">
    </span>
    <span style="font: normal normal bold x-small Arial">
    End Date (YYYY-MM-DD): <input name="EndDate"
    value="<%=Request.Form("EndDate")%>">
    </span>
    <input type="submit" value="Submit">
    <input type="button" value="Clear"
    onclick="document.getElementById('StartDate').value='';
    document.getElementById('EndDate').value='';
    document.forms(0).submit()">
    </form>
    <table border="1"
    style="border-color:black;border-collapse:collapse">
    <TR style="background-color:lightblue">
    <TH>Job Code</TH>
    <TH>Style</TH>
    <TH>Quantity</TH>
    <TH>Fabrication</TH>
    <TH>Ship Date</TH>
    </TR>
    <%=sHTML%>
    </table>
    </BODY>
    </HTML>

    HTH,
    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Feb 26, 2005
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Meheraj shaik via .NET 247

    Crysatl report load report failed

    Meheraj shaik via .NET 247, May 28, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    3,242
    saapr
    Jun 7, 2005
  2. James Wallace
    Replies:
    0
    Views:
    1,051
    James Wallace
    Oct 17, 2003
  3. =?Utf-8?B?aHVicmlzdGljYm9i?=

    Load Report Fails on ASP.NET app when report is on a different ser

    =?Utf-8?B?aHVicmlzdGljYm9i?=, Mar 17, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    411
    Brock Allen
    Mar 17, 2005
  4. Mark
    Replies:
    0
    Views:
    677
  5. yonialhadeff

    Group By Rows and Total Rows

    yonialhadeff, Jan 23, 2007, in forum: ASP .Net Datagrid Control
    Replies:
    1
    Views:
    847
    Eliyahu Goldin
    Jan 23, 2007
Loading...

Share This Page