如何在 Microsoft PowerBI 中使用 SQL Server 存储过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37589448/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use SQL Server stored procedures in Microsoft PowerBI?
提问by AsafSavich
I want to generate reports from my SQL Server tables.
我想从我的 SQL Server 表生成报告。
I have some already made stored procedures that I would like to use to generate reports from.
我已经制作了一些我想用来生成报告的存储过程。
I haven't found a way to do so.
我还没有找到这样做的方法。
Only by rewriting the queries.
只能通过重写查询。
Thanks :)
谢谢 ：）
回答by Radhieka
To execute your SP in Power BI-->
在 Power BI 中执行 SP-->
1.In SQL Server right click on your SP and select Execute. Your code is executed and a new query window opens up which was responsible for execution. Copy that Query.
1.在 SQL Server 中右键单击您的 SP 并选择执行。执行您的代码并打开一个负责执行的新查询窗口。复制该查询。
2.In Power BI Query Editor, select New Source-->SQL Server. After giving the server and database, in the same window click on "Advanced Options", paste the query in the "SQL Statement" that opened up. Check "Navigate using full hierarchy" and click OK.
2.在Power BI Query Editor中，选择New Source-->SQL Server。提供服务器和数据库后，在同一窗口中单击“高级选项”，将查询粘贴到打开的“SQL 语句”中。选中“使用完整层次结构导航”并单击“确定”。
3.You will see data for the parameters you passed in SP only.
3.您将只看到您在 SP 中传递的参数的数据。
- On Applying these changes, you will see the dataset for this in Power BI Desktop from where you can create reports.
- 在应用这些更改时，您将在 Power BI Desktop 中看到此数据集，您可以从中创建报表。
NOTE: This works in"Import Query" option.
注意：这适用于“导入查询”选项。
Hope this works for you as it did for me, Cheers!
希望这对你有用，就像对我一样，干杯！
回答by Niraj
You can use openquery. The following syntax will work with both import data and direct query methods in Power BI Desktop.
您可以使用 openquery。以下语法适用于 Power BI Desktop 中的导入数据和直接查询方法。
SELECT *
FROM OPENQUERY ([server name],
'EXEC dbname.dbo.spname @parametername = ''R1''');
回答by Paul Andrew
Firstly I think its best to make the point out that Power BI isn't Reporting Services, it expects to be given existing table data or views that you then model within it's own environment before creating your dashboards.
首先，我认为最好指出 Power BI 不是 Reporting Services，它希望获得现有的表数据或视图，然后在创建仪表板之前，您可以在其自己的环境中建模。
If you can get Power BI to do the work of your stored procedures. Once you have your data model defined within Power BI it can be reused.
如果您可以让 Power BI 来完成存储过程的工作。一旦在 Power BI 中定义了数据模型，就可以重复使用它。
There is a great introduction course for this on the Power BI website:
Power BI 网站上有一个很好的介绍课程：
https://powerbi.microsoft.com/en-us/guided-learning/powerbi-learning-2-1-intro-modeling-data/
https://powerbi.microsoft.com/en-us/guided-learning/powerbi-learning-2-1-intro-modeling-data/
回答by C. Augusto Proiete
Expand the Advanced optionsin the Get Data -> SQL Serverdialog, and write a SQL Statement to execute your stored procedure such as:
展开Advanced options了在Get Data -> SQL Server对话框中，并写SQL语句执行存储过程，如：
EXEC [dbo].[usp_NameOfYourStoredProcedure]
回答by sqlprogrammers
"Hi,
“你好，
- In an Excel workbook, open the ""Power Query"" tab.
- Then choose the “From Database” drop down button and select “From SQL Server Database”
- Fill in the Server and Database textboxes and click OK
- In the Navigator window, double-click the desired table
- In the Query Editor window, click on to the column to be displayed.
- Click close and select the ""Load"" button
- Connect to stored procedures.
- Create another power window and fill in the server and database textboxes.
- In the SQL statement textbox, type “EXECUTE ?Procedure_Name”
- Click Close and Load button "
- 在 Excel 工作簿中，打开“Power Query”选项卡。
- 然后选择“From Database”下拉按钮并选择“From SQL Server Database”
- 填写服务器和数据库文本框，然后单击确定
- 在导航器窗口中，双击所需的表
- 在查询编辑器窗口中，单击要显示的列。
- 单击关闭并选择“加载”按钮
- 连接到存储过程。
- 创建另一个电动窗口并填写服务器和数据库文本框。
- 在 SQL 语句文本框中，键入“EXECUTE ?Procedure_Name”
- 单击关闭并加载按钮“
