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!
Set Up
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.
=FormatDateTime(Today(),DateFormat.ShortDate)
-
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.
Formatting
-
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.
Nice work!