how to create report using group by for year/qtr with sum measure

[kbasha2004]

Hi Friends and Admin,

I need your help, I am supposed create a report like this.I amusing cognos 8.2. The Data in table "PROD" is like this ... and the report output should as below at bottom

Table ----  Prod
---------------------------------
Year   Qtr   Prod   QTY   
2003   1   PAA   10   
2003   2   BAA   22   
2003   3   ARR   30   
2003   4   PAA   15   
2003   1   BAA   25   
2003   2   ARR   35   
2003   3   PAA   12   
2003   4   BAA   24   
2003   1   ARR   28   
2003   1   ARR   28   
2003   3   ARR   13   
2003   4   PAA   17   
---------------------------------------


          Report Format/Layout
       
Report       
-----------------------------------------------------      
           Year    Qtr       ARR       BAA      PAA
           2003     1         56          25          10
                      2          35         22           0
                      3          43          0          12
                      4           0         24          32
------------------------------------------------------
2003 Total                   134        71          54
-----------------------------------------------------
Grand Total                 134         71          54
------------------------------------------------------


I need to you help.

Thanks inAdv

Kbasha

[DSR]

Hi

Report       
-----------------------------------------------------     
           Year    Qtr       ARR       BAA      PAA
           2003     1         56          25          10
                      2          35         22           0
                      3          43          0          12
                      4           0         24          32
------------------------------------------------------
2003 Total                   134        71          54
-----------------------------------------------------
Grand Total                 134         71          54
------------------------------------------------------

This Can be achieved by using a cross tab report.

Where you need to drag Rows as Year,Qtr ,Measure=QTY and Column=PROD.

For getting the totals and grand totals you need to select totals for the Each year...which will finally gives you Year totals and Grand totals.

Let us know if you are not able to achieve your goal by using the above mothod.

Regards,
Sanjeev

[kbasha2004]

Hi Sanjeev,

Thanks, it worked.

I have one more question regarding prompts. My situation is ...

I need to display data in the report ,based on the value of the prompt and also Report header needs to reflect the same value of the Prompt.

For example: I have a query item Country:

If COUNTRY = 'US' is chosen ,the US data needs to be displayed and Report header should display ""  US Only  ""

IF other country chosen we need to display Non-US data and heading as NON-US (COUNTRY NOT= 'US').

Please help me in accomplishing the prompt.

Thanks in Adv,


Kbasha

[DSR]

Hi,

By writing a single expression you can achive this....which is

Just you need to create one layout calculation on the header ....and need to drag your parameter in to the layout calculation..

Where you need to write one case or if statement

when param=US
then US only
else Non-US

Good Luck....:-)

Regards,
Sanjeev