Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |