Best way to display data

[knappster]

Hi,
I have a database with a table containing data which contains a date for each entry. There will be about 5-10 entries for a day. What I want to do is display the data grouped by date plus a totals row for each day.
e.g.

18/08/07
data1 data2 data3 data4 data5 data6
data1 data2 data3 data4 data5 data6
data1 data2 data3 data4 data5 data6

total1 total2 total3 total4 total5 total6


19/08/07
data1 data2 data3 data4 data5 data6
data1 data2 data3 data4 data5 data6
data1 data2 data3 data4 data5 data6
data1 data2 data3 data4 data5 data6
data1 data2 data3 data4 data5 data6
data1 data2 data3 data4 data5 data6
data1 data2 data3 data4 data5 data6

total1 total2 total3 total4 total5 total6


20/08/07
data1 data2 data3 data4 data5 data6
data1 data2 data3 data4 data5 data6
data1 data2 data3 data4 data5 data6
data1 data2 data3 data4 data5 data6
data1 data2 data3 data4 data5 data6

total1 total2 total3 total4 total5 total6


What's the best way of doing this? I take it a datagrid is out of the question because of the date and totals row, so do I need to use a listview? Also is it possible to have it so that the data can be expanded or collapsed by clicking on the date?

 

[JohnH]

A Listview can display items in groups, but not expand/collapse, to do this the easiest is to use a Treeview for grouping and expand/collapse and a tabular control like datagridview/listview for data display. There also exist third-party custom controls for various combinations of tree and grid.

 

[JuggaloBrotha]

I would have either a Treeview or Combobox for the date (Treeview would make more sense) then use a ListView for the actual data for the selected date

 

[knappster]

Thanks for your answers, the Treeview's a good idea for the date so I might give that a go, along with a listview to then display the data.
Cheers!

 

[cjard]

Hi,
I have a database with a table containing data which contains a date for each entry. There will be about 5-10 entries for a day. What I want to do is display the data grouped by date

From what I can see, the items arent actually "grouped by date" - they are "ordered by date"

plus a totals row for each day.

In database terminology, that's where the grouping would come in


To have the database pull data in this fashion, you would arange a query that:

selects an incremental counter form any table with a lot of rows, limited by the number of days you want to search for, and adds this incremental counter to the starting date of the range.

For example in oracle, if I wanted a column of 10 days from 01 sep to 09 sep, I would do:

SELECT :START_DATE + rownum FROM someTable WHERE rownum < 11

This would give me 10 rows, :START_DATE is a date parameter set to the 31 august. Because in oracle a date + an integer does day addition, we effectively get, for the first 10 rows of SomeTable:
Aug31 +1
Aug31 +2
Aug31 +3

...

This gives us a discrete range of dates from 01 sep to 09 sep

I would then group and sum the data you want for the totals:

SELECT SUM(data1) as d1, SUM(data2) as d2 .. FROM SomeTable WHERE theDate BETWEEN :START_DATE and :END_DATE GROUP BY TRUNC(theDate)


TRUNC removes any time portion of a date, leaving just the date, so all entries sum correctly

Then to this we union the raw data:

UNION ALL
SELECT data1, data2 ... FROM SomeTable


Finally we left join this union to our original date range:

SELECT * FROM

(SELECT :START_DATE + rownum FROM someTable WHERE rownum < 11) dateRange
LEFT OUTER JOIN
(
SELECT SUM(data1) as d1, SUM(data2) as d2 .. FROM SomeTable WHERE theDate BETWEEN :START_DATE and :END_DATE GROUP BY TRUNC(theDate)
UNION ALL
SELECT data1, data2 ... FROM SomeTable WHERE theDate BETWEEN :START_DATE and :END_DATE
) data
ON
  dateRange.theDate = data.theDate
ORDER BY dateRange.theDate

The data we get out looks like:

01Sep07, 1, 2
01Sep07, 1, 2
01Sep07, 1, 2
01Sep07, 3, 6
02Sep07, 5, 3
02Sep07, 5, 3
02Sep07, 5, 3
02Sep07, 15, 9
03Sep07,, <-- if there is no data for 3rd sept

This can be pulling into a tree/grid like john says, with the date forming the tree node and the rest of the data showing in a grid

If you want to get really smart, you can do stuff like this:

SELECT CASE WHEN evenOrOdd = 'even' THEN dateRange.TheDate ELSE d1, d2, ... FROM

(SELECT CASE WHEN MOD(rowNum, 2) = 0 THEn 'even' ELSE 'odd' END as evenOrOdd, :START_DATE + TRUNC(rownum/2) as theDate FROM someTable WHERE rownum < 22) dateRange
LEFT OUTER JOIN
(
SELECT 'odd' as linkTo, 'totals' as rowContents, SUM(data1) as d1, SUM(data2) as d2 .. FROM SomeTable WHERE theDate BETWEEN :START_DATE and :END_DATE GROUP BY TRUNC(theDate)
UNION ALL
SELECT 'odd' as linkTo, 'data' as rowContents, data1, data2 ... FROM SomeTable WHERE theDate BETWEEN :START_DATE and :END_DATE
) data
ON
  dateRange.theDate = data.theDate AND
  dateRange.evenOrOdd = data.linkTo
ORDER BY dateRange.theDate, evenOrOdd, rowContents

Which would give data looking like:

01Sep07, <-- this is the 'even' row
3, 5
3, 5
3, 5
12, 15
02Sep07,
1, 2
1, 2
1, 3
3, 7

Why does it work? Well, the data and the totals are always assigned a linkTo of 'odd' So the left join will only match dates AND 'odd' rows. Note the discrete date range query has been extended to 21 rows, but it still only calcs 10 dates because all the rownum are div by 2

so our discrete range looks like:
01Sep07, 'even'
01Sep07, 'odd'
02Sep07, 'even'
02Sep07, 'odd'

When we joined in the data we got:
01Sep07, 'even'
01Sep07, 'odd', 'data', 3, 1
01Sep07, 'odd', 'data', 3, 1
01Sep07, 'odd', 'data', 3, 1
01Sep07, 'odd', 'totals', 9, 3
02Sep07, 'even'
02Sep07, 'odd', data', 1, 2
02Sep07, 'odd', data', 1, 2
02Sep07, 'odd', data', 1, 2
02Sep07, 'odd', totals', 3, 6


We then reshaped this by saying:
If the row is 'even' put the date in the first column, otherwise put the data item (either a data or a total) in the column

This gives a results set pretty much like what youre looking for, as to how you want to collapse and expand it.. um that might be doable with an old style datagrid showing a parent/child relationship.. but the query would need further massage

If youre not using oracle (best DB) then this will be a little harder, because sqlserver isnt very good - last i knew there was no easy way to get the row number, but a sqlserver guru might be able to help translate this query

 

[knappster]

Cheers cjard, this looks very promising. I'm not up on my SQL so I was trying to do it in code, but that SQL looks just what I need. Thanks again!

 

[JohnH]

From the original post I would say the data is grouped.

 

[cjard]

From the original post I would say the data is grouped.

In the Windows XP "group icons by XXX" maybe, but grouping in database terms would produce a very different result set. It would be impossible to have a database group by date (only) and produce multiple rows bearing the same date.

The totals row is grouped, the rest are ordered by, with a visual break inserted into the data.. That doesnt make them grouped

The point I was making was merely that it isnt wise to use words like group in a lay-context, when talking to users who are not lay-men and understand a different meaning entirely..

 

[knappster]

Just to let you know, this is what I've done:

SELECT  dateRange.theDate, sumData1, sumData2, sumData3
FROM
(
     SELECT DISTINCT theDate FROM theTable
) dateRange
LEFT OUTER JOIN
(
     SELECT SUM(data1) as sumData1, SUM(data2) as sumData2, SUM(data3) as sumData3, theDate, 1 as isTotal
     FROM theTable
     WHERE theDate BETWEEN #07/29/2007# and #08/21/2007#
     GROUP BY theDate
     UNION ALL
     SELECT data1, data2, data3, theDate, 0 as isTotal
     FROM theTable
     WHERE theDate BETWEEN #07/29/2007# and #08/21/2007#
) data
ON dateRange.theDate= data.theDate
ORDER BY dateRange.theDate, isTotal

I put the distinct in for now and just pull back all distinct dates in the table, have to work out how to do what you done in SQL server so that I can have a start and end date parameter so that all dates between these are used instead. I then added an isTotal column which is 1 if the row is the total row or 0 otherwise. I can then sort by this row as well so that all the totals are the last row for that date, as before this they were ending up anywhere within the relevant date block. I can now use this to populate a treeview / listview combination and know that the totals are always the last row in that date block...