Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
My question is mainly that I cannot find a solution on how to use a stored procedure through the Direct Query data connectivity method for Power BI, that is, I want to make a report in Power BI using Direct Query for its data set it's in a stored procedure.
Reading and consulting for several days I have found a possible solution (here they will tell me if it is the most optimal for my case) is to create a native query which calls the stored procedure, this in order to make use not of the stored procedure but of the native query from Power BI.
The stored procedure that I have to do the native query as I am thinking is the following:
ALTER proc [dbo].[TravelsCustomers]
@TypeOp int=0,
@Customer varchar(50)
as
begin
set nocount on
SELECT Estatus,
ClaveCustomer
Lote,
Address,
OC
Into #Response
FROM TravelVw V
WHERE Estatus = 'PROGRESS'
and TypeOp = Case when @TypeOp = 0 then TypeOp
When @TypeOp = 1 then 'FLASH'
When @TypeOp = 2 then 'SMALL'
End
and isnull(@Customer, '0') in ('0', ClaveCustomer)
and TypeOp != 'SALE'
Select V.*, I2.Coment, I2.MODIFIEDBY, I2.CreatedDateTime
From #Response V
left join (Select I.Travel,
isnull(I.Coment, '-') Coment,
I.ModifiedBy,
DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(),GETDATE()), I.CreatedDateTime) CreatedDateTime
From dbo.Travel I
Where I.CREATEDDATETIME = ( Select MAX(CREATEDDATETIME)
From dbo. I3
Where I.Travel = I3.Travel
)
) I2 on V.Travel = I2.Travel
end
Here I would like to have your support either to generate the native query or to give me a better option regarding the use of stored procedures with Direct Query from Power BI.
Hi @sebastianslz ,
I'm not quite sure if I understand your needs accurately.
Please see if the article helps:
How to load Stored Procedure data into SQL Server with DirectQuery
If I have not understood your needs correctly, please do not hesitate to inform me.
Hope it helps,
Community Support Team _ Caitlyn
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
90 | |
82 | |
62 | |
61 | |
58 |
User | Count |
---|---|
160 | |
114 | |
100 | |
74 | |
65 |