How to Optimize Report Performance with Hierarchies in Excel
In this article, you will use Excel to create two tables to compare data collection methods. You will also learn how to edit a table to sort the incidents and change their value to a percentage of the whole.
To learn more about Northcraft, take a look at our Resources page.
This video provides step-by-step instruction, or you can follow the steps listed here.
Step 1: To begin our comparison, select an appropriate database
Step 2: Select your multidimensional database (MDB)
Step 3: Scroll down to select the ‘Incident Management Cube’
Step 4: Now click on the ‘Next>’ button on the bottom of the window
Step 5: Now click on the ‘Finish’ button in the next pop-up. Feel free to change the file name if you’d like.
Step 6: Now click on ‘OK’ in the last pop-up window.
Step 7: Now select the search field to begin selecting data. Type in ‘closed incidents’
Step 8: Select ‘Closed Incidents’
Step 9: Now click the ‘Clear Search’ button.
Step 10: Since we’re going to be comparing two different methods of collecting data, select another cell a few places to the right of our first selection.
Step 11: Now click on the ‘Insert’ tab at the top of the page.
Step 12: Now click on ‘Pivot Table’ in the top left corner of the page.
Step 13: Now click on ‘Use an external data source’
Step 14: Now click on the ‘Choose Connection …’ button
Step 15: Now click on the first connection, which is the same as the one we already used for the first. This is what we can use to compare our data collecting methods.
Step 16: Now click on the ‘OK’ button
Step 17: Now go back to the search field and type in ‘closed incidents’ again.
Step 18: Now click on the ‘Closed Incidents’ field.
Step 19: Now select the first table we created so we can show how easy it is to use one of the canned hierarchy fields.
Step 20: Now click on the search field and type in ‘hierarchy – assignee’
Step 21: Now click on ‘Hierarchy – Assignee’
Step 22: Now click on the ‘Clear Search’ button to ready our search field again. Notice how easy it was to get our table set up using a Hierarchy field. Our second table will be built from the ground up.
Step 23: Now click on the second table so we can begin to fill it out.
Step 24: To begin, click on the search field and type in ‘assigned support organization’
Step 25: Now click and hold on ‘Assigned Support Organization’
Step 26: Now drag it down into the ‘Rows’ area to add it to our table
Step 27: Now click on the ‘Clear Search’ button
Step 28: Now click on the search field again and type in ‘assigned group’
Step 29: Now click and hold ‘Assigned Group’
Step 30: Now drag it into the ‘Rows’ area to bring it into the table
Step 31: Now click on the ‘Clear Search’ button again.
Step 32: Select the search field and type in ‘assignee’
Step 33: Scroll down to the ‘Assignment’ folder and click and hold on ‘Assignee’
Step 34: Drag it down into the ‘Rows’ area
Step 35: Right click on the second field and select ‘Expand/Collapse > Collapse Entire Field’
Step 36: Now we have both tables set up. You can see how much easier it is to set up the first one. Before we finish, we’re going to quickly go through manipulating the data to arrange it better.
Step 37: Now right-click on one of the cells in the left table.
Step 38: Now go to ‘Sort > Set Largest to Smallest’
Step 39: Now right-click on the table again.
Step 40: Now select ‘Show Value As > % of Grand Total’
Step 41: Now our first table is arranged to show the highest percentage of closed incidents, and we’ve shown how much easier it is to use the Hierarchy field than building our table from the ground up.
Support by website
You can obtain technical support from Northcraft Analytics 24 hours a day, 7 days a week at https://northcraftanalytics.desk.com. From this website, you can:
- Find the most current information regarding Northcraft Analytics products.
- Manage your account information
- Upload documents for content sharing exchange.
- View shared documents from other users.
- Order or download product documentation.
- Download products and maintenance releases.
- Report an issue or ask a question.
Support by telephone or email If you need technical support either by phone or email:
Call (+1) 800 565 1375
or by email using:
Every effort has been made to make this as complete and as accurate as possible, but no warranty or fitness is implied.
The information is provided on an “as is” basis. The author and Northcraft Analytics, LLC, shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this manual or from the use of the discs or programs that may accompany it.
Copyright © 2010 Northcraft Analytics LLC. All Rights Reserved.
Trademark Legal Notice. All product names, trademarks and registered trademarks are property of their respective owners. All company, product and service names used in this website are for identification purposes only. Use of these names, trademarks and brands does not imply endorsement.