Pass Input Parameters to SQL Queries

[MSingh1]

Hello Experts,

I am new to Power BI and working on a Power BI Desktop report.

In this report I have created an SQL connection to execute a Stored procedure with 2 Date parameters.

Currently, in power BI Desktop,  I select edit queries and pass the Input Parameters (Invoke Function) to pull data between required timestamps.

To improve user experience, I want to pass Input Parameters without having to navigate to Edit Queries every time. Is there any way to do this in power BI desktop or service. OR is there any better way to call sql stored procedure for which user does not have to select Edit Queries every time.

Many Thanks in advance,

MS

[vanessafvg]

@MSingh1  there is no way to interact from the front end  of power bi to your sql source, but maybe there is a way you can dyanmically adjust your parameters, is it adhoc dates you putting in or is there a pattern of what you doing?  ie. are you always running data for yesterday?  what is the purpose of what you doing?

[MSingh1]

@vanessafvg Thanks for your reply.

The data in Database  is refreshed everyday as new data arrives.

But I need to pass adhoc dates to analyse the past as well current data. The Timeperiod I pass can be a week or a month or any specific dates. Since multiple people will be using this report, I want to avoid back end interaction (either desktop or BI service whichever is feasible).

Thanks Again,

MS

[vanessafvg]

@MSingh1 cant the users just manage their own time periods via the slicers, the key for you is to bring all the data in that they might use.  The only other way to only bring the data in exactly as the user requests it would be to use direct query mode, but you saying you dont want back end interaction, i am assuming by that you mean from power query to the database?

[MSingh1]

@vanessafvg Users are currently managing report as per their own required time period. so a particular user goes to edit queries and invoke the query and pass say 1-Jun till 14-Jun. Then a permissions message pops up. Once accepted, data is refreshed. And then The user explores the data using date slicers on the report. to select data for a different time period, he repeats the same process.

I was hoping to find a feature so that user can invoke query from the report itself (something similar to slicers). My end objective is to host the report to BI service and allow multiple users to pass random time ranges and fetch the data.

[vanessafvg]

@MSingh1 well that feature is essentially a live connection do the database as opposed to importing the data into the model.

is there too much data to import all the data in or can you not change over to a live connection?