Pass a parameter/s to the SQL behind the Power BI Report via the URL
I am trying to embed Power BI into an ASP.NET application for Web Deployed Analytics. Everything is fine embedding the Power BI Report into the ASP.Net Application using iframe and a dynamic URL link to change Power BI Reports in the frame, but what I really need is the ability to pass a client based parameter to the SQL query (or stored procedure) of the Power BI Report via the URL.
The query parameter would be the User ID so that the SQL behind will filter the Database before returning the data to the Power BI Report.
This is a fundamental requirement as a minimum to create a robust Web BI Solution.
For large data sets, even passing parameters to the Query is key, to limit the data returned. Passing Filters to the Report does not seem a particularly elegant solution as it implies the entire data set is returned to the Power BI Report with the returned results filtered.
The need to pass parameters to the SQL Query or Stored procedure is in line what can be done via SSRS to achieve row level security and filtered datasets for optimal performance.
At this point in time, and from all the feed back, the only real option at the moment is to create separate Power BI reports by user and grant each user access to only their URL Reports.
The only other option I found that could work, but extremely clunky, was to capture the Logged in User as part of the behind query and use the User to derive the Row Level security. This requires the users to setup in the Database security :-
select * FROM [dbo].[datatable] [db]
inner join [dbo].[users_table] [ut]
on [ut].[LOGON_ID] = SYSTEM_USER
inner join [dbo].[user_access] [ua]
on [ua].[LOGON_ID] = [ut].[LOGON_ID]
and [ut].[ROLE_ACCESS] = [db].[ROLE_ACCESS]
(not the actual code, but you get the idea)