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