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:
- Create your own crosstab
- Update the script in the console with appropriate keys and sorting decisions
- Copy the cross tab element directly in the report designer
- Execute the code in the script console
- Paste the updated crosstab back in to the report
You will, in fact, get a "sorted" crosstab:
or at least not practical ↩︎