How to calculate running total

[venkatamurali]

 



Hi,





This is regarding my reporting requirement.



Report has two queries which pick data for Active user’s distinct count.



Query 1:- Picks data from user selected date range(from date to  To date â€" For eg Aug 1 08 to  Oct 30 08)

Query 2 Picks data from   data base column date to user selected From date (For eg :- Jan 01 08 to Aug 01 08)





Union of these two queries will get populated as Benefit, Period, Users Activated within Period and Total Active (in last 16 months).



Benefit
Period (How far back depends on input)
Users Activated within Period
Total Active (in last 16 months)
Remarks

Assist Tool box
March
30
400
This count is from database column date range to user selected From date


April
40
440
Previous count+April count(400+40)


May
70
510
440+70


June
100
610
510+100


 
 
 
 


 
 
 
 

Year to Date
430
 
 
 

Total Active Users
610
 
 
 






Issue: I need to calculate a running total for (Total Active (in last 16 months) which gives me based on the UserActivated column within period.

            Remarks is the column which I provided for explanation purpose.



            How to achieve the Total Active Users  count which  is the final count from column Total Active (in last 16 months) which 610 here as shown above.



And also find the attachment which gives the report format.





Please help in achieving this is on  high priority.



Thanks

Murali

[bonniehsueh]

I haven't tried it myself, but you may want to look into a function called running-total. I saw a paper on this function on the Cognos KB.

Just a suggestion, you may get more replies if the question is more clear and precise. There seems to be a lot of information that a person reading the post can easily lose track to whats pertinent to the issue.

[blom0344]

Run the output you have now as a tabular data-set. For each month you would want one row as output:

March 400
April    40
May    70
June   100

Then use an additional dataitem that stores the running-total, then you should have:


March 400  400
April    40   440
May    70   510
June   100  610

Note: We have skipped running-total altogether with Cognos. 9 out of 10 times the values that are presented are way-off.

[bonniehsueh]

Looks good. Much more clear.

I've worked more with dimensional reporting. So for relational data I'm not sure and also am wondering myself.

For dimensional data, the periods to date() function will give you the running total.

[blom0344]

Looks good. Much more clear.

???

That was a reply to the initial post..

running-total is a typical BI / OLAP matter. The average relational database will have to compile a complex and inefficient SQL construction to calculate it.

Here is a reference for SQL server:

http://www.databasejournal.com/features/mssql/article.php/3112381

[Suraj]

???
That was a reply to the initial post..

???
Same here...

:-) just adding confusion.

[bonniehsueh]

???

running-total is a typical BI / OLAP matter. The average relational database will have to compile a complex and inefficient SQL construction to calculate it.

Here is a reference for SQL server:

http://www.databasejournal.com/features/mssql/article.php/3112381

Blom0344 - After reading the article posted I can understand the complexity involved to calculate running totals.

What are options in calculating running with relational data?

Sound like one option is to create a olap data source, however that takes a good deal of work. Thoughts?

[blom0344]

Last month I worked on the revision of an Cognos Cube that incorporated a number of running totals as measures. I am not sure if this is a very good practice as the cube itself can then only serve  to show static data (no point in calculating subtotals on running-totals or drilling)
The only upside is that all aggregates are pre-computed, which is going to be a great benefit for large data-sets.

The latest versions of the premium RDBMS vendors all have some support for  build-in OLAP functions:

Code Select Expand


sum([measure])  over (order by [dimension1] ,[dimension2])


[bonniehsueh]

What I meant is what are the options using relational data to calculate running total other than the article you posted.

[blom0344]

Well, I answered that question. Check if your database supports OLAP type functions. DB2 and Oracle support them since some years. I believe SQL Server 2005 does as well.
Then it is a matter of defining them at the database level..

[bonniehsueh]

Ahh got it. It is possible in certain situations to use olap type functions with relational data. Thanks for the clarification!