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.
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.
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.
SELECT i.customer, i.invoice, i.amount, (SELECT SUM(amount)
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
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.
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.