No option to apply customized sorting in BIP

Though we write a order by clause in the query, the pivot table gets sorted on first column by default. When we write a customized sort column, we are forced to include the custom column in the report output and there is no option to hide the column used for sorting, which results in having an additional unnecessary column in he pivot output. Also we have a restriction of 5 rows in the pivot, so we are not able to include the sort column.

What is the best way to have custom sorting in BI Publisher reports developed using a layout editor?