Need to store data from previous months reports 1

[AZdesertdog]

Using CR 10 against MS SQL 2000 db

I have a monthly report that returns a value based on the month I run the report in. The report only calculates the last full months data and can't run data previous to that as it has changed. So..I need a way to store a previous month value (as it was at that point in time) and display it along with the current month value.

Desired results:

Report run in September (for August data):

Month Value
August 04 2.4 <- this data is calc. from the september
report
July 04 2.8 <- this value I'd like saved
from the previous months report
June 04 3.4 <- this value I'd like saved
from the previous months report

Report run in October (for September data):

Month Value
Sept 04 3.9 <- this data is calc. from the october
report
Aug 04 2.4 <- this value I'd like saved
from the previous months report
July 04 2.8 <- this value I'd like saved
from the previous months report

So basically I want to keep this score for each month I run the report in and be able to show the current month (calculated in report) and last two months scores in the current report. Any help is appreciated. Thanks!!

 

[synapsevampire]

What you're suggesting is that a reporting tool act as a data warehouse because your database is not storing required data.

I would create a Stored Pocedure to create the data each month in a summary table, and then point the report to the summary table.

The SP could either execute the summary table population and then pull the data from the table, or you would probably be better off to set this up as a monthly process on the SQL Server to allow for more precise failure notifications.

-k

 

[AZdesertdog]

That sounds like a good plan. Thanks!