Report on the number of reading lists associated with a higher level in the hierarchy

When you're reviewing faculty or department engagement it can be useful to identify the number of lists that are associated with a higher level node on your hierarchy in Talis Aspire Reading Lists (TARL). You can pull this information into a report by completing the following steps and formulas in a spreadsheet software such as MS Excel.

1. First run and export (as .csv) the following reports from TARL:
• Hierarchy Snapshot Report - node type filtered to the top level hierarchy level you are wanting to report on (eg. Department) - this will give you the hierarchy code for this level.
• Hierarchy Snapshot Report - node type filtered to the next level down in your hierarchy (eg. Unit) - this will give you the hierarchy code for this node, and also their parent hierarchy code.
  Note: The number of Hierarchy Snapshot Reports you will need to run is dependent on the structure of your hierarchy. For the examples listed below the levels are Department > Course > Unit
• All Lists Report filtering list status to 'Published' and 'Published with unpublished changes', and the time period(s) you're wanting to include in the final report.
  
  
2. Once you have exported these reports, open each in MS Excel (or similar) and move them into the same workbook/file so that you can collate the data into a summary sheet.
   
   
3. Some lists and hierarchy nodes can be linked to multiple nodes on your hierarchy, so you'll need to split them into separate cells so the formulas will identify all appropriate matches.
   1. Insert extra blank columns after the code column in your spreadsheet (add enough columns so that when you split the codes, each code has it's own cell).
   2. Highlight the hierarchy code's column and use the 'Text to Columns' feature to split the codes into separate cells - when the popup appears select 'delimited' and Next, then select semicolon as the field delimiter to split the codes - if you haven't added enough blank columns you will get a popup in MS Excel notifying you that it will over write data.
      
      
4. Now on the lowest level of your hierarchy spreadsheet, add a formula to search your All Lists report for lines that match your hierarchy code. 
   For example on the units sheet: =COUNTIF(all_lists!G:X,A2)
• all_lists! - tells the formula what sheet to look in
• G:X - is the range of columns on the all lists sheet that contain a code
• A2 - is the cell that contains the hierarchy code you are wanting to match on the list.  When you fill this down the column this cell number will update to match the corresponding code cell.
  
  
5. On the next level up on the hierarchy, you'll need a formula to search the lower lever hierarchy node's spreadsheet, matching the parent code to the next level up. (ie. what department the unit is linked to).
   For example on the department sheet titled MRKS: =(COUNTIF(all_lists!G:X,MRKS!A2))+SUMIF(hierarchy_snapshot_units!G:AG,MRKS!A2,hierarchy_snapshot_units!AL:AL)
   • (COUNTIF(all_lists!G:X,MRKS!A2)) - this does the same as the previous sheet's formula, looking for lists that have been linked to this level of the hierarchy instead of to a unit.
   • +SUMIF(hierarchy_snapshot_units!G:AG,MRKS!A2,hierarchy_snapshot_units!AL:AL) - This looking for a match of the hierarchy code from the the unit's hierarchy snapshot sheet for a match
   • hierarchy_snapshot_units!G:AG - this tells the formula to look at the range containing the hierarchy code in the hierarchy snapshot units sheet
   • MRKS!A2 - in the previous range, look for a match to this cell
   • hierarchy_snapshot_units!AL:AL - for any matches, take the information from this column
     
     
6. Now add a new sheet to the workbook where you can manually create a summary of each department.