Creating Historical Report...

[gyfu]

Hi All..
I have been using Crystal Report for a while, and I must say this forum has helped me a lot in my troubles or challenges. However, this next challenge I have is something I have no experience at all and am trying to grasp the concept on how to do it.

As mention above, it is creating a historical report.

I have a customer with over 500K accounts. On a periodically basis, the accounts state change. The following fields are the fields that will change.

1) AccountBalance
2) AccountUser
3) AccountGrade
etc.

Currently, I know that the database I am working on do not capture the data historically. Basically on a daily basis, the batch process just updates the account values.

My objective is to have the capability to produce the life cycle of the account.

For eg.

Aug 1, Acctid 123; Balance = 100, User = John, Grade = A
Aug 5, Acctid 123; Balance = 300, User = Mary, Grade = B
Aug 15, Acctid 123; Balance = 500, User = John, Grade = C
Aug 23, Acctid 123; Balance = 400, User = Peter, Grade = B

From my thoughts, I was thinking of capturing the state of the account every day, but that would definitely increase the number of records in the table exponentially. Even if I do that, I don't think I can create the report with that table.

I am sure there is a smarter way of doing it but just now sure how to do it. I read here that someone mention of using data mart or something like that but currently am not sure of it as well.

I appreciate if any of you can point me to the right direction of the concept.

Database : SQL Server 2005
Crystal Report 2008.

Thanks.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[Madawc]

You can export the report output, in various forms including an Excel spreadsheet or a text file, both of which can then be used as Crystal input. Does that help?

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[gyfu]

Hi Madawc,
That is the manual way of doing it and the users are doing that at the moment. Where they export multiple excel files and just aggregate it in Excel itself.

I got friends who uses SAS reporting tool and it seems that in SAS there is a function that can be used to do such a thing as above, provided you have the historical data captured. I am not sure about Crystal Reports though.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[Madawc]

New to me. Can anyone else help?

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[gyfu]

Anyone with any ideas on this problem?

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[kenhamady]

The data has to be stored somewhere so you will need to create another table to hold the historical changes. It would start out as simply a duplicate copy of the existing table with a date column showning the date of the original copy. Changes would be appended and each appended record would be dated.

You could this several ways but the most efficient would be to have someone write a stored procedure that is scheduled to run each day. It would compare the current values of each account to the latest value for that same account in the historical table. If there is a change it would append the latest record to the historical table with the date of the change. If the current value matches the latest historical records, nothing is appended.

If you have to do it from Crystal you could write a report using the CUT Light DLL (see my LINKS page). The report would compare the two tables and look for changes as described above. CUT Light has the ability to execute a SQL statement which could INSERT a record into the historical table each time it finds a change.

But this would probably be slower than having it done directly in the SQL. In an ideal world you would modify the existing update routine to append records when there was a change, instead of changing them. But I realize that it isn't always possible or simple to modify an existing process.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[IdoMillet]

Using SQL Server Integration Services (SSIS) would be an efficient way of taking data snapshots.

If you don't have access to SSIS skills (in house or via consultants), but you do have Crystal skills, there's an alternative described Here:

http://www.milletsoftware.com/Download/Visual CUT ODBC Export Functionality.pdf

If you need to capture only cases of changes in status, that technique could easily be adapted to that scenario as well.

hth.
- Ido

view, email, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[gyfu]

Thanks guys,
I did have someone written a SP and capture the history as mention.

Seems to be working well for now.



-- gyfu --
Crystal Report 2008
Microsoft SQL 2005