October 2, 2008 at 11:17 pm
Comments posted to this topic are about the item Let the Excel Play
October 3, 2008 at 12:41 am
Hi,
I am a great fan of using Excel for reporting.
I have made many similar reporting tools. With a little bit of extra VBA, you can easily format the returned data and make it look like a "formal" report.
One command I have found useful is :
rng.CopyFromRecordset rst
where you can dump the entire contents of the recordset at a specified range. This is useful if you don't want to take action on returned data such as substituting "--" for returned nulls.
Another variation of this sort of reporting tool I use a lot when checking data is a tool that allows me to write a SQL query as text in a few cells at the top of a particular worksheet. I then need to click a "Get Data" button which rolls the text in the adjacent cells together into a single string which I then pass on to the server. The resulting recordset is returned and I apply simple formatting to it. Not for your average end-users. As it stands, it handles all valid SQL queries, but I never use it to run DELETE, INSERT, UPDATE or DROP queries.
Adrian
October 3, 2008 at 2:09 am
Nice article.
October 3, 2008 at 2:18 am
I'm also a great fan of SQL data displayed in Excel, and I use it daily. However, I prefer to have the SQL in a stored procedure in the database, so you need only one line in VBA.
For all the rest, just how I like it!
Ronald
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
October 3, 2008 at 2:42 am
In MSAccess there is a tool called Microsoft Office Spreadsheet that you can insert in a form and write pretty much similar code to populate it with wanted data. Now, I am currently working on billings reporting spreadsheets where the parameters for the queries is captured in textboxes on the form and then a button that you click to populate the spreadsheet. I have about 6 different spreadsheets to create and the way I currently do it is a bit heavy and must eventually be exported to Excel. I will try your way and hope it will work well for me.
Thanks for a great article!:D:D:D:D:D:D:D:D
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
October 3, 2008 at 3:45 am
r.hensbergen (10/3/2008)
I'm also a great fan of SQL data displayed in Excel, and I use it daily. However, I prefer to have the SQL in a stored procedure in the database, so you need only one line in VBA.For all the rest, just how I like it!
Ronald
Here it is http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
Failing to plan is Planning to fail
October 3, 2008 at 3:56 am
This is exactly the type of solution I come across every day in our organization and I absolutely hate it! I'm not criticize it from a technical point of view but it is very dangerous when such applications spread in an organization. Imagine to work in such an organization with hundreds of such tools and every user asks for support and new features. It can be helpful for a very specific and one off solution but never ever let it become something where you value chain is depending on without taking care of who will support it, where does it fit in your application landscape and others questions you have to ask you.
Sorry to spoil your day with this.
October 3, 2008 at 5:40 am
Consider Sharepoint Excel Services instead - it's the way to go!
October 3, 2008 at 6:52 am
VBA is a good item to have in your tool kit. A couple of warnings:
-I have worked in environments where everyone was able to connect to a live instance of production and write whatever query they want. It can be difficult to monitor, track down, and generally to police performance problems that *will* happen (unless you have a server with unlimited resources).
-Be careful what permissions you give your users. In your example they must have SELECT permissons granted only. You may find one day that *someone* deleted all the rows in your bank_fraud_case table even if they do not use your Excel reporting solution. You can google anything these days.;)
October 3, 2008 at 6:54 am
hug.newsletter (10/3/2008)
This is exactly the type of solution I come across every day in our organization and I absolutely hate it! I'm not criticize it from a technical point of view but it is very dangerous when such applications spread in an organization. Imagine to work in such an organization with hundreds of such tools and every user asks for support and new features. It can be helpful for a very specific and one off solution but never ever let it become something where you value chain is depending on without taking care of who will support it, where does it fit in your application landscape and others questions you have to ask you.Sorry to spoil your day with this.
Some valid points there, have (in fact still do) encountered this type of "solution" loads in the past. If managed and supported correctly then it might not to be a problem, but the danger is when users are let loose on it themselves and you end up doing a cleanup job a year or two down the line.
Still, good article.
Agreed on moving the SQL into a Stored Proc. Also it's good practice to avoid hard-coding your connection string.
October 3, 2008 at 8:47 am
This type of a solution is extraordinarily powerful. Being able to retrieve data directly from the database for use in Excel has been a huge help in trying to get information out to the users. Once the data is in VBA, there are lots of other nice things that can be done with the data via VBA that doesn't work quite as well with SQL (my opinion). In addition, formatting of the spreadsheet is endless with VBA. With that said, i would like to add some caveats...
PROTECT YOUR CODE. In your example, the connection string had the userid and password in the clear. One person already mentioned this. This is really, really bad. If you don't have very tight controls over who can access data in your database, you have opened yourself up to data corruption from nefarious users that have free reign in your SQL Server once they find the userid and password. Several ways to work with this - PASSWORD PROTECT your VBA code - don't let your users see this. It is more work for the developer - you need to write the code to retrieve information, but it keeps people from doing bad things. Your reports become in effect, read only. Use an ODBC connection. It would need to be set up on the users machine, and depending on how many you have, this could be problematic (or not). Create a generic userid / password that gives access to selected files as READ ONLY. You do not have to put userid / password into the connection string.
October 3, 2008 at 9:02 am
I use ExcelWriter and combine with VBA through application to display SQL data to the Excel. This is more secure to data server I think.
October 3, 2008 at 10:04 am
hug.newsletter (10/3/2008)
This is exactly the type of solution I come across every day in our organization and I absolutely hate it!
Oiye...
Wow. I whole-heartedly agree! I am sitting here reading this post with some small amount of terror... of the pain of experience in dealing with such issues. It seems that every organization has some self appointed Excel evangelist that I spend half my time having to DEBUNK the values s/he contorted into their PIVOT and postulated to management as a whole. I despise having to go and fix their range calls, rounding errors, truncated zeros, and etcetera, merely because "our numbers do not match".
As a standard of practice, anytime someone comes to me with an Excel reporting issue, I lay it aside and go DIRECTLY to the source of the data. They are usually saying, "But that is not what my Excel sheet says...". It takes far less time to get the facts straight than trying to absorb their particular self-inflicted Excel predicament.
And the awful plentitude of those that think Excel is a good format to ship data in... Lesson #1: Zip Codes do NOT play well in Excel without some careful hand-holding, which never seems to happen at the sender end.
--- soap box mode = off ---
Please pardon my rant...
October 3, 2008 at 11:50 am
I prefer SSRS (Reporting Services) with its Export button where you can export to Excel, PDF and other formats.
Regards,Yelena Varsha
October 3, 2008 at 12:15 pm
Yelena Varshal (10/3/2008)
I prefer SSRS (Reporting Services) with its Export button where you can export to Excel, PDF and other formats.
Agreed. As long as the end user sticks to the source, the export for walking is fine in Excel (with minimal caveats).
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply