comparison reporting -- technique suggestions?

M

matt

hello,

i have been given a challenging project at my org.

i work on an inventory management web application -- keeping tracking
of parts assigned to projects. in the past, i built an in-house ASP.NET
reporting system. just your standard stuff -- user clicks on a report,
the page executes a pre-defined SQL query, a dataset is formed and
bound to a data repeater. that works well for us. and using some nifty
CSS, my reports look good on screen & on paper.

now, however, my users would like....comparison reports. with
comparison reporting, theyd like the ability to:


a) take a "snapshot" of a given live report, storing it for later
b) compare a given report to one of the previously-stored snapshots

the mechanism for this would ideally show them a screen of three sets
of data:

- new rows (parts added since the referenced snapshot)
- deleted rows (parts that existed in the snapshot, but dont now)
- changed rows (parts that existed in the snapshot, but have at
least one different column)

ideally, theyd like a technique that can be used generically for any of
our current or future reports (but limited, of course, to
same-report-type comparisons).


....some tall order -- this is nothing ive ever attempted before. a few
ideas came to mind for doing this:


1) when a user takes a snapshot, the dataset is serialized into text,
and dumped into a CLOB in the db. when the user later does a
comparison, the desired CLOB is restored into a dataset. then code must
be written to enumerate and evaluate the tables. rows matching one of
the categories must be inserted into new a "results" dataset
(consisting of the 3 tables for new/deleted/changed rows).

2) alternatively, some sort of "history" could be implemented on our
tables on the (Oracle) backend -- like triggers that update new history
tables, that log changes. then some means of comparing present-day data
to the history tables must be devised.

3) investigate a 3rd-party reporting package that offers this ability.


as i mentioned, ive never set about this particular task in my years of
..net web apps. no idea on how it can or should be done.

can anyone point me to references, or suggest possible solutions?



thanks!
matt
 
C

Cowboy \(Gregory A. Beamer\)

Save the result set (history table?) and tag it rather than the actual
report. You can reproduce the original report using that data or a
comparison report against a new tagged set of data. It will be easier to
drill through the data and add a variety of comparison metrics. If you save
the rendered report, you can compare with a newly rendered report, but your
ability to slice and dice will be hindered until you turn it back into raw
data again.
 
M

matt

hi greg,
If you save
the rendered report, you can compare with a newly rendered report, but your
ability to slice and dice will be hindered until you turn it back into raw
data again.
Click to expand...

perhaps i didnt make Idea 1 clear. i wouldnt save the *rendered* report
(html, css, etc); only it's underlying datasource -- ie, taking the
dataset that im binding from, and serializing it (binary) into
characters for storage in the db as a giant CLOB (in a table like
"HistoricSnapshots"). then deserializing it later when its called on as
a comparison point.
Save the result set (history table?) and tag it rather than the actual
report.
Click to expand...

what do you mean by "tag it"?


thanks!
matt
 
M

matt

[email protected] said:
(html, css, etc); only it's underlying datasource -- ie, taking the
dataset that im binding from, and serializing it (binary) into
characters for storage in the db as a giant CLOB
Click to expand...

tho i am assuming i can do this to a dataset in v1.1. never tried it
before!


matt
 
J

John Sitka

Have you considered using a wiki? They natively support versioning.

failing that.....

you basically need a diff display,
you can't do that with either the whole previous document stored somewhere,
or if each item on a snapshot can maintain a unique identifier
then it is very easy to use SQL and just keep versions.

But when it comes to parts lists there isn't a whole lot of experience out there with
"In Process" identifiers being different than "balloon numbers" on drawings or part numbers
on exploded views.

Anyways if you can save versions AND maintain unique line item identifiers you can actually use a simple
list view to display the delta(diff).


I did one with two list views as version pickers and one list view as the DIFF display
Then call a stored procedure that accepts as parameters a @leftversion and @rightversion

CREATE PROC tool_Get_Bom_Versions_for_Diff
(@leftversion int = 1, @rightversion int = 1, @sono varchar(6) = 'C9999')

AS SET NOCOUNT ON


-- DECLARE @leftversion int
-- DECLARE @rightversion int
-- DECLARE @sono varchar(6)
-- SET @leftversion = 10
-- SET @rightversion = 11
-- SET @sono = 'C2729'



