cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Power BI parameter for SQL Query

Hi Community,

Do you know if there is a way that I could use Paramter in Power BI for my SQL query?

I have a simple SQL query like this:

SELECT *
FROM table1
WHERE year = 2018

I would like replace 2018 with Parameter that a user could enter. I am using Import mode in Power BI and custom SQL for my sources. And feedbacks or thoughts would be appreciated

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi NickNg278,

 

Yes, you can call query parameter when you implement SQL statement in power bi, you can also refer to this blog:http://biinsight.com/power-bi-desktop-query-parameters-part2-dynamic-data-masking-and-query-paramete....

 

Regards,

Jimmy Tao

View solution in original post

4 REPLIES 4
Community Support
Community Support

Hi NickNg278,

 

Yes, you can call query parameter when you implement SQL statement in power bi, you can also refer to this blog:http://biinsight.com/power-bi-desktop-query-parameters-part2-dynamic-data-masking-and-query-paramete....

 

Regards,

Jimmy Tao

View solution in original post

Hello, 

 

Somebody have a solution ? 

Is actually a HUUUUUUUUGE Problem for my company and block all development on powerbi. 

How this version can be in production ??? 

 

Thanks. 

I'm probably a bit late but maybe this can help someone else.

 

First create a stored proceedure in your SQL server that has a start date (can be start + end date) but for our company we just want a start date:

Here is some simple SQl code to show this - outlined in bold the iimportant bits (im not here to teach sql lol)

USE [MyMainDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[sp_Midnight_Census]
(
@vStartDate Date
)
AS
BEGIN
SET NOCOUNT ON;

-- Step 1 is to create a temporary table to store our date range…
create table #dateTable (thedate datetime)

--Step 2 is create some variables for the start and end date.
declare @dtEnd date, @hour int, @days int
set @days = DATEDIFF(DAY, @vStartDate, CURRENT_TIMESTAMP)
set @dtEnd = dateadd(day, @days, @vStartDate)
set @hour = 00

--Step 3 is to insert our dates into the temporary table… we do this with a bit of SQL Server magic that selects sequential numbers from a master table and then adds the same number of days to our base date.
--I have also added 00 hours to the date (@hours) because for this dataset we want midnight patients. For midday etc @hour would be set to 12
insert into #dateTable
select DATEADD(hour, @hour, CONVERT(datetime, dateadd(day, number, @vStartDate))) as 'thedate'
from
(select number from master.dbo.spt_values
where [type] = 'P'
) n
where dateadd(day, number, @vStartDate) < @dtEnd


--Final step is to join this date table with the MyDATA transfer table. 
--NOTE: dischg_dtm = null so my join is in actual fact slightly wrong
select d.thedate, t.ward, count(*) as occupancy
from [server\HOSP2020].[MyDATA ].[dbo].[tablename] t
join #dateTable d on d.thedate between ward_admit and ward_disch
where t.ward_id like 'FP%' 
group by d.thedate, t.ward
order by d.thedate, t.ward

drop table #datetable

END

* Now I want to create my Power BI query/new paramter to handle this SQL connection

  • Open Power BI
  • Open the Document to link the data to
  • Click on: Get data > Blank Query
  • Click on Manage Parameters > New Parameter
  • Under Name type "Somethign Descriptive"
  • Type server\name into “Current Value”
  • Select – OK
  • Select Advanced Properties
  • Replace anything here with

    let 

    SQLSource = (vStartDate as date) => 

    let 

    Source = Sql.Database("server\Name", "ClinEpiReporting", [Query="exec sp_Midnight_Census '"& Date.ToText(vStartDate) & "' #(lf)#(lf)#(lf) #(lf)"]) 

    in 

    Source 

    in 

    SQLSource

  • Click Done
  • Select Query and enter a start date and then click “Invoke”.
  • It may show a warning
  • Click “Edit Permission”
  • It may show a warning to run the query in Native Mode. Click “Run”.
  • It will load all the data.
  • Click “Close and Apply”.
  • This should get you going, we found it relatively easy to create a stored proceedure using google. Everything outlined here is pretty much how we start off - of course our stored proceedure has a lot more in it and the one here is a simngle table example, not something useful to you, but ive outlined in bold text the main parts which let you make it a start date paramter, which I've found really useful in Power BI. Hope it helps.
Impactful Individual
Impactful Individual

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors