Report Card with Paginated Reports (Part 1): Connecting to Excel Data

During this pandemic, teachers have had one of the toughest jobs. From having to adjust to virtual classes, to developing distance learning plans, to ensuring their classrooms adhere to all protocols, they have had a lot on their plates. This tutorial aims to make at one piece of their job a lot easier by using Power BI Paginated Reports to simplify and speed up the report card making process. While specifically targeted to teachers, this tutorial should be helpful for anyone looking to make a paginated report from an Excel workbook.

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. This tutorial is split into two parts: Part 1) connect to Excel data in Power BI Report Builder and Part 2) design a report card in Power BI Report Builder. Let’s get started with Part 1!

Excel Data

Excel spreadsheets remain one of the most popular tools for any sort of data entry. Among teachers, spreadsheets are often used for keeping track of students’ grades which ultimately end up on some sort of report card. Depending on what system is in place, this can be a tedious and manual process.

For this tutorial, you can use your own Excel spreadsheet or download my example spreadsheet here.

This spreadsheet contains randomly generated names and grades for a variety of subjects.

Connect to your Excel Data in Power BI Report Builder

To get started, you will need 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.

There are two methods of bringing data from Excel into a paginated report: 1) Enter data and 2) Create and connect to a Power BI Dataset. Either option works and we will walk through both here.

Method 1: Enter Data

Entering data directly into your paginated report is the easier but less elegant of the two methods. You need to keep in mind that any time your make adjustments to your Excel spreadsheet, you will have to re-enter your data into your paginated report.

Open Power BI Report Builder and right click on Datasets in the Report Data pane to add a dataset.
Within Dataset Properties select New to add a data source. Within Data Source Properties select Enter Data form the dropdown and click OK.
Select Query Designer and create as many columns as you have in your Excel data.
Copy your data from Excel and paste it here. Ensure you enter your column headers in the header row, not the first row of data.
Change each column containing numerical grades to type Float. Click OK once you are done.
You should now see your dataset in the Report Data pane.

Method 2: Create & Connect to a Power BI Dataset

The second option for connect to your data is to create a Power BI Dataset. While this will take a few more steps to set up, it can allow for you paginated report to be automatically updated every time a change is made to your Excel spreadsheet. A lot easier than having to re-copy your data into Power BI Report Builder every time you make a change!

You will need to have an account in the Power BI service (app.powerbi.com) since that is where we will be publishing our dataset. Any license will work for this tutorial, including a Free license. If you do not already have a Power BI account, you can sign up for one for free here.

You will also need to have Power BI Desktop downloaded for this method. If you do not already have this installed, you can find the free download for Power BI Desktop here.

Make sure your Excel document is saved within a folder in your OneDrive.
Open your Excel document. Click on File and then select the Info tab. Click on Copy path.
Open up for text editor of choice (I’m using trusty old Notepad) and paste the path. Delete ?web=1 from the end of the path and then re-copy the result.
Open up Power BI Desktop (free download here).
Click on the dropdown just below Get Data and select Web from the list. Do not pick Excel as tempting as it may be.
Enter the URL you copied in your text editor and click OK.
In the Navigator window, select the spreadsheet with our data and click Load. Here I am only concerned with Term 2 grades from my spreadsheet.
Now that we have our data loaded into Power BI Desktop, we want to publish this data up to the Power BI service (the cloud). To do this, click Publish on the home ribbon and then select the workspace where you want to publish this dataset. Any workspace including My workspace will work.
Switch back over to Power BI Report Builder. Right click on Data sources in the Report Data pane and select Add Power BI Dataset Connection.
Select the Workspace and then Power BI Dataset you just created and the click Select.
Despite its name, the Power BI Dataset we just connected to will be used as our Data Source, not our Dataset, in Power BI Report Builder. We will create a Dataset from this Data Source by right clicking on Datasets in the Report Data pane.
In the Dataset Properties window, select your newly added Power BI Dataset from the Data source dropdown. You can name this dataset anything you like then click Query Designer.
Drag in each column from the Model pane into your query and then click OK. Click OK again in the Dataset Properties window to create your dataset.
You should now have your dataset in your Report Data pane (or two datasets if you followed both the Entered Data method and this Power BI Dataset method).