Sorting at universe level

[Former Member]

Hello All,

I would like to do sorting on time peroid objects(year,quarter,month,week)

i can do it at report level using Ascending ,descending,custom sort

but i would like do it at universe level only .

i tried like this:

List of values>>Year>>edit>>here i have set sorting on Year object as descending>>save n close>>then reexported the universe

again tried to query in infoview >> but the data was not sorted in descending order

Please help me in this issue

Regards,

Swetha

[Former Member]

Hi Swetha, when you go to the Properties tab of the "Year" LOV object and click on "Display", are you not seeing the years in descending order?

Another way to force the descending order is to go to the Properties tab, click on Edit, then SQL. Add the sorting at the end of the query with something like "order by year desc" (or whatever field that you want to sort on). Then check the "Do not generate SQL before running". Click "OK" and "Save and Close". This should force your "year" LOV to be listed in descending order.

Hope this helps.

-

---

If you are using the BusinessObjects tool, you should join [ASUG|www.asug.com]

[Former Member]

Hello,

I have created derived table for monthyear object which shows List of values chronologically,

but when i tried to create a report ,its showing alphabetically.any suggestions on this?

[Former Member]

Hello Simon,

I tried in the same way ,it is showing descending order when tried to dispay it,but when exported and checked at report level

its not woking,can you help on this?i tried to create a derived table,which also displays correct data in LOV's but at report level its showing alphabetically ordered months(ex:april,august,december,march...) can you help me on this?

[Former Member]

Hi Ann, it is always helpful to have a dim_time table in your data mart where you have these 2 fields (among others):

month_number	month_full_name
     1          January
     2         February
and so on...

Then you can put something like the following SQL in your derived table called "MONTH"....

select distinct month_number, month_full_name

from dim_time

order by 1

Create a dimension object, says, "month_lov". Go the Properties tab, click "Edit", then "SQL". In the query panel, put in something like this...

select month.month_full_name

from

(select distinct month_number, month_full_name

from dim_time

order by 1) month

Make sure you check "Do not generate SQL before running", then click "OK" and "Save and Close". Then create a condition object to point to this LOV. You should be able to get the months listed in the real order.

Hope this helps.

-

---

If you are using the BusinessObjects tool, you should join [ASUG|www.asug.com]

[Former Member]

Similiar issue..

Dimension = "Date" format is dd-mm-yyyy-hrs-min-sec.

I have crated another dimension;

"Month", as to_char(Date),'Mon-yyyy')

However, still formats as alphanumeric... this is the only "Date" object in table. I've tried changing sql to include an "Order By Desc", changing to_dateas to_char(Date),'Mon-yyyy'),'Mon-yyyy') but get an expression error

???

[amrsalem1983]

what you did is to sort the values in the List of values when you put this dimension into the query filter

but to sort the result values you can not do it from the universe, its a report level task you can do it only from the Infoview

good luck

Amr

[Former Member]

Amr,

Yes,i feel the same because i have even tried to ceate a derived table which is showing the Months arranged properly(jan,feb,mar..) but when exported and checked in webi report its showing alphabetically ordered months..