Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I've been asked to create a Power BI report using a couple stored procedures accessing some confidential data. I've played around with the parameter feature on PBIDesktop, but never attempting to pass the data to a stored procedure.
Here is the proc with the dataset:
EXEC dbo.usp__SSRS__GetLoginDetails @p_LocationCode = 'ORA';
The options for the Parameters are received using the following proc:
EXEC dbo.usp__SSRS__GetLocations
ORA in the first proc is one of the location codes that are available from the second proc...
I'm looking for a way to be able to a location code on the report, and have that passed to the stored proc to provide the data for the report.
Thanks in advance for your assistance.
- Jarrin
HI @JJenkins2005,
Please refer to below link mentioned about invoke SP with query parameter.
let SQLSource = (para as text) => let Source = Sql.Database("testserver", "testdb", [Query="EXEC dbo.usp__SSRS__GetLoginDetails @p_LocationCode =' "¶&" ' "]) in Source in SQLSource
Regards,
Xiaoxin Sheng
Thanks for the information. I was able to get that to work, but that appears to require the user to edit the query... Is there any way to run an SP, provide the user with a filter to select the parm they want, and once selected that is passed to the SP and the data is provided? Users won't be able to edit the queries when we publish this to the service or PBIS.
HI @JJenkins2005,
Unfortunately, current query parameter and custom functions not work on power bi service.
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |