November 18, 2005 at 12:51 pm
Hi experts,
We are in SQL Server 2000.
We want to distribute our Sales Report (in excel) via email to our 31 district managers. Each district manager will receive this excel for his own sales report with his own stores, and sample part of the excel looks like this:
Growing Real Sales Total Store4755 Store8473 Store8797
Food Sales Comp 8,484.78 3,469.36 2,537.48 2,477.94
Food Sales Non-Comp0.00 0.00 0.00 0.00
Total Food Sales 8,484.78 3,469.36 2,537.48 2,477.94
PY Food Sales Comp 7,780.32 3,621.00 1,971.94 2,187.38
Variance $ 704.46 (151.64) 565.54 290.56
Variance % 15.13% -4.19% 28.68% 13.28%
We want to design a program to manipulate sales data and load in a sales report table, for this excel sheet.
We have more than 250 stores, managed by 31 district managers. The excel will contains about 50 performance data, such as Food Sales Comp....
So far I have no clue how to get it done, and I need to gather some ideas from you experts:
What's the best way to design the table so that the data can be easily populated to excel?
How to dynamically distribute the data to 31 excels without create 31 excel templates in advance? (because we sometimes re-align the districts and we will have more district managers in the future)
Thank you.
November 18, 2005 at 4:25 pm
Hi,
I have to think about the dynamic implementation, but I do the following that I think can be modified to be implemented dynamically:
1. I create SQL Server Views that will return report data. I have a special Reports database that contains views and intermediate data
2. Create a Data source to your database from a computer running Excel
3. In Excel create a Pivot Table to get data from the views
4. Totals are calculated by Pivot Tables functionality.
5. You can not calculate totals for Variance (I think) or median numbers % because to get a average or median you are not adding %%. In this case in Excel I use VBA module (Macro) to populate appropriate cells.
6. Add appropriate formatting before and after Pivot Table to make a report look good.
Macro is essentially VBA. You can run ADO code there, so you will be able to manipulate what query is executed in SQL Server, with what parameters, like "select .... from my view... where managername = @@manager".
Or you can do it inside the loop from the regular VB using Excel object model
Hope it helps,
Regards,Yelena Varsha
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply