<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by shashank bhide
»
Let the Excel Play
36 posts, Page 1 of 4
1
2
3
4
»
»»
Let the Excel Play
Rate Topic
Display Mode
Topic Options
Author
Message
shashank-666535
shashank-666535
Posted Thursday, October 02, 2008 11:17 PM
Grasshopper
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
AdrianJ
AdrianJ
Posted Friday, October 03, 2008 12:41 AM
Forum 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
Anipaul
Anipaul
Posted Friday, October 03, 2008 2:09 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: 2 days ago @ 2:50 AM
Points: 4,785,
Visits: 1,334
Nice article.
Post #580124
Ronald H
Ronald H
Posted Friday, October 03, 2008 2:18 AM
Ten Centuries
Group: General Forum Members
Last Login: Friday, November 30, 2012 1:52 AM
Points: 1,116,
Visits: 602
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
Manie Verster
Manie Verster
Posted Friday, October 03, 2008 2:42 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 7:01 AM
Points: 1,151,
Visits: 878
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
Life is about choices.... I choose to be happy today
Post #580137
Madhivanan-208264
Madhivanan-208264
Posted Friday, October 03, 2008 3:45 AM
Old Hand
Group: General Forum Members
Last Login: Wednesday, September 12, 2012 5:17 AM
Points: 329,
Visits: 461
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
liebesiech
liebesiech
Posted Friday, October 03, 2008 3:56 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, May 10, 2013 3:55 AM
Points: 126,
Visits: 751
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
chris.ingram
chris.ingram
Posted Friday, October 03, 2008 5:40 AM
Forum 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
scott mcnitt
scott mcnitt
Posted Friday, October 03, 2008 6:52 AM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, April 23, 2013 10:50 AM
Points: 29,
Visits: 270
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
daft
daft
Posted Friday, October 03, 2008 6:54 AM
Valued Member
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:29 AM
Points: 58,
Visits: 151
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 »
36 posts, Page 1 of 4
1
2
3
4
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
