The Reporting Dashboard allows you to extract relevant details from a large set of data. It has several features that allow you to group or filter data to include or exclude columns and break down data into years and months. The speciality of this dashboard is that it allows you to create Microsoft Excel-like pivot tables. The Pivot Mode function allows you to organise data in a variety of layouts and save a suitable layout as a view. You can access the saved view whenever you want to. Data contained in a view can also be exported to Excel format.
Amplify provides custom reporting views which can be utilised as is or by making relevant changes to create custom views.
The reporting dashboard provides high performance tabled reports at all levels of hierarchy, enabling pivot style reporting using data across your enterprise. In the following section, we will explore a standard Amplify reporting dashboard view that is created using the Pivot table mode and discuss how it is designed.
Example View: Budget Costs by Category
This view essentially summarises the budgeted costs for various categories per quarter for multiple years. You can see the different categories of costs in the rows on the left-hand side, initiatives using that cost category is listed under each category. The budgeted costs for each initiative is summed up for each quarter in specific years is displayed in the columns on the right.
Enabling the Pivot feature
Enabling the Pivot Mode allows the data to be arranged in a pivot chart layout on the dashboard. To enable the pivot feature, click on the Columns tab to reveal a side panel. Put a tick in the Pivot Mode checkbox.
Step1: Data set selection
The first step is to select the data series that you want to add to the report. In the example report, we will add the Budget series for Cost.
Select the Cost option and then the Budget option on the top right of the table.
Select the Include Active Baseline option to include the current baseline.
Step 2: Resolution selection
Once you have selected the series to be added to your report, you must select the resolution. The resolution determines what frequency of data you want to extract. For the above example, we have enabled Month, Quarter, and Financial Year so that the budget costs for each category can be summarised for every month of each quarter for multiple years. Expand Step 2: Desired Resolution under Filters, to set resolution filters.
Next, drag the required fields into one the RowGroups, Columns and Values box areas. The Filters box is used to apply global filters to the table. The following sections explain how to proceed.
If custom fields are being used, you will see them grouped by the name of the object that they are mapped to.
Deciding the Data Grouping
The RowGroups box must contain the fields that control which data are shown in the rows of the table. For example, if you place the Name (i.e name of the cost) field in the RowGroups, the table gets updated so that the rows will contain information for each of the different types of costs. You could add multiple fields here. To create rows for each initiative under each cost name, place Name under Initiatives in the RowGroups box. Note that if you reverse the order of fields, i.e if you place Name field under Initiative, the data grouping will also be reversed and you will see rows for each cost item per initiative.
The Column Labels box contains the fields that control which data are shown in the columns of the table. The mathematical function that you are going to specify in the Values field(see the following section) is going to be applied to these values. You can place multiple fields here. The order of the fields determines how data is presented. For our example, to show values for all quarter under each Financial Year, place the Quarter field under the Financial Year field.
The Values box contains fields that control which data are summarised in the pivot table. Values box allow different representations of data. You can show the sum of all values, count, average etc. To create the Budget Cost by Category report, drag the Value field to the Values box. The budgeted cost values for each initiative is summed up for each quarter in specific years is displayed in the columns on the right. You will see that Amplify automatically creates the sum of the budget values.
Amplify will create a pivot table based on the specified settings. Budget costs of a certain type are summed up for each quarter and are displayed per year. You can now enter a name for the view and click on the Save as button to make it available the next time you log in.