SELECT
externalballoon,
CASE WHEN laguid is null OR raguid is null THEN 1
ELSE 0
END
as deleteaddflag,
CASE WHEN lprocess <> rprocess THEN 1
ELSE 0
END
as processmodified,
CASE WHEN lqty <> rqty THEN 1
ELSE 0
END as qtymodified,
CASE WHEN lpartno <> rpartno THEN 1
ELSE 0
END as partnomodified,
CASE WHEN lpartnodesc <> rpartnodesc THEN 1
ELSE 0
END as partnodescmodified,
CASE WHEN lneeddate <> rneeddate THEN 1
ELSE 0
END as needdatemodified,
lprocess, lqty, lpartno, lpartnodesc, CONVERT(char(10),lneeddate, 101) as lneeddate, laguid,
rprocess, rqty, rpartno, rpartnodesc, convert(char(10),rneeddate, 101) as rneeddate, raguid
FROM
(
SELECT Z.externalballoon,
Y.process as lprocess, Y.qty as lqty, Y.partno as lpartno, Y.partnodesc as lpartnodesc, Y.needdate as lneeddate, Y.aguid as laguid,
X.process as rprocess, X.qty as rqty, X.partno as rpartno, X.partnodesc as rpartnodesc, X.needdate as rneeddate, X.aguid as raguid
FROM
(
SELECT B.externalballoon from engversion A
INNER JOIN engboms B
ON A.aguid = B.aguid
WHERE B.sono = @sono
AND
A.bomversion = @leftversion

UNION

SELECT B.externalballoon from engversion A
INNER JOIN engboms B
ON A.aguid = B.aguid
WHERE B.sono = @sono
AND
A.bomversion = @rightversion
)
Z
LEFT JOIN
(
SELECT B.externalballoon, B.process, B.qty, B.partno, B.partnodesc, B.needdate, B.aguid from engversion A
INNER JOIN engboms B
ON A.aguid = B.aguid
WHERE B.sono = @sono
AND
A.bomversion = @leftversion
)
Y
ON
Z.externalballoon = Y.externalballoon
LEFT JOIN
(
SELECT B.externalballoon, B.process, B.qty, B.partno, B.partnodesc, B.needdate, B.aguid from engversion A
INNER JOIN engboms B
ON A.aguid = B.aguid
WHERE B.sono = @sono
AND
A.bomversion = @rightversion
)
X
ON
Z.externalballoon = X.externalballoon
)
J


The idea is to include a double wide result set, the Y and Z alias tables where Z is the In process unique ID.
Then across all the In process unique ID's in BOTH versions, do a conditional comparison for each column that represents
an attribute of that part. Then each row has a binary flag that represents a change has happened across that attribute.

then in the Windows form
a click to display
which sets up the call to the double wide result set and
sets the grid display iteratively.
then bild a lillte legend with some labels at the bottom that explain what each little colored cell means
like deleted, or qty change etc.


