Paginated Report Tutorial: Chart with Excel-Style Data Table

This tutorial will teach you to build a chart with an accompanying Excel-style data table in a Paginated Reports using Power BI Report Builder. This format is great when you have categorical data that you want to display graphically but also provide detailed values that can be easily referenced. For this tutorial, I use sales data split up by product category and display this for each month of a given year. A big thanks to Jason Thomas who first demonstrated how to build this in SSRS.

To get started, all you will need is Power BI Report Builder installed on your machine. If you don’t already have this installed, you can find the free download for Power BI Report Builder here.

Connect to Data

Before you get started building your chart and data table, you need to connect to a data source. For this tutorial, you can use any one of the AdventureWorks sample databases. These databases are freely available for download and are great for educational purposes. I am specifically using the AdventureWorks database from the Power BI Paginated Reports in a Day course. You can follow this tutorial with any version of the AdventureWorks databases. Depending on which version you have your field names may vary slightly from what is shown in this tutorial (i.e. EnglishProductCategoryName vs. CategoryName).

With your own AventureWorks database set up, you can connect to it as a data source in Power BI Report Builder.

1. In Power BI Report Builder, right click on Data Sources in the Report Data pane and select Add Data Source.
2. In Select connection type pick Microsoft Azure SQL Database or Microsoft SQL Server depending on where you have you AdventureWorks database.
3. Click Build and enter your credentials, saving your password.
4. Click OK in Connection Properties and then after testing your connection and then OK in Data Source Properties.

Create a Dataset

1. Right click on Datasets in the Report Data pane to add a dataset. From the data source dropdown, select the data source you just connected to.

   

2. If you are using the AdventureWorks data source from the Paginated Reports in a Day course, you can simply enter the following query into the Query field and click OK.

   SELECT
     FactInternetSales.ProductKey
     ,FactInternetSales.OrderDateKey
     ,FactInternetSales.SalesAmount
     ,DimDate.DateKey
     ,DimDate.EnglishMonthName
     ,DimDate.MonthNumberOfYear
     ,DimDate.CalendarYear
     ,DimDate.FullDateAlternateKey
     ,DimProduct.EnglishProductName
     ,DimProductSubcategory.ProductSubcategoryKey
     ,DimProductSubcategory.EnglishProductSubcategoryName
     ,DimProductCategory.ProductCategoryKey
     ,DimProductCategory.EnglishProductCategoryName
   FROM
     DimProductSubcategory
     INNER JOIN DimProductCategory
       ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey
     INNER JOIN DimProduct
       ON DimProductSubcategory.ProductSubcategoryKey = DimProduct.ProductSubcategoryKey
     INNER JOIN FactInternetSales
       ON DimProduct.ProductKey = FactInternetSales.ProductKey
     INNER JOIN DimDate
       ON FactInternetSales.OrderDateKey = DimDate.DateKey
   WHERE
     DimDate.CalendarYear = 2019
   GROUP BY
     FactInternetSales.ProductKey
     ,FactInternetSales.OrderDateKey
     ,FactInternetSales.SalesAmount
     ,DimDate.DateKey
     ,DimDate.EnglishMonthName
     ,DimDate.MonthNumberOfYear
     ,DimDate.CalendarYear
     ,DimDate.FullDateAlternateKey
     ,DimProduct.EnglishProductName
     ,DimProductSubcategory.ProductSubcategoryKey
     ,DimProductSubcategory.EnglishProductSubcategoryName
     ,DimProductCategory.ProductCategoryKey
     ,DimProductCategory.EnglishProductCategoryName

3. If using a different AdventureWorks data source, select Query Designer.
4. From the database tables, you will need to select sales amount, sales dates including both month number and month name, and product category names.

   

5. In the Applied filters section of the Query Designer, add a filter for a specific calendar year. This tutorial uses 2019.

   

Start Building in Power BI Report Builder

Now that you have connected to a data source and built your query, it’s time to start building the chart and data table.

1. Drag and drop an empty matrix onto your canvas.

   

2. Add EnglishProductCategoryName as the row field, EnglishMonthName to the column field, and SalesAmount to the data field.

   

3. Running the report right now, columns are sorted by months alphabetically. Obviously, this isn’t what we want. To sort from January through to December, select your data field and then open Group Properties. Withing the Sorting tab change to sort by MonthNumberOfYear.

   

4. Add two rows above the bottom row with Outside Group – Above.

   

5. Now you can delete the top row (but not the associated group) as shown.

   

6. Now we want to add the month name above the data value. Here, I just want to display the first three letters of the month. Select the cell above [Sum(SalesAmount)] and enter the following expression:

   =Left(Fields!EnglishMonthName.Value,3)

   

7. You can also do some quick formatting, hiding the textbox for the three textboxes as shown below as well as setting the data type for [Sum(SalesAmount)] to Currency.

   

8. Time to start working on the chart. Insert a stacked column chart onto the canvas outside of your matrix.

   

9. With the chart selected, drag SalesAmount to Values, MonthNumberOfYear to Category Groups, and EnglishProductName to Series Groups.

   

10. Delete the chart title, legend, and horizontal axis from the chart.
11. Next, to dynamically set the maximum value of the vertical axis, right click the top left textbox of the matrix and select Expression. Enter the following expression (where “EnglishMonthName” is the name of the Column Group in the matrix) and make note of the name of this textbox (mine is named Textbox7 as seen in the properties pane):

    =Max(Sum(Fields!SalesAmount.Value, "EnglishMonthName"))

    

12. Set the font colour to white to hide this expression.

    

13. Select Vertical Axis Properties and set the maximum value equal to the following expression, replacing Textbox7 with the name of your respective textbox identified in Step 11:

    =1.2*ReportItems!Textbox7.Value

    

14. Select the vertical axis and switch HideLables to True and disable MajorTickMarks.

    

15. Before placing the chart in the matrix, we need to make sure there are no whitespaces in the chart area. Select the chart area and change the properties shown below.

    

16. Ensure the BorderStyle property is set to None.
17. Drag and drop the chart into the top right matrix cell and adjust the row height.
18. We need to ensure the vertical axis is only displayed for the first column. Select the vertical axis and set the LineStyle property to the expression below, replacing “dsChartsDataTables” with the name of your dataset.

    =IIf(Fields!MonthNumberOfYear.Value=First(Fields!MonthNumberOfYear.Value, "dsChartsDataTables"),"Solid","None")

    

19. Add a column inside the group to the left of EnglishProductCategory.

    

20. This column will be our colour column. We can use the Custom Code for consistent colours as described here. Then set the BackgroundColor property for the column and Color property for chart series equal to the following expression, where “EnglishProductCategoryName” is your row group name:

    =Code.GetColor(Fields!EnglishProductCategoryName.Value)

    

21. Now you can Run the report and you will see sales data broken up by product category and month in both chart and data table formats. You’re done! Please leave any comments/feedback/questions in the comments.