By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,277 Members | 1,255 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,277 IT Pros & Developers. It's quick & easy.

comparison reporting -- technique suggestions?

P: n/a
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

Oct 18 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*************************************************
Think outside of the box!
*************************************************
<ma**@mailinator.comwrote in message
news:11**********************@e3g2000cwe.googlegro ups.com...
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

Oct 19 '06 #2

P: n/a
hi greg,

Cowboy (Gregory A. Beamer) wrote:
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.
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.
what do you mean by "tag it"?
thanks!
matt

Oct 19 '06 #3

P: n/a

m...@mailinator.com wrote:
(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
tho i am assuming i can do this to a dataset in v1.1. never tried it
before!
matt

Oct 19 '06 #4

P: n/a
any other ideas or suggestions, folks?

thanks!
matt

Oct 23 '06 #5

P: n/a
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.SelectedIte ms.Count == 0 || uxlistviewCADBOMVersionDiffRightPicker.SelectedIte ms.Count
== 0)
{
MessageBox.Show("highlight two versions...\n\n ...one in each list");
}
else
{
leftaguid = Convert.ToInt32(uxlistviewCADBOMVersionDiffLeftPic ker.SelectedItems[0].Text.ToString());
Debug.WriteLine(leftaguid);
rightaguid = Convert.ToInt32(uxlistviewCADBOMVersionDiffRightPi cker.SelectedItems[0].Text.ToString());
Debug.WriteLine(rightaguid);
uxlistviewCADBOMVersionDiff.Items.Clear();
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Para meters["@sono"].Value = bomroot.strsono;
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Para meters["@leftversion"].Value = leftaguid;
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Para meters["@rightversion"].Value = rightaguid;
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Conn ection.Open();
System.Data.SqlClient.SqlDataReader dr =
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Exec uteReader(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.Con nection.State.ToString() == "Open")
{
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Conn ection.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.






<ma**@mailinator.comwrote in message news:11**********************@e3g2000cwe.googlegro ups.com...
any other ideas or suggestions, folks?

thanks!
matt

Oct 23 '06 #6

P: n/a
oops - you can't do that with either the whole previous document stored somewher
should be you CAN do that

"John Sitka" <jo*******@REMOVEhotmail.comwrote in message news:ei**************@TK2MSFTNGP05.phx.gbl...
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.SelectedIte ms.Count == 0 || uxlistviewCADBOMVersionDiffRightPicker.SelectedIte ms.Count
== 0)
{
MessageBox.Show("highlight two versions...\n\n ...one in each list");
}
else
{
leftaguid = Convert.ToInt32(uxlistviewCADBOMVersionDiffLeftPic ker.SelectedItems[0].Text.ToString());
Debug.WriteLine(leftaguid);
rightaguid = Convert.ToInt32(uxlistviewCADBOMVersionDiffRightPi cker.SelectedItems[0].Text.ToString());
Debug.WriteLine(rightaguid);
uxlistviewCADBOMVersionDiff.Items.Clear();
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Para meters["@sono"].Value = bomroot.strsono;
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Para meters["@leftversion"].Value = leftaguid;
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Para meters["@rightversion"].Value = rightaguid;
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Conn ection.Open();
System.Data.SqlClient.SqlDataReader dr =
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Exec uteReader(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.Con nection.State.ToString() == "Open")
{
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Conn ection.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.






<ma**@mailinator.comwrote in message news:11**********************@e3g2000cwe.googlegro ups.com...
>any other ideas or suggestions, folks?

thanks!
matt


Oct 24 '06 #7

P: n/a
On 23 Oct 2006 09:53:55 -0700, ma**@mailinator.com wrote:
>any other ideas or suggestions, folks?
- 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.
Oct 24 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.