topic Re: Add parameter in query designer (query from Power BI Performance analyser) in Report Server

Add parameter in query designer (query from Power BI Performance analyser)

loicr — Thu, 02 Dec 2021 21:11:23 GMT

Hi guys,

I am trying to add a parameter in my report and add the parameter in the query designer module.

The query was generated within Power BI via Performance analyser.

I saw different posts on this issue but no clear solution.

First I have tried to create a parameter in the report (in my dataset properties) and add it with @ in my query such as

// DAX Query DEFINE VAR __DS0FilterTable = TREATAS({@Test}, 'FACT - Conflicts'[Conflict category])

However, I have an error message that the parameter test was not declared.

So I declared it again through "Query Parameters" module within the query designer (same name and another name).

This time I can execute the query but it returns no row with a default value "Group" (which should not be the case - if I had put TREAT({"Group"}, it returns some rows).

I tried to see what were the values in the Parameters properties window and I can see this.

Which I don't understand because "Test" is not the name of my dataset..

Any idea to resolve this?

Thanks a lot in advance!

Re: Add parameter in query designer (query from Power BI Performance analyser)

Icey — Mon, 06 Dec 2021 06:13:40 GMT

Hi loicr ,

Please try to add paramters like so:

Reference:

Tutorial: Add a Parameter to Your Report (Report Builder) - SQL Server Reporting Services (SSRS) | Microsoft Docs

Best Regards,

Icey

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

Re: Add parameter in query designer (query from Power BI Performance analyser)

loicr — Mon, 06 Dec 2021 20:02:19 GMT

Hi Icey ,

Thanks for your input, however, this does not solve my issue.

As explained, I know how to add a parameter and did it several times.

The example you displayed relates to SQL where I want to add my parameter in a DAX query (copied from Power BI).

Best,

Re: Add parameter in query designer (query from Power BI Performance analyser)

loicr — Wed, 08 Dec 2021 20:01:14 GMT

Hi all,

I was able to find a solution to my problem (that raised other problems which I solved as well).

What I was trying to do:

- Use a DAX query (created by Power Bi Desktop) into Power BI Report Builder but modify it to insert some parameters that I could use in my report in Power BI Report Builder.

- The is the different steps I have done (for a multi-value parameter):

Add the query in Query Designer. My query from Power BI Desktop looked like this (ensure to use a slicer with at least one value unchecked. If only one item is selected, this is what you DAX query should start with. And this is what we are going to modify.
// DAX Query DEFINE VAR __DS0FilterTable = TREATAS({"Entity"}, 'DIM_Legal_Entities'[Legal_Entity])
Add the parameter in the query designer by clicking on "Query Parameters";
Integrate the parameter in the DAX query like this (in my case my parameter is called "Legal_Entities")
VAR __DS0FilterTable = CALCULATETABLE('FACT_Conflicts',PATHCONTAINS(@Legal_Entities,DIM_Legal_Entities[Legal_Entity]))
Click on okay - don't try to execute the query; even if you select Default values in Query parameters, the query won't return any row (I'm not sure why at the moment)
The parameter should be created automatically
Right-Click on the dataset (on which you execute the query) then Dataset properties / Parameters
Set the following expression for the parameter; then click on okay: =join(Parameters!Legal_Entities.Value,"|")
Now, we need to change the available values and default values - for some reasons as well, declaring them in Query parameters does not work as expected (or maybe I do something wrong).
Right-Click on your parameter (Parameter properties) in the parameters section of the main left menu:
Click on available values and change the different fields (in my case, I'm using a different value that the one on which I execute the query - the query is executed on a FACT dataset; where the parameter is linked to a DIM dataset)
These are the articles that helped me to understand:
- To understand why the query does not return anything in Query designed: Detect missing parameters in DAX queries - SQLBI
- To understand how multi-value parameters can work: Adding multiple parameters to a DAX query as a filter in Power BI Report Builder | by Janani Govindasamy | BI3 Technologies | Medium
To understand how to change the default values: https://www.mssqltips.com/sqlservertip/3506/set-select-all-as-default-for-multivalue-report-parameters-in-sql-server-reporting-services/
Hope it will help someone!
Best,

Re: Add parameter in query designer (query from Power BI Performance analyser)

W_O_L_F — Tue, 28 Jun 2022 14:24:04 GMT

Great post thanks! Just wanted to comment on param if connecting to Oracle it is :Param

Re: Add parameter in query designer (query from Power BI Performance analyser)

Janaki_Sankaran — Fri, 29 Mar 2024 07:45:30 GMT

Hi loicr ,
I need your help in this similar use case which I'm currently working on. I'm not clear on how to make the parameters work. Should I need to add Report Parameters first and then create a Dataset where in the Query Designer do I need to add the query parameters separately? If so, how should I associate the Report and Query parameters? Please assist here.