表在Excel中来自SQL Server存储过程，在工作簿中包含参数字段

I need to create a dynamic report using Excel 2010 and SQL Server. Going off of previous ones created by others I figured out how to create the data connection and use the stored procedure with a specific parameter in the connection properties definition tab. The other reports use a ? in the command definition and use a cell in the workbook to define the parameter.

我需要使用Excel 2010和SQL Server创建一个动态报告。通过使用其他人创建的之前的方法，我了解了如何创建数据连接，并在connection properties definition选项卡中使用带有特定参数的存储过程。其他报告使用a ?在命令定义中使用工作簿中的单元来定义参数。

How do I do that too?

我该怎么做呢?

Specific steps to creating the connection/report/parameter selection or any help much appreciated.

创建连接/报告/参数选择或任何帮助的具体步骤非常感谢。

1 个解决方案

#1

I got it to work using the steps provided at the following linked blog post.

我使用以下链接的博客文章中提供的步骤使它工作。

http://codebyjoshua.blogspot.com/2012/01/get-data-from-sql-server-stored.html

Steps copied here in case link breaks in the future.

这里复制的步骤，以防将来链接中断。

Excel 2007 Instructions:

Excel 2007指令:

Select the Data tab on Excel's Ribbon, then within the Get Exernal Data group choose the "From other Sources" drop-down. Then Choose "From Microsoft Query"

选择Excel功能区上的Data选项卡，然后在Get用力数据组中选择“From other Sources”下拉列表。然后选择“从微软查询”
Within "Choose Data Source" pop-up box, select your SQL Server, then hit OK.

在“选择数据源”弹出框中，选择SQL服务器，然后单击OK。
Close the "Add Tables" popup if necessary.

如果需要，关闭“添加表”弹出窗口。
Click on the "SQL" button, or choose View > SQL to open the SQL pop-up editor.

单击“SQL”按钮，或者选择View > SQL打开SQL弹出编辑器。
Enter the following syntax: {CALL myDatabaseName.dbo.myStoredProc (?, ?, ?)}

输入以下语法:{调用myDatabaseName.dbo。myStoredProc(?、? ?)}

For example: {CALL northwind.dbo.spGetMaxCost (?, ?, ?)}

例如:{ northwind.dbo打电话。spGetMaxCost(?、? ?)}

Be sure to include the squiggly braces around the call statement. Each Question Mark (?) indicates a parameter. If your stored procedure calls for more or less parameters, add or subtract question marks as needed.

一定要在call语句周围包含一些弯弯曲曲的大括号。每个问号(?)表示一个参数。如果您的存储过程需要或多或少的参数，根据需要添加或减少问号。
Hit the OK button. A question box should pop-up saying "SQL Query can't be represented graphically, continue anyway?", just hit the OK button.

点击OK按钮。一个问题框应该弹出说“SQL查询不能用图形表示，继续吗?”，点击OK按钮。
You will now be asked for sample parameters for each question mark you included above. Enter valid parameter values for the data you are querying.

现在，您将被要求为上面包含的每个问号获取样本参数。为要查询的数据输入有效的参数值。
Once you have entered the last parameter, you should get some results back in Microsoft Query. If they look good, close Microsoft Query.

一旦您输入了最后一个参数，您应该在Microsoft Query中返回一些结果。如果它们看起来不错，那么关闭Microsoft查询。
You should now be looking at an "Import Data" pop-up. Click the Properties button, which will bring up the "Connection Properties" pop-up.

您现在应该看到一个“导入数据”弹出窗口。单击Properties按钮，它将弹出“连接属性”弹出。
Select the Definition tab, then select the Parameters button. You should now see a "Parameters" pop-up, where you can connect the parameter to a specific cell.

选择Definition选项卡，然后选择Parameters按钮。现在应该会看到一个“Parameters”弹出，您可以在其中将参数连接到特定的单元格。
Select Get the value from the following cell, and then connect to an appropriate cell in Excel that will hold your parameter, by clicking the little box with the arrow.

选择从下面的单元格中获取值，然后通过单击带有箭头的小框连接到Excel中的适当单元格，该单元格将保存您的参数。
If you want the data to refresh every time you change the cell containing the parameter, check the box stating "Refresh automatically when cell value changes"

如果每次更改包含参数的单元时都希望数据刷新，请选中“当单元格值更改时自动刷新”的复选框
Continue as above for the other parameters. When finished, click OK, to return to the Connection Properties pop-up. Click OK to return to the Import Data pop-up, and click OK again.

继续上面的其他参数。完成后，单击OK，返回连接属性弹出。单击OK返回导入数据弹出，然后再次单击OK。
You should now have some data straight from your stored procedure.

现在应该直接从存储过程中获得一些数据。

#1