Hi, I am using cognos 11.1 with a relational model,
is it possible to use Sql window function like LEAD,LAG... inside reporting without using sql objects with native or pass-thru sql sintax?

Using running-total or rank present in the list of "Summaries", I see that cognos is using windows functions in the sql generated by the report.
The list of "Summaries" functions, i think is the same since cognos 8...

Request for IBM for possible improvement of the reporting: introduce in the summaries the  missing functions that generates windows functions in sql like lead ,lag...

Thanks




------------------------------
Magnum BI
------------------------------

#CognosAnalyticswithWatson

Hi,
I've seen a report lately, that was based on a data module which used multidimensional functions like prevMember() on relational data. But I have to admit that I've never tried it out by myself.
Is your data source a DM or FM model?

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
------------------------------

FM PACKAGE( ONLY RELATIONAL, DQM)...i can't see how to use LEAD and LAG.

I would like that this  useful windows functions  could be called directly using cognos summary functions without using for example dmr or dimensional .

------------------------------
Magnum BI
------------------------------

Original Message:
Sent: Tue July 13, 2021 05:38 AM
From: Robert Dostal
Subject: Sql window functions (lag,lead...) with relational package dqm

Hi,
I've seen a report lately, that was based on a data module which used multidimensional functions like prevMember() on relational data. But I have to admit that I've never tried it out by myself.
Is your data source a DM or FM model?

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
------------------------------

In this case I have no other solution than using data items with native or pass-thru sql syntax.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
------------------------------

Original Message:
Sent: Tue July 13, 2021 07:16 AM
From: Magnum BI
Subject: Sql window functions (lag,lead...) with relational package dqm

FM PACKAGE( ONLY RELATIONAL, DQM)...i can't see how to use LEAD and LAG.

I would like that this  useful windows functions  could be called directly using cognos summary functions without using for example dmr or dimensional .

------------------------------
Magnum BI
------------------------------


Original Message:
Sent: Tue July 13, 2021 05:38 AM
From: Robert Dostal
Subject: Sql window functions (lag,lead...) with relational package dqm

Hi,
I've seen a report lately, that was based on a data module which used multidimensional functions like prevMember() on relational data. But I have to admit that I've never tried it out by myself.
Is your data source a DM or FM model?

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

HI community, please support and vote this idea.
I would like to have all the powerful sql window functions available inside reporting like lead and lag. 
Actually as of today, there is only sum and rank i think as possibilities.
A need that could save us from developing dmr models and going to use dmr and cubes for having these particular functions.

now day every db from db2 to ntz oracle sql server... support windows functions like lead, lag, firstvalue, rownumber etc.

So,IBM please give us these and make me happy!

https://ibm-data-and-ai.ideas.aha.io/ideas/CAOP-I-407

Thanks

------------------------------
Magnum BI
------------------------------

Original Message:
Sent: Tue July 13, 2021 07:34 AM
From: Robert Dostal
Subject: Sql window functions (lag,lead...) with relational package dqm

In this case I have no other solution than using data items with native or pass-thru sql syntax.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
------------------------------


Original Message:
Sent: Tue July 13, 2021 07:16 AM
From: Magnum BI
Subject: Sql window functions (lag,lead...) with relational package dqm

FM PACKAGE( ONLY RELATIONAL, DQM)...i can't see how to use LEAD and LAG.

I would like that this  useful windows functions  could be called directly using cognos summary functions without using for example dmr or dimensional .

------------------------------
Magnum BI


Original Message:
Sent: Tue July 13, 2021 05:38 AM
From: Robert Dostal
Subject: Sql window functions (lag,lead...) with relational package dqm

Hi,
I've seen a report lately, that was based on a data module which used multidimensional functions like prevMember() on relational data. But I have to admit that I've never tried it out by myself.
Is your data source a DM or FM model?

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

This is a good idea. Tipp (as robert writes): A few of these functions already exist.

On a relational data module members can be addressed e.g.
[time].[time].[years]->[2021]

And also the functions nextMember and prevMember work:
prevMember([time].[time].[years]->[2021])

Result: 2020

------------------------------
Jens Bäumler
Senior Consultant and Trainer
Apparo Group
------------------------------

Original Message:
Sent: Sat July 17, 2021 11:08 AM
From: Magnum BI
Subject: Sql window functions (lag,lead...) with relational package dqm

HI community, please support and vote this idea.
I would like to have all the powerful sql window functions available inside reporting like lead and lag. 
Actually as of today, there is only sum and rank i think as possibilities.
A need that could save us from developing dmr models and going to use dmr and cubes for having these particular functions.

now day every db from db2 to ntz oracle sql server... support windows functions like lead, lag, firstvalue, rownumber etc.

So,IBM please give us these and make me happy!

https://ibm-data-and-ai.ideas.aha.io/ideas/CAOP-I-407

Thanks

------------------------------
Magnum BI
------------------------------


Original Message:
Sent: Tue July 13, 2021 07:34 AM
From: Robert Dostal
Subject: Sql window functions (lag,lead...) with relational package dqm

In this case I have no other solution than using data items with native or pass-thru sql syntax.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen


Original Message:
Sent: Tue July 13, 2021 07:16 AM
From: Magnum BI
Subject: Sql window functions (lag,lead...) with relational package dqm

FM PACKAGE( ONLY RELATIONAL, DQM)...i can't see how to use LEAD and LAG.

I would like that this  useful windows functions  could be called directly using cognos summary functions without using for example dmr or dimensional .

------------------------------
Magnum BI


Original Message:
Sent: Tue July 13, 2021 05:38 AM
From: Robert Dostal
Subject: Sql window functions (lag,lead...) with relational package dqm

Hi,
I've seen a report lately, that was based on a data module which used multidimensional functions like prevMember() on relational data. But I have to admit that I've never tried it out by myself.
Is your data source a DM or FM model?

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

Hi Jens, thanks for this tip, i hope that ibm will renew and expand this part of reporting functions and make it more clear when they are available e.g. your tip i wasn't aware of this possibility with datamodule.

I hope also that a new kinda of dimensional layer like DMR will be feasible also with data modules.

Hoping ...

------------------------------
Magnum BI
------------------------------

Original Message:
Sent: Mon July 19, 2021 02:43 AM
From: Jens Bäumler
Subject: Sql window functions (lag,lead...) with relational package dqm

This is a good idea. Tipp (as robert writes): A few of these functions already exist.

On a relational data module members can be addressed e.g.
[time].[time].[years]->[2021]

And also the functions nextMember and prevMember work:
prevMember([time].[time].[years]->[2021])

Result: 2020

------------------------------
Jens Bäumler
Senior Consultant and Trainer
Apparo Group
------------------------------


Original Message:
Sent: Sat July 17, 2021 11:08 AM
From: Magnum BI
Subject: Sql window functions (lag,lead...) with relational package dqm

HI community, please support and vote this idea.
I would like to have all the powerful sql window functions available inside reporting like lead and lag. 
Actually as of today, there is only sum and rank i think as possibilities.
A need that could save us from developing dmr models and going to use dmr and cubes for having these particular functions.

now day every db from db2 to ntz oracle sql server... support windows functions like lead, lag, firstvalue, rownumber etc.

So,IBM please give us these and make me happy!

https://ibm-data-and-ai.ideas.aha.io/ideas/CAOP-I-407

Thanks

------------------------------
Magnum BI


Original Message:
Sent: Tue July 13, 2021 07:34 AM
From: Robert Dostal
Subject: Sql window functions (lag,lead...) with relational package dqm

In this case I have no other solution than using data items with native or pass-thru sql syntax.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen


Original Message:
Sent: Tue July 13, 2021 07:16 AM
From: Magnum BI
Subject: Sql window functions (lag,lead...) with relational package dqm

FM PACKAGE( ONLY RELATIONAL, DQM)...i can't see how to use LEAD and LAG.

I would like that this  useful windows functions  could be called directly using cognos summary functions without using for example dmr or dimensional .

------------------------------
Magnum BI


Original Message:
Sent: Tue July 13, 2021 05:38 AM
From: Robert Dostal
Subject: Sql window functions (lag,lead...) with relational package dqm

Hi,
I've seen a report lately, that was based on a data module which used multidimensional functions like prevMember() on relational data. But I have to admit that I've never tried it out by myself.
Is your data source a DM or FM model?

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen