If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Sorting in Framework Manager

Started by phanicrn, 06 Nov 2007 11:18:41 AM

Previous topic - Next topic

phanicrn

Hi guys
I want perform sorting in framework manager on time dimension, for that i have selected pass through sql and changed my sql

select * from time_dimension where year is not null order by year, month, day.

this sql puts me by year, month, day.

data for these columns resides in database as

Year-2005 as int16
month--11 as int 16.
day -- jannuary-21-2007,12:00:00( for date i have changed properties.) character dtype
I am doing calculation for month with case statement

ex: case when month=1' then 'Jan'  end, this datatype is character.

Now i am creating a predefined filters in fm for use of report studio.

I have created Year, Month, Day Month will cacased on year, day wil cascase on month.these are all value prompts..

in report Studio. when i select year from the value box, year is orders,
Month prompt shows data as "January"  when i select month prompt, it puts the order in character like 'Aug', 'Apr'  but i want the month order to be show as "Jan"  and precedding..

even if i do the cast function by converting month to char, it does work.

What should i do in fm , so that month get sorted  in report studio showing as " January","Febuary" and ....

thanks
phani.p

miwis

Hi phani,
It's hard to make it in FM (without concatenating- i.e. 1-Jan).
Try this in RS:
1. Add a query item: month (1,2,...,12) to month prompt query - this will be a sort key.
2. Go to prompt page and select month prompt.
3. In properties pane click Sorting and add month to Sort List. Default sort is ascending.
4. Run the report ;)

Hope this helps a little...
Or describe more about your problem

Regards
Michal
--
Michał Wiśniewski
ACCRE, Cognos BI Consultant