Paginated Reports 101 – Building your first Paginated Report off of a Power BI dataset

 

Note: this blog post is part of a short series covering how to convert a standard Power BI report (a .pbix file) into a Power BI paginated report (an .rdl file) using the same underlying Power BI dataset. A companion video is available at the bottom of this post —and supporting files are available here on GitHub for those who would like to follow along.

Woof, try saying that three times fast.

Our last blog post went through the similarly-named data elements in Power BI Paginated Reports, Power BI Analytical Reports, and the Power BI Service. These can be very confusing, even if you’ve carefully color-coded the different types as I have in this blog series. If you’re baffled just reading the title of this post, I recommend you go one post back before reading this one.

If not, let’s jump into creating a Paginated Report Dataset!

What is a Paginated Report Dataset: A paginated report dataset pulls data from the Paginated Report’s Data Source to generate a table based on the fields, filters, and parameters selected. While you may associate the word dataset with Power BI Datasets in the Power BI service, paginated report datasets are simpler creatures.

When you pop the hood and take a good look at what makes Power BI Datasets work, you realize they contain multitudes. Power BI Datasets are in-memory columnar databases that support Power BI’s data modeling and storage capabilities. In addition, Power BI Datasets rely on Power Query’s impressive range of connectors and transformative capacity working to give you the precise table structures you need.

Paginated report datasets are much simpler creatures; they’re a query that makes a table from a single data source. And while you’d think that setting up a paginated report dataset would therefore be easier for a veteran Power BI user, you’ll soon see that the logic applied can be very different.

 

 

Creating your first paginated report dataset

1. Start by right-clicking on “Datasets” in the report data pane, and selecting “Add Dataset”

2. Select the Data Source you set up as part of the last blog post,

3. then click on Query Designer

 

 

From there, you can use the Query Designer to define what fields and columns will be pulled from the Power BI dataset to create a big flat table. This will feel very similar to choosing the fields and measures that you want in a table visualization in a standard Power BI report.

After you click “OK”, the Query Designer will generate a query on your behalf like the one below:

3 best practices when creating the main portion of your paginated report dataset:

1. Minimize the number of fields you include: If you’ve been using Power BI for a while, you already know that you want to minimize the number of columns to just what you need when you make a table. This is doubly true for your paginated report’s dataset since you’re basically building a wide flat table to feed your report. Parameters and filters will also help minimize the number of rows in your dataset—something we’ll cover in the next post.

2. Use Measures, NOT fields from your fact tables: When you select a field, every unique instance of that field will populate in the query—if the source table has 3601 distinct values, you’ll get 3601 rows.

You can test this by selecting a single field in a fact table, like the one on the left for Quantity.

Note the long scroll bar. The measure corresponds to the following query:

EVALUATE SUMMARIZECOLUMNS(‘Smore Sales Table'[Quantity])

If I plug this query into DAX studio, I get 3601 rows, exactly the number of unique values in my PBIX File.

In comparison, if I use the measure [Total Quantity] (which is just SUM([Quantity]), I’ll just get a single value. This single value is the total quantity in the dataset before any dimensional details have been added like customer, region, or date. Where using the field will work in some cases, using the measure will create an efficient dataset with the minimum number of rows necessary to create the dataset query based on the field you selected.

 

 

 

Wait, why doesn’t the Paginated Report’s Query Editor know to aggregate the data as it does in Power BI Desktop?

Power BI desktop generates implicit measures as part of the DAX Query that supports each visual. By default, Power BI will sum the values of any field that can be aggregated. Unless you change this to “do not summarize”, the DAX query that supports a Power BI visual will generate something like this:

EVALUATE
              ROW(
                  “SumQuantity”, CALCULATE(SUM(‘Smore Sales Table'[Quantity]))
)

As the report writer, you don’t need to do anything to add that SUM() to the DAX query. Power BI Desktop just did that for you, generating the single total value that you probably expected.

Paginated reports’ Query Editor does not create these kinds of implicit measures. This can be awfully confusing when you’re used to Power BI Desktop anticipating your needs with implicit measures and similar logic on the back end. When you add the ‘Smore Sales Table'[Quantity] value to the Paginated Report Query Editor, it simply generates:

EVALUATE
SUMMARIZECOLUMNS ( ‘Smore Sales Table'[Quantity] )

(note the lack of a SUM() around the [Quantity] field. SUMMARIZECOLUMNS <> SUM() )

The above logic will create that long list of values you can see in the screenshot above from the paginated report builder.

Explicit measures:

Using a measure like [Total Quantity] in Power BI Desktop, you get an identical reporting result and a very similar DAX Measure on the back end of the visual:

EVALUATE
ROW(
“Total_Quantity”, ‘Smore Sales Table'[Total Quantity] )

This works because the measure Total Quantity = SUM(‘Smore Sales Table'[Quantity]). Power BI Desktop generates a single value as expected.

If we use that same explicit measure, [Total Quantity], in Paginated Reports’ Query Editor, the Query that is generated is nearly the same:

EVALUATE
SUMMARIZECOLUMNS ( “Total Quantity”, [Total Quantity] )

Using an explicit measure will give you the same result in both Power BI Desktop and the Report Builder Query Designer:

 

3. When you use a Power BI dataset as your paginated report’s data source, it’s a little like choosing the fields from the Power BI Data Pane and adding them to a table’s list of fields. You can drag and drop from the fields listed in your model (keeping in mind the two points above). And just like with a Power BI visual, you can always add additional fields if you find you need them later. 🙂