1 Reply Latest reply on Jun 25, 2014 9:03 AM by Erin Gehn

    Crosstab (Automatic Grouping & Sorting) Hints-and-Tricks

    Ayman Teradata

      Sometimes your business users require that you get them some classical tabular data rather than graphical visual reports, in other words: crosstabs! To some people crosstab formatting and views can be tricky to understand ... I will try to simplify matters from my experience with them.


      First, for any crosstab, the order of columns analyzed are from left-to-right (remember this as this is very important later). Right-to-left is not supported.


      Always remember that Measures go on the right, dimensions go on the left (you can convert measures to dimensions to sort them differently, but in that case you won't be able to calculate grand-totals on the columns). Measure names also goes on the right of the dimensions when placed on the columns shelf.


      By default, the crosstab does the sorting for columns from left to right. That means any column you want to sort with first must go on the leftmost position, the second column will then be sorted by it after the first column on the left, and so on. By default too, the crosstab groups the values repeated in a column for consecutive rows in the left-to-right order. So, if your first column on the left has repeating values for consecutive rows the value will be shown once in a big block on the column (the column can be formatted so that the values can be placed on the top, middle, or bottom of the view).


      Hope this was helpful for the classical type of users ...


      On the other hand, it would be great if you share your hints, tricks and hacks about crosstabs too!!