Homework 12
Excel Pivot Tables For Global BI
Goal
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 style and colors as close as you can make then).
- The third button is labeled "Departmental Variances by division" and sets up a pivot table that must look like this screenshot (including style and colors as close as you can make then).
- 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. So you do not have to copy that order exactly 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. At start there should not be data on your
screen.