Dynamic Database selection from Dashboard/Report

[DrPhillip]

Hi All,

I am very new to Power BI and trying to find out if meet our needs.  We currently have an on Prenmise SQL Server and have an extensive set of paginated reports built for our analysis.  We started looking at Mobile Reports but then came to Power BI after discovering the price point of a 4 core minimum SQL Enterprise license.  So....Power BI pro license it is LOL

We are mostly interested in using the technology for building dashboards as a relacement for some of the older pagineated reports however, there is a problem.  Our organization's data is contained in 5 seperate databases depenfing on previous acqusitions and also, possible future acqusuitions.  There is a need from our accounting/financing team to keep the data segregated and analysis perfoemd individually on each database.

In our paginated reports, we setup the connection string of our datasource where we pass in the database name as a parameter within the report that the user can select from a dropdown box.  The list of databases actually comes from a master database so, it is truly dynamic.  When building a report, we only build 1 version and we do not create different datasets for each database, just pass in the database name as described.  Can this same functionality be recreated in a dashboard?  My research on this forum suggests it is not but, wanted to reach out and ask the pro's before I have to find a different reporting tool.

Thanks

[Anonymous]

Hi.

We want to do exacly what you did. We want to select the correct (1) Database and (2) Company (in paginated reports) based on User Login ID

We have a mining ERP. Each client has a different database (i.e. not one cloud database where you just have to select the company).  We want to embed Paginated Reports but it does not allow parameters for the Server Name and Database Name like Power BI.  We can use URL parameters to set other parameters but it is not available for the Server and Database parameter settings. 

We want to use the same workspace and/or Apps for all the clients (i.e. not duplicate everything for each company).

Can you please explain where the parameter was passed? Is it in the connection string for Server and Database or is it a parameter that you passed in the stored procedure?

Not sure if we are allowed to communicate directly but my e-mail is hannes@datasaint.com

[AlexisOlson]

At the very least, you can set up parameters in the query editor to quickly switch between data sources in the Query Editor.

For the end-user to be able to do this, I think you'd need DirectQuery with Dynamic M query parameters. This is pretty new though and I haven't tried it myself.

[DrPhillip]

Hi @AlexisOlson,

Thank you for your suggestion.  It took me a while to find what you were referring to but yes, this can be done by editing the query on the Power BI portal/website.  I am currently working with Microsoft to see if there is a more user firendly way this can be achieved.  I will hopefully have a solution next week but, when switching databases on the query, you also need to switch the Gateway connection too because the gateway connect the Power BI site to the on-premise data.  You might be surprised by this but, when I change the DatabaseName Parameter Value, the Power BI Site also automatically changes the gateway being used in the Query (as long as the gateway Data Source has been pre-configured).

Hope to have a full solution to post next week.

[DrPhillip]

I should have mentioned that I have implemented a parameter in the underlying query based on some other forum posts.  It just seems like the ability to set the parameter from within the dashboard is not available as a feature.

To be honest though, I haven't discovered how I would set the paramter when loading the dashboard so I still have to work that part out first.  I just started installing the gateway so that I can use direct query too.