Data display?

[Ben R.]

Hi guys,

I've got a DB table of timecards with these fields in the table:

ID (Int)
UserID (Int)
DateWorked (DateTime)
HoursWorkedOnThatDate (Double)

I'd like to display a grid, with Monday - Sunday across the top (in columns)
and the users in rows vertically. I am not conceptually aware of a way to do
this. The biggest question is msut I query the same data 7 times? (For Each
day?) I would hope I could quiery once with the date bounds to get a
resultset with the 7 day range, and then somehow get the data in place. I was
thinking of applying a filter after this step to the resultset via a custom
dataview that I could change 7 times. Would a datagrid fit the job? If it
came down to it, I could enumerate through the resultset and for each value
increment a certain cell, but that seems cumbersome and I would hope I could
do this via databinding. Any help would be very helpful.

Thanks!

-Ben

[Ben R.]

Hi, I'm not sure if the solutions you mentioned are usable from within an
ASP.NET 2.0 page. Are they? Also, could you elaborate on how a "subquery"
would fit into this?

Thanks...

-Ben

"Spam Catcher" wrote:

=?Utf-8?B?QmVuIFIu?= <be**@newsgroup.nospam> wrote in
news:D2**********************************@microsof t.com:

I've got a DB table of timecards with these fields in the table:

ID (Int)
UserID (Int)
DateWorked (DateTime)
HoursWorkedOnThatDate (Double)

I'd like to display a grid, with Monday - Sunday across the top (in
columns) and the users in rows vertically. I am not conceptually aware
of a way to do this. The biggest question is msut I query the same
data 7 times? (For Each day?) I would hope I could quiery once with
the date bounds to get a resultset with the 7 day range, and then
somehow get the data in place.

You could use a subquery to calculate the data.

To display the data you could use a grid or perhaps a reporting solution
like Crystal, ActiveReports, XtraReports, or Reporting Services.

[JohnH]

This type of display is generally referred to as a Crosstab report.
Crystal reports has a Crosstab wizard. Excel calls it a PivotTable.

It's only really difficult when you have an unknown number of columns
to display. Wince you have a fixed number of columns you can do this
one of many ways:

1. Use the crosstab query functions in SQL Server 2005 if you're using
it.
2. Query the table "as-is" and then build a dataset in memory.
Manully construct the columns of the dataset and fill them with sums
you calculate off of the query results.
3. Build a query where you join the timecards table 7 times with a
join on clause specifying a different day of the week for each join.
In the select list, select the sum of hours by employee from each of
the seven joined tables and name them hours_mon, hours_tue, etc.

John H.

[Ben R.]

Hi John,

I'm very interested in the 2nd and 3rd suggestions you gave. For the dataset
suggestion, would I start by using a dataadapter to pull the current table
into a dataset and then perform modifications to that data? Or what would you
suggest?

And for the multiple query suggestion, I was hoping you could elaborate some
more. In either example, if you could point me to some reading (if any exists
that you know of), that would be very helpful.

Thanks...

-Ben

"JohnH" wrote:

This type of display is generally referred to as a Crosstab report.
Crystal reports has a Crosstab wizard. Excel calls it a PivotTable.

It's only really difficult when you have an unknown number of columns
to display. Wince you have a fixed number of columns you can do this
one of many ways:

1. Use the crosstab query functions in SQL Server 2005 if you're using
it.
2. Query the table "as-is" and then build a dataset in memory.
Manully construct the columns of the dataset and fill them with sums
you calculate off of the query results.
3. Build a query where you join the timecards table 7 times with a
join on clause specifying a different day of the week for each join.
In the select list, select the sum of hours by employee from each of
the seven joined tables and name them hours_mon, hours_tue, etc.

John H.

[JohnH]

Lets say your timecards table is called timecards and your list of
users is in a table called users. Your query for suggestion 3 would
look like this:

select u.UserID, u.UserName,
SUM(tc1.Hours) as mon, SUM(tc2.Hours) as tue,
SUM(tc3.Hours) as wed, SUM(tc4.Hours) as thu, SUM(tc5.Hours) as fri
from users u
inner join timecards tc1 on tc1.UserID = u.UserID and tc1.DateWorked =
'1/1/2006'
inner join timecards tc2 on tc2.UserID = u.UserID and tc2.DateWorked =
'1/2/2006'
inner join timecards tc3 on tc3.UserID = u.UserID and tc3.DateWorked =
'1/3/2006'
inner join timecards tc4 on tc4.UserID = u.UserID and tc4.DateWorked =
'1/4/2006'
inner join timecards tc5 on tc5.UserID = u.UserID and tc5.DateWorked =
'1/5/2006'
group by u.UserID, u.UserName

You'd need to set the actual dates for the week you are interested in
for the join clauses.

I suggestion 2, yes you could use a DataReader or a DataAdapter to get
the data, and you can put it into a dataset (generic or strongly typed)
as you please. However, once the data is retrieved, it is still in the
form:

UserID, DateWorked, HoursWorkedOnThatDate

So now you want to create a new DataSet and add a new DataTable to it.
Manually add 6 (or 8 if you count weekends) DataColumns to the table -
the first column is the user and the other 5 (or 7) columns will
contain the hours for that user for each day. Now loop over the users
in your raw data. For each user, add a new DataRow to the DataTable
and set the first column value to the user id. Next, within the user
loop, loop over your 5 (or 7) dates and add up the hours (in memory,
using your raw data that you have already retrieved from the database)
for that user for each of the days and set the appropriate column of
the DataRow to the number of hours. Finally, bind your datagrid to
this new DataSet.

I don't have time right now to do a code example that's specific for
your case. However, below is an example of the same thing but more
generalized for any number of columns. In this example, your "users"
are my "limits", your "days" are my "samples", and your "hours" are my
"results" (although I'm doing some other stuff like comparing the
result in each cell of the matrix to a predefined limit value). The
main logic is in the CompareCrossTab method, where I create a new
DataSet, add a DataTable with the appropriate columns, then loop
through and add DataRows.

HTH,

John H.
== Example Code - Crosstab.cs ==

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

using mystuff.common;

namespace mystuff.crosstab
{
public class Crosstab
{
public Crosstab()
{
}

public DataSet CompareCrossTab(SqlConnection conn, ArrayList
samplePKs, int limitTypeId)
{
DataSet ds = CreateEmptyCompareDataSet(samplePKs);
DataTable dt = ds.Tables["compare"];

if (conn.State != ConnectionState.Open)
conn.Open();

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;

ArrayList limits = GetLimits(cmd, limitTypeId);

foreach (LimitInfo li in limits)
{
DataRow dr = dt.NewRow();
SetLimitColumns(dr, li);

foreach (SamplePK sample in samplePKs)
{
AddSampleData(cmd, dr, li, sample);
}

dt.Rows.Add(dr);
}

return ds;
}

private DataSet CreateEmptyCompareDataSet(ArrayList samplePKs)
{
DataSet ds;
DataTable dt;
DataColumn dc;

ds = new DataSet();
dt = new DataTable("compare");

dc = new DataColumn("matrix", Type.GetType("System.String"));
dt.Columns.Add(dc);

dc = new DataColumn("analyte_class", Type.GetType("System.String"));
dt.Columns.Add(dc);

dc = new DataColumn("analyte", Type.GetType("System.String"));
dt.Columns.Add(dc);

dc = new DataColumn("cas_number", Type.GetType("System.String"));
dt.Columns.Add(dc);

dc = new DataColumn("operator", Type.GetType("System.String"));
dt.Columns.Add(dc);

dc = new DataColumn("limit", Type.GetType("System.Decimal"));
dt.Columns.Add(dc);

dc = new DataColumn("limit_units", Type.GetType("System.String"));
dt.Columns.Add(dc);

foreach (SamplePK sample in samplePKs)
{
string colName;

colName = sample.SampleID + "|" +
sample.SampleDate.ToShortDateString() + "|R";
dc = new DataColumn(colName, Type.GetType("System.String"));
dt.Columns.Add(dc);

colName = sample.SampleID + "|" +
sample.SampleDate.ToShortDateString() + "|U";
dc = new DataColumn(colName, Type.GetType("System.String"));
dt.Columns.Add(dc);

colName = sample.SampleID + "|" +
sample.SampleDate.ToShortDateString() + "|Q";
dc = new DataColumn(colName, Type.GetType("System.String"));
dt.Columns.Add(dc);
}
ds.Tables.Add(dt);

return ds;
}

private ArrayList GetLimits(SqlCommand cmd, int limitTypeId)
{
ArrayList limits = new ArrayList();

string sql;
sql = "SELECT analyte, matrix, analyte_class, cas_number, operator,
limit, limit_units ";
sql += "FROM limits ";
sql += "WHERE limit_type_id = " + limitTypeId + " ";
sql += "AND limit IS NOT NULL ";
sql += "AND cas_number IS NOT NULL ";
sql += "ORDER BY matrix, analyte_class, analyte";

cmd.CommandText = sql;
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
LimitInfo li = new LimitInfo();
li.analyte = rdr.GetString(0);
li.matrix = rdr.GetString(1);
li.analyteClass = rdr.GetString(2);
li.casNum = rdr.GetString(3);
li.op = rdr.GetString(4);
li.limit = rdr.GetDecimal(5);
li.units = rdr.GetString(6);
limits.Add(li);
}
rdr.Close();

return limits;
}

private void SetLimitColumns(DataRow dr, LimitInfo li)
{
dr["matrix"] = li.matrix;
dr["analyte_class"] = li.analyteClass;
dr["analyte"] = li.analyte;
dr["cas_number"] = li.casNum;
dr["operator"] = li.op;
dr["limit"] = li.limit;
dr["limit_units"] = li.units;
}

private void AddSampleData(SqlCommand cmd, DataRow dr, LimitInfo li,
SamplePK sample)
{
string sql;
sql = "SELECT result_text, qualifier_combined, units, PQL_text ";
sql += "FROM lab_results ";
sql += "INNER JOIN matrix_xref ON matrix_xref.lab_matrix =
lab_results.matrix ";
sql += "WHERE field_sample_id = '" +
AppUtils.EncodeForDB(sample.SampleID) + "' ";
sql += "AND collection_date = '" +
sample.SampleDate.ToShortDateString() + "' ";
sql += "AND limit_matrix = '" + AppUtils.EncodeForDB(li.matrix) +
"' ";
sql += "AND cas_number = '" + AppUtils.EncodeForDB(li.casNum) +
"' ";
sql += "AND QAQC_type IS NULL ";
sql += "AND surrogate_compound IS NULL ";
cmd.CommandText = sql;

SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
string resultText = "";
if (!(rdr.IsDBNull(0)))
resultText = rdr.GetString(0);

string qualifierCombined = "";
if (!(rdr.IsDBNull(1)))
qualifierCombined = rdr.GetString(1);

string resultUnits = rdr.GetString(2);

string pqlText = "";
if (!(rdr.IsDBNull(0)))
pqlText = rdr.GetString(3);

string colValue = "";
if (qualifierCombined.ToUpper().IndexOf("U") != -1)
colValue = "< " + pqlText;
else
colValue = resultText;

string colName;
colName = sample.SampleID + "|" +
sample.SampleDate.ToShortDateString() + "|R";
dr[colName] = colValue;
colName = sample.SampleID + "|" +
sample.SampleDate.ToShortDateString() + "|U";
dr[colName] = resultUnits;
colName = sample.SampleID + "|" +
sample.SampleDate.ToShortDateString() + "|Q";
dr[colName] = qualifierCombined;
}
rdr.Close();
}

private string GetList(ArrayList a)
{
if (a.Count == 0)
return "";

string s = "(";
foreach (string id in a)
{
s += "'" + id + "',";
}
s = s.Substring(0, s.Length - 1) + ")";
return s;
}
}

public class ColumnInfo
{
public string fieldSampleId;
public DateTime sampleDate;
}

