How to use SQL Server stored procedures in Microsoft PowerBI?

sql sql-server stored-procedures business-intelligence powerbi
AsafSavich picture AsafSavich · Jun 2, 2016 · Viewed 42.1k times · Source

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

Answer

Radhieka picture Radhieka · Jul 22, 2016

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!

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
Search text in stored procedure in SQL Server

I want to search a text from all my database stored procedures. I use the below SQL: SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_…

Read More
SQL Server - SELECT FROM stored procedure

I have a stored procedure that returns rows: CREATE PROCEDURE MyProc AS BEGIN SELECT * FROM MyTable END My actual procedure is a little more complicated, which is why a sproc is necessary. Is it possible to select the output by …

Read More