Perspective Report Crosstab Column Sorting

[Daniel_Calderon]

Does anyone know how the crosstab table component decides how it sorts rows and columns? and if it's even possible to control it? Thanks.

Editted a typo

[gunnar.newell]

I don't use the CrossTab componenet much, but I would try presorting the data before you populate it into the crosstab.

[Daniel_Calderon]

It ignores whatever order you pass and sorts its own way. Which I am trying to figure our and control.

[gunnar.newell]

I played around with it a bit.

It looks like it does first appearance sorting. The Documentation on CrossTabs doesn't look like it gives us any detail on sorting order.

I used the example data in the documentation, and I could only ever get it to sort in order of appearance.

So this data

has this cross tab

and this data

has this cross tab

note I'm not doing any summing for the values they are also by first occurance.

[paul-griffith]

It's not possible^[1] to set explicit sorting for cross tabs. I'm really surprised no one has noticed this before.

That said, along the lines of previous nerd sniping, it is at least technically possible. I would not advise anyone actually do this.

Given crosstab data like this:

	# Faux row-level data for a Reporting Cross Tab.
	headers = ['Region', 'Product', 'Quarter', 'Sales', 'Units']
	rows = [
		['North', 'Widgets', 'Q1', 12500.00, 118],
		['North', 'Widgets', 'Q2', 14750.00, 136],
		['North', 'Widgets', 'Q3', 13925.00, 129],
		['North', 'Widgets', 'Q4', 16200.00, 151],
		['North', 'Gadgets', 'Q1', 9800.00, 92],
		['North', 'Gadgets', 'Q2', 11125.00, 105],
		['North', 'Gadgets', 'Q3', 10875.00, 101],
		['North', 'Gadgets', 'Q4', 12400.00, 116],
		['South', 'Widgets', 'Q1', 10350.00, 99],
		['South', 'Widgets', 'Q2', 11900.00, 112],
		['South', 'Widgets', 'Q3', 13250.00, 126],
		['South', 'Widgets', 'Q4', 14100.00, 134],
		['South', 'Gadgets', 'Q1', 8700.00, 83],
		['South', 'Gadgets', 'Q2', 9400.00, 89],
		['South', 'Gadgets', 'Q3', 10150.00, 96],
		['South', 'Gadgets', 'Q4', 11325.00, 108],
		['East', 'Widgets', 'Q1', 15100.00, 143],
		['East', 'Widgets', 'Q2', 16850.00, 159],
		['East', 'Widgets', 'Q3', 17400.00, 165],
		['East', 'Widgets', 'Q4', 18950.00, 180],
		['East', 'Gadgets', 'Q1', 10725.00, 102],
		['East', 'Gadgets', 'Q2', 12100.00, 114],
		['East', 'Gadgets', 'Q3', 11650.00, 110],
		['East', 'Gadgets', 'Q4', 13575.00, 128],
		['West', 'Widgets', 'Q1', 9300.00, 88],
		['West', 'Widgets', 'Q2', 10450.00, 99],
		['West', 'Widgets', 'Q3', 11225.00, 106],
		['West', 'Widgets', 'Q4', 12750.00, 121],
		['West', 'Gadgets', 'Q1', 7600.00, 73],
		['West', 'Gadgets', 'Q2', 8350.00, 80],
		['West', 'Gadgets', 'Q3', 9100.00, 87],
		['West', 'Gadgets', 'Q4', 9950.00, 95]
	]

	data['crossTabData'] = system.dataset.toDataset(headers, rows)

And a script like this in the script console:

# grouping key -> list of (sort key, "ascend" | "descend")
SORTS = {
    "Quarter": [("total.Sales", "descend")],
    "Product": [("Product", "ascend")],
}

from java.awt import Toolkit
from java.awt.datatransfer import DataFlavor, Transferable, UnsupportedFlavorException
from java.io import ByteArrayInputStream
from java.lang import String as JString
import xml.etree.ElementTree as ET

RM_FLAVOR = DataFlavor("application/reportmill", "ReportMill Shape Data")

class ShapeTransferable(Transferable):
    def __init__(self, xmlString):
        self.bytes = JString(xmlString).getBytes("UTF-8")

    def getTransferDataFlavors(self):
        return [RM_FLAVOR, DataFlavor.stringFlavor]

    def isDataFlavorSupported(self, flavor):
        return flavor in self.getTransferDataFlavors()

    def getTransferData(self, flavor):
        if flavor.equals(RM_FLAVOR):
            return ByteArrayInputStream(self.bytes)
        if flavor.equals(DataFlavor.stringFlavor):
            return JString(self.bytes, "UTF-8")
        raise UnsupportedFlavorException(flavor)

clipboard = Toolkit.getDefaultToolkit().getSystemClipboard()
root = ET.fromstring(clipboard.getData(DataFlavor.stringFlavor).encode("utf-8"))

for grouping in root.iter("grouping"):
    sorts = SORTS.get(grouping.get("key"))
    if sorts is None:
        continue
    for existing in grouping.findall("sort"):
        grouping.remove(existing)
    for key, order in sorts:
        attrs = {"key": key}
        if order == "descend":
            attrs["order"] = "descend"
        ET.SubElement(grouping, "sort", attrs)
    print "Set sorts on grouping %r: %s" % (grouping.get("key"), sorts)

clipboard.setContents(ShapeTransferable(ET.tostring(root)), None)

If you:

1. Create your own crosstab
2. Update the script in the console with appropriate keys and sorting decisions
3. Copy the cross tab element directly in the report designer
4. Execute the code in the script console
5. Paste the updated crosstab back in to the report

You will, in fact, get a "sorted" crosstab:

---

1. or at least not practical ↩︎