Power BI Paginated Reports: Filters
Adding a parameter to a report in a Power BI Paginated Report was more difficult that it initially looked. This was how I did it.
Basic filter
The report is going to output one table, from one SQL query. That SQL query will have a variable it in, named for the parameter.
SELECT *
FROM timesheet
WHERE projectId = @projectId
Here, we are going to specify a parameter value of @customerId elsewhere, and the final query for the data table is going to include that in its filters.
Let us call this dataset Timesheet.
Making parameter
What we want to make is a dropdown box for users to click on and choose the value of the parameter to be put on. Naturally, we would want the selection to come from the data table itself.
However, when we create parameters, they cannot be based on the data table to be outputted. This would create a circulate dependency. A new query must be created to fetch the values for the parameter dropdown field. Ideally, we already have a table we can derive from; otherwise, use a DISTINCT collection from the main data table.
So, to serve the parameter, we create a new dataset, Project.
SELECT
projectId
, projectNo
, projectName
FROM project
In order to have a working dropdown, we need to set the Available Values. The type is to "Get value from a query". The parameter we make needs three things:
- Dataset
- Value field
- Label field
The dataset in this case needs to be Project. The value field is the value that will be inserted into the main data table query. We can see that the placeholder variable that we specify in the main query is @projectId, so the value field needs to be projectId. The label field is the values that will actually be shown on the dropdown box. This needs to be human readable. In this case, we can use projectName.
Cascading parameters
What if we need filters to cascade?
In this case, the query that the parameter reads from needs to have its own WHERE clause.
SELECT
projectId
, projectNo
, projectName
FROM project
WHERE customerId = @customerId
The main query will also need to be updated to include the "upstream" parameter.
SELECT *
FROM timesheet
WHERE projectId = @projectId
AND customerrId = @customerId