March 7, 2005 at 10:32 am
Hello,
I need to select a good strategy of producing a Cross-Tab report from SQL Server outputted into Excel format by a job. Please, share your opinion. The preferred solution I would like to be direct, from SQL Server to Excel. My first 2 solutions that are simple to develop do involve a report server in between.
My posible solutions include:
1. Use a third-party Front End like Business Objects, Crystal they can do that but the formatting in Excel output is not perfect. In addition the third-party front end is like a middlewhare between 2 forms of Data Storage
2. Use SQL Server Reporting Services.
2. Use T- SQL and use DTS to run it as is. I may use a solution from the book "Inside Microsoft SQL Server..." that includes Steve Rori's idea of creating a unit matrix. I have this book for SQL Server 7 and the page number 712 in this book, the chapter on Examples.
3. Use T-SQL and use DTS with additional steps running ActiveX script. I am not sure, can we manage Excel objects from VBScript?
4. Use Excel functinality. I have to research this better. Excel has pivots and I assume I can somehow use VBA.
Well, maybe someone already did something similar and will share the experience?
Thanks in advance,
Yelena
Regards,Yelena Varsha
March 8, 2005 at 9:05 am
Yes, you can use EXCEL and its Pivot Table with data coming
from external data sources as long as you can read from these source using OLEDB.
1. Open a new EXCEL sheet and go to the menu "Data -> Pivot Table"
2. Choose the option "External data source"
3. Click the button "Get Data"
4. Choose an existing data source from the list ot create a new one.
5. If you decide to create a new data source, click the connection button and define the OLE DB connection parameters
6. Once you reach your data source, choose from the list of tables and views and click "next"
7. you can choose a sort order and such and click "next"
8. Now you have the choice of editing the query in the Microsoft Query tools (choose fields, relate tables, set filters, etc.)
9. If you are done, take the data to EXCEL. You are then presented with the Pivot Table "drop area" that you can use to design your cross tab.
Finally, if you need to prepare the data source using VBA code here is a small hint :
Function ShowQuery()
Dim PV As PivotTable
Set PV = ActiveSheet.PivotTables(1)
Dim PC As PivotCache
Set PC = PV.PivotCache
Debug.Print PC.CommandText
End Function
Explore the pivot table and pivot cache objects. You will likely find everything you need to customize you solution.
HABIB.
March 8, 2005 at 10:42 am
Habib,
Thanks!!!!!! This is EXACTLY that the project manager wanted. She sent me 2 reports that her former consultant used to send to her. They look exactly like the one I created using your guidlines. I explored the options on the way, discovered a lot of presentation options including totals, subtotals, all the stuff. I had to group values on the way and use functions, so my query can be edited only in SQL panel and headers were somehow lost on the way, only headers from the field that came "as is" stayed.I still have to verify the numbers. Does not really matter, I can pre-process with functions and grouping into the staging table using SP, and let Excel take data from this staging table.
Do you think there is a way to use it as a job? I have to read about it. I don't have a clear understanding if it will refresh data when you open the report or if a report recipient has to have the data source of the same name, I suppose I will read about it.
I would like my script to run, refresh data and then email the file to the user. I already have the email with attachments part working in other projects.
What is your opinion of the following article that lets you insert a recordset by code? But, then, I have to pivot this recordset somehow.
http://support.microsoft.com/default.aspx?scid=kb;en-us;247412
"INFO: Methods for Transferring Data to Excel from Visual Basic"
Yelena
Regards,Yelena Varsha
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply