Paginated Reports and Power BI Datasets.
I have been exploring creating paginated reports with Power BI Datasets and the below is a summary of my findings, with links to tutorials for each step. There are some excellent free tutorials on how to design and build your reports which I can thoroughly recommend, I’ve pasted the links to these at the end of the blog post.
1. Create a Power BI Report and publish to a Premium Capacity Workspace. Points to note:
a. The relationships in the Power BI model do not follow through to the Paginated report, i.e., a data model is not created within Report Builder.
b. The data source in Paginated Reports can hold various sources of data from different datasets, although this is not usually required.
c. You cannot create measures in Report Builder or clean and model your data. Do this in Power BI before you start to use Report builder.
2. You need to use a query to turn the Power BI data set (in Paginated reports this is called a data source) into a paginated report dataset, you can do that in a few ways:
a. Create a table in Power BI Desktop and copy the DAX query generated using Performance Analyser. The DAX code is simpler if you exclude totals from your table. Ensure you have a measure in your table to avoid CrossJoins if you are pulling in fields from different tables.
b. DAX Studio – Connect to your model either using External Tools or direct from DAX Studio. Either use Query Builder or write the code in to create your query and then copy that query and follow the same process. Example DAX code below provided by Julia Baunemann. NB: Julia has used a filter table as a variable to filter out blank values and data she doesn’t want.
c. Use the Query Builder menu inside Paginated Reports to generate the DAX for you. There is a link to this tutorial at the end of the blog to give an insight into how to do this. Query Builder works well when connecting to Sql Server databases.
3. Ensure that you have a measure in your Query otherwise you can get data which does not tie together.
4. If you need to create a relationship between the datasets you can enable a relationship between the different datasets. You can use a look up formulae.
5. If you want to use two dates to select between you need to use Query Builder within Report Builder because replacing the hardcoded filters with @ parameters only works for single select parameters. Multi value parameters in Report Builder pass arrays and DAX does not have an array type. This is an excellent link to how to use parameters in DAX Studio.
Key take outs:
relationships from power bi dataset don’t follow the dataset into report builder, need to use DAX to create the relationships and specify the filters.
Dataset in Power BI means Datasource in Report Builder
Query in Power BI maps to Dataset in Report Builder
Any data cleansing must take place in power bi before publishing. Report builder works best with clean simple data.
Workflow from Power BI to Report Builder using DAX Studio
Create Data Source from Power BI dataset. Right click on Data Sources and choose dataset from Power BI service.
Create dataset in Report Builder by right clicking on Data Source:
Click on Query Designer:
Then define Measures and Filters and then summarise and evaluate the required columns with the filters and measures applied. DAX Studio is my choice for writing code. Example code below:
DEFINE
MEASURE Document[Total words] =
SUM ( Document[Words] ) //define at least one measure that calculates an aggregate
VAR __loggedout =
FILTER (
KEEPFILTERS ( VALUES ( ‘Session'[Logout] ) ),
ISBLANK ( ‘Session'[Logout] )
) // define any necessary filters. in this example: only use records where the user is still logged in
EVALUATE
SUMMARIZECOLUMNS (
ROLLUPADDISSUBTOTAL (
ROLLUPGROUP (
‘Session'[Login],
‘User'[origin] // list all table columns that you want to include
),
“IsGrandTotalRowTotal“ // required if you want to have a total of all records
),
__loggedout, //any filters defined above
“Total words”, [Total words], //measure defined above (can be several)
)
ORDER BY
‘Session'[Date] // columns to which it should be ordered by
No need to define any parameters in the DAX here. The parameters are done in the next step as separate datasets within Report Builder.
Parameters
For me the easiest way to create parameters was to create a separate dataset for the values to be filtered on using any of the options mentioned in the first page. My choice is DAX Studio and Query Builder. For example, if I want to have a parameter to select “Continent”, I create a dataset that only includes that column and create the parameter with that field.
Right click on the parameter folder and click on Add Parameter. You will then have a parameter which Report Builder knows is a parameter with the @sign in front
You can now set up the parameters available values to pull from the Dataset that you have created.
- Click on the ‘Filters’ field and add. In the ‘Expression’ field, choose from the dropdown which field you want to filter on.
- Choose how you want to filter and in ‘Value’ click on ‘FX’.
- Then click on ‘Parameters’ then double click on whichever parameter you want to use.
If I want to select multiple variables, I select this:
Important: If all values should be selected by default in the end report, it cannot have any blanks in the dataset. Those would need to be filtered out in the query of that specific dataset already, otherwise the report will not show anything by default.
Filter
Once the parameter has been set up, you need to tell the report to use it to filter.
If I want to create a parameter where the user can search for text strings (no available or default values), for example to search part of a name of a document, you create a new dataset again with just the field you want to search on and create a parameter with that.
If the user input is not mandatory, you need to do the following settings:
- Allow blank value (“”)
- Specify default value with the expression: =””
Then add it in the main dataset as a filter with the operator LIKE and the following expression: =”*” + Parameters!ParameterName.Value + “*”
For a date picker, the best explanation can be found here.
Showing selected values in report header
- If you want to show the selected values from the parameters in the report header, add a textbox with the following expression, for example:
=iif(Parameters!Country.Count = 5, “All countries selected”, Join(Parameters!Country.Value, “,”))
In case all 5 countries of the dataset are selected, it shows a text for that selection, otherwise it lists all selected values separated by a comma.
- To show the date values from the data picker in the Report you can refer to the Parameter name in the report:
= Parameters!FromDate.Value and =Parameters!ToDate.Value
Any questions, happy to help,
Sue