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
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.
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.
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.
Quote from: CognosBuddy11 on 24 Oct 2008 10:55:47 AM
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
Quote from: blom0344 on 24 Oct 2008 12:57:06 PM
???
That was a reply to the initial post..
???
Same here...
:-) just adding confusion.
Quote from: blom0344 on 24 Oct 2008 12:57:06 PM
???
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?
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:
sum([measure]) over (order by [dimension1] ,[dimension2])
What I meant is what are the options using relational data to calculate running total other than the article you posted.
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..
Ahh got it. It is possible in certain situations to use olap type functions with relational data. Thanks for the clarification!