Sorting by Group Totals / Variable results

[joepmo]

Is there any way to sort within iReports by the results of a variable expression?

 

I have a report that has invoice amounts by customer which I then sum into a variable placed on the report group footer.  I would then like to sort the groups in descending order based on the results of the variable.  See the example below - I would like report to list Customer C first, then Customer A, then Customer B.

For example:

   Invoice 1   2,000

   Invoice 2   3,000

Customer A       $5,000 in total sales (variable SumInvoice)

    Invoice 3   2,000

    Invoice 4   2,000

Customer B        $4,000 in total sales (variable SumInvoice)

    Invoice 5   7,000

    Invoice 6   1,000

Customer C        $8,000 in total sales (variable SumInvoice)

I have searched the forum and documentation and cannot seem to find a solution, though as always I am sure it is somehow right in front of my eyes.

Thanks,

Joe

[lukus]

Hi Joe,

You may be able to achieve this using a crosstab report but this may cut down on the type of formatting you want to achieve.

 

If I were you I would perform this task in the Query before the data set hits the report.  You could insert a corolated sub query in your SELECT statment to get the customer total for each tupple. 

 

eg

SELECT i.customer, i.invoice, i.amount, (SELECT SUM(amount)

FROM invoicetable

WHERE customer = i.customer

GROUP BY customer) AS totalinvoice

FROM invoicetable i

ORDER BY totalinvoice DESC, i.customer

This may be too costly to perform but there are many ways to rewrite the query if you wish

 

Cheers

Lukus

[daltonames2]

Is there a way to sort within iReport by the results of a variable expression for a Domain based report?

I have a problem similar to this link. 

http://jasperforge.org/plugins/espforum/view.php?group_id=83&forumid=101&topicid=48308

 However, I need to use Domains because we have security layer that filters out certain rows.  The problem I have is that we need to perform aggregation and then sort by that results of that aggregation because we then want to feed the top 5 or 10 entries to a chart.  We can’t do it in the query because the query happens before the security layer filters out certain rows.