- Microsoft SQL Server 2014 Business Intelligence Development Beginner’s Guide
- Reza Rad
- 280字
- 2021-08-13 17:55:28
Time for action – creating a hierarchy from a single table
In this example, we want to create a calendar hierarchy in the Date table. So, this example shows how to create a hierarchy from a single table. A calendar hierarchy will have the year, quarter, month, and date level:
- Go to Diagram View, maximize the Date table (with a maximize icon in the top right-hand side of the Date table).
- Create a hierarchy (the second icon from the top right-hand side of the
datetable shown in the next screenshot), name the hierarchy asCalendar, and drag-and-drop CalendarYear, CalendarQuarter, EnglishMonthName, and FullDateAlternateKey as levels of this hierarchy.
- Save the changes and view the tabular model in Excel. Select Calendar under Date. Also, select Sum of SaleSamount from measures. You will see that the calendar hierarchy shows the hierarchy of years, quarters, months, and dates.
- We observe that the order of month in the calendar is not correct; it is already ordered based on the month name, but it should be ordered based on the month number as well.
- Go to Grid View of the Date table, select the EnglishMonthName column, and then from the tabular toolbox icons on the top, click on the Sort By Column icon. Choose MonthNumberOfYear as the Sort By Column.
- Save the changes and refresh PivotTable; now you will see that the order of the month shows correctly as January, February, and so on.
What just happened?
Creating hierarchies from a single table is as simple as dragging-and-dropping columns (step 1 to 4). Also, in this example, you saw how you can change the sort order of a column based on other columns (step 5).
推薦閱讀
- Deploying Node.js
- Spring 5.0 Microservices（Second Edition）
- Web前端開發技術：HTML、CSS、JavaScript（第3版）
- 造個小程序：與微信一起干件正經事兒
- Unity Virtual Reality Projects
- Mastering Predictive Analytics with Python
- Learning Hadoop 2
- Cocos2d-x Game Development Blueprints
- Clojure for Java Developers
- Web App Testing Using Knockout.JS
- Arduino機器人系統設計及開發
- 零基礎C#學習筆記
- Mastering SciPy
- Mastering ASP.NET Web API
- SQL Server 2014 Development Essentials