sql - "Rolling up" groups in Jaspersoft iReport -

i have profit , loss report has 3 levels of grouping: 1. pharmacy 2. customer 3. packaging type

i've set report prompt user provide values pharmacy, customer, , packaging type. detail band displays measures revenue , margin. selecting default pharmacy, customer, or packaging type returns values in group.

i'm wondering - there way, if user selects "use default" parameter, tell ireport roll grouping? right now, if select pharmacy , customer use default packaging type, still detail band each of several packaging types. how can tell ireport sum on packaging types? or, if no customer specified, sum on customers given pharmacy? or in query? have oracle database , using ireport professional 4.5.1.

thanks, lisa

edited add code:

 select       fac.fill_month fill_mo,       fac.phar_code phar_code,       fac.fac_id fac_id,       fac.packtype_code packtype,       sum(fac.tot_rxcount_num) rx_count,       sum(fac.tot_revenue_amt) revenue,       sum(fac.tot_cogs_amt) cogs,       sum(fac.tot_margin_amt) margin        myschema.table fac        fac.fill_month between $p{startdate} , $p{enddate}       , $x{in, fac.phar_code, pharmacy}       , $x{in, fac.fac_id, facility}       , $x{in, fac.packtype_code, packtype}  group       fac.phar_code,       fac.fac_id,       fac.packtype_code,       fac.fill_month  order       phar_code asc,       fac_id asc,       packtype asc,       fill_mo asc 

you have sql correct raw data want. you'll need modify force packtype (and other fields) group differently in special case user specifies no packtype.

create new parameter $p{packtype_select_sql}. default value directly based on value of existing parameter $p{packtype}. (this means $p{packtype} must appear first in .jrxml.) set $p{packtype_select_sql}'s default value this:

$p{packtype}==null ? " 'all package types' " : " fac.packtype_code " 

then modify sql query (only single line modified, rest context):

select      fac.fill_month fill_mo,      fac.phar_code phar_code,      fac.fac_id fac_id,      $p!{packtype_select_sql} packtype, ...      fac.fill_month between $p{startdate} , $p{enddate}      , $x{in, fac.phar_code, pharmacy}      , $x{in, fac.fac_id, facility}      , $x{in, fac.packtype_code, packtype} 

in case $p{packtype} not null generated sql before. in case $p{packtype} null you'll hard-coded string in place of packtype_code.

presumably report grouping on packtype. should able leave layout , grouping in report unchanged.

you imagine variations adding boolean input control explicitly let user choose whether keep packtypes in report or not. basic idea should need.