Hierarchies and Drilling are inter-related wherein hierarchies should be defined in universe to do drilling in the report.

Drilling hierarchy determines the drill path from high-level summary data to low-level detailed data. Drilling is used to perform analysis based on trends in the required data that can be recognized when looking at low-level detailed data.

Define Hierarchy

Hierarchies should be defined in universe based on your business requirement.  First identify the required objects and arrange them under one class so that it’s easy to update / maintain the hierarchies.

To create hierarchies, go to Tools – Hierarchies.
All the available objects will come in the Hierarchies editor left side and those are considered as Default hierarchies. Default Hierarchies may not have proper hierarchy since those will come as is in the form of alphabetical order of the classes.

To meet with user requirement we have to create custom hierarchies. If we keep all the required objects in class in the custom hierarchies, it is easy to access and maintain. To create Custom hierarchy, click New and add the one Hierarchy name and select he required objects one by one in the order add.
If you want to arrange the hierarchy levels for the object within the same class, you can change the levels using Move UP & Move Down button.

Note: If you can add multiple Classes into customer Hierarchies. Also you can create separate class in Hierarchy editor by clicking New and add one by one object from any classes in the left side.

In the above example, Hierarchy has been set “Country – State – City – Product Category – Product Sub Category – Product Name”.

Once hierarchies have been created, you can create the report and do trend analysis by using Drilling techniques.

Drill Techniques

There are three type of drilling.
1.    Drill UP – Drill up the levels from Low level detailed data to high level summarized data.
2.    Drill Down – Drill down the levels from high level summarized data to Low level detailed data.
3.    Drill By – Drill By is an advanced drill feature that is also listed in the speed menu with Drill Up and Drill Down. It allows you to skip over objects in the current hierarchy or to jump to a different hierarchy for additional analysis.
Prior to Drilling, Hierarchy objects (Required objects for your trend Analysis) should be placed into report query panel through “Scope of Analysis” option by selecting Custom Level. It is not necessary to add those required hierarchy objects into result objects section in the query panel. It will reflect once you added into the scope of analysis section.
Scope Level can be changed to One Level / Two Level / Three Level or Custom Level depends on the requirement.

For example, report is having the Product Name and Revenue and you want to do trend analysis by drilling from Product name to till Country Hierarchy.

The Drilling preferences can be set in Tools – Options (in Drilling Tab), whether the drill report should be in same tab (existing tab) or should open a new tab (duplicate tab) and process drilling.
Start drilling click Analysis – Drill or Drill icon in the tools. It will duplicate the tab for Drill (since Drill preferences set to create duplicate report) and Hierarchy object should have drill up icon in the header. Since below example chosen the low level detail object it you can only do Drill up.

By clicking the up arrow you can reach the higher level summarized data. If you want wan to drill down for each values of the next level you can click that report value to check detailed level for that particular data.

By Clicking the “Commercial – Bus ILS” Will go down to detailed level analysis for that value.

You can go up / Down from each values of the hierarchy objects for trending analysis. Also you can jump to any level by using Drill by option from the hierarchy objects.

Drilling is one of the better options for trend analysis and it will process the trending within report data and you will get result faster to take good business decisions.