Anybody managed to produce an excel chart from data in an SQL server?
(without importing the data to excel)
Answers on a postcard or better in a txt file
Chat Forum
pushing luck on excel questions (with added sql)
-
Posted 3 months ago #
-
When you say without importing data into Excel are you talking manually or automatically?
If manually, embed a Microsoft Query query in the spreadsheet, create the chart from the data table and set the query to refresh when the spreadsheet is openned etc. If you want the chart to be linked directly to the underlying data table, then no, don't know if that is possible
Posted 3 months ago # -
I've done it. The exact instructions will depend on the version of excel you are using.
This breaks it down for 2010:
Google search 'ms query excel external data' will get you various tutorials for various versions.
I may be able to help with more specific questions but I basically winged it and got there with half a day of messing about.
Pivot charts worked especially well for what I was doing. They are pretty cool.
Posted 3 months ago # -
You need to get the data into excel. So MS Query like the chap above said.
I don't think there's any way to chart data directly from a data connection - the data well come into a work sheet and then you can chart it.
Posted 3 months ago # -
Think it's poss without an embedded query if you set up a pivot chart as there is no data access for the user (unless they query the pivot cube using VBA).
Prob not a fantastic solution as my experience with pivot charts is they're bobbins
EDIT: Seems some other users like them...
Posted 3 months ago # -
pivot charts are for losers!!
the idea is not to let the data reside in excel as there is a metric **** ton of it!
Have 1 method to try tomorrow using Visual Studio but nothing else
off to explore c# and web
Posted 3 months ago # -
You only embed the query in the spreedsheet - written using the ms query UI, although I think it allows you to view and manually modify the SQL. Then when you refresh, it pulls in the data from the database so you can manipulate it to your heart's desire. The spreadsheet size does not increase based on the amount of data.
Pivot charts seem to be the most integrated but I had it working with simpler graphs as well.
I found the option to refresh the data on reopening the spreedsheet did not work and I had to hit refresh twice - once to reload the data and once to refresh the graphs - but I guess I could have been doing something wrong.
You can do all sorts of funky stuff by inserting information from variables into the query from cells (I was using dates, for example, to create daily, monthly and yearly reports).
It works up to a point but it really depends on what you are trying to do as to whether it will work for you or whether you need something more sophisticated.
Posted 3 months ago #
Reply
You must log in to post.
