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

Title: how to create report using group by for year/qtr with sum measure
Post by: kbasha2004 on 20 Oct 2009 08:42:10 PM

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

Title: Re: how to create report using group by for year/qtr with sum measure
Post by: DSR on 21 Oct 2009 01:14:18 AM

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

Title: Re: how to create report using group by for year/qtr with sum measure
Post by: kbasha2004 on 21 Oct 2009 08:56:22 AM

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

Title: Re: how to create report using group by for year/qtr with sum measure
Post by: DSR on 28 Oct 2009 06:49:39 AM

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

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: kbasha2004 on 20 Oct 2009 08:42:10 PM