如何在Microsoft PowerBI中使用SQL Server存储过程？

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 :)

谢谢：）

4 个解决方案

#1

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查询编辑器中，选择新建源 - > 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!

希望这对你有用，就像它对我一样，干杯！

#2

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''');

#3

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/

#4

"Hi,

“嗨，

In an Excel workbook, open the ""Power Query"" tab.
在Excel工作簿中，打开“”Power Query“”选项卡。
Then choose the “From Database” drop down button and select “From SQL Server Database”
然后选择“从数据库”下拉按钮并选择“从SQL Server数据库”
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”
在SQL语句文本框中，键入“EXECUTE Procedure_Name”
Click Close and Load button "
单击关闭并加载按钮“

#1