private void uxbtnCADBOMVersionDiffCompare_Click(object sender, System.EventArgs e)
{
//tool_Get_CadBom_Versions_for_Diff

int leftaguid = 0;
int rightaguid = 0;
if (uxlistviewCADBOMVersionDiffLeftPicker.SelectedItems.Count == 0 || uxlistviewCADBOMVersionDiffRightPicker.SelectedItems.Count
== 0)
{
MessageBox.Show("highlight two versions...\n\n ...one in each list");
}
else
{
leftaguid = Convert.ToInt32(uxlistviewCADBOMVersionDiffLeftPicker.SelectedItems[0].Text.ToString());
Debug.WriteLine(leftaguid);
rightaguid = Convert.ToInt32(uxlistviewCADBOMVersionDiffRightPicker.SelectedItems[0].Text.ToString());
Debug.WriteLine(rightaguid);
uxlistviewCADBOMVersionDiff.Items.Clear();
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Parameters["@sono"].Value = bomroot.strsono;
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Parameters["@leftversion"].Value = leftaguid;
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Parameters["@rightversion"].Value = rightaguid;
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Connection.Open();
System.Data.SqlClient.SqlDataReader dr =
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.ExecuteReader(CommandBehavior.CloseConnection);
DataUtils.DataReaderAdapter dra = new DataUtils.DataReaderAdapter();
DataTable dtDiff = new DataTable();
dra.FillFromReader(dtDiff,dr);
dr.Close();

if (cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Connection.State.ToString() == "Open")
{
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Connection.Close();
}


foreach(DataRow row in dtDiff.Rows)
{
ListViewItem item4 = new ListViewItem(row["xxxID"].ToString());
item4.UseItemStyleForSubItems = false;
item4.SubItems.Add(row["lprocess"].ToString());
item4.SubItems.Add(row["lqty"].ToString());
item4.SubItems.Add(row["lpartno"].ToString());
item4.SubItems.Add(row["lpartnodesc"].ToString());
item4.SubItems.Add(row["lneeddate"].ToString());
item4.SubItems.Add("-");
item4.SubItems.Add(row["rprocess"].ToString());
item4.SubItems.Add(row["rqty"].ToString());
item4.SubItems.Add(row["rpartno"].ToString());
item4.SubItems.Add(row["rpartnodesc"].ToString());
item4.SubItems.Add(row["rneeddate"].ToString());

if(row["deleteflag"].ToString() == "1")
{
item4.SubItems[0].BackColor = Color.Red;
item4.SubItems[6].BackColor = Color.Red;
}

if(row["addflag"].ToString() == "1")
{
item4.SubItems[0].BackColor = Color.Red;
item4.SubItems[6].BackColor = Color.Red;
}

if(row["processmodified"].ToString() == "1")
{
item4.SubItems[1].BackColor = Color.LightGreen;
item4.SubItems[7].BackColor = Color.LightGreen;
}

if(row["qtymodified"].ToString() == "1")
{
//item4.BackColor = Color.LightBlue;
item4.SubItems[2].BackColor = Color.LightBlue;
item4.SubItems[8].BackColor = Color.LightBlue;
}

if(row["partnomodified"].ToString() == "1")
{
item4.SubItems[3].BackColor = Color.HotPink;
item4.SubItems[9].BackColor = Color.HotPink;
}

if(row["partnodescmodified"].ToString() == "1")
{
item4.SubItems[4].BackColor = Color.LightPink;
item4.SubItems[10].BackColor = Color.LightPink;
}

if(row["needdatemodified"].ToString() == "1")
{
item4.SubItems[5].BackColor = Color.Yellow;
item4.SubItems[11].BackColor = Color.Yellow;
}
uxlistviewCADBOMVersionDiff.Items.Add(item4);
}
}
}

Maybe this can give you some ideas, But keep in mind. as the parts list changes over time, each line item
has to be unique. That can be done only two ways.
With multiple whole documents where line position and content
define it, That is exactly what the diff view in Visual Source Safe displays to you.
OR
by making a commitment that once a line item is included on a parts list it has it's own identity forever.
Subsequent versions of that parts list can then be compared.
 
J

John Sitka

oops - > you can't do that with either the whole previous document stored somewher
should be you CAN do that

John Sitka said:
Have you considered using a wiki? They natively support versioning.

failing that.....

you basically need a diff display,
you can't do that with either the whole previous document stored somewhere,
or if each item on a snapshot can maintain a unique identifier
then it is very easy to use SQL and just keep versions.

But when it comes to parts lists there isn't a whole lot of experience out there with
"In Process" identifiers being different than "balloon numbers" on drawings or part numbers
on exploded views.

Anyways if you can save versions AND maintain unique line item identifiers you can actually use a simple
list view to display the delta(diff).


I did one with two list views as version pickers and one list view as the DIFF display
Then call a stored procedure that accepts as parameters a @leftversion and @rightversion

CREATE PROC tool_Get_Bom_Versions_for_Diff
(@leftversion int = 1, @rightversion int = 1, @sono varchar(6) = 'C9999')

AS SET NOCOUNT ON


-- DECLARE @leftversion int
-- DECLARE @rightversion int
-- DECLARE @sono varchar(6)
-- SET @leftversion = 10
-- SET @rightversion = 11
-- SET @sono = 'C2729'



