Drill To Transaction Details on Dashboards
Allowing end-users to have visibility into source transactional data is very important for many Anaplan customers. There is always the ability to Drill-Down into source data, but at times, this may not be intuitive or may take several drill-downs to get back to the raw data.
There is another option that can be easily accomplished and can give users all the information they need on one centralized dashboard. This is accomplished using a series of filters and with synchronization on a dashboard. This allows users to select any dimension of a module on a dashboard and synchronize a separate element on that dashboard to show the related transactions that make up a value.
We can accomplish this by using the following steps.
1. We will want to store data in a flat fashion. We will have a module that has a primary key (unique identifier) for each row of transactional data, along with line items for each category of the transaction
2. We will then have a separate module that is essentially a carbon copy of the first raw data module and will formulaically link to the raw data module. One consideration here, is that this second module will consume space. However, since this is a flat module, this will typically not consume a significant amount of extra space in the model. Also, a reason that we want this second module is to differentiate the module we are loading raw data to, from the module that will be present on a dashboard to end-users
3. There are a few intricacies to note on the module above.
- Filters - We will use a series of filters to filter the data to display what a user is selecting. In the examples below, we have filters for Cost Center, Location, and Account. The formulas here test to see whether the appropriate dimension selected by a user equals that dimension of the transaction data. For example, if a user selects Cost Center 1 on a page selector, this filter will be used to filter only transactions that are related to Cost Center 1
- Applies To of Module Itself – We will add Applies to for each dimension that we want to filter based on. Adding these allows us to synchronize to the selection made by users on a dashboard. It is important to add the Applies To to the module itself and NOT to the Raw Transactions line items. This ensures we are eliminating sparsity as much as possible. i.e. the Applies To of the line items in the Raw Transactions section is only the Unique ID
- Applies To of Filter Line Items – It is important to add the Unique ID as a dimension here, along with the appropriate dimension that each line item is filtering on. For example, the Location Filter line item applies to only Unique ID and Location lists
- Summary Method – We will designate the Summary Method of the filter line items to Any. This allows a user to select any level of a composite hierarchy and for us to still be able to filter raw transactions. For example, we have the Cost Centers set up as a composite hierarchy with three levels. If a user selects a node from level 1 or level 2 (both summary levels), then the filter will show transactions that make up the summary node
The end result is a dashboard where a user can view a module, such as the P&L shown below.
When the user selects any node in the dimension of the P&L, the related Transaction details filter to show exactly the transactions that make up what the user is selecting. This allows users to have more visibility into source data in an easier fashion.
Our example above shows data for just one time period. In theory, this concept could be used to allow a user to select a column of any time period and also sync transaction data for that time period. Or, any other dimension! Hope this helps!
Could Dynamic Cell Access be used to make the import module "read only" for end users? This would eliminate the need for duplicating the entire dataset in a formula-based module.0
Yes DCA could! This was created before DCA was released but it would definitely streamline this process now. Good call out!1
This use case is sooo useful it should be pushed to anapedia as it really serves the "drill through" needs we have to have a better understanding of the details summing up into figures0