Creating Before/After Report

[splaisance]

I need to create a report that shows records before and after they are changed. How can I do this using Crystal Reports 9 Developer? The changes to be made to the original before record come from an Access database table. I am using VB 6.0 Enterprise to write the program to perform the updating. I can't figure out how to send just the information I want to print out to Crystal.

Ex:

Before Record
Name Address Loan# LoanAmount

After Record
Name Address Loan# LoanAmount

and so on.

Please Help!

Shannon

 

[chelseatech]

Cr shows the data as it is now.

You'll need an audit table somewhere showing what has changed. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[splaisance]

The information is in two different databases. Isn't there a way to show one record from one database table then immediately after that one record from a different database table?

How is this done?


Shannon

 

[vannojl]

You could do this through subreports. Have one subreport look at the Before table and another look at the After table.

If you make copies of the same report the formatting will be the same.

Joe Van Norman
ABS Associates

 

[synapsevampire]

If you have a key that links the two tables, join the tables by that key (or you might gang fields and create a uniqueness key.

Group by the key, and sort by the date field (this assumes that you have one). I think that natural order may work for your purposes too.

Place your data in the details section.

You can even do calculations of one field against the other by using the next() function to demonstrate differences.

if next(key) = key then
{table.loanamount) - next({table.loanamount))

Or whatever might suit your purposes.

-k

 

[chelseatech]

Another solution is to use SQL and do a UNION join of the two tables to create a view with both records.

Base your report on the view. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[splaisance]

Thank you everyone for your suggestions.

I found a solution that works and will use some of the other suggestions for future problems I am sure!

I pulled in both tables, grouped by the loan number (which is the link between them) then suppressed the group header and the group footer. And I created two detail lines, a has the before information and b has the after information.


chelseatech - where can I create the union join? do you mean in crystal or before I send it to crystal? I've used that approach where I created a union query in access and had crystal look at that to get all records. In this instance the tables are from two different types of databases.... so can I create a union join in crystal and where? Sorry I'm still learning intermediate and advanced ways to work with crystal! I've only created approx 6 reports with it so far.


Again thank you everyone!

Shannon

 

[chelseatech]

Hi Shannon,

If you are using Access, you can create a Query that is a UNION of two tables. It is just that the columns in the two tables must match. So Only UNION fields from the tables you need in the final report.

If one of the tables is in another DB, in Access you can LINK to a foreign data source. it slows down a lot, but with not too many records it works okay.

If you have a lot of records, copy all the tables into a SQL sever, and do the UNION there using a view (jsut like an Access Query). This approach is sometimes called a DataMart and can reduce performance hits on the original data.

Finally, Cr9 lets you write a SQL command to retrieve data for your report. This command can include UNION. It can also use DELETE and INSERT, in fact any SQL seems valid (and a little dangerous in the wrong hands). Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm