What I’m trying to accomplish here is to have ‘items’ that are not in the factSales table to appear in the pivot table as one of the ‘Header’.
The Fact Tables that I had are:
factSales – where I had the sales amount by profit center (which is city), account category and date.
factParkingLot – where I had the number of parking lot per profit center (which is city).
factLeadsAndNoOfCustomers – where I had the number of Leads and/or No. of Customers by profit center (which is city and date.
Other tables are:
Header – where I want the pivot table to show the ‘Headers’
Account – where I had listed the account names and sub headers given the relationship with the ‘Header’ table
Profit Center – where I had a list of profit centers’ ID with it’s respective city.
DateTable – where I had a list of dates, breaken out by year and month. The purpose of this table is so that I can create YTD (Year To Date)
Below are some images of the tables as well as my relationship, and the desired result.
Note that 'Desired Result is an image that I've put together.
Fact Tables
Fact Tables.png
Other Tables
Other Tables.png
Relationship
Relationship.png
Desired Result
Desired Result.png
Notice that on my desired result, I'd like to accomplish:
1. The groupings, where users can expand and collapse.
2. To have the correct figures for each 'headers' and/or sub-headers (i.e. Leads, No. of Customers, ..., Total Expense, Controllable expense, Advertising etc)
Bookmarks