How do I automate a report on variance in the same SQL table fields on monthly basis?

Question

I have a T-SQL view with integer fields. I need a report on a monthly basis regarding the difference from one month to the next, i.e. so many people were engaged in a particular activity on 8am of the 1st of this month, so many the previous month, here is the difference. The numbers fluctuate all the time. I need a variance between 2 snapshots in time.

I am using the SSRS, however in reporting services I can only display the "current" situation. I could run a report at 8am of the 1st of each month and then calculate the differences manually. But how could I automate this calculation and then report on the difference?

I have tried to import data from SQL to 1 Excel spreadsheet from 1 month, then to the 2nd spreadsheet from the 2nd month. The 3rd spreadsheet calculates the difference. But how do I create a nice looking report from Excel?

Additionally I cannot send the report by email. It has to be available online.

Furthermore, each office wants their figures to be confidential and not visible to another office.

Thanx in advance.

Answer 1

Can you add a UserCount table that stores each office's user count for each month? It could have columns like:

• id
• date
• user_count
• office_id

You would insert a new row each month based on what the view tells you that month for each office. Then it's as simple as exporting that table to Excel and graphing it using Excel's built-in graphing tools.