SELECT
externalballoon,
CASE WHEN laguid is null OR raguid is null THEN 1
ELSE 0
END
as deleteaddflag,
CASE WHEN lprocess <> rprocess THEN 1
ELSE 0
END
as processmodified,
CASE WHEN lqty <> rqty THEN 1
ELSE 0
END as qtymodified,
CASE WHEN lpartno <> rpartno THEN 1
ELSE 0
END as partnomodified,
CASE WHEN lpartnodesc <> rpartnodesc THEN 1
ELSE 0
END as partnodescmodified,
CASE WHEN lneeddate <> rneeddate THEN 1
ELSE 0
END as needdatemodified,
lprocess, lqty, lpartno, lpartnodesc, CONVERT(char(10),lneeddate, 101) as lneeddate, laguid,
rprocess, rqty, rpartno, rpartnodesc, convert(char(10),rneeddate, 101) as rneeddate, raguid
FROM
(
SELECT Z.externalballoon,
Y.process as lprocess, Y.qty as lqty, Y.partno as lpartno, Y.partnodesc as lpartnodesc, Y.needdate as lneeddate, Y.aguid as
laguid,
X.process as rprocess, X.qty as rqty, X.partno as rpartno, X.partnodesc as rpartnodesc, X.needdate as rneeddate, X.aguid as raguid
FROM
(
SELECT B.externalballoon from engversion A
INNER JOIN engboms B
ON A.aguid = B.aguid
WHERE B.sono = @sono
AND
A.bomversion = @leftversion

UNION

SELECT B.externalballoon from engversion A
INNER JOIN engboms B
ON A.aguid = B.aguid
WHERE B.sono = @sono
AND
A.bomversion = @rightversion
)
Z
LEFT JOIN
(
SELECT B.externalballoon, B.process, B.qty, B.partno, B.partnodesc, B.needdate, B.aguid from engversion A
INNER JOIN engboms B
ON A.aguid = B.aguid
WHERE B.sono = @sono
AND
A.bomversion = @leftversion
)
Y
ON
Z.externalballoon = Y.externalballoon
LEFT JOIN
(
SELECT B.externalballoon, B.process, B.qty, B.partno, B.partnodesc, B.needdate, B.aguid from engversion A
INNER JOIN engboms B
ON A.aguid = B.aguid
WHERE B.sono = @sono
AND
A.bomversion = @rightversion
)
X
ON
Z.externalballoon = X.externalballoon
)
J


The idea is to include a double wide result set, the Y and Z alias tables where Z is the In process unique ID.
Then across all the In process unique ID's in BOTH versions, do a conditional comparison for each column that represents
an attribute of that part. Then each row has a binary flag that represents a change has happened across that attribute.

then in the Windows form
a click to display
which sets up the call to the double wide result set and
sets the grid display iteratively.
then bild a lillte legend with some labels at the bottom that explain what each little colored cell means
like deleted, or qty change etc.


