hello to everybody!
first of all sorry for my bad english...
I am working with Cognos 8.3 and I modelled a DMR; in my reporting activity I need often to recover informations in the same report such as billing this year, billing previuos year, stock YtD, etc... avoiding multy queries to improve performance
Well, Cognos8 specialist maked me sure that Dimensional Functions (such us nextMember, previuosMember, periodsToDate, etc...) work good as well in DMR that in Cubes, but I don't find still the way for them!!!!!!
Really, my time hierarchy is totally un-sorted in the tree-hierarchy!
I wanna give u more details, and i hope that samebody can help me...really! I am going to be mad.
my data sorce is oracle and my table TIME_TIME is this (same rows)
id_day cd_language date_day month_year month_code quarter_year quarter_code year
(number) (varchar) (date) (varchar) (varchar) (varchar) (varchar) (varchar)
1 EN 01-01-2006 2006-January 200601 2006-01 200601 2006
2 EN 02-01-2006 2006-January 200601 2006-01 200601 2006
.. .. ............. ............................................................................
1 IT 01-01-2006 2006-Gennaio 200601 2006-01 200601 2006
2 IT 02-01-2006 2006-Gennaio 200601 2006-01 200601 2006
.. .. ............. ............................................................................
then:
1) I forced sorting in Oracle (in fact my table is correctly sorted for cd_language and id_day
2) I imported this table in FM
3) I defined a filter for multilanguage (and it works good)
4) I defined determinants in this way:
key attributes
year group by
quarter_year quarter_code group by
month_year month_code group by
date_day (all) unique
5) Notice that when I run query example in FM this is still order!!!!!!! (I don't know if it's important)
6) I defined my regular dimension:
YEAR year business_key, member_caption
QUARTER quarter_year business_key, member_caption quarter_code attribute
MONTH month_year business_key, member_caption month_code attribute
DAY to_char(date_day….) business_key, member_caption
and i flag unique level for all (as is correct)
....well!!!... the level DAY is still sorted but THE OTHER LEVEL ARE NOT!!!!!! and this problem persists and maintains in report studio:
this is the result for example in the hierarchy:
2006
200601
200601
2006-01-01
2006-01-02
2006-01-03
...............
200603
2006-03-01
2006-03-02
2006-03-03
...............
200602
2006-02-01
2006-02-02
2006-02-03
...............
so when i run dimensional function (for example PreviousMember(200602) it gives me 200603!!!!!)
can samebody help me???????
I hope my explaination is quite good :-[
if u need more details to have a cleaner situation don't hesitate to contact me!!!!
please!
Perhaps this will shine some light on your issue:
(known issues for 8.2 from the Knowledge Base):
Quote
Cannot Specify the Order of Members When Defining a Level
In the current release, you cannot specify the order of the members when defining a level for a regular dimension on a relational data source. This limitation results in the following functions returning members who are dependent on the sort order in which the data was retrieved:
*
firstFromSet
*
remainderSet
*
head
*
item
*
lag
*
lead
*
nextMember
*
prevMember
*
lastPeriods
*
closingPeriod
*
openingPeriod
*
parallelPeriod
*
periodsToDate
*
subset
*
cousin
*
firstChild
*
firstSibling
*
lastChild
*
lastSibling
We recommend that you do not use these functions when using dimensionally modeled relational data sources. To ensure that report authors do not use these functions, use Quality of Service to exclude them from the functions list.
491332
You mention that data is correctly sorted in the Oracle table. That is a known misconception. Databases do not care much about sorted storage. The query that extracts the data is responsible for the correct order by..
THANKS VERY MUCH... :)
but I called Cognos Support in London and they said me that this functions work in Cognos 8.3!!!!!
what do u think?
thanks again...
We're currently looking into DMR for our production 8.1 clients and sure hope that 8.3 offers better functionality..
Did you try and enforce proper sorting through the relational source (like adding a query subject that involves the correct order by , instead of just reading the table in a random way?
if I import my table from the data-source by this query: select * from cognos_connection.table_name order by column_name asc nothing's happened....in query explenation the "order by" disappear!
really i don't know... I tried with a view too....nothing
Change the SQL settings under options to native and add the order by. You will need to explicitly name the correct database/schema for this to work as it bypasses Cognos. The order by is used as FM uses a named table expression :
with
New_Table as
(tt....{ Select * from db.dbo.table order by 1})
thanks blom0344,
I never tried this....but how can I change SQL native add the order by?
Excuse me ....my knowladge about Cognos is not so big
If you edit the query subject, then there is a tab test. In the right lower corner is an options link where you can tweak some settings (among others the SQL generated)
Obviously, this has only some use with SQL objects, not with model query subjects..
blom0344,
I did: nothing:
select id_day, id_month, id_quarter, id_year
from table_time
order by 1
is sorted but for example:
select id_month from (
select id_day, id_month, id_quarter, id_year
from table_time
order by 1
)
group by id_month
is not....
what can I try still?!??!?!!?? ???
Gio -
8.4 has added capability to set an item to use for the order for the members of the Levels of a Hierarchy. In 8.3 and back, you will have to do the ordering at the report level.