Forum Discussion

ldwf's avatar
ldwf
Icon for Helper V rankHelper V
3 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 from and to dates for example).  It appears that when the report processes, it returns all the records from the semantic model, and then filters the data based on the parameters entered.  I would instead like to have the parameter values passed to the semantic model so that the data is filtered at that level instead of after the data is returned.  Is this possible? If so, how is this done?  I assume I need to make changes on the semantic model to accept the parameters and filter the SQL in the semantic model, but there are other reports that use the semantic model that do not prompt the user, so I don't know if making changes to the model impacts any dashboard/report that uses it

Need Help
  • Cookistador's avatar
    Cookistador
    3 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

4 Replies

  • Cookistador's avatar
    Cookistador
    Icon for Super User rankSuper User
    3 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

  • ldwf's avatar
    ldwf
    Icon for Helper V rankHelper V
    3 days ago

    Hi.  The semantic model shows the Storage mode being 'Import' mode, not direct query.  So this cannot be done in import mode?  And how simple is it to change to 'direct query'?  And where is the DAX query applied?  Is it applied somewhere on the semantic model or on the Report Builder report?  And there are other PBI dashboards that use the semantic model that are not parameter driven.  Would those need changing?

  • Cookistador's avatar
    Cookistador
    Icon for Super User rankSuper User
    3 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
  • jubinsoni's avatar
    jubinsoni
    Icon for Advocate IV rankAdvocate IV
    2 days ago

    Hi ldwf, to add to the steps above, the key point is where the filter lives. If the date filter is applied in the dataset Filters tab or in a tablix filter, the report still pulls the full result and filters it afterwards. If the filter is part of the DAX query itself, either through the query designer filter pane with the parameter box ticked or through a DEFINE VAR with the @parameter mapped in Dataset Properties, the semantic model engine only returns the matching rows. You do not need to change the semantic model for this, so other reports that use it are not affected. Also note that if the model is in Import mode the data is already in memory, so the benefit is a smaller result set sent to the report, while with DirectQuery the filter can also flow through to the SQL source.

    AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.