private void uxbtnCADBOMVersionDiffCompare_Click(object sender, System.EventArgs e)
{
//tool_Get_CadBom_Versions_for_Diff

int leftaguid = 0;
int rightaguid = 0;
if (uxlistviewCADBOMVersionDiffLeftPicker.SelectedItems.Count == 0 || uxlistviewCADBOMVersionDiffRightPicker.SelectedItems.Count
== 0)
{
MessageBox.Show("highlight two versions...\n\n ...one in each list");
}
else
{
leftaguid = Convert.ToInt32(uxlistviewCADBOMVersionDiffLeftPicker.SelectedItems[0].Text.ToString());
Debug.WriteLine(leftaguid);
rightaguid = Convert.ToInt32(uxlistviewCADBOMVersionDiffRightPicker.SelectedItems[0].Text.ToString());
Debug.WriteLine(rightaguid);
uxlistviewCADBOMVersionDiff.Items.Clear();
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Parameters["@sono"].Value = bomroot.strsono;
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Parameters["@leftversion"].Value = leftaguid;
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Parameters["@rightversion"].Value = rightaguid;
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Connection.Open();
System.Data.SqlClient.SqlDataReader dr =
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.ExecuteReader(CommandBehavior.CloseConnection);
DataUtils.DataReaderAdapter dra = new DataUtils.DataReaderAdapter();
DataTable dtDiff = new DataTable();
dra.FillFromReader(dtDiff,dr);
dr.Close();

if (cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Connection.State.ToString() == "Open")
{
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Connection.Close();
}


foreach(DataRow row in dtDiff.Rows)
{
ListViewItem item4 = new ListViewItem(row["xxxID"].ToString());
item4.UseItemStyleForSubItems = false;
item4.SubItems.Add(row["lprocess"].ToString());
item4.SubItems.Add(row["lqty"].ToString());
item4.SubItems.Add(row["lpartno"].ToString());
item4.SubItems.Add(row["lpartnodesc"].ToString());
item4.SubItems.Add(row["lneeddate"].ToString());
item4.SubItems.Add("-");
item4.SubItems.Add(row["rprocess"].ToString());
item4.SubItems.Add(row["rqty"].ToString());
item4.SubItems.Add(row["rpartno"].ToString());
item4.SubItems.Add(row["rpartnodesc"].ToString());
item4.SubItems.Add(row["rneeddate"].ToString());

if(row["deleteflag"].ToString() == "1")
{
item4.SubItems[0].BackColor = Color.Red;
item4.SubItems[6].BackColor = Color.Red;
}

if(row["addflag"].ToString() == "1")
{
item4.SubItems[0].BackColor = Color.Red;
item4.SubItems[6].BackColor = Color.Red;
}

if(row["processmodified"].ToString() == "1")
{
item4.SubItems[1].BackColor = Color.LightGreen;
item4.SubItems[7].BackColor = Color.LightGreen;
}

if(row["qtymodified"].ToString() == "1")
{
//item4.BackColor = Color.LightBlue;
item4.SubItems[2].BackColor = Color.LightBlue;
item4.SubItems[8].BackColor = Color.LightBlue;
}

if(row["partnomodified"].ToString() == "1")
{
item4.SubItems[3].BackColor = Color.HotPink;
item4.SubItems[9].BackColor = Color.HotPink;
}

if(row["partnodescmodified"].ToString() == "1")
{
item4.SubItems[4].BackColor = Color.LightPink;
item4.SubItems[10].BackColor = Color.LightPink;
}

if(row["needdatemodified"].ToString() == "1")
{
item4.SubItems[5].BackColor = Color.Yellow;
item4.SubItems[11].BackColor = Color.Yellow;
}
uxlistviewCADBOMVersionDiff.Items.Add(item4);
}
}
}

Maybe this can give you some ideas, But keep in mind. as the parts list changes over time, each line item
has to be unique. That can be done only two ways.
With multiple whole documents where line position and content
define it, That is exactly what the diff view in Visual Source Safe displays to you.
OR
by making a commitment that once a line item is included on a parts list it has it's own identity forever.
Subsequent versions of that parts list can then be compared.
















any other ideas or suggestions, folks?

thanks!
matt
Click to expand...
Click to expand...
 
R

Registered User

any other ideas or suggestions, folks?
Click to expand...
- from original post -
now, however, my users would like....comparison reports. with
comparison reporting, theyd like the ability to:

a) take a "snapshot" of a given live report, storing it for later
b) compare a given report to one of the previously-stored snapshots

the mechanism for this would ideally show them a screen of three sets
of data:

- new rows (parts added since the referenced snapshot)
- deleted rows (parts that existed in the snapshot, but dont now)
- changed rows (parts that existed in the snapshot, but have at
least one different column)

ideally, theyd like a technique that can be used generically for any
of our current or future reports (but limited, of course, to
same-report-type comparisons).
- end -

First of all I see one pretty obvious gotcha to avoid. There is an
assumption all comparisons will be done again previously generated
reports. At some point someone will want to create 'live' report for a
previous date and then compare that to the current live report.

The task sounds similar to the revision history a source control tool
might keep.

- from original post -
2) alternatively, some sort of "history" could be implemented on our
tables on the (Oracle) backend -- like triggers that update new
history tables, that log changes. then some means of comparing
present-day data to the history tables must be devised.
- end -

You might consider parallel table(s) to track the revisions,
deletions, etc. The parallel table would have some extra fields for
things like the date the record was put in the table. Then using some
SQL-magic a live report could be created for any historical date.

regards
A.G.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Suggestions on building an AI? 0
Different Serialization Technique In .NET 0
Only one table shows up with the information 2
Reporting Woes (extremely simple) 1
reporting proxy porting problem 0
Asp.Net and SQL Reporting Service 2
SQL Reporting Services Security 0
Reporting Solution Recommendations? 15

Members online

No members online now.
Total: 53 (members: 1, guests: 52)
Robots: 316

Forum statistics

Threads
473,994
Messages
2,570,222
Members
46,809
Latest member
moe77

Latest Threads

Top