COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: Sarayucog on 30 Dec 2011 03:29:17 AM

Title: How to apply the sort on the last month
Post by: Sarayucog on 30 Dec 2011 03:29:17 AM
Hi All,

I have a cross tab report with Measure in columns and Month under the measure in columns and Country in Rows. I have a prompt also with month. I will select some number of months in the prompt. These select months data is displaying in the cross tab. Till now its fine.

Here is my problem. I want to apply sorting on the last month of the selected prompt values.

EX: I have selected from Jan 2011 to May 2011 in the prompt. Here LAST MONTH is MAY 2011. So I have to sort the measures based on the May 2011 month values.

If I select from Aug 2011 to Dec 2011. Here last month is Dec 2011. So I have to sort based on the Dec 2011 values.


Please share your ideas to achieve this task.

Thanks in advance.
Title: Re: How to apply the sort on the last month
Post by: pricter on 30 Dec 2011 04:20:22 AM
Do you use a relational model or a cube?
Title: Re: How to apply the sort on the last month
Post by: Sarayucog on 30 Dec 2011 05:22:48 AM
Thanks for you quick reply,

I am using Relational model.
Title: Re: How to apply the sort on the last month
Post by: blom0344 on 30 Dec 2011 05:49:00 AM
1. Define an additional calculated item - 'sorter' as:

CASE
when
[month] = maximum([month] for report)
THEN
[Somemeasure]
ELSE
(0)
END

2. Associate this one with the crosstab node member (columns) properties
3. Apply advanced sorting on the row node using the sorter in sort list.

I fear this will only work if you use a numerical value for the month in the query, like:  201109  instead of  SEP 2011.

So you may need to add this as well in order for the case to work properly
Title: Re: How to apply the sort on the last month
Post by: Sarayucog on 06 Jan 2012 03:39:40 AM
Blom,

Thanks for your reply and its working for me, but I have a probelm with this logic.

Actually I have 4 measures in the cross tab columns. I have to apply this for only one column and I am using also. I have static prompt to select the top count of countries. Here the promplem is using this logic into my report if I select 10 or 20  or 30 in my top country static prompt some values are missing means its displaying some cells as blank. Can you tell me where the problem.

Your help will more appreciated...
Title: Re: How to apply the sort on the last month
Post by: blom0344 on 10 Jan 2012 01:43:23 AM
Sorry, I really have no clue what you mean.  :o
Title: Re: How to apply the sort on the last month
Post by: Sarayucog on 10 Jan 2012 07:27:49 AM
Hi Blom,

Actually I have 3 prompts in report page. 2 are required and 1 is optional. Optional prompt is STATIC prompt which contains values as 10 and 20. If I select all these 3 prompts some values are missing in my crosstab report (Its displaying blank cells) , but I have data for all the countries and months.

Where to check for this.
Title: Re: How to apply the sort on the last month
Post by: Sarayucog on 10 Jan 2012 07:30:39 AM
Thanks for ur reply.

Sorry I misunderstood your answer. Don't consider the previous reply.