Displaying Data with Summary rows

[henna]

Hi,
I have a database table which I want to display in the following format. A summary header with running totals.

10 August 1975
Description TotalExpense
aasdasd $1.50
asdasd $2.50
total $4.00

11 August 1976
Description TotalExpense
aasdasd $1.99
asdasd $2.50
total $4.00

Is there a way to display this information using datalist or repeaters or SQL queries.

 

[firstdivision]

Hi,

There's a couple ways I've done this in the past. Depending on what you're doing using "WITH ROLLUP" might be the easiest solution. Somehting like:

SELECT Description,Sum(Expense) AS TotalExpense WITH ROLLUP;

Another way is to use a datadapter to fill a dataTable and then loop through the dataTable and detect the change in the value (Decscription in this case). When you see a change, insert a new row into the datatable and insert your running total into it. Then you can bind the now modified datatable to a datagrid.

 

[firstdivision]

I should mention, I think WITH ROLLUP might only be available in SQL Server. Depending on what DBMS you're using, there might be a similar statment you can execute.

 

[henna]

Hi,

Thanks for your reply. I am using SQL Server and also have tried the Rollup method.

My main problem is how to get the summary header information. eg the Date Header. I have also tried the DataReader loop but havnt been able to get the results.

I would really appreciate if you could send me source code sample for the DataTable loop and how you display the Header summary.

Once again, thanks for your quick response.

 

[firstdivision]

Hmm, yeah I guess I kinda ignored the whole part about the date headers huh?

One thing I can think of probably isn't the most efficient solution, but I think it will work. You can place a label and datagrid in a template column of another datagrid. You can bind the "outside" datagrid to a query that would return only the dates that you are interested in.

Then in the outside datagrid's ItemDataBound event, you can use the date that was returned to bind the "inside" datagrid to a query that will return the sums and descriptions for products associated with that date.

The downside is that you're going to hit the database once for a list of dates, and then again for each date (to fill the inside datagrid). An alternative would be to get the whoe dataset at once, and then use the DataTable.Select() method to select only the rows from the datatable during each ItemDataBound event (You'd have to declare the datatable at the page level I think).

I'm sorry I don't have any code here (no VS at home ), but I'll try to put something here that will get the idea across hopefully.

Dim decSumAmount as Decimal
Dim LastDate as Date
Dim i as Integer
Dim sqlQuery as String = "SELECT Date,Description,SUM(Amount) FROM aTable GROUP BY Date,Description"
Dim MyDataAdapter as new sqlDataAdapter(sqlQuery,SqlConnection1)


sqlconnection1.Open
MyDataAdapter.Fill(MyDataTable)
SqlConnection1.Close

For i = 0 to MyDataTable.Rows.Count - 1
  If myDataTable.Cells("Date") <> LastDate Then
    'this is a new row
     Dim MyDataRow as new DataRow
     MyDataRow = MyDataTable.NewRow  '(maybe MyDataTable.Rows.NewRow ? )
     MyDataRow.Cells("Amount") = decSumAmount
     MyDataTable.Rows.InsertAt(MyDataRow,i)
  Else 'add to the running total
     decSumAmount =+ MyDataTable.Cells(1)
     LastDate = MyDataTable.Cells("Date")
  End If
Next

MyDataGrid.DataSource = MyDataTable
MyDataGrid.DataBind

Or something like that. P-) I hope that isn't too full of errors. lol

FD

 

[henna]

Great - it works now.
Thanks a lot for your help.

 

[ca8msm]

Another example can be found at the following URL and uses the Repeater control:

http://www.dotnetjunkies.com/Tutorial/4365153F-D2F5-4C77-9FE7-94DD1AAF6F98.dcik

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.