How to apply the sort on the last month

[Sarayucog]

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.

[pricter]

Do you use a relational model or a cube?

[Sarayucog]

Thanks for you quick reply,

I am using Relational model.

[blom0344]

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

[Sarayucog]

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...

[blom0344]

Sorry, I really have no clue what you mean.  :o

[Sarayucog]

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.

[Sarayucog]

Thanks for ur reply.

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