Hi All, 

I am writing a report in which I want to use oracle's linear regression functions. I know I can use the AGGREGATE: trick to get the report to pass these functions through as summary calculations. But is the a method of specifying the scope of the function?  

For example, in this report:

I can get the total rev for prod. type using the expression total( [Revenue] for [Product Type] ). The FOR clause lets you specify the level. I want to do something similar with a regr function.

I cannot use a regr function directly in Cognos, but I can if i prefix with aggregate. For example: aggregate:REGR_SLOPE( [Revenue], [Year] ) 

However I want to perform this function at the product type level: aggregate:REGR_SLOPE( [Revenue], [Year]  for [Product Type]) 

The for clause doesn't work with the aggregate: trick. I have tried the Oracle syntax of OVER/Partition etc and that fails to.

Anyone know how to do this?

Thanks/

------------------------------
Marc Reed
------------------------------

Hi Marc,

have you tried to bypass pure SQL code into the generated code by using {}? Everything between { and } gets send 1:1 to the database and is not interpreted by Cognos. I use this trick to sqeeze a subselect into where-clauses like.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ Gebr. Müller Apparatebau GmbH & Co. KG
Ingelfingen
------------------------------

Hi Robert.  I tried the old {}  trick before remembering aggregate:

In the most simple form of the function { REGR_SLOPE( [Revenue], [Year] )  } fails with ORA-00936: missing expression, whilst aggregate:REGR_SLOPE( [Revenue], [Year] ) works. So before I even try and apply a level for the calc it fails with {}.
I've also tried every combinations of aggregate, {}, for OVER (PARTITION BY xxxx ) just in case there is some other undocumented trick :)

------------------------------
Marc Reed
------------------------------

Original Message:
Sent: Wed March 27, 2024 08:59 AM
From: Robert Dostal
Subject: Aggregate: and Linear Regression Functions

Hi Marc,

have you tried to bypass pure SQL code into the generated code by using {}? Everything between { and } gets send 1:1 to the database and is not interpreted by Cognos. I use this trick to sqeeze a subselect into where-clauses like.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ Gebr. Müller Apparatebau GmbH & Co. KG
Ingelfingen
------------------------------

Hi Marc,

Did you try double braces {{ <code> }} rather than single braces? If memory serves, single braces denote Native SQL and double braces denote Pass-Thru SQL. There might still be some query engine interference using Native SQL? 

Just a thought.

MF.

------------------------------
Mark Fry
Technical Consultant
------------------------------

Original Message:
Sent: Wed March 27, 2024 09:47 AM
From: Marc Reed
Subject: Aggregate: and Linear Regression Functions

Hi Robert.  I tried the old {}  trick before remembering aggregate:

In the most simple form of the function { REGR_SLOPE( [Revenue], [Year] )  } fails with ORA-00936: missing expression, whilst aggregate:REGR_SLOPE( [Revenue], [Year] ) works. So before I even try and apply a level for the calc it fails with {}.
I've also tried every combinations of aggregate, {}, for OVER (PARTITION BY xxxx ) just in case there is some other undocumented trick :)

------------------------------
Marc Reed
------------------------------


Original Message:
Sent: Wed March 27, 2024 08:59 AM
From: Robert Dostal
Subject: Aggregate: and Linear Regression Functions

Hi Marc,

have you tried to bypass pure SQL code into the generated code by using {}? Everything between { and } gets send 1:1 to the database and is not interpreted by Cognos. I use this trick to sqeeze a subselect into where-clauses like.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ Gebr. Müller Apparatebau GmbH & Co. KG
Ingelfingen

ISO-SQL defines functions which may not include a window specification. 

Dynamic Query provides both a built-in implementation of the ISO-SQL regression aggregates (non-Windowed), it will push them to vendor's whose product/version supports them re the equivalent form.

Regarding another facet of your question, refer to the docs re construct that has been supported in an expression for many releases.
Using scalar and aggregate functions - IBM Documentation

------------------------------
NIGEL CAMPBELL
------------------------------

@NIGEL CAMPBELL Thanks for the link. But I have run out of talent in trying to get CA to use Oracles Linear Regression Functions. 

For example, let's say I have a query with [Product Line], [Month] and [Sales].

In Oracle I would write something like REGR_SLOPE( MONTH, SALES) OVER (PARTITION BY PRODUCT_LINE).

