Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Pass parameters dynamically from Power BI to Stored Procedure

  • 1.  Pass parameters dynamically from Power BI to Stored Procedure

    Silver Contributor
    Posted Jun 06, 2018 04:55 PM
    Hi,

    I have a stored proc that takes a hotel name as a parameter to retrieve a whole set of measures required by my dashboard.
    I want to be able to allow the users to select a specific hotel from a drop down/slicer and pass that hotel name to my stored proc and refresh the dashboard for the selection made.
    I cannot load all the data first and then allow user to select the hotel name since the fact tables are extremely huge and would impact performance. Is there any way to dynamically pass the values to the Stored procedure as a parameter(at run time)?

    Any iseas would assist.

    Thanks


  • 2.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Posted Jun 07, 2018 03:25 AM
    ​declare Parameter1
    exec StoredProcedure Parameter1

    ------------------------------
    Dražen Đurinić
    CEO
    DOMINO GRUPA d.o.o.
    Zagreb
    +38516182500
    ------------------------------



  • 3.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Silver Contributor
    Posted Jun 07, 2018 10:30 AM
    I am already doing this. I want the selection from the slicer to be passed as a parameterSP during runtime.
    So the SP should be executed dynamically


  • 4.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Bronze Contributor
    Posted Jun 07, 2018 03:49 PM
    I think what you are looking for is writeback functionality. Unfortunately unsupported by Power BI currently despite a lot of use cases. What you may require is a PowerApps app that you can embed in Power BI. That is one of the only ways I have seen someone take information from the current report's evaluation context and use it to change data in a source system.

    ------------------------------
    Anthony
    ------------------------------



  • 5.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Silver Contributor
    Posted Jun 07, 2018 04:31 PM
    @Anthony Yanan​ Thanks for your recommendation. I hope Pwer BI gets this functionality very soon, this seems to be such an important requirement, I am surprised it is not yet available


  • 6.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Bronze Contributor
    Posted Jun 08, 2018 02:56 AM
    The nearest I've seen to this is by using a PBI template and sharing that using PBI desktop. It wouldn't allow you to switch parameters in the report though, you'd have to reload the template each time, and you wouldn't be able to share the report via the PBI service

    ------------------------------
    Johnny Winter
    Business Intelligence Developer
    ------------------------------



  • 7.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Bronze Contributor
    Posted Aug 25, 2018 03:29 PM
    Hi Shilpa,

    I am also looking for same functionality,If you resolved or find any solutions​,Please let me know .


    ------------------------------
    srini N
    Power Bi Developer
    4252299069
    ------------------------------



  • 8.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Posted Mar 14, 2019 05:18 AM
    Hi, Sirini,
    I am also  facing the same issue. If you found any solution, kindly share.

    ------------------------------
    Sana Abdul
    Developer
    30040033300
    ------------------------------



  • 9.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Silver Contributor
    Posted Jun 08, 2018 04:12 AM
    @Rose Spitzer is there anyway you can run a 'Community Best Question/Answer of the Month" and reach out to community members to share their knowledge and potential answers, as this question merits a community effort to get a credible answer.
    Thanks David

    ------------------------------
    David Moss
    Advanced Analytics & Power BI Azure Solutions
    Wottabyte
    London
    ------------------------------



  • 10.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Posted Jun 08, 2018 08:57 AM
    Hi @David Moss. Yes, I can try to bring some attention to this question. Do you know if the feature mentioned above is already listed on the ideas forum? This is a good way to get the issue in front of the Power BI Development team: ​

    ------------------------------
    Rose Spitzer
    PUG General Manager
    rose.spitzer@pbiusergroup.com
    ------------------------------



  • 11.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Silver Contributor
    Posted Jun 08, 2018 03:45 PM
    @Rose Spitzer I do see some ideas for this issue:

    Parameters
    Microsoft Power BI remove preview
    Parameters
    Execute stored procedure parameters
    View this on Microsoft Power BI >


    Passing Parameters to Power BI through Stored Procedure
    Microsoft Power BI remove preview
    Passing Parameters to Power BI through Stored Procedure
    There Should be Provision of passing the Parameter to the report like we do in SSRS Reports. There should be any method to execute Stored Procedure by passing the Parameters
    View this on Microsoft Power BI >
    https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/20367082-passing-parameters-to-power-bi-through-stored-proc​​




  • 12.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Posted Jun 08, 2018 04:42 AM
    ​Hi Shilpa,

    You may have seen these before, the below Guy in a Cube video's might give you some ideas. They're not exactly the use case you have described, but they are close and might help !.

    Failing that, as already mentioned, PowerApps might be the best solution

    Parameters and Templates with Power BI Desktop
    YouTube remove preview
    Parameters and Templates with Power BI Desktop
    In this video, Patrick shows a way you can use parameters and templates, with Power BI Desktop, to provide prompts for your connection's server and database properties. This is great for when you are creating a report, and need to share it with a customer that may have a different name for the server and database name, but the database schema is the same.
    View this on YouTube >



    Dynamic Power BI reports using Parameters
    YouTube remove preview
    Dynamic Power BI reports using Parameters
    In this video, Patrick shows you how you can use a parameter, within a Power BI report, to dynamically change the data in a report. This uses M Functions within Power Query and a second source that has the key values to pull. Then using a gateway to enable refresh.
    View this on YouTube >



    Dynamic filtering with Power BI
    YouTube remove preview
    Dynamic filtering with Power BI
    In this video, Patrick looks at taking URL filtering to the next level. He shows how to use dynamic filtering with Power BI reports. He also shows a neat trick of setting a link image for Web URL fields. Filtering in Power BI: https://powerbi.microsoft.com/en-us/blog/filter-a-report-with-a-url-query-string-parameter/ https://powerbi.microsoft.com/en-us/documentation/powerbi-service-url-filters/ LET'S CONNECT!
    View this on YouTube >


    ------------------------------
    Gavin Clark
    Process Manager Data Visualisation
    Pramerica
    Letterkenny
    749197911
    ------------------------------



  • 13.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Silver Contributor
    Posted Jun 08, 2018 03:38 PM
    @Gavin Clark​ Thanks much, I have seen these videos. They won't work in our scenario. We are embedding Power BI on our application and we want to avoid our end users having to enter parameters, so we are looking for a slicer option.
    For now, we will probably just avoid the Stored procedure execution from dashboard. We will pre-load the dashboard data on an aggregate table and schedule the Power BI data refresh multiple times a day. So we can use slicers on the dashboard. But I think allowing for dynamic parameters via slicer would be an ultimate solution so that we will not have to build aggregates for each dashboard. This is such a powerful tool.


  • 14.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Posted Jul 02, 2018 05:17 AM
    Hey Shilpa,

    If you are using PowerBI Embedded and want to filter the report to specific data, before being rendered, you may want to try setting the filters using jscript as described @ Introducing the new Power BI JavaScript API | Microsoft Power BI Blog | Microsoft Power BI
    Microsoft remove preview
    Introducing the new Power BI JavaScript API | Microsoft Power BI Blog | Microsoft Power BI
    Today we are excited to announce the release of our new JavaScript API, which provides bidirectional communication between Power BI reports and your application. The JavaScript API enables you to more easily embed reports into your applications and to programmatically interact with those reports so that the applications and the reports are more integrated.
    View this on Microsoft >


    ​Hope this helps.

    Regards,

    ------------------------------
    Ashish Ruparel
    Sonata Software North America
    Bangalore
    ------------------------------



  • 15.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Posted Mar 14, 2019 05:21 AM
    Hi Shilpa,

    I am also looking for same functionality,If you resolved or find any solutions​,Please let me know .

    ------------------------------
    Sana Abdul
    Developer
    30040033300
    ------------------------------



  • 16.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Posted Jun 26, 2018 10:12 AM
    For the similar solution, I am using Direct Query to solve the issue.

    But, I flattened the table and as my parameter is user logged in, I am not using the slicer to select the value(s).
    So, it was an easy stuff for me.
    Table

    ------------------------------
    Yaswanth (Yash) Muthakapalle
    Greater Chicago Area
    (302) 724-9274
    ------------------------------



  • 17.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Posted Sep 25, 2018 04:46 AM
    How do you know which user is logged to sql server in direct mode?
    Because if I deploy Desktop to Pro, I must define sql user/password in adwance, and all queries in direct mode is executed on sql server thru gateway use this only user?

    ------------------------------
    se
    ------------------------------



  • 18.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Silver Contributor
    Posted Sep 25, 2018 01:48 PM
    This would be a handy feature to have, but it's not possible at this time and I think enabling it would require some pretty huge changes in how the data flow is programmed. What you are asking for is similar to a Direct Query. Every time you change the parameter, you'd have to rebuild your data model, which would likely take more than a few seconds if your datasets are large. It would also mean that the end users have the ability to execute that stored procedure on your server however frequently they want (potential for DDOS).

    Can you explain a bit more about how you ideally would like it to work? How frequently does your source table update/change? How much traffic do you expect on the report? When you switch hotels, are you looking for fresh up-to-the-minute data? Is there a reason Direct Query is out of the question?


    ------------------------------
    Alexis Olson
    DFW, Texas
    ------------------------------



  • 19.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Posted Oct 26, 2018 01:43 PM
    ​You can actually develop an SSRS report using ReportBuilder and publish it to a PowerBI Report Server.
    See example below.
    We allow end user to enter their filter parms/values. Then, query executes with those parms before bringing the data.
    Bonus... You can export data into multiple formats: PDF, Excel, Power point...etc.

    That's the beauty of PowerBI. I can bring my legacy SSRS reports while developing new ones, and have them run side by side.

    Passing Filter parms via PowerBI

    ------------------------------
    MAJ SWEIDAN
    ------------------------------



  • 20.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Top Contributor
    Posted Mar 14, 2019 07:31 AM
    Have you considered using a Direct Query connection for your facts table?

    ------------------------------
    Lutz
    ------------------------------



  • 21.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Posted Jun 11, 2019 11:04 AM
    I was just investigating this myself.
    I use SSRS all day long and parameters, dynamic parameters, and cascading parameters are a big part of how we use SSRS.
    It seems like going backwards in Power BI.

    It should be a simple task to call a stored procedure and return information that needs to be presented to the user for selection to run their report.
    In addition, we use SSRS to pull user info so that they only get parameters or results that are applicable to them.

    ------------------------------
    Timothy Caldwell
    Programmer
    7274810624
    ------------------------------



  • 22.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Top Contributor
    Posted Jun 11, 2019 12:07 PM
    I think we're missing the point here. You can't compare SSRS to Power BI, they serve different purpose. The OP stated they "can't load all data and then let the user decide"  but that's exactly what Power BI and the other BI tools are about. They are supposed to afford the end user the flexibility to choose the cut of the data that suits them most, combining data from many different sources. Dynamic restrictions are applied in the datasets, not in the data source.

    ------------------------------
    Lutz
    ------------------------------



  • 23.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Posted Jun 19, 2019 01:52 AM
    Hi, I am 100% with Shilpa in that I desperately need the ability to pass a parameter to the SQL stored procedure behind in a Direct Query PowerBI uploaded to PowerBI Report Server, same as you can with SSRS, the reason being obvious.
    Microsoft have supplanted SSRS Report Server with PowerBI Server in SQL2017, as PowerBIServer will manage SSRS Report Projects same as SSRS, and you can't run SSRS Report Server and PowerBI Server concurrently under SQL2017, so the direct Microsoft is taking in clear, PowerBI Server is the way forward for Web Hosted Reporting and PowerBI Services.  Hence one would expect an on-premise deployment for PowerBI via the Web to work the same as SSRS.
    The key thing being able to imbed PowerBI into an ASP.Net Application, same as SSRS Reports, although for SSRS it's via a ReportViewer Control and for PowerBI, it's via a rather crude URL link to an iframe.  Now imbedding PowerBI into an ASP.Net Application works OK, and is pretty easy, the only thing that you can pass via the URL are Filter Parameters, and these don't work for Direct Query.
    What I want, and so many others want, is to imbed PowerBI into an ASP.Net application in Direct Query mode, so interaction and Security is dynamic.  Just a single parameter to the Stored Procedure would mean true row level security could be implemented where the SQL Store Procedure would only return the rows that match the security profile of the logged in user.  The logged in user would be resolved by the ASP.Net application.
    We use dynamic SQL queries in our stored procedures (are dynamically constructed) and are executed as exec(blaa blaa blaa) commands which provided the first hurdle in that Direct Query in PowerBI can't consume an exec command, Import does, but not Direct Query, so the only workaround was to use the OPENQUERY option in SQL Server to specify the Result Set definition.
    I don't want to use PowerBI Filters on prepopulated PowerBI Models, I want interactive dynamic modeling and Dashboards through ASP.Net where data is dynamically presented and application driven Row Level Security can be applied.​

    ------------------------------
    Eugene Harvey
    1080
    Auckland
    274999485
    ------------------------------



  • 24.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Top Contributor
    Posted Jun 20, 2019 01:08 AM
    A suggestion is like, you can create View/Table out of your Stored Procedure Code and create Direct Query Method for the this Fact Table and Join it with your Hotel Table, now if you filter it with Hotel, Direct Query will do the same for you like you are trying to do with Stored Procedure {it will load only that data in your report which is filtered}.

    I know its a workaround but right now I feel it would be one of the option for you.

    Looking forward for your feedback

    ------------------------------
    Farhan Ahmed
    Senior Business Intelligence Consultant
    Xerva
    karachi
    3452523688
    ------------------------------



  • 25.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Posted Jan 02, 2020 09:49 AM
    Hi Shilpa,

    I'm also working on the same page, Have you got any solution for this.

    Thank you,

    ------------------------------
    venu sura
    ------------------------------



  • 26.  RE: Pass parameters dynamically from Power BI to Stored Procedure

    Posted Jan 03, 2020 08:54 AM
    Edited by Peter Gregory Jan 03, 2020 08:56 AM
    I'm an accountant that builds reports so the fancy technical solutions are out of my league, but I have a simpler idea.  If I'm reading your problem correctly could you just create a home page that uses bookmarks (labeled as hotel name) that when clicked jump to a different page where your parameter is set for that hotel?

    ------------------------------
    Peter Gregory
    ------------------------------