Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Let the Excel Play


Let the Excel Play

Author
Message
shashank-666535
shashank-666535
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 93
Comments posted to this topic are about the item Let the Excel Play
AdrianJ
AdrianJ
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 83
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
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6275 Visits: 1407
Nice article.



Ronald H
Ronald H
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1131 Visits: 612
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
Manie Verster
Manie Verster
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1308 Visits: 986
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!BigGrinBigGrinBigGrinBigGrinBigGrinBigGrinBigGrinBigGrin

:-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)
Madhivanan-208264
Madhivanan-208264
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 Visits: 476
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
liebesiech
liebesiech
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 854
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.
chris.ingram
chris.ingram
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 11
Consider Sharepoint Excel Services instead - it's the way to go!
scott mcnitt
scott mcnitt
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 429
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.Wink
daft
daft
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search