public class LimitInfo
{
public string analyte;
public string matrix;
public string analyteClass;
public string casNum;
public string op;
public decimal limit;
public string units;
}
}

[Ben R.]

Thanks, John. That was a very helpful response. It made me really gain a
better understanding of SQL joins and other circumstances where they can be
used.

Would there be any reason for one to use either of these methods above the
other? It seems like the first method requires less effort on the user's
part. Is there a performance difference between the two that you know of?

Also, out of curiosity, why do you suggest creating a brand new dataset
rather than adding a new datatable to the already existing dataset for the
datatable that we generate and bind to?

Thanks again for the great info...

-Ben

"JohnH" wrote:

Lets say your timecards table is called timecards and your list of
users is in a table called users. Your query for suggestion 3 would
look like this:

select u.UserID, u.UserName,
SUM(tc1.Hours) as mon, SUM(tc2.Hours) as tue,
SUM(tc3.Hours) as wed, SUM(tc4.Hours) as thu, SUM(tc5.Hours) as fri
from users u
inner join timecards tc1 on tc1.UserID = u.UserID and tc1.DateWorked =
'1/1/2006'
inner join timecards tc2 on tc2.UserID = u.UserID and tc2.DateWorked =
'1/2/2006'
inner join timecards tc3 on tc3.UserID = u.UserID and tc3.DateWorked =
'1/3/2006'
inner join timecards tc4 on tc4.UserID = u.UserID and tc4.DateWorked =
'1/4/2006'
inner join timecards tc5 on tc5.UserID = u.UserID and tc5.DateWorked =
'1/5/2006'
group by u.UserID, u.UserName

You'd need to set the actual dates for the week you are interested in
for the join clauses.

I suggestion 2, yes you could use a DataReader or a DataAdapter to get
the data, and you can put it into a dataset (generic or strongly typed)
as you please. However, once the data is retrieved, it is still in the
form:

UserID, DateWorked, HoursWorkedOnThatDate

So now you want to create a new DataSet and add a new DataTable to it.
Manually add 6 (or 8 if you count weekends) DataColumns to the table -
the first column is the user and the other 5 (or 7) columns will
contain the hours for that user for each day. Now loop over the users
in your raw data. For each user, add a new DataRow to the DataTable
and set the first column value to the user id. Next, within the user
loop, loop over your 5 (or 7) dates and add up the hours (in memory,
using your raw data that you have already retrieved from the database)
for that user for each of the days and set the appropriate column of
the DataRow to the number of hours. Finally, bind your datagrid to
this new DataSet.

I don't have time right now to do a code example that's specific for
your case. However, below is an example of the same thing but more
generalized for any number of columns. In this example, your "users"
are my "limits", your "days" are my "samples", and your "hours" are my
"results" (although I'm doing some other stuff like comparing the
result in each cell of the matrix to a predefined limit value). The
main logic is in the CompareCrossTab method, where I create a new
DataSet, add a DataTable with the appropriate columns, then loop
through and add DataRows.

HTH,

John H.
== Example Code - Crosstab.cs ==

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

using mystuff.common;

namespace mystuff.crosstab
{
public class Crosstab
{
public Crosstab()
{
}

public DataSet CompareCrossTab(SqlConnection conn, ArrayList
samplePKs, int limitTypeId)
{
DataSet ds = CreateEmptyCompareDataSet(samplePKs);
DataTable dt = ds.Tables["compare"];

if (conn.State != ConnectionState.Open)
conn.Open();

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;

ArrayList limits = GetLimits(cmd, limitTypeId);

foreach (LimitInfo li in limits)
{
DataRow dr = dt.NewRow();
SetLimitColumns(dr, li);

foreach (SamplePK sample in samplePKs)
{
AddSampleData(cmd, dr, li, sample);
}

dt.Rows.Add(dr);
}

return ds;
}

private DataSet CreateEmptyCompareDataSet(ArrayList samplePKs)
{
DataSet ds;
DataTable dt;
DataColumn dc;

ds = new DataSet();
dt = new DataTable("compare");

dc = new DataColumn("matrix", Type.GetType("System.String"));
dt.Columns.Add(dc);

dc = new DataColumn("analyte_class", Type.GetType("System.String"));
dt.Columns.Add(dc);

dc = new DataColumn("analyte", Type.GetType("System.String"));
dt.Columns.Add(dc);

dc = new DataColumn("cas_number", Type.GetType("System.String"));
dt.Columns.Add(dc);

dc = new DataColumn("operator", Type.GetType("System.String"));
dt.Columns.Add(dc);

dc = new DataColumn("limit", Type.GetType("System.Decimal"));
dt.Columns.Add(dc);

dc = new DataColumn("limit_units", Type.GetType("System.String"));
dt.Columns.Add(dc);

foreach (SamplePK sample in samplePKs)
{
string colName;

colName = sample.SampleID + "|" +
sample.SampleDate.ToShortDateString() + "|R";
dc = new DataColumn(colName, Type.GetType("System.String"));
dt.Columns.Add(dc);

colName = sample.SampleID + "|" +
sample.SampleDate.ToShortDateString() + "|U";
dc = new DataColumn(colName, Type.GetType("System.String"));
dt.Columns.Add(dc);

colName = sample.SampleID + "|" +
sample.SampleDate.ToShortDateString() + "|Q";
dc = new DataColumn(colName, Type.GetType("System.String"));
dt.Columns.Add(dc);
}
ds.Tables.Add(dt);

return ds;
}

private ArrayList GetLimits(SqlCommand cmd, int limitTypeId)
{
ArrayList limits = new ArrayList();

string sql;
sql = "SELECT analyte, matrix, analyte_class, cas_number, operator,
limit, limit_units ";
sql += "FROM limits ";
sql += "WHERE limit_type_id = " + limitTypeId + " ";
sql += "AND limit IS NOT NULL ";
sql += "AND cas_number IS NOT NULL ";
sql += "ORDER BY matrix, analyte_class, analyte";

cmd.CommandText = sql;
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
LimitInfo li = new LimitInfo();
li.analyte = rdr.GetString(0);
li.matrix = rdr.GetString(1);
li.analyteClass = rdr.GetString(2);
li.casNum = rdr.GetString(3);
li.op = rdr.GetString(4);
li.limit = rdr.GetDecimal(5);
li.units = rdr.GetString(6);
limits.Add(li);
}
rdr.Close();

return limits;
}

private void SetLimitColumns(DataRow dr, LimitInfo li)
{
dr["matrix"] = li.matrix;
dr["analyte_class"] = li.analyteClass;
dr["analyte"] = li.analyte;
dr["cas_number"] = li.casNum;
dr["operator"] = li.op;
dr["limit"] = li.limit;
dr["limit_units"] = li.units;
}

private void AddSampleData(SqlCommand cmd, DataRow dr, LimitInfo li,
SamplePK sample)
{
string sql;
sql = "SELECT result_text, qualifier_combined, units, PQL_text ";
sql += "FROM lab_results ";
sql += "INNER JOIN matrix_xref ON matrix_xref.lab_matrix =
lab_results.matrix ";
sql += "WHERE field_sample_id = '" +
AppUtils.EncodeForDB(sample.SampleID) + "' ";
sql += "AND collection_date = '" +
sample.SampleDate.ToShortDateString() + "' ";
sql += "AND limit_matrix = '" + AppUtils.EncodeForDB(li.matrix) +
"' ";
sql += "AND cas_number = '" + AppUtils.EncodeForDB(li.casNum) +
"' ";
sql += "AND QAQC_type IS NULL ";
sql += "AND surrogate_compound IS NULL ";
cmd.CommandText = sql;

SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
string resultText = "";
if (!(rdr.IsDBNull(0)))
resultText = rdr.GetString(0);

string qualifierCombined = "";
if (!(rdr.IsDBNull(1)))
qualifierCombined = rdr.GetString(1);

string resultUnits = rdr.GetString(2);

string pqlText = "";
if (!(rdr.IsDBNull(0)))
pqlText = rdr.GetString(3);

string colValue = "";
if (qualifierCombined.ToUpper().IndexOf("U") != -1)
colValue = "< " + pqlText;
else
colValue = resultText;

string colName;
colName = sample.SampleID + "|" +
sample.SampleDate.ToShortDateString() + "|R";
dr[colName] = colValue;
colName = sample.SampleID + "|" +
sample.SampleDate.ToShortDateString() + "|U";
dr[colName] = resultUnits;
colName = sample.SampleID + "|" +
sample.SampleDate.ToShortDateString() + "|Q";
dr[colName] = qualifierCombined;
}
rdr.Close();
}

private string GetList(ArrayList a)
{
if (a.Count == 0)
return "";

string s = "(";
foreach (string id in a)
{
s += "'" + id + "',";
}
s = s.Substring(0, s.Length - 1) + ")";
return s;
}
}

public class ColumnInfo
{
public string fieldSampleId;
public DateTime sampleDate;
}

public class LimitInfo
{
public string analyte;
public string matrix;
public string analyteClass;
public string casNum;
public string op;
public decimal limit;
public string units;
}
}

[Steven Cheng[MSFT]]

Hi Ben,

I think the #1 suggestion from John will mostly rely on the backend
database server, so if there is no much concerns on the flexibility of the
backend database, that'll be good. As for #2 which load all the datatables
separately into our ASP.NET application's memory (in a single dataset),
that'll make it easy to manipulate the unformated data in our application
layer's code(especially when there is any manipulation or formatting that
can not be done at database layer). However, caching large object like
dataset in ASP.NET memory is very expensive, you would also consider this
according to your server machine's condition.

Regards,

Steven Cheng
Microsoft Online Community Support
==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[JohnH]

The first method (using a multi-join query) is the easiest and probably
the better choice when you know the number of columns ahead of time.
It is harder to program a true crosstab query this way, where the
number of columns is variable.

There's no particular reason why I said to use a new dataset. In my
code example, you'll see I actually use SqlDataReaders to get the data
out of the database - they are much faster for read-only data
extraction than DataAdapters and DataSets.

Glad I could help,

John H.

[Ben R.]

Thanks John and Steven!

-Ben

"JohnH" wrote:

The first method (using a multi-join query) is the easiest and probably
the better choice when you know the number of columns ahead of time.
It is harder to program a true crosstab query this way, where the
number of columns is variable.

There's no particular reason why I said to use a new dataset. In my
code example, you'll see I actually use SqlDataReaders to get the data
out of the database - they are much faster for read-only data
extraction than DataAdapters and DataSets.

Glad I could help,

John H.

[Ben R.]

Hi John,

I'm going the dataset route as access doesn't seem to be too keen on that
level of complexity in joins. Anyway, I'm curious as to why it makes more
sense to fill a datatable and then contsruct a new datatable from the first
one. Why not just create a datatable based off of a datareader? To be honest,
I'm not too familiar with the other method of accessing data through a data
table. I'm more used to a forward-only cursor. I invision enumerating through
the datareader and for each entry saying something like,

"If username doens't already have a datarow, create one. Now, add hours to
the appropriate column."

So it would create the row if necessary, and then in either case, would add
the hours. Is there a way to do this type of check? it would avoid the need
to enumerate through the datasource adding all the rows and then re-enumerate
through to add hours.

I guess I'm just not sure of how to enumerate through a datasource which
gives you this level of flexibility. My mental model has always invovled
working around the narrow confines of a foreward only cursor and not a random
access model. Any thoughts?

-Ben

"JohnH" wrote:

The first method (using a multi-join query) is the easiest and probably
the better choice when you know the number of columns ahead of time.
It is harder to program a true crosstab query this way, where the
number of columns is variable.

There's no particular reason why I said to use a new dataset. In my
code example, you'll see I actually use SqlDataReaders to get the data
out of the database - they are much faster for read-only data
extraction than DataAdapters and DataSets.

Glad I could help,

John H.