Apptio for All

Ingredients

• General Ledger with sparse vendor details
• Vendor List (if you can’t make from scratch, store bought is fine)
• A pinch of scripts

Steps:

1. Load General Ledger into Apptio Costing and utilize appropriate date partition
2. Load Vendor List into Apptio Costing
   1. Call it myVendorList
   2. Add formulas step and formula called Vendor Concat
      1. Vendor Concat =Vendor ID&&Vendor Name
3. Create two transform datasets of your General Ledger – lets call them Transform A and Transform B
   1. Transform A – add formula step (will complete later), add join for chart of accounts (if needed), and add map columns (map to Cost Source Master Data) – set aside to rest
   2. Transform B – add hide and rename step, and formula step
4. Create a Key – identify columns in the general ledger that you want to use to make each row distinct. I’m using (Account, Description, Amount, Voucher).
   1. Create formula Key =Key(Account, Description, Amount, Voucher)
   2. Add this formula to both Transform A and B
   3. Finally, in Transform B’s hide and rename step – hide all columns that are not vendor related nor used in the Key().
5. Create New Editable Table – choose Enriched Table (aka Generated)
   1.  Source Data Set = Transform B
   2. Primary Key Column = Key
   3. Columns to include = all
6. Configure two additional columns in Editable Table
   1. Vendor Name Override
      1. Type =Label
      2. Possible Values =%myVendorList/!LIMIT_COLUMNS[Vendor Concat]/!SORT[Vendor Concat|asc]
   2. Updated By
      1. Type=Label
7. Create new report - name of your choice
   1. Go to report tab, and add editable table from ribbon
   2. Drag columns from your enriched editable table to this report level editable table
   3. Make sure the report level table is highlighted, and go to the editable table ribbon at top of page
   4. Click on Script, copy and paste the below
      1. if eventType = "cellEdit" OR eventType = "addRow"{row.Updated By}="<%=$CurrentUser:Users.Id%>"end if
8. Save Report
9. Create new table and call it GL Vendor Updates
   1. From Source step select Editable Table, and use your enriched editable table as source
10. Navigate back to Transform A (which is done resting by this point)
    1. Add two formulas
       1. Vendor Name Override Lookup =lookup(Key, GL Vendor Updates, Key, Vendor Name Override)
       2. Vendor Name (edit this column from original general ledger) =if($_=””, Vendor Name Override Lookup, $_)
11. Check-in
12. Bake for 1-3 months with finance and IT teams. Enjoy!