Crosstab query output

Discussion in 'ASP General' started by PW, May 19, 2006.

  1. PW

    PW Guest

    I am trying to create a crosstab report in ASP. I'm using ASP/VBScript and
    Access database. I used Access to create the SQL query, then copied the SQL
    and embedded it in my ASP like this ...

    mySQL = ""
    mySQL = mySQL & "TRANSFORM Sum(Transactions.Hours) AS SumOfHours "
    mySQL = mySQL & "SELECT Transactions.Activity" & myActSecond & " as
    myActivity, Sum(Transactions.Hours) AS myHours "
    mySQL = mySQL & "FROM Transactions "
    mySQL = mySQL & "WHERE CommDate >= #" & myDateFr & "# AND CommDate <= #" &
    myDateTo & "# "
    mySQL = mySQL & "GROUP BY Transactions.Activity" & myActSecond & " "
    mySQL = mySQL & "PIVOT Transactions.Activity" & myActFirst

    When I try to output the results, I do it like this ...

    rs1.open mySQL,mydsn
    Do while NOT rs1.EOF
    response.write rs1("myActivity")
    response.write ", "
    response.write rs1("myHours")
    response.write "<br>"
    rs1.movenext
    Loop

    So all I get as output is a list of the "myActivity" and values of
    "myHours".

    How do I get the other axis as column headings across the top?


    TIA,
    PW
     
    PW, May 19, 2006
    #1
    1. Advertising

  2. PW wrote:
    > I am trying to create a crosstab report in ASP. I'm using
    > ASP/VBScript and Access database. I used Access to create the SQL
    > query, then copied the SQL and embedded it in my ASP like this ...
    >

    <snip of ugly concatenation>
    > When I try to output the results, I do it like this ...
    >

    Try this:
    Save your crosstab query in Access, naming it (for the sake of this example)
    "TransactionsCrosstab". Then use this code in your page:

    <%
    dim cn, rs
    set cn = Server.CreateObject("ADODB.Connection")
    set rs = Server.CreateObject("ADODB.Recordset")
    rs.CursorLocation=adUseClient
    cn.Open "provider=microsoft.jet.oledb.4.0;data source=" & _
    server.MapPath("dbname.mdb")
    cn.TransactionsCrosstab rs
    set rs.ActiveConnection=nothing
    cn.Close:set cn=nothing
    dim fld,i,val
    %>
    <table border="1" cellspacing="0"><tr>
    <%
    for each fld in rs.Fields
    Response.Write "<th>" & fld.name & "</th>"
    next
    Response.Write "</tr>"
    do until rs.EOF
    Response.Write "<tr>"
    for i=0 to rs.Fields.count - 1
    val=rs(i).Value & ""
    if len(val) = 0 then val="&nbsp;"
    Response.Write "<td>" & val & "</td>"
    next
    Response.Write "</tr>"
    rs.MoveNext
    loop
    rs.Close:set rs=nothing
    %>
    </table>
    --
    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], May 19, 2006
    #2
    1. Advertising

  3. PW

    PW Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:%...
    > PW wrote:
    >> I am trying to create a crosstab report in ASP. I'm using
    >> ASP/VBScript and Access database. I used Access to create the SQL
    >> query, then copied the SQL and embedded it in my ASP like this ...
    >>

    > <snip of ugly concatenation>
    >> When I try to output the results, I do it like this ...
    >>

    > Try this:
    > Save your crosstab query in Access, naming it (for the sake of this
    > example) "TransactionsCrosstab". Then use this code in your page:
    >
    > <%
    > dim cn, rs
    > set cn = Server.CreateObject("ADODB.Connection")
    > set rs = Server.CreateObject("ADODB.Recordset")
    > rs.CursorLocation=adUseClient
    > cn.Open "provider=microsoft.jet.oledb.4.0;data source=" & _
    > server.MapPath("dbname.mdb")
    > cn.TransactionsCrosstab rs
    > set rs.ActiveConnection=nothing
    > cn.Close:set cn=nothing
    > dim fld,i,val
    > %>
    > <table border="1" cellspacing="0"><tr>
    > <%
    > for each fld in rs.Fields
    > Response.Write "<th>" & fld.name & "</th>"
    > next
    > Response.Write "</tr>"
    > do until rs.EOF
    > Response.Write "<tr>"
    > for i=0 to rs.Fields.count - 1
    > val=rs(i).Value & ""
    > if len(val) = 0 then val="&nbsp;"
    > Response.Write "<td>" & val & "</td>"
    > next
    > Response.Write "</tr>"
    > rs.MoveNext
    > loop
    > rs.Close:set rs=nothing
    > %>
    > </table>
    > --
    > 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"
    >



    Thanks Bob. I implemented your example into my existing code and it works
    great. Much obliged.
     
    PW, May 20, 2006
    #3
    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. PW

    Crosstab query output

    PW, May 19, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    1,982
  2. Kamal Ahmed
    Replies:
    0
    Views:
    495
    Kamal Ahmed
    Jun 28, 2006
  3. H5N1
    Replies:
    0
    Views:
    3,598
  4. j.t.w

    Crosstab Query in ASP.Net Page

    j.t.w, Jan 25, 2008, in forum: ASP .Net
    Replies:
    2
    Views:
    3,913
    j.t.w
    Feb 5, 2008
  5. joshblair

    Format data in dynamically generated columns from crosstab query

    joshblair, Jan 12, 2006, in forum: ASP .Net Datagrid Control
    Replies:
    4
    Views:
    298
    joshblair
    Jan 16, 2006
Loading...

Share This Page