This is Part 2 of a tutorial for creating report cards with Power BI Paginated Reports. We hope this tutorial will prove helpful for any teacher or educational administrator that finds themselves laboriously moving data from a spreadsheet to a report card. With Paginated Reports, we have the ability to automate the data populating and report card creation process.
While specifically geared toward educators, this tutorial is also great for anyone looking to gain experience working in Power BI Report Builder!
In Part 1, we connected to data in an Excel spreadsheet that contained student information and grades through Power BI Report Builder. Here in Part 2, we will build a report card template in Power BI Report Builder that will be auto-populated with this data.
By the end of this tutorial, you will have create a Paginated Report with each page containing a report card for a unique student based on data from Excel. From here, you can then easily print or distribute these report cards. If updates to student grades are made in Excel, these will be automatically reflected in your Paginated Report (provided Method 2 was followed in Part 1 of this tutorial). Let’s get started!
All you will need to get started is Power BI Report Builder installed on your machine. If you do not already have this installed, you can find the free download for Power BI Report Builder here.
This tutorial assumes you have already connected to your data in Power BI Report Builder. Please refer to Part 1 if you have not yet completed this step.
Creating Your Report Card Template
- Open your Paginated Report in Power BI Report Builder with connected data from Part 1.
- We will start by adjusting our Report Properties. Access this by right clicking in the grey space of the report and selecting Report Properties. Adjust all the margins to 0.5in. Click OK.
- Since the width of our page is 8.5in and we have reduced our margins to 0.5 on each side, we want to increase the size of our report Body so it fills all of the usable space. Select the Body of the report and increase the Width to 7.5in in the Properties pane.
- Next, add a header to your Paginated Report by again right clicking in the grey space of the report and selecting Add Page Header. With the header selected, adjust the BackgroundColor and Height properties in the Properties pane. See below for the property values.
- Drag the existing Report Title textbox into the header add the name of your educational institution here. Center the text within the textbox and adjust the Top property under Location and the Width property under Size. Choose a desire font and font color.
- Right click in the header to insert another textbox for the subtitle. Enter a subtitle (i.e. Student Report Card) and make any desired adjustments to the formatting.
- With the Page Header selected, confirm the Height is still set to 1.5in.
- We do not need a footer for our report card design. To delete the footer, right click in the page footer and select Remove Page Footer.
- Insert a table by selecting Insert Table from the Insert ribbon.
- Delete the Header row.
- With your remaining row selected, right click and select Insert Row and Insert Group – Below. Repeat until your have approximately 15 rows. You can always add more later. Note: We are adding a row inside the group instead of outside because we will want to group all data together for each student.
- Insert 1 column to the right so you are left with 4 columns.
- Type “Student Name:” in the cell seen below. In the cell to the right, select the blue and white menu button to select your [Student_Name] field from your dataset.
- Select any row and right click. Select Row Group and then Group Properties. Within Group on add your data field [Student_Name]. This will ensure all data added to this table is grouped by student.
- Within the Group Properties window, switch to the Page Breaks tab. Select Between each instance of a group. Click OK. This will ensure each there is a new page created for each student, since we defined [Student_Name] as our row group.
- Below your Student Name cell, add any addition information you would like to appear on each student’s report card (i.e. Grade and Section, Teacher Name). I have entered these fields manually since they will be identical for each student but you can just as easily use a data field here to make this dynamic if you have multiple class sections for example.
Below the cells you just added, add another cell for Date. In the cell to
the right, right click and select Expression. Enter the
following and click OK. This will auto populate this cell
with today’s date.
- Add headings for Subject and Grade as shown below.
- Below the Grade heading, select the blue and white menu box to select the [Art] data field containing the student’s grade for that subject. Repeat this for all subjects in the Grade column. Do not add the [Comment] field yet, we will place separately from the grades.
- To the left of this data column, add a title for each subject.
- Leaving a row gap after the student’s grades, add a heading for the Comment in the same column as the Subject heading. Select this cell and the next cell to the right. Right click and select Merge Cells. Repeat this merge for the two cells below the Comment heading.
- Select the blue and white menu symbol in this merged cell and select your [Comment] data field.
- Now if you we run the report (click Run in Home ribbon) we can see that each student has their own page with the respective grades and comment (select Print Layout). Awesome! At this point, we have completed all the heavy lifting required to make this report card from data in Excel. All that is left is to do some formatting to make the report card look prettier.
- With the entire table selected, change both Left and Right to 0in so the table is position directly below the header.
- Select any cell in the top empty row of your table. Adjust the the row Width and Height (within Size in the Properties pane) to 1.25in and 0.75in respectively. This is to provide consistent spacing from the header.
- Drag to select all cells in your table. Set the VerticalAlign property to Middle.
- For all rows containing some text or data (except for the field contain the [Comment] data), increase their Height property to 0.35in. Do this by selecting any cell in the row and setting its Height property to 0.35in. For the field containing [Comment], set its Height property to 1in.
- Select any cell in the second column. Set the Width of this cell to 3in. Make sure the text is left justified.
- Select any cell in the third column. Set the Width of this cell to 2in. Right justify the data in the metadata section (Student to Date cells) and center data in the Grade section as seen below.
- Select an area outside of the table to adjust the report Body parameters. Set the Width to 7.5in and the Height to 8.5in.
- Select the fourth row of your table and set its Width to 1.25in. Your table should now exactly fill the width of the report body.
- Set the Height property of any cell in the row after your Date row as well as any cell in the row your Comment heading to 0.75in.
- Select all the cells containing the student’s grades. Set their Format property to P1 (P = percentage, 1 = 1 decimal value).
- Optionally change the font and font size of all cells. To do so, drag to select all cells containing text or data and set their font style to Segoe UI and their font size to 12 in the Home ribbon.
- Drag to select the entire table. Set the BorderStyle property to None.
- Drag to select the metadata cells in the top section of the report card. Within the Border Style property, set the Bottom border to Solid. Set BorderWidth to 2pt and BorderColor to LightSalmon.
- Select the cells containing the Subject and Grade column headings. Set the BackgroundColor property to LightSalmon.
- Do the same for the merged Comment heading cell.
- Drag to select all cells contain the subject names and the grades. Set BorderStyle to Solid, BorderColor to LightSalmon and leave BorderWidth at 1pt.
- Apply these same properties to the cell containing [Comment].
- Finally, if you have a school logo you wish to include, you can add this selecting Image from the Insert ribbon and importing an image from your local file system. Place your logo in page header.
- You’re done! Now click Run to see a report card generated for each student with their respective grades. Select Print Layout to see each student’s report card as a separate page. From here you can print your report cards, export them as PDFs or publish this Paginated Report to the Power BI service (powerbi.com) if you have a Premium capacity or Premium Per User license.
Great job on making it through this tutorial! If you followed along with this tutorial as well as Part 1, you have successfully created a paginated report from data in Excel. If you followed Method 2 in Part 1 and created a Power BI dataset from your Excel data, anytime you update your data in Excel, your paginated report will automatically reflect these updates.
If you have any comments or questions related to this tutorial, please leave them below and I will do my best to answer them.