Getting the most out of your data

Reports General

The DCS' reports function can be used to export a range of useful information from the DCS. This can be further used to analyse and interrogate the data to see how DCS-copied content is being used at your institution. 

Firstly you should enter the 'Reports' section of the DCS. You should choose 'HEI Report' from the drop-down list if it does not automatically appear. On this page you can select academic year and run the report. Note for report types where you can specify date, the date range must not cross academic years, it can only run from August 1st to August 1st, for example July 1st to September 1st will cause an error message.

Selecting a different date range will not remove links that were not yet created. All links from the selected academic year will be listed. However the date range will affect the number of downloads (clicks) and the status of the record, as per that date. 

Once you've selected the year, you can run the report using the 'Download Report' button.

You can then see the report, listing all the requests, both published and unpublished. The following information is currently extracted: Active Request ID, Licence Type, Course Code, Course Name, Lecturer, Number of students, Number of weeks, ISN, Title, Extract Title, Author, Extract Author, Publisher, Place of Publication, Year, Issue, Volume, Page Range, Status, Number of Downloads and Link.

Once open you can save this file, and we recommend saving as an Excel spreadsheet.

Vlookup Formula

Now you are ready to analyse your data. But what if some key fields that you store in-house are missing? Perhaps on your Student Record Systems (SRS) you also store other pertinent information.

We would advise using a Vlookup formula to add the data.

In our example we will be adding Subject Librarian and their email addresses to the report.

Firstly pull the data from your only holdings (for example the SRS), and then add these to an additional tab in your report.

Now decide on a unique reference which should appear in both your DCS report and the information you pull from your SRS. We have decided on 'Course Code'. This must be the first column for the data you place into the second tab of the report spreadsheet.

Now you name the data by highlighting it. The name can be added using 'Name Manager' under the 'Formulas' tab, or else using the cell directly above the table (indicated below).

Now you'll want to enter your formula into your DCS 'HEI Report' spreadsheet. Enter the first cell you wish to populate and, under the 'Formulas' tab, select 'Insert Function'. Select Vlookup and click 'OK'.

Now fill in the Vlookup table as follows:

Lookup_value this is the cell number of the first unique reference we are using, in this case 'Course Code' (the cell must be in your current spreadsheet and not the table).

Table_array select your range to select. As we have named a table we should add this. Click 'F3' to select a table.

Col_index_num which column from the table are you selecting from. As this first formula is for the Subject Librarian name we will select '2' as this is pulled from the second column of the table.

Range_lookup is this an exact match or a fuzzy match? For a fuzzy match we put 'True', however this is an exact match (we only want to pull the data if the 'Course Code' matches exactly) so we will put 'False'.

Then click 'OK'.

This should then populate the data. If you still see a formula, right click on the cell and select 'Format Cells', and ensure the cell is set to 'General'; once changed you can double click in the cell and this should change the formula to a result. If you get an #N/A result ensure that the 'Course Code' appears in the table and ensure there are no spaces preceding or succeeding the 'Course Code' in either spreadsheet. Hidden spaces can be removed from cells use the CLEAN formula (don't forget to Paste Special afterwards if you want to replace the cells - see below for more details). 

Once this is complete you can repeat for the second heading, in our case 'SL Email' by repeating the steps but changing the Col_index_num to '3' instead of '2'.

Once both fields are in place you can paste this data down for the rest of the cells. Just highlight, right click 'Copy' and then pull down to the bottom of the spreadsheet and select 'Paste'. This should add the names for the rest of the extracts.

You will see that the cells still hold formulas if you click on one of them. If you were to remove your table all the data would become an error message (#REF!). To stop this, highlight both the 'Subject Librarian' and 'SL Email' columns, right click 'Copy' and then right click 'Paste Special'; in the 'Paste Special' box select 'Values' and then click 'OK'. 

Now the cells have the actual data within them and you can remove the table if you so wish.

Grouping Data

Now we are going to group the data by the Course Code, perhaps to facilitate sending content to lecturers or subject librarians.

Before we group we should sort. To sort, highlight all the data in the spreadsheet using the cell to the left of column 'A' and above row '1'. In the 'Data' tab select 'Sort'. First we'll sort by 'Course Code' making the 'Order' 'A-Z'. Then we will 'Add Level' and make 'Lecturer' the second sort range, again 'A-Z'. Then click 'OK'.

Now the data is sorted we can add a group. Still in the 'Data' tab, select any cell. Then select 'Subtotal' (this makes it easier to group this type of data than the 'Group' icon). 

In the 'Subtotal' box you should select 'Course Code' under 'At each change in:'; 'Use function' should be 'Sum'; and 'Add subtotal' will be 'Number of Downloads'. This will group by 'Course Code' whilst also listing the sum for 'Number of Downloads'. Then click 'OK'.

Now the data is grouped. This may facilitate copying and pasting to lecturers or subject librarians, but what if you want to make a table to send to senior management? For this we would advise using the Pivot table function.

Pivot Tables

Once again select all the data. In the 'Insert' tab click 'Pivot Table'. As you already have selected the area you can just click 'OK'.

The Pivot table will open in a new tab of the file.

In the 'Field List' on the right hand side select the Row Labels you want by ticking the box. This time we'll select 'Course Name' or 'Course Code'. Then drag the 'Values' you want to the values field; for this data we want to see the 'Number of Downloads'. 

Currently the values field is automatically set to 'Count'. This basically will just count the number of extracts. If we want to see the total 'Number of Downloads' we should change this field to 'Sum' using the 'Value Field Settings' by clicking on the value, which is currently set to 'Count of Number of Downloads'.

After clicking on 'Value Field Settings', change to 'Sum' and click 'OK'.

Now you have the sum for 'Number of Downloads' for each course. 

Copy this Pivot Table to elsewhere in the spreadsheet. Here you can add additional fields by using a 'Vlookup' as described in the section above (making sure the unique column is first, naming a table, extracting the data from other columns in the table). You may wish to add Subject Librarian or Course Name/Code for example.

In order to look at the number of extracts for each course you can change the 'Value Field Settings' back to 'Count'. This can then be added as another column for your table.

You can copy the data to your new column in your table.

Now you can see how many downloads there are per extract. Add another heading called 'Downloads Per Extract' and type the calculation in the cell =E2/F2 (where E2 is downloads and F2 is extracts).

You can then remove the additional decimals by using the right click 'Format Cells' and removing decimals places.

Then copy this down. 

Now you can sort by number of downloads per extract and see how actively engaged the students are for each course.

If you can think of any other data tricks that might help HEIs, please email us at [e-mail]. 

Here is a video of the above, recorded from a recent webinar: Using Your DCS Report Data