Sorting dimension

[RudiHendrix]

In FM I have created a year dimension. This is based on a table with two fields: ID_PK and YEAR.

Everywhere in FM where I look and test I see nice results displaying: 2009, 2010, 2011, 2012 etc.

But as soon as the package is published in Cognos Connection and drag the item in a query in Query Studio the sorting is wrong. It's like 2009, 2010, 2013, 2018, 2025, 2039, 2043, 2014 etc

Where is this going wrong?

[RudiHendrix]

I found the reason what's causing it...however...no solution yet.

In RS and QS the generated SQL is doing a select distinct. If I do a select distinct directly on the database I get the same result. How can I change this?

[SSNCOG]

Hi,

you can set Autogorup & Summary property for Query in RS to "NO".so that you wont get distinct records.

for your previous post:

if you're using only ID_PK  and year query items in the report then,
I think the data may be sorting on ID_PK as its an Identifier.
Apply sorting on year query Item in FM.

if you're using some other query items along with ID_PK  and year,then check whether the sorting property been applied.

Thanks,
SSNCOG

[RudiHendrix]

The time dimension table is filled as follows:
ID_PK     YEAR
1           2009
2           2010
3           2011
4           2012
5           2013

Both the ID_PK and the YEAR field have data type "NUMBER"

In my model I only publish the YEAR field.

Sorting in RS is possible. And off course in QS I can do the same, but this is not something I want users to do.

How can I apply sorting on year query item in FM? (I've tried adding "order by t_dim_year."year"" but it didn't work.)

[RudiHendrix]

Found it! Man! This was easy!

I'd better mention it here...perhaps somebody else can use it.

In Cognos FM 8.4 (I don't remember this from previous versions) on the definition of the hierarchy there is a tab "Members Sort". I really don't know why I didn't use that as the first option!

Just select the hierarchy tick the "Data" tick box and hit "Detect"

[blom0344]

It is the most important reason to use 8.4 Earlier versions do not support the sorting of members this way..

[RudiHendrix]

My reason to go for 8.4 is because the client chose it :) Now I'm happy with it.

[cham]

Hi,

I am facing the same problem with DMR in cognos 8.3. I have time dimension with year, quarter and month. but none of them are sorted. I tried to use the business_key, but no luck yet.

Does anybody has any work around solution for this? Please let me know.

thank you in advance!!!

[RudiHendrix]

Perhaps you can check the data type of the field you would like to sort on and next set it as a business key. If dates are stored as strings sorting can end up in a weird way :(

[blom0344]

Hi,

I am facing the same problem with DMR in cognos 8.3. I have time dimension with year, quarter and month. but none of them are sorted. I tried to use the business_key, but no luck yet.

Does anybody has any work around solution for this? Please let me know.

thank you in advance!!!

Upgrading to 8.4   :-\

Your example is the prime reason why DMR was not mature yet in pre8.4 versions.
Sorting (or really the lack of proper sorting) does not seem to depend on datatypes. Sorting by year integers is a mess as well

[jive]

Hi ,
try to change you definition and add in the select an order by the field you need and pass it Thu native SQL.