business-objectscrystal-reports
So I have a simple crosstab report that calculates number of sales in the columns, and the rows are each office. The last column on the right is a total column, which contains a summary field (via a count of the identity field) that adds up the total number of sales per office.
How can I sort the crosstab data so the office with the most sales is the top row, and the rest in order under it?
Depending on how you're working with it, you can adjust the input to order the data ascending.
SELECT customer, sum(amountdue) AS total FROM invoices GROUP BY customer ORDER BY total ASC
If you're doing in a way that you can't change that information, could you provide a little more insight?
I had this issue not too long ago. Here's what I did:
First, I suppressed the row and column headers (which you'll have to do individually for the "Total" text objects as well as the row/column headers objects themselves) as LittleBobbyTables suggests.
To fix the empty cells, I simply removed the borders. You can do this in the CrossTab Expert by going to the "Customize Style" tab, and then hitting the "Format Grid Lines" button (I'm not sure what versions of CR have this...may be limited to newer versions). Now you can deselect the "Show Gridlines" check box to hide the entire grid. Now there will be no evidence of the row/column headers except for blank white space which you can then overlay with whatever you want.
I found that overlaying a drawing object is unwieldy and usually looks terrible (particularly if your crosstab is going to grow and shrink a lot) and would suggest avoiding this.
Best Answer
Try this: Right-click on the crosstab. Go into the Group Sort Expert menu. There you will see options to display the top N rows, Sort All by the aggregate of a field of your choice, etc.
(I'm using Crystal XI. If your version is different, this may not work exactly)