Help on SQL expressions in IDT

[Former Member]

Hello experts,

I have relational connection type universe connected to SAP BW multiprovider.

I have 2 questions...

1. I want to be able to derive the cumulative total by fiscal year period. In WebI, the function is runningsum. What expression can I use in IDT business layer to calculate runningsum.

Example..

2. In WebI, there is a function sum[amount] ForAll ([Fiscal Yr Period]). How can I acheive the same function in IDT's business layer SQL expression?

Example..desired result is below highlighted in yellow colum

Appreciate any help or guidance.

Just to clarify, I need to keep the calculations in the universe and not in WebI report.

Thanks

Ann

[Former Member]

Anybody?

[Former Member]

Hi Ann,

below url is helpful check it once

http://blog.davidg.com.au/2011/04/moving-average-in-webi.html

[Former Member]

Not in WebI. I am looking for a solution at the universe level (either business layer or data foundation)??

Thanks

Ann

[Former Member]

Ann,

To be perfectly honest, I would advise against it unless you have a really specific requirement which you cannot do in Webi. Cumulatives like running sum are better achieved in Webi because it offers more flexibility. You build a universe to provide the flexibility of adhoc analysis - try and visualise in SQL terms what you want to do and then consider that this can be mixed and matched with other dimensions and it throws the granularity.

What benefits do you perceive from doing it at the IDT level? Is it simply because the end users are struggling with the context operators?