Sort Month Name in LOV

building semantic layer
1

Hi Folks,

I am on Teradata database. I know this is an old question but I am in trouble with no solution :nonod:

I have calendar table from where I am taking my Month Prompt LOV. The format is ‘Month Name-Year’ (ex. Jun-2009). Now, i need to sort this in LOV. (Jan-2009, Feb-2009 etc).

I have all date/time objects available in Calendar. I was just wondering what all possible options I have to achieve this.

  • I read about the method by which I can sort LOV by another column and then hide this column. I am not sure how to hide a column in LOV :frowning:
  • I can include another object in LOV (like month number) but that would be displayed in LOV and Users just want to have the prompt displayed as ’ Month Name-Year’
  • I was looking for solutions that do not include derived table (as there is already lot of performance issues in reports).

Is there any way of doing this? I know I don’t have too many options but any suggestion would be helpful.

Smith85 (BOB member since 2009-12-10)

2

Generally speaking, on the object properties tab click Edit then click the SQL button. Enter the SQL like

SELECT [MonthName-Year]
      
  FROM [dbo].[DimDate]
  group by MonthNumberOfYear,MonthName-Year
  order by [MonthNumberOfYear]

Make sure you click the Do not generate SQL before running box.

charlie :us: (BOB member since 2002-08-20)

3

Thanks for your response Charlie.

I have done this and my LOV is working right, I can see sorted records.

I just wanted to confirm, is there any other solution besides this? The customer is not too comfortable for keeping a customized LOV in Universe, but I am not sure of any other solution. :frowning:

Smith85 (BOB member since 2009-12-10)

4

Even if you used physical or derived tables, you would still need a custom sort to get the desired order.

What exactly is their objection?

charlie :us: (BOB member since 2002-08-20)

5

Hi,

To be honest, you don’t have too many options for implementing this without any kind of customization (LOV or Derived table).

Having said that, there is one thing that you can try. There are some databases which allow you to sort your results without a need to include sort objects in Select Clause. This means you can sort your results (Month Name) by Month Number by just keeping Month Name in Select Clause.

If i remember correctly, there is a Parameter - SORT_BY_NO (Don’t think much about the name, it is somewhat confusing :)) which when set to ‘NO’ enables ‘Manage Sorts’ on Designer through which you can select Month Number as sort column. You will find this Parameter in .PRM file. Modify this in the code related to your database and restart Designer.

This works for Oracle and I am pretty sure it will also work for Teradata.

  • Cheers !

Rajat Sapru :us: (BOB member since 2008-08-28)

6

Thats a great tip Rajat, thanks for sharing :).

Mak 1 :uk: (BOB member since 2005-01-06)

7

Really. And of course, Dave blogged it.

charlie :us: (BOB member since 2002-08-20)