Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Let the Excel Play Expand / Collapse
Author
Message
Posted Thursday, October 2, 2008 11:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 17, 2012 5:38 AM
Points: 16, Visits: 93
Comments posted to this topic are about the item Let the Excel Play
Post #580057
Posted Friday, October 3, 2008 12:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 20, 2012 6:04 AM
Points: 9, Visits: 82
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
Post #580084
Posted Friday, October 3, 2008 2:09 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:46 AM
Points: 5,406, Visits: 1,400
Nice article.


Post #580124
Posted Friday, October 3, 2008 2:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 9, 2013 5:08 AM
Points: 1,123, Visits: 603
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 Hensbergen

Help 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
Post #580131
Posted Friday, October 3, 2008 2:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:21 AM
Points: 1,205, Visits: 923
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


Manie 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)
Post #580137
Posted Friday, October 3, 2008 3:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:59 AM
Points: 329, Visits: 470
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




Madhivanan

Failing to plan is Planning to fail
Post #580151
Posted Friday, October 3, 2008 3:56 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 2:51 AM
Points: 130, Visits: 802
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.
Post #580154
Posted Friday, October 3, 2008 5:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 28, 2009 4:19 AM
Points: 2, Visits: 11

Consider Sharepoint Excel Services instead - it's the way to go!
Post #580196
Posted Friday, October 3, 2008 6:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 5:39 AM
Points: 41, Visits: 378
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.;)
Post #580234
Posted Friday, October 3, 2008 6:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 9, 2013 3:40 AM
Points: 58, Visits: 153
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.
Post #580235
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse