ProductsAI & Analytics

Sorting a Crosstab by a Field Not Displayed in the Crosstab

Say you want to create a crosstab showing payments or orders by year for each customer. That is easy enough. You create a dataCube with Customer and year as groups/dimensions and their payment/order totals as you measure. In your dataSet, you also have a credit limit for each customer. You would like to sort by this so that your “typically” bigger customers are at the top of your list.

If you went to the sorting tab in the property editor for the crosstab with things setup as described above, you wouldn”t find the option to sort by credit limit available because you aren’t displaying it in your crosstab. You don’t want to put this value in your crosstab though, so you’re stuck, right? Wrong. There is a feature in BIRT that is somewhat hidden if you don’t know the right place to look that will solve this.

If you open your dataCube and double click on the group/dimension that you’d like to sort, a group level editor will pop up:

As you can see in the above image, there is an attribute section for the grouping. If you click where it says “Create New Attribute”, you can select your credit limit field and it will then be listed under the attributes section as you can see done above. Now when you go to your sorting tab for your crosstab and click on “Add” and select the group you added the attribute to, if you click on the function button next to sort key, you’ll be able to see and select the attribute as your sort key.

The new sort key will be in the form of dimension[“groupName”][“dimensionName”][“attributeName”]. The resulting crosstab will now not be sorted alphabetically by customername or by the summary field, it’ll be sorted by the credit limit value from your dataSet. The following link is to devShare entry on this with this example included.

Show More


OpenText is the leader in Enterprise Information Management (EIM). Our EIM products enable businesses to grow faster, lower operational costs, and reduce information governance and security risks by improving business insight, impact and process speed.

Related Posts

Back to top button