Forum Discussion

ldwf's avatar
ldwf
Icon for Helper V rankHelper V
16 days ago
Solved

Report Builder - Pass parameter values to semantic model

I have a Report Builder/Paginated Report that connects to a published semantic model which points to a SQL Server database.  The report prompts the user to enter in various parameter values (like fro...
Need Help
  • Cookistador's avatar
    Cookistador
    16 days ago

    I think I did not understand your need
    Can you try the following steps (even in import mode, it should work)

    1. Right click on the dataset for which you want to add some filters and select Dataset Propreties
      You should have something like:
      EVALUATE
      SUMMARIZECOLUMNS(ColumnName...)

      And replace it by the following code snippet
      DEFINE
      VAR param1 = @ParamName (Name of the first parameter you want to test)

      EVALUATE
      FILTER( TableName, TableName['ColumnToFilter'] = param1)

    2. Link your parameter to the query, right click on your dataset --> Dataset propreties -->Parameters

      and here pass the name of your parameter

    3. Run your query, normally it will ask to pass paramter BEFORE to generate the report
Cookistador's avatar
Cookistador
Icon for Super User rankSuper User
16 days ago

Hello ldwf​ 

If you are using Import mode, it is not possible
With the import mode, the data are loaded into the semantic model during the refresh process  and your report queries are executed against the data already stored in the model.
As a result, report parameter cannot be pushed back to the source database

If you are using direct query, it should be possible
In that case, you need to pass your report parameters directly into the DAX query rather than applying them as report or dataset filters. To do this, you must modify the report dataset and reference the parameters inside the DAX query itself.

For example, instead of retrieving all data and filtering it afterward, the query should look something like:

DEFINE
VAR pStartDate= DATEVALUE(@FromDate)
VAR pEndDate= DATEVALUE(@ToDate)
VAR pCountry = @Country

EVALUATE
FILTER(
Table,
Table[Date] >= pStartDate &&
Table[Date] <= pEndDate
&&  Table[Country] = pCountry 
)

If you are using import mode, -a way to achieve that, would be to replace your dataset by a direct connection to the sql Server and directly pass parameter to your query (or use stored proc)

Do not hesistate to answer if you need more help to investigate on this issue