>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

news:1161202550.653457.169270@e3g2000cwe.googlegroups.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
>

<m***@mailinator.com> wrote in message news:1161622435.708451.130450@e3g2000cwe.googlegroups.com...
> any other ideas or suggestions, folks?
>
> thanks!
> matt
>

"John Sitka" <johnsi***@REMOVEhotmail.com> wrote in message news:eiXhqXu9GHA.4552@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.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.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> <m***@mailinator.com> wrote in message news:1161622435.708451.130450@e3g2000cwe.googlegroups.com...
>> any other ideas or suggestions, folks?
>>
>> thanks!
>> matt
>>
>
>