Parameters in Direct Query on SQL Server

[NeerajR]

I have the following situation:

1) Data stored in SQL Server 2008 R2.

2) Power BI module connected to that database through Direct Query mode.

3) We have a SQL query that is supposed to return a result in Power BI report page. The SQL query has parameters that depend on user input.

Now how do I display the input text/value parameters on the report page of Power BI ?

And the query result has to be made dependent on the input parameter. How do I achieve this in Power BI ?

Below is a sample of what I intend to do. Any assistance would be valuable.

The result has to be made dependent on the input parameter. How do I achieve this in Power BI ?

[v-chuncz-msft]

@NeerajR,

You may take a look at the post below.

https://community.powerbi.com/t5/Desktop/parameterize-connection/m-p/205900#M90712

[NeerajR]

Thanks @v-chuncz-msft for your reply.

Appreciate your assistance.

However I need something where the end user would be able to toggle input parameters on the report page itself and get the result from the parametric SQL query for the input parameters selected.

And I have a direct query connection to a SQL server. The idea is to not load any data tables into Power BI, but just run the parametric SQL query to return and display the final result on Power BI report page.

All the materials on using parameters in Power BI say that one needs to navigate to Edit Parameters under Edit Query to toggle/change the value of input parameters. And the end user is restricted from editing the input parameters.

Please correct me if I am wrong or if I am missing something.

Thanks and Regards,

NeerajR

[NeerajR]

Hello everyone, here are some more details of the things that I have tried so far.

I have a database containing 5 tables, each having about 4 lakh rows and around 30 columns.
The SQL query that I am trying to execute has about 25 input parameters and performs some complex calculations using all the tables in the database.

This time I invoked a SQL Stored Procedure to run a parametric query from the Query Editor module of Power BI.
The query gets executed and shows the results in Power BI Query Editor. But the same results do not get applied on the report page.
It either shows error in applying or it displays execution time out.

And all this actually works for smaller data. But I am having problems to get the same thing running for about 4 lakh rows of data.

Is Power BI a suitable chioce for working on such amounts of data?
Or is it that I need to clean and shorten my SQL code for it to be more efficient on the server?

Any assistance would be helpfull.

Thanks and regards,

NeerajR