How to pass filters in Report Builder before retrieving data from database

[Anonymous]

Hi!

I am trying to create a report in Power BI Report Builder. The query that I have put in the Dataset Properties is supposed to call a few columns from the database but holds no filter (where clause) there. I have added parameters that act as filters for the dataset but the reduce the amount of data only after fetching from the database. But since the data being fetched is huge (90 million records), PBI gives my "Out of Memory" error and is unable to present me the report.

Is there any way for me to pass the filter values with the query that I pass and bring only that much data as I require at the time? Instead of PBI trying to bring data from DB and then applying filter values?

[v-janeyg-msft]

Hi, @Anonymous 

Have you tried using top sql statement here?

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi! Thank you for replying. I tried that approach, however, I am getting the following error:

This comes after I have put the parameter value so, I know that PBI is accepting the call to parameter. It's just not able to hit the database with it I suppose.

[Anonymous]

In case you need more info, I am using Apache Hive as my database (connecting through ODBC connection). I think '@' works with SQL Server or Oracle database but not with Hive.

Do you know what would work with Hive?

[bradsy]

You can add a query parameter via the where clause. Take a look at section 3 of this article. https://docs.microsoft.com/en-us/sql/reporting-services/tutorial-add-a-parameter-to-your-report-repo...