Excel with Stored Procedures

Question

Excel with Stored Procedures

Viewing 15 posts - 76 through 90 (of 120 total)

You must be logged in to reply to this topic. Login to reply

Eric L Hackett SSC Eights! Points: 978 More actions · Answer 1

Right click on the toolbar and select Customize.

On the Commands tab, select Data from the Categories list. You should then be able to find 'Refresh All' in the Commands list.

Drag it and drop it on your toolbar where you'd like it.

This is for Excel 2003, not sure about other versions.

This change will be specific to the computer, not to the spreadsheet.

mmalik SSC Rookie Points: 29 More actions · Answer 2

mmalik

SSC Rookie

Points: 29

March 6, 2009 at 3:36 pm

#955312

Thanks, it got this working...

mark.loomis Grasshopper Points: 13 More actions · Answer 3

I've used the DQY queries / SQL parameterized procedures for a while with SAP among other things (because people always want to see the data in Excel). It's super fast, simple, and clean!

Once the DQY has brought back data to Excel can additional commands in the DQY file execute Excel commands (for instance to create graphs of the data)?

Thanks.

Eric L Hackett SSC Eights! Points: 978 More actions · Answer 4

The .dqy file is very limited. The query text itself cannot even have a line break.

However I use named ranges to create graphs that change with the result of the dynamic query. Use the OFFSET and COUNT functionS to define columns (and number of rows) within the result set (which is automagically a named range in Excel). Then use the SERIES function (what you see in the formula bar when you click on a series in a graph) to define the series using the named ranges rather than relative references.

Nugby SSCommitted Points: 1612 More actions · Answer 5

I can't believe I've never noticed that 'Import External Data' before. Would probably end up being quicker than writing an Ad Hoc SQL query in some cases (where the repeatable ones or those with only one or two variables I do have set up using VBA).

Thanks!

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 6

Nice article as it focus on a common need:

"Basically people are comfortable with Excel that is why they use it."

Sometimes you only what figures or graphs to be presented without the need to do more

with the data. Plain Reports.

Sometimes you (the user) have the need to massage the data. The question is how to provide the

user with a tool to do that (and also to select the data). The spreadsheet like Excel has now for decades been an accepted tool to fulfill that need. Is it true that all analysing tools either OLAP like

Cognos or other tools claim they have an interface to Excel?

With tested!! stored procedures or views where the user can select data with parameters and paste

the result into one or more Excelsheets is a solution I have good experience with. I use VBA , Remote OLE DB and ADO which might be off line for members of this forum? Also I use the same approach to let the user enter data from Excel into the database. If interested I can write more about my solution.

//Gosta

Stephen Byrne SSC Enthusiast Points: 156 More actions · Answer 7

I've been using Excel as the presentation layer of my reporting for ages now, I just found it to be quick and versatile, plus people can play around with the data as much as they want.

As regards parameterised queries, I always use an SP and then manually set the parameters in the Before and After Refresh methods of the WorkSheet

i.e in the "ThisWorkbook" code:

dim withevents qry as Querytables

Private Sub Workbook_Open()

Set qry = Sheet1.QueryTables(1)

End Sub

Private Sub qry_BeforeRefresh(Cancel As Boolean)

'get your parameters here e.g

x = Sheet1.Cells(1,2).Value

y=Sheet1.Cells(1,3).Value

qry.CommandText="ReportServer..MyProc @x=" & X " ,@y=" & y

End Sub

Private Sub qry_AfterRefresh(ByVal Success As Boolean)

'put formatting and post-processing code here

End Sub

I find this gives you total control over how the query refreshes and where the params come from - for example you can display a custom dialog box when the user clicks "Refresh" whereby the parameters for the query might be selected from a list generated by another query. Also allows you to properly save the formats of the sheet (code required to do this) and so on

Plus is allows me in the formatting and processing code to do things you can't easily do when you're using an auto-formatted report, like maybe doing summing or averaging by some weird method which your users insist you do. Sure, you have to manually handle any grouping code, but it's not that hard once you get into it.

sgleeson SSC Rookie Points: 49 More actions · Answer 8

By using {Call [Proc Name] (?)} you can still use the native parameter handling without VBA code.

db042188 SSC Eights! Points: 819 More actions · Answer 9

does anybody know if excel can work with views? Or will sps be the only recourse in that situation?

sgleeson SSC Rookie Points: 49 More actions · Answer 10

Yes, but without parameters. A View works just like a table. so the SQL could be as simple as SELECT * FROM [ViewName] in the MS query, or simpler still just select the View name from the pick list.

Stephen Byrne SSC Enthusiast Points: 156 More actions · Answer 11

...and that's where VBA helps you out because you can dynamically construct a WHERE clause for your Select from the view, with "Parameters"

LadyReader Ten Centuries Points: 1192 More actions · Answer 12

I have a spreadsheet, created via .Net automation, and populated from a sproc, with one column left blank (the sproc supplies the column header). Now I need to continuously update that column with the result set of another sproc, using as input parameters the values in 3 columns returned by the first sproc. That is, the values in A2, B2 and C2 will be used as parameters to populate F2, and A3, B3 and C3 will be used to call the same sproc to populate F3, etc...

The spreadsheet is re-populated via the first sproc once each morning, but the 2nd needs to be run every x minutes. How can I accomplish this? Thank you for all suggestions and explanations, in advance.

sgleeson SSC Rookie Points: 49 More actions · Answer 13

Indeed, I guess I just prefer to keep VBA out of the picture for security compliance where possible. You can wrap the view with a patameterized proc and call the proc from excel using parameters without VBA

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 14

LadyReader

Is it OK if I do it in VBA for you? I am bussy at the moment so please let me know if my help

is accepted.

//Gosta

sgleeson SSC Rookie Points: 49 More actions · Answer 15

Maybe better to do the work on the SQL side? If not maybe you could establish a zone on the sheet that combines the results of the ,Net automation one and the more frequent query, but I don't think an call to external data can update an area that is feed by another external query.