Published using Google Docs
Advanced_Excel_Reports (2)
Updated automatically every 5 minutes

End of Year Reports

You may want to run reports showing statistics for the end of the year for the different categories of your collection. Go to Reports=>Library Reports=>Collection Statistics Historical

Choose the dates of the school year. Organize by Dewey and do an output to Excel. Open the report in Excel and delete any call number types as needed.

This to this=>:

Highlight all of the information and click on Insert=Chart Choose the Pie Chart

Enlarge the image so you know what it looks like:


 000 - 099

 100 - 199

 200 - 299

 300 - 399

 400 - 499

 500 - 599

 600 - 699

 700 - 799

 800 - 899

 900 - 999










Now, you need to add labels.

Title: Click on the Circulations and change to Total Circulations 2013-2014

Data Labels: To add in the total counts, go to Layout=>Data Labels=>Best Fit

Then, go to More Data Label Options and add percent:

Here is your final chart to add to your Annual Report!:

Patron Report

Go to Reports=>Patron Reports=>Patron Statistics Historical.  Setup the report as follows:

Go to Back Office=>Job Manager to see the results:

Copy or type into Excel and make a chart! Remember to only highlight Patron types and total circulations.

Title: Change to Total Patron Circulations 2013-2014

Data Labels: To add in the total counts, go to Layout=>Data Labels=>Best Fit

Then, go to More Data Label Options and add percent

Finished product:

Historical Patron Data

To examine patron circulation statistics for a particular timeframe, go to Reports=>Library Reports->Extract Copy transaction data.

Choose a Date Range:

Make certain to include associated Patron Data.

You will be saving the report as a XML file.  Please open Excel and choose option 2 to open as a Read-Only worksheet document.  Make certain to delete any fields you don’t want to display.  I recommend these ones:

Step 2: Highlight all items to put in the Pivot Table. You can right-click and Hide columns you do not want to view. Please rename any columns.  I would delete the Transaction part of the Column titles.

Step 3: Go to Insert=>PivotTable

The example below just includes Title, Call Number and Circulations.

Step 4: Drag them into the PivotTable List Like so:

I have included the patron type and the total counts of circulations.

You can sort the total counts of circulations for the different types of patron types or count them together.

Here are results by sorting by Patron Type.  Please note that blank would be in-library circulations:

In the next example, I enter the Publication Year (PubYear).  I click on the Circulation default and click on Value Setting to change the numeric calculation:

Now I can find the average publication date of the books students, teachers, and in-library use stats.

Other reports examples…

Checkout of specific Dewey range.  I entered both the Call Number and the Title in Rows so I could see what titles in the 500s checked out:

You could also look up the checkout dates (Group by year and month to see which ones are the most popular.  Setup on next screen.  

Chart for students:


In-Library Use

Pivot Charts

Click on PivotTable Tools=>Pivot Chart to choose the type of graph you would like to display. I choose a column cha

See the sample:

You can also filter the results to just focus on a particular Dewey range of the 500s (510s, Math or 530s, Chemistry)

Customized Reports in Report Builder

You may want to run reports that contain information not included in one of the canned reports. For example, if you wanted to breakdown current statistics (only what is checked out. You cannot run past statistics with these reports) by grade, homeroom and/or gender.

Go to Reports=>Report Builder=>New Report.  Choose Patron—Current Checkouts Report

Choose the information to include.


I want to compare the checkout statistics by gender for the eighth grade classes. I also want to examine the types of books (by title) checked out by different homerooms and by gender.

Click through the next six steps.  (The exception is step 3.  Make certain the columns are organized

as you would like).

Name the Report and run.

It will be saved as an XML file. Right click over the download and save to the Desktop (or somewhere else on the computer). Name the file with your school number and the included information (i.e. PS999genderhomeroomreport)

Open Microsoft Excel. Search for the file you saved and open it. Choose Option 1 to open it. (As an XML table)

Now, use the filters to examine the checkouts by gender and homeroom.  See the example below.