Problem
Q: In some cases you need to select top ten (five, 20, 25) records for a chart from a very large dataset, and adding a second dataset with a LIMIT 10 condition is not an option.
Solution
A: You can use report variables and filter expressions to filter data for chart.
- You would need your query ordered by some condition, for example (sample foodmart db):
SELECT SUM(customer_sales."store_sales")
AS customer_sales_store_sales, customer_sales."customer_fullname"
AS customer_sales_customer_fullname
FROM "public"."customer_sales" customer_sales
GROUP BY customer_sales."customer_fullname"
ORDER BY customer_sales_store_sales DESC, customer_sales."customer_fullname"
- Add a Chart (this works for both Charts and Charts Pro) with the same dataset as your report.
- Edit Chart Data -> Dataset -> Filter Expression for Charts and Chart Properties -> Chart Data -> Filter Expression for Charts Pro. Add a filter expression, for example:
($V{REPORT_COUNT} > new Integer(10)) ? false : true
Recommended Comments