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.
- 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.
- 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).
- 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.
For example on the units sheet:
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.
For example on the department sheet titled MRKS:
(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