In CA I have tried:

aggregate:REGR_SLOPE( [MONTH], [SALES] ) OVER (PARTITION BY [Product Line]).

This fails with an oracle error. ORA-00909: invalid number of arguments. Yet, I know I am supplying the correct arguments, so I must have the CA syntax wrong.

aggregate:REGR_SLOPE( [MONTH], [SALES] ) OVER ( [Product Line]).(as per doc)

fails with invalid token Product Line.

What syntax would I use in CA to reproduce what I can do in Oracle?

Just to test aggregate functionality, I tried a simple

aggregate: sum( [sales]) over( partition by [Product Line] )

and that works. So there must be something about those more complicated REGR functions.

------------------------------
Marc Reed
------------------------------

Original Message:
Sent: Wed March 27, 2024 06:29 PM
From: NIGEL CAMPBELL
Subject: Aggregate: and Linear Regression Functions

ISO-SQL defines functions which may not include a window specification. 

Dynamic Query provides both a built-in implementation of the ISO-SQL regression aggregates (non-Windowed), it will push them to vendor's whose product/version supports them re the equivalent form.

Regarding another facet of your question, refer to the docs re construct that has been supported in an expression for many releases.
Using scalar and aggregate functions - IBM Documentation

------------------------------
NIGEL CAMPBELL
------------------------------

Hi Marc,

you cannot use Cognos data items between { }. All elements have to be named like in the native tables like you wold have written manually in SQL.

I've never used these kind of Oracle functions but your expression { REGR_SLOPE( [Revenue], [Year] )  } should be translated into { REGR_SLOPE( Revenue) OVER (PARTITION BY Year )  }. Maybe the column names have to be in FQDN too. Have you tried that?

------------------------------
Robert Dostal
Team Leader BI
GEMÜ Gebr. Müller Apparatebau GmbH & Co. KG
Ingelfingen
------------------------------

Original Message:
Sent: Thu March 28, 2024 03:27 AM
From: Marc Reed
Subject: Aggregate: and Linear Regression Functions

@NIGEL CAMPBELL Thanks for the link. But I have run out of talent in trying to get CA to use Oracles Linear Regression Functions. 

For example, let's say I have a query with [Product Line], [Month] and [Sales].

In Oracle I would write something like REGR_SLOPE( MONTH, SALES) OVER (PARTITION BY PRODUCT_LINE).

In CA I have tried:

aggregate:REGR_SLOPE( [MONTH], [SALES] ) OVER (PARTITION BY [Product Line]).

This fails with an oracle error. ORA-00909: invalid number of arguments. Yet, I know I am supplying the correct arguments, so I must have the CA syntax wrong.

aggregate:REGR_SLOPE( [MONTH], [SALES] ) OVER ( [Product Line]).(as per doc)

fails with invalid token Product Line.

What syntax would I use in CA to reproduce what I can do in Oracle?

------------------------------
Marc Reed
------------------------------


Original Message:
Sent: Wed March 27, 2024 06:29 PM
From: NIGEL CAMPBELL
Subject: Aggregate: and Linear Regression Functions

ISO-SQL defines functions which may not include a window specification. 

Dynamic Query provides both a built-in implementation of the ISO-SQL regression aggregates (non-Windowed), it will push them to vendor's whose product/version supports them re the equivalent form.

Regarding another facet of your question, refer to the docs re construct that has been supported in an expression for many releases.
Using scalar and aggregate functions - IBM Documentation

------------------------------
NIGEL CAMPBELL

Thought I would update this in case anyone comes across this in the future...

It looks like these particular sql functions (regr_slope, regr_line etc) require a GROUP BY statement in the SQL if they use a ORDER/PARTITION BY. With no group by statement the functions will not work. And this is the crux of the issue. Despite trying a few different things to trick Cognos into adding a Group By statement into the SQL it was too clever for me.  

I did come up with a work around, not a very efficient work around but something is better than nothing. 

The final query looks like this:

Base data is the data I am using. I put this into the Slope query to work out the regr functions to draw a straight line. I can then use these in the Straight line query to do a line of best fit. With the straight line and base data I can then do the Rsquare analysis. Finally I need to show all the bits together so have to rejoin it all to itself to get all the bits in the crosstab query.

A workaround!

------------------------------
Marc Reed
------------------------------