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!
this might help:
http://naazvilantakath.blogspot.jp/2013/11/over-partition-by-query-in-cognos.html
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
Also, what is the RegularAggregate property of the item currently set to?
MF.
Hi Cognos810, no, there is no determinant set on it.
MFGF,
The regular aggregate property is set to 'Calculated'
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