Showing 3 levels of Territories on the same report without using hierarchies

[VinitaSinha]

This is a short blog to explain how you can show 3 levels of Territories in the same report. Eg: on a Visits Report, if you want to show the Territory associated with the Visit, its parent and grandparent territories. Typically, through the Territory dimension on the report, you can show its parent only. This blog explains how you can show the Grandparent territory as well on the same report

For this purpose, I have created a Join Data Source between the Territory and the Visits, because the Grandparent information is not available already in the Visits Data Source (or any data source for that matter)



This is a simple Inner Join, the Join attribute being the Sales Territory on the Visit header with the Territory attribute on the Territory master

I have also included the attribute Parent Territory (not Parent Territory ID) additionally. Why? Because with this dimension you will be able to pull in additional attributes as we’ll see later

Now, create a report on this Data Source and include these characteristics for the Sales Territory and Parent Territory



In the report view pull in the Visit Territory (Sales Territory) and the Parent Territory



Now, to get the grandparent territory, let us use the Add Fields

Using the Parent Territory dimension, you can get its parent as well now. Recall that we included the Parent Territory attribute and NOT the Parent Territory ID. This allows you to pull in additional attributes of the Parent Territory



Now, you see the Sales Territory on the Visit, its parent and grandparent territories in the same report



Cheers !

Vinita