Homework 12

Excel Pivot Tables For Global BI

Goal

source:memegenerator.net

By completing this exercise you will learn how to load a pivot table with data that is stored in a remote databases, and how to set up the pivot table to a desired configuration.


Context

Life is good.  Your career is moving up and fast. You are now a financial analyst at Acme Global, a multinational company with a global presence.
It is year-end, and the Budget Director, Ms. Penny Pinchersen, wants to audit the differences between budgeted costs and actual costs throughout the year. Seems an easy enough job, except that the budget for the multinational is very detailed: it has over 15,000 entries. Rather than spending hours on the analysis, you decide to create a BI application that will perform the two required analyses in seconds.  Life is good indeed!


Criteria

The budget director wants you to create a simple app with three buttons:

  • The first button downloads 15,000+ rows of financial data from the SmallBankDB enterprise database. The table containing the financial data is called ACME_BUDGET and it is stored on the DBMS server f-sg6m-s4.
  • The second button is labeled "Departmental Variances" and sets up a pivot table that must look like this screenshot (including values,  number formats, headers). Set colors as close as you can make them. Ok if the colors are not very close. Also ok if the position is no exactly the same as the picture.
  • The third button is labeled "Departmental Variances by division" and sets up a pivot table that must look like this screenshot (including values, number formats, headers).Ok if the colors are not very close. Also ok if the position is no exactly the same as the picture.
  • At start there should not be any data on your screen. 
  • One exception: VS may not let you choose via code the order in which the pivot filters appear on your screen.  That is ok.  So you do not have to reproduce that exact order when you are mimicking the two pics above.

It is your lucky day! Googling "VBA & pivot table" you found this code that you will need to adapt to your purposes.
There is also a vLab.  As usual, you will need to modify the app shown in the video to complete your assignment.