Ordering lines by a hidden column on a tabular report
SolveXia Analytics & Reporting provides a simple way to create powerful reports based on managed tables. With the latest BI and data visualisation techniques, you can create reports that communicate and inform effectively.
This article will guide you through how to sort the data within a tabular report by an order that is neither ascending nor descending with respect to any of the displayed columns.
Before we get started
Before you start, please ensure that you have a managed table with at least one row of data. If you need help to create a managed table or to import data into a managed table, please refer to the articles below.
- How to create a managed table
- How to import data from an excel file into managed table
- How to import data from text file into managed table
- How to import data from access file into managed table
Imagine that your managed table is of the following format:
Imagine that your managed table contains the following data only:
Imagine also that you wish to create a tabular report from this data which resembles the following image (for help on creating tabular reports, see the Get started – create a tabular report article).
As you can see, the desired result cannot be achieved by sorting the data on either Column A or Column B. That is, applying either an ascending or a descending sort by either an alphabetical or a numerical order will not create the desired result. The solution is to create an extra field in your data (as well as your) managed table. This new field will contain numbers which represent the desired sorting order.
The managed table will therefore be modified to the following format:
The data of the solution will therefore resemble the following image:
1. Import the new data into the new managed table. For help on importing data into managed tables, see for instance, the How to import data from an excel file into managed table article.
2. Once the data import has completed, create a new Report.
3. At the ‘Report Data’ stage, [highlight txt=’drag’] all the available fields from the [highlight txt=’Data Field’] pane into the [highlight txt=’Columns’] pane.
4. [highlight txt=’Navigate’] to the ‘Preview’ stage:
5. Click the arrow at the right side of the header cell of the ‘Sort_Ordering’ column.
6. A dropdown will appear. Select ‘Sort Ascending’ from the dropdown.
7. You will see that the tabular report has been sorted into the desired format. Click arrow at the right side of the header cell of the ‘Sort_Ordering’ column again. This time, select ‘Hide Column’ from the dropdown that opens up.
8. The tabular report should now resemble the following image: