19

I want to generate reports from my SQL Server tables.

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

Asaf Savich
  • 623
  • 1
  • 9
  • 29

5 Answers5

13

To execute your SP in Power BI-->

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.

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.

3.You will see data for the parameters you passed in SP only.

  1. On Applying these changes, you will see the dataset for this in Power BI Desktop from where you can create reports.

NOTE: This works in"Import Query" option.

Hope this works for you as it did for me, Cheers!

Radhieka
  • 131
  • 3
  • 1
    This answer is still showing how to use a sproc- it is pasting in the 'exec' statement for running the sproc, not the contents of the sproc. – Rich Apr 20 '18 at 12:26
  • 2
    This can't pass dynamic variables to the procedure but at least this answer is the best one to be found on the web so far – Wouter Vanherck Sep 24 '18 at 12:01
11

You can use openquery. The following syntax will work with both import data and direct query methods in Power BI Desktop. 

SELECT *
FROM OPENQUERY ([server name],
'EXEC dbname.dbo.spname @parametername = ''R1''');

enter image description here

DatumPoint
  • 419
  • 4
  • 21
Niraj
  • 1,782
  • 1
  • 22
  • 32
  • 1
    Hey niraj, I keep getting an error when executing with OPENQUERY. "Microsoft SQL: 'OPENQUERY' rowset provider not supported in this version of SQL Server." – Asaf Savich Jun 09 '16 at 08:26
  • 1
    which version of sql server you are using – Niraj Jun 09 '16 at 10:18
  • 1
    It's 2021 and it's still not supported in Azure SQL server. Any ideas? I'm receiving the same error as @AsafSavich – Hanyu Wang Feb 04 '21 at 01:28
  • I also get this error when trying to DirectQuery Azure SQL, "Microsoft SQL: 'OPENQUERY' rowset provider not supported in this version of SQL Server." – Lauren Oldja Nov 12 '21 at 18:51
4

Expand the Advanced options in the Get Data -> SQL Server dialog, and write a SQL Statement to execute your stored procedure such as:

EXEC [dbo].[usp_NameOfYourStoredProcedure]

execute stored procedure in Power BI

C. Augusto Proiete
  • 24,684
  • 2
  • 63
  • 91
  • 3
    On Apply Changes the following error occurs: /!\ "Query name" Microsoft SQL: Incorrect syntax near the keyword 'EXEC'. Incorrect syntax near ')'. any way to get around this? – DeFlanko Oct 08 '19 at 17:55
  • This is what I ended up doing, and it worked on Azure SQL. I had to switch to Import mode. These documents helped me ensure my use case would be adequately supported in Import mode: Direct Query vs Import https://community.powerbi.com/t5/Desktop/direct-query-vs-import/m-p/112212 and Configuring Import Scheduled Caching https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-scheduled-refresh – Lauren Oldja Nov 12 '21 at 22:41
2

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.

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.

There is a great introduction course for this on the Power BI website:

https://powerbi.microsoft.com/en-us/guided-learning/powerbi-learning-2-1-intro-modeling-data/

Paul Andrew
  • 3,233
  • 2
  • 17
  • 37
  • 8
    I just want to point out that it's much easier to edit a large stored procedure in SQL versus trying to modify what Power BI has done to your query, which looks absolutely ridiculous. – Lord Helmet Jul 19 '18 at 15:29
1

"Hi,

  1. In an Excel workbook, open the ""Power Query"" tab.
  2. Then choose the “From Database” drop down button and select “From SQL Server Database”
  3. Fill in the Server and Database textboxes and click OK
  4. In the Navigator window, double-click the desired table
  5. In the Query Editor window, click on to the column to be displayed.
  6. Click close and select the ""Load"" button
  7. Connect to stored procedures.
  8. Create another power window and fill in the server and database textboxes.
  9. In the SQL statement textbox, type “EXECUTE  Procedure_Name”
  10. Click Close and Load button "
sqlprogrammers
  • 29
  • 1