cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
premjitsaha Frequent Visitor
Frequent Visitor

Re: Use SQL Store Procedure in Power BI

thanks it worked

Zak2815 Frequent Visitor
Frequent Visitor

Re: Use SQL Store Procedure in Power BI

@nirajdubey, I can't for the life of me get this to work for Direct Query mode:

 

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

 

I've tried each of these below:

 

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

 

SELECT *
FROM OPENQUERY ([server name],
'EXEC dbname.dbo.spname @AsOfDate =
''03-16-2017 23:59:59'' ');

 

SELECT *
FROM OPENQUERY ([server name],
'EXEC dbname.dbo.spname @AsOfDate = CONVERT(DATETIME,''03-16-2017 23:59:59'',101) ');

 

None seem to work.  The times I can get a preview loaded, there's always an error on the "Apply".

 

Please help if anyone has succeeded in calling a Stored Proceedure in Direct Import Mode.

 

 

vineetmodi Frequent Visitor
Frequent Visitor

Re: Use SQL Store Procedure in Power BI( Passing Parameters)

I tried the same way to execute one of the stored procedure inside my Oracle package but failed

 

-- create a variable to hold SQL command to run
DECLARE @SqlCommand varchar(8000)
-- set the command to run
SET @SqlCommand = 'INTG_PA_BAL_DASHBOARD_PUB.populate_balance_db'
-- run it!
EXEC (@sqlCommand @parameter1=529,@parameter2="ALL",@parameter3="Feb-17",@parameter4=" ",@parameter=" ")

vineetmodi Frequent Visitor
Frequent Visitor

Re: Use SQL Store Procedure in Power BI( Passing Parameters)

I tried the same way to execute one of the stored procedure inside my Oracle package but failed

 

-- create a variable to hold SQL command to run
DECLARE @SqlCommand varchar(8000)
-- set the command to run
SET @SqlCommand = 'INTG_PA_BAL_DASHBOARD_PUB.populate_balance_db'
-- run it!
EXEC (@sqlCommand @parameter1=529,@parameter2="ALL",@parameter3="Feb-17",@parameter4=" ",@parameter=" ")

anakoom Regular Visitor
Regular Visitor

Re: Use SQL Store Procedure in Power BI

Have you find any solution as I have similar issue as I wants to pass the input parameter
But it won't be allow
Super User
Super User

Re: Use SQL Store Procedure in Power BI

@vineetmodi@Zak2815@anakoom Letting the cat out of the bag a little early here as I'm going to write up a blog around this, but rather than use a stored procedure you can use a Table Valued Function. This function allows for input parameters to be passed in. For example: if I were to query the AdventureWorks2014 database and a function in there looks like this:

 

 

CREATE FUNCTION [dbo].[ufnGetContactInformation](@PersonID int)
RETURNS @retContactInformation TABLE 
(
    -- Columns returned by the function
    [PersonID] int NOT NULL, 
    [FirstName] [nvarchar](50) NULL, 
    [LastName] [nvarchar](50) NULL, 
	[JobTitle] [nvarchar](50) NULL,
    [BusinessEntityType] [nvarchar](50) NULL
)
AS 
-- Returns the first name, last name, job title and business entity type for the specified contact.
-- Since a contact can serve multiple roles, more than one row may be returned.
BEGIN
	IF @PersonID IS NOT NULL 
		BEGIN
		IF EXISTS(SELECT * FROM [HumanResources].[Employee] e 
					WHERE e.[BusinessEntityID] = @PersonID) 
			INSERT INTO @retContactInformation
				SELECT @PersonID, p.FirstName, p.LastName, e.[JobTitle], 'Employee'
				FROM [HumanResources].[Employee] AS e
					INNER JOIN [Person].[Person] p
					ON p.[BusinessEntityID] = e.[BusinessEntityID]
				WHERE e.[BusinessEntityID] = @PersonID;

		IF EXISTS(SELECT * FROM [Purchasing].[Vendor] AS v
					INNER JOIN [Person].[BusinessEntityContact] bec 
					ON bec.[BusinessEntityID] = v.[BusinessEntityID]
					WHERE bec.[PersonID] = @PersonID)
			INSERT INTO @retContactInformation
				SELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Vendor Contact' 
				FROM [Purchasing].[Vendor] AS v
					INNER JOIN [Person].[BusinessEntityContact] bec 
					ON bec.[BusinessEntityID] = v.[BusinessEntityID]
					INNER JOIN [Person].ContactType ct
					ON ct.[ContactTypeID] = bec.[ContactTypeID]
					INNER JOIN [Person].[Person] p
					ON p.[BusinessEntityID] = bec.[PersonID]
				WHERE bec.[PersonID] = @PersonID;
		
		IF EXISTS(SELECT * FROM [Sales].[Store] AS s
					INNER JOIN [Person].[BusinessEntityContact] bec 
					ON bec.[BusinessEntityID] = s.[BusinessEntityID]
					WHERE bec.[PersonID] = @PersonID)
			INSERT INTO @retContactInformation
				SELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Store Contact' 
				FROM [Sales].[Store] AS s
					INNER JOIN [Person].[BusinessEntityContact] bec 
					ON bec.[BusinessEntityID] = s.[BusinessEntityID]
					INNER JOIN [Person].ContactType ct
					ON ct.[ContactTypeID] = bec.[ContactTypeID]
					INNER JOIN [Person].[Person] p
					ON p.[BusinessEntityID] = bec.[PersonID]
				WHERE bec.[PersonID] = @PersonID;

		IF EXISTS(SELECT * FROM [Person].[Person] AS p
					INNER JOIN [Sales].[Customer] AS c
					ON c.[PersonID] = p.[BusinessEntityID]
					WHERE p.[BusinessEntityID] = @PersonID AND c.[StoreID] IS NULL) 
			INSERT INTO @retContactInformation
				SELECT @PersonID, p.FirstName, p.LastName, NULL, 'Consumer' 
				FROM [Person].[Person] AS p
					INNER JOIN [Sales].[Customer] AS c
					ON c.[PersonID] = p.[BusinessEntityID]
					WHERE p.[BusinessEntityID] = @PersonID AND c.[StoreID] IS NULL; 
		END

	RETURN;
END;

 

In my advanced options - SQL Query in Power BI I can call it just executing the select like this:

select * from [dbo].[ufnGetContactInformation]  (73)

and the output is returned as expected. This also allows you to do any CTE's or temp tables within the function.


Near SE WI? Join our PUG Milwaukee Brew City PUG
anakoom Regular Visitor
Regular Visitor

Re: Use SQL Store Procedure in Power BI

Thanks but what if I wants to use SP instead of table valued function
Super User
Super User

Re: Use SQL Store Procedure in Power BI

@anakoom You can't. If you watch what is actually sent to the SQL server you will see that an added syntax is added to the command which invalidates the statement. In Import, it just executes the SQL statement. In Direct Query it changes it to something like Select * from () - this is off the top of my head. In either case, that is why the openquery works, or the table valued function because both of those wouldn't create un-executable code.

I'm giving you an alternative to a SP because that method doesn't work, you can transform whatever you are doing in that SP to the TVP. 


Near SE WI? Join our PUG Milwaukee Brew City PUG
anakoom Regular Visitor
Regular Visitor

Re: Use SQL Store Procedure in Power BI

Thanks ..let me convert to TVF..is there any way I can allow user to
Pass parameter value and data will fetch based on given parameter?
Super User
Super User

Re: Use SQL Store Procedure in Power BI

@anakoom Probably not in the way you are thinking...

Power BI is best when you pull in the larger dataset and add slicers and filters to the report.

I'm not aware of any way to pass a parameter all the way downstream from a deployed report in the Power BI Service. You can do this in the desktop, but that would be stuck in that mode from my understanding.


Near SE WI? Join our PUG Milwaukee Brew City PUG

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 1,832 guests
Please welcome our newest community members: