Sorting Data in FM

[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

[rockytopmark]

You need month value as int16 also in the query which would be used for sorting only, within the Report Studio.

Why not do Dimensional Modeling and skip the cumbersome cascades?  Single Prompt (Tree) and one selection necessary, instead of Selection, Query, Selection, Query, Selection.

Much more elegant solution, from my experience.  Cascades will work, just trying to offer view of better solution.

[phanicrn]

can we create tree prompts in fm, i dont  know  how to do ..?
Actutally  I developed two models , one for Dimensional modeling which has all regular dimensions and measures. and for details filters.

now this model is used for details filtering, which generally has quersubjects and queryitem. whole idea is user should not create prompt in RS, I though i can do that in fm.


Coming to you question, when i am doing a case calculation on month, it converst month datatype to character in fm. i am using this column as reference and usage in prompt info.. i did try to change datatypes in fm, but didnt work..? :)

[rockytopmark]

Tree Prompts get created in Report Studio, but require a Dimensionally Modeled source.  So in FM you need to create a Regular Dimension of your "Time" table

[phanicrn]

see, i think you didnt understand..

I have package which is dimenasionally modeled. no worried,

I have another model which has query subject & query items.. in this model i am creating predefined value prompts in fm, so that in report studio, user again need not to create prompts for every report.
as i explained.. I have two column , year & month both are numeric columns... i written case statement for month, saying when month =1 then 'jan' .....

in report when select these value prompts..  years shows in order, but month come with aplabectical order..

month should start with jan ,feb... ... that's what  iam looking for..


any questions let me know

thanks

[rockytopmark]

There is a Use value and Display value in the prompt world.  I suggest learning how these are used. 

That said, the use value will still be the numeric month and the displayed value is what the users will see... and in the correct order.  Don't change the data used for filtering, just change what the user sees.

Regarding Tree Prompts in FM... the control is not available in FM, as is no other prompt control available in FM.  You can still create the Filter in FM that pits the user's input against Dimensionally modeled Queries, and when used in RS, will render a Tree Prompt.

Good Luck!

[phanicrn]

i tried back & forth of use & display , nothing worked...

[Feanor]

In addition to the use & display method:

In report studio create a query with [Month name] and [Month number]. Connect the query to the prompt and select [Month name] as display value and [Month number] as use value. Now use the sorting property of the prompt to sort on [Month number]. I think that should work.

[phanicrn]

i think we cant do that, since we created predefined prompts in fm. In RS when we place columns on list and then from package, when do double click on filter, it gets added as details filter, it doesnt show up in prompt page..

the one u said, when we select any toolbox item, and then we can set use & display, for this i have set the settings of Use & display in fm.. but its not working..


i am attaching my model docs, so far what i have done.. thanks

any more ideas..

[rockytopmark]

Weird... the Cognos system generated Prompt is referencing Month_Char which isn't even defined in the Prompt settings in FM, the filter or the report.

[phanicrn]

i just created another calculation for test instance in fm, whether i can convert both into char and see. that didnt work.. for this i have created calculation in fm as monthcar..which converts int to char.. but its for test instance, not to implemented anywhere..

[phanicrn]

I did some calculations and changed datatypes. it got worked, in fm..

thanks lot
phani