Hi Team,
I have a Problem where based on the selection of metrics the Values has to be aggregated at different levels like Country, state and city in Jasper report. How can i achieve this. Is this to modify in the SQL query or any implementation in the Jasper Studio. Help is most appreciated. Please find below query used in the report.
select a.store_country,a.store_state,a.store_city, sum(b.Sales) as Sales,sum(b.Revenue) as Revenue ,
sum(b.Profit) as Profit
from schema.Stores a join schema.Sales b
on a.store_id=b.store_id
where $X{IN,a.store_country,Country} and $X{IN,a.store_state,State} and $X{IN,a.store_city,City}
group by a.store_country,a.store_state,a.store_city
Ex: If i select any country like USA in the Country parameter and metrics like Profit the entire Profit has to be aggregated for the Country USA and displayed in a single Row. If i select County and two states with respect to that Country, the results has to be split into 2 rows and the aggregated values has to be displayed for the same country and two states in two rows and Vice versa with the Cities.
How to achieve above results. Please let me know, if further information required...
Thanks,
Ayyaz