Impromptu and SQL server - Running Total / Moving Sum

[drlex]

From my limited knowledge of SQL server, I understand there's no running total or moving sum function available in SQL server.

I have an impromptu report that includes a field that refers to previous row value. The report runs against a SQL server DB and the output is saved as an excel file which is then used as a source file for loading back into SQL server. I'd like to perform the entire operation in SQL server, so as to avoid any timing issues.

If the report is saved as SQL, no running total function is shown; when saved as an IQD, shows an XMOVING SUM function being used to determine a value from the previous row

If I attempt to cut and paste the SQL from the IQD into a SQL server transformation which uses the same System DSN ODBC connection as impromptu, I get an unspecific error message.

I assume that there must be some processing occuring in Cognos applications that is not replicated in SQL server; I've checked cogmod.ini for any likely parts.

Can anyone confirm this assumption or better, indicate how I can bring this functionality into SQL server? It's only a small table, so I imagine I ought to look at T-SQL to generate an intermediate table with previous row values included. TIA

I imagine this may also be a valid enquiry for forum 961 (SQL server), so apologise in advance to the non-SQL server users.

soi la, soi carre

 

[binway]

Hi Dr Lex,
Had a similiar experience but with Oracle. The running sum function would work when created by Impromptu and even when I turned it into freehand SQL run via impromptu. It would not work when I tried to run it via toad on the same database - I didn't need to resolve this issue as the running sum question was raised by an Oracle programmer and they didn't tell me what their solution was. For SQL Server I went to the microsoft web site and that has some sql but for an access database which may be of assistance.

Hope this helps.

 

[Recce]

Hi drlex,

I work a bit with SQL server but, as far as I know there are no specific funtion for this. The best way to solve you problem would be to use something similar to the below examples. I got these from a website but have never used it myself.Hope it helps.

http://www.sqlteam.com/item.asp?ItemID=3856

"We know nothing but, that what we know is not the truth..." - Me