Allocated Fund Changes - Reporting, Analysis, and Distribution

Owned by Laura Morse
Last updated: Aug 09, 2019 by Amy Dittman


  1. Analytics Query to identify associated orders

    1. A generic query that disregards order type has been saved in Shared/Harvard University/_Departmental Reports/HL ITS called Orders for Fund Change Template

      1. Certain filters might not be identical, and other fields might need to be added or removed, particularly if any cleanup is to be performed in advance, or to split this into onetime vs. continuous orders

        1. An added field that can be useful for cleanup is receiving date, since this could include e-resources, that field is left off of the generic version, as these do not have receiving data.

    2. Basic queries - generate these in advance, for review, in hopes of identifying items eligible for cleanup before the fund change process needs to happen

      1. One Time order version of the query

      2. Continuous order type version of the query

    3. Send filtered versions of these to any associated PO Line Owners for review at least 1 month in advance of the rollover, to allow for any closure/correction before the rollover

  2. In anticipation of updating the remaining orders, during the last week of the FY, run fresh reports that capture the one-time and continuous orders remaining after any cleanup, and/or closure due to receipt/invoice payment that took place during Fiscal Close process

    1. You might choose to remove the filters for continuity to run a single query

    2. Otherwise, paste the two query exports together to form a single report in Excel

  3. Pull out anything with multiple funds for manual update

    1. Cost-shared orders: Filter by POL owner to remove anything with ITS 625 eResources - these are by definition cost-shared, and require manual update

      1. Cut these out and move them to their own tab

    2. Split-fund orders - in other words those orders that utilize multiple funds, from the same tub: 

      1. Orders split between multiple funds to be changed should already be in the query’s results, isolate those first:

        1. Use Conditional Formatting to highlight duplicate POL numbers

        2. Sort by color of the column

        3. Cut and paste all highlighted columns onto their own tab of the spreadsheet, called split funds

      2. Create an itemized set of all remaining PO Line Reference Numbers on the report’s original tab, after the previous filtering has taken place and moved cost-shares and split-funds onto their own tabs

      3. Export the resulting POL list from Alma

        1. Move the fund column to the very end of the sheet

        2. Use Text to Columns on the fund column with the ( character as a delimiter

        3. Filter to anything where multiple funds appear

          1. () displays the percentage of a fund applied to an order, using this as a delimiter will split multiple funds into their own columns

            1. Filtering at this point to remove anything that showed 100 in one of the columns was helpful

        4. Copy the PO Line Reference Numbers from this report that show multiple funds after this process into the bottom of the PO Line Reference column of the Analytics report output

      4. Use conditional formatting to highlight duplicate values for POLs on the Analytics query output again

      5. Sort the sheet on color of cells and data for POL Reference

      6. Cut all highlighted orders and move to their own tab of the spreadsheet

      7. Delete the POL Reference entries that came from the Alma export, so that each order only appears once - sorting on another field like title will pull these together, since it should be blank for them all.

  4. Marking each set of orders with the new fund from the cross-walking table

    1. On all tabs of the spreadsheet, sort by fund

    2. Create a new column named ‘new fund’

    3. Filter by fund one by one

      1. Referring to the cross-walking table of old funds to new, enter the new fund into the New Fund Column for each fund involved, changing the filter from one fund to the next as you work

    4. Filter the split fund tab by PO Line Owner Library Name, and in the case of ITS 625 Acquisitions, by PO Line Type Name and distribute to anyone who will be making manual changes of these orders, to send out to associated Section Heads and/or Unit managers, to inform them of the orders that they are responsible for that will require manual update once the rollover has been completed

      1. Send the list of cost-shared resources to the Unit Manager for eResources to have the unit update all orders manually once the rollover has completed

    5. To ascertain which funds can be updated by batch job, and which will require manual update from this main tab with the remainder of the orders, create a pivot table by fund, using POL Reference as the data column, with Count 

      1. Sort by number of orders per fund

      2. Divide the report into two tabs 

        1. For those with at least 10 Orders associated, send the list to LTS for update by batch job

        2. Filter the remainder, where there are less than 10 orders per fund, to create individual lists by POL Owner Library Name, and in the case of ITS 625 Acquisitions, by PO Line Type Name and distribute to anyone who will be making manual changes of these orders

  5. Verify that all relevant funds received an allocation after the new fiscal year, run a query in Analytics with the following criteria:

    1. Filter the resultant report to cases where the allocation = $0

    2. Check for any of these funds on the fund crosswalk of old funds to new funds, to check for matches

      1. Alternately, the filter could be changed from Parent Fund Ledger Name to Fund Ledger Code, including the data of all of the new fund strings, so that results would not need to be checked against the crosswalk

  6. NB: Doing updates, any fund with more than five orders associated with it was faster to create an itemized set and run the Update POL Transactions Job

  7. Quality Control/making sure you caught them all - one day after finishing all batch and manual updates, run a query in Analytics using the following criteria:

    1. Investigate/update any results on this tab. 
    2. If they are owned by another technical services unit, follow up with that unit