Attribute vs Fact in FM. Interesting problem.

[intrepid402]

Hi'yall

So there is a query item in FM which is an Amount and I know for a fact to is a Fact  :P

However, when I use is it a fact in usage properities, the sql is generated as

SELECT
    "T0"."C0"              "IDnumber" ,
    "T0"."C1"              "Date" ,
    sum ("T0"."C2")  over ( ) "Amount"

As a result, the amounts are all being added up and shows the same value for all the rows.

However, if I change the usage property to Attribute instead of Fact, the result generated is correct. SQL for Amount as Attribute

SELECT distinct
    "a"."acct_num"         "IDnumber" ,
    "a"."exp_dte" "Date" ,
    "T2"."amt"          "c3"

Is there is any way to stop Cognos from using sum over ( ) clause? If I remove the over ( ) from sql and have it as sum (amount), the result is correct. However, I am unsure as to how to stop Cognos from using over () function.  :o

And the amount field is just straight forward from a table. There are no calculations involved in this query item.
Also, I played around with aggregate properties to no avail.

Thanks for your help!

[Francis aka khayman]

this might help:

http://naazvilantakath.blogspot.jp/2013/11/over-partition-by-query-in-cognos.html

[cognos810]

Hello Interpid402,
Can you check to see if the query subject that you are using has a determinant defined on it? It could be on the model query subject that you use or most-likely in the Data Source query subject in the Database/Import view namespace within Framework Manager.

-Cognos810

[MFGF]

Also, what is the RegularAggregate property of the item currently set to?

MF.

[intrepid402]

Hi Cognos810, no, there is no determinant set on it.

MFGF,
The regular aggregate property is set to 'Calculated'

[CharlesAZ]

Curious why it is set to Calculated, is it a derived field?  If it is not, wouldn't want that set to Sum?

Thanks

Charlie