Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Crosstab query output

Reply
Thread Tools

Crosstab query output

 
 
PW
Guest
Posts: n/a
 
      05-19-2006

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





 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      05-19-2006
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"


 
Reply With Quote
 
 
 
 
PW
Guest
Posts: n/a
 
      05-20-2006

"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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.




 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Crosstab Query in ASP.Net Page j.t.w ASP .Net 2 02-05-2008 09:55 AM
Best way to show a crosstab in gridview ( m x n table of checkboxes) H5N1 ASP .Net 0 07-29-2006 11:38 PM
How to generate data driven HTML Table like CrossTab Kamal Ahmed ASP .Net 0 06-28-2006 02:00 PM
Crosstab query output PW ASP .Net 0 05-19-2006 02:22 AM
Format data in dynamically generated columns from crosstab query joshblair ASP .Net Datagrid Control 4 01-16-2006 11:02 PM



Advertisments