Custom Sort Order for Months

building reports webintelligence legacy 1/2
1

I would like to know if it possible to control the sort order for an object containing values for month names from the Webi client creating a document (Custom rather then Ascend/Descend). I can easily do this from the BusinessObjects client, but see no way to accomplish this feat from Webi. If not from Webi, can this be done from the Universe (i.e. Designer side) like the Object Format feature?

Thanks Mike

Listserv Archives (BOB member since 2002-06-25)

2

Mike,

Try utilising a month number object in with the query, and sort on this object. You can easily hide it in the report later.

Ang.

Listserv Archives (BOB member since 2002-06-25)

3

Ref: Try utilising a month number object in with the query, and sort on this object. You can easily hide it in the report later.

Thanks for the suggestion, although I would prefer to have another option if possible.
We have several types of users creating documents (free form ad-hoc) and would hate to have to make this a training issue. I have seen more elegant solution for other products such as “sort Column A(month_name) by Column B(month_number)” The goal is to make this as transparent to the end-user as possible.

Mike

Listserv Archives (BOB member since 2002-06-25)

4

Mike.

I can’t find your original posting - but if what you’re trying to do is display month NAMES in CHRONOLOGICAL order as opposed to numeric - try this. I got it from BO support. It works fine.

The way to do this in the universe is to create a MONTH NUMBER object with the following
syntax:

TO_DATE(‘01’ || ‘/’ || SUBSTR(TO_CHAR(column date),4,2) || ‘/’ || ‘1900’ || ’ ’ || ‘00:00:00’)

After this, rightclick on the object - Object Format - Number tab - Properties box(Date/Time) - “Mmm” - OK
( define a new object format. Add the format “Mmm” and select it.) <<

Darren.

Listserv Archives (BOB member since 2002-06-25)

5

TO_DATE(‘01’ || ‘/’ || SUBSTR(TO_CHAR(column date),4,2) || ‘/’ || ‘1900’
|| ’ ’ || ‘00:00:00’)

Or try:

TO_DATE(‘01/’ || TO_CHAR(date,‘MM’) || ‘/1900’)

or perhaps more usefully

TO_DATE(‘01/’ || TO_CHAR(date,‘MM/YYYY’))

Brian Patterson

Listserv Archives (BOB member since 2002-06-25)

6

Just a comment about the multitude of questions regarding customising Month sort orders in Webi reports
Sometimes it is worth asking “owners” how much they value the text “Jan” over the value “1”,
Often when they realise the cost in time and effort spent in elegant solutions to doing this, they prefer that effort to be re-directed to adding true value. It’s amazing how little training people need to associate 9 with September, and with a little help from “metadata”, within no time they have all 12 down pat.

Seriously transitioning Reports to new delivery systems should also be seen as not simply as an exercise of duplicating reports onto new software, but how best to redesign the report (and/or database) to take full advantage of the reporting features of the new tool.

Simon

Listserv Archives (BOB member since 2002-06-25)