|
 |
aspx_beginners thread: Building report to display data from SP
Message #1 by "Tim Farrell" <timothy.farrell@c...> on Tue, 15 Oct 2002 18:25:27
|
|
I have a stored procedure that captures data from a table:
SELECT Component, Activity, COUNT(*) AS [Total Records], SUM(Hours) AS
Hours
FROM LIEF_tbl
WHERE (Component = 1)
GROUP BY Component, Activity WITH ROLLUP
ORDER BY Component, Activity
What I want to be able to do is use this procedure to generate a report.
I'm not sure whether to use a Repeater Control or DataList. Perhaps I
should use Crystal Reports?
What I want the report to look like:
Component AN
--------------------------------------
Activity Records Hours
ADM 21 160
CMD 135 250
INSP 48 48
--------------------------------------
Total Hours: 458
There may be as many 50 different Components to the report will need to
repeat in some fashion.
Could someone point me in the right direction?
Thanks a lot.
Tim
Message #2 by "John Tyson" <jtyson@t...> on Tue, 15 Oct 2002 10:21:30 -0700
|
|
Here is a VB example using a repeater to group data by a category. I'm
sure you could extend it to include multiple fields. (It's kinda messy
right now with multiple command objects, but it gives you the idea of
how to fill the dataset and return nested repeaters).
<script language=3D"VB" runat=3D"server">
Sub Page_Load(s As Object, e As EventArgs)
Dim dbconn As SqlConnection =3D New SqlConnection _
(ConfigurationSettings.AppSettings("ConnectionString2"))
Dim cmd As SqlDataAdapter
Dim ntwrkParam As SqlParameter =3D Nothing
cmd =3D New SqlDataAdapter("sp_getFacilities_434", dbconn)
cmd.SelectCommand.CommandType =3D CommandType.StoredProcedure
ntwrkParam =3D New SqlParameter("@network", SqlDbType.nVarChar,
4)
ntwrkParam.Direction =3D ParameterDirection.Input
ntwrkParam.Value =3D CStr(Request.Params("id"))
cmd.SelectCommand.Parameters.Add(ntwrkParam)
dbconn.Open()
Dim ds =3D New DataSet()
cmd.Fill(ds, "Facilities")
Dim cmd2 As SqlDataAdapter
cmd2 =3D New SqlDataAdapter("sp_getStates_434", dbconn)
cmd2.SelectCommand.CommandType =3D CommandType.StoredProcedure
cmd2.Fill(ds, "States")
dim parentCol As dataColumn
dim childCol As dataColumn
dim objRelation as DataRelation
parentCol =3D ds.Tables("States").Columns("stateID")
childCol =3D ds.Tables("Facilities").Columns("state")
objRelation =3D new dataRelation("Relation", parentCol,
childCol)
ds.Relations.Add(objRelation)
rptState.DataSource =3D ds.Tables("States")
Page.DataBind()
dbconn.close()
End Sub
</script>
<body>
<form runat=3D"server">
<!-- start parent repeater -->
<asp:Repeater id=3D"rptState" runat=3D"server">
<itemtemplate>
<ul><%# DataBinder.Eval(Container.DataItem, "stateName") %>
<!-- start child repeater -->
<asp:Repeater id=3D"rptFacility" runat=3D"server"
datasource=3D'<%#
CType(Container.DataItem,
System.Data.DataRowView).row.GetChildRows("Relation") %>'>
<itemtemplate>
<li>
<%# DataBinder.Eval(Container.DataItem,
"[facName_short]") %><br>
</li>
</itemtemplate>
</asp:Repeater>
<!-- end child repeater -->
</ul>
</itemtemplate>
</asp:Repeater>
<!-- end parent repeater -->
</form>
</body>
</html>
-----Original Message-----
From: Tim Farrell [mailto:timothy.farrell@c...]
Sent: Tuesday, October 15, 2002 11:25 AM
To: aspx_beginners
Subject: [aspx_beginners] Building report to display data from SP
I have a stored procedure that captures data from a table:
SELECT Component, Activity, COUNT(*) AS [Total Records], SUM(Hours)
AS
Hours
FROM LIEF_tbl
WHERE (Component =3D 1)
GROUP BY Component, Activity WITH ROLLUP
ORDER BY Component, Activity
What I want to be able to do is use this procedure to generate a report.
I'm not sure whether to use a Repeater Control or DataList. Perhaps I
should use Crystal Reports?
What I want the report to look like:
Component AN
--------------------------------------
Activity Records Hours
ADM 21 160
CMD 135 250
INSP 48 48
--------------------------------------
Total Hours: 458
There may be as many 50 different Components to the report will need to
repeat in some fashion.
Could someone point me in the right direction?
Thanks a lot.
Tim
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=3D1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=3D1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
|
|
|
View Cart
