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.
