Clarity

Dynamic group by at different levels of Aggregation in table dataset in jaspersoft

  • 1.  Dynamic group by at different levels of Aggregation in table dataset in jaspersoft

    Posted 02-16-2017 09:19 AM
    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