Need to group similar columns in a report

[lifzgud]

Hi,
this is my first try at reporting!!
I need to create a report in cognos
In list format it looks like this
Division_no   Region_no  area_no  transaction_key sales_units item_desc
1                   1             1                123              2               A
1                   1             1                123              2               B                                       

I need to create it like this
Division_no   Region_no  area_no  transaction_key sales_units item_desc
1                   1             1                123              2               A
                                                                                        B

Thats is i have to group the similar values in one cell and split the item_desc in two cells.The simliar values will be in one cell

Could nebody tell me the native sql i should write so that my preview is like this.
I dnt have access to framework manager so i dnt have idea of how to set the measures n all
PLz help

[hpmkruit]

Assuming your using Report Studio for this:

Select the list column item_desc, then select Group/Ungroup from the Structure menu. This will group the data per item_desc value.

Hope this helps.

[lifzgud]

Thanks!!

[lifzgud]

Hi ,
there seems to be new problem now..
If i have division_number as 3 and Region_number as 1 it will group the same Region_number in the same cell but this 1 should be in a separate cell corresponding to divsion number 3 ???

[lifzgud]

Division_no   Region_no  area_no  transaction_key sales_units item_desc
1                   1             1                123              2               A
1                   1             1                123              2               B           
2                   1             1                123              2               B   
Ideally it should be 
1                   1             1                123              2               A
                                                                                        B
2                   1             1                123              2               B

But its showing
1                   1             1                123              2               A
                                                                                        B
2

[hpmkruit]

Yes because you're grouping on item_desc. Report Studio only makes another group if item_desc is a new one. Try to work out on wich column you want to group your data.

[lifzgud]

I'm sorry
what i did was i selected all the columns apart frpm item_desc and did a group on all of them
So what should be the right approach?

[hpmkruit]

I think first of all you need to get a better understanding of the grouping concept. If you create a group, every other row with the same grouping attributes will be placed in that group.

So if you group on Division_no alone, every row with Division_no = 1 will be placed in group 1. Every row with Division_no = 2 will be placed in group 2 etc.

If you group on every column except item_desc, you will create lot's of small groups because of the numerous combinations of grouping attributes of those 5 columns. I don't think that is what you want.

Think like this:

If you want to see all data per division, group on division.
If you want to see all data per region, group on region.
If you want to see all data per division and region combination, group on division and region.

[lifzgud]

Ok...
Now what i did was that i gouped on division then region then are and so on like a hierarchy till  item_desc ..getting the correct results now
Thnx
There is also a date column and i need to show the records only for the past week
How do i do this?

[lifzgud]

I am trying to use a filter..but its showing some error..i think it has to do with the format of the date tht i am giving..
Its a complicated case
For eg if i want run the report on a monday or any other weekday it should show me the data from the last sunday  to the last saturday
eg if i run the report on 14 sept 2009 it should show me data from 6th to 12th sept
Any ideas on this?