powerbuilder , a complicated crosstab datawindow

the query is ready. As i saw a crosstab datawindow has a grid layout. how can me make each detail to consist of two lines f.e . is it possible for a grid?

it means... u want group and sub-group wise total of each part number....
or if u provide  the query it will be help to me...

the query is quite simple.

select
   ma1_part_num,
   ma1_descr,
   ma1_group,
   ma1_subgroup,
   ma1_sales_date,
   ma1_sales_qty,
   ma1_sales_val
from
   fmapo1
where
   month(ma1_sales_date) between :from_date and :to_date
order by
   ma1_group, ma1_subgroup, ma1_part_num

control (group) headers
===================
header 1  ma1_group
header 2  ma1_group, ma1_subgroup
header 3  ma1_part_num

select crosstab datawindow ....
2. paste your query
3. define your rows and coulumm
      column ---- date as like month
      rows  ------       1 . ma1_group
                  2 . ma1_subgroup
                  3. ma1_part_num      
      values             qty
                  amount


try this

i did all of it. the main issue is that the result is contained in one line only per part number. you may see in the attached file that each detail consists of one line only. (since we have agrid)

can u send me the sample data with exact your need to output...

i believe that we cant do it since crosstab datawindow is a grid. we cannot spit a detail into two lines

u can use composite data window Type

try this...

select
   ma1_part_num,
   to_char(ma1_part_num),
   ma1_group,
   ma1_subgroup,
   ma1_sales_date,
   ma1_sales_qty
from
   fmapo1
where
   month(ma1_sales_date) between :from_date and :to_date
union all
select
   ma1_part_num,
   ma1_descr,
   ma1_group,
   ma1_subgroup,
   ma1_sales_date,
   ma1_sales_val
from
   fmapo1
where
   month(ma1_sales_date) between :from_date and :to_date
order by
   ma1_group, ma1_subgroup, ma1_part_num;

Now use below in your cross tab:

rows:
1. ma1_part_num (first column in select statement)
2. ma1_part_num (second column in select statement)

columns:
month(ma1_sales_date)

values:
sum(ma1_sales_qty for crosstab)

And done, i tried with a table from sample datatabe with PowerBuilder and i got it. Let me know if you need any help. Also note that to_char function is from oracle if you are using any other database you may need to use proper function to convert numeric to character.

Regards,
Sandeep

thanks sandeep. this approach has one disadvantage - i will receive only one sum for ma1_sales_val, ma1_sales_qty which is not correct. i need two sums in every control break.

thanks sandeep. it seems it will be an excellent approach. i will let you know as soon as possible