Running count cognos

Running count cognos DEFAULT

By Paul Hausser, Envisn, Inc.
audit data icon streamThe biggest challenge faced by Cognos administrators in any Cognos BI environment is having the tools and information needed to manage it. A key part of this needed information is a complete picture of usage and overall utilization. Creating this can be a challenge but once it’s done the payoff can be huge. In this blog we will focus on going beyond the basics and using the Cognos audit data in some creative ways.

Going Beyond Basics

Can you use the audit data available from the standard, out-of-the-box offering that IBM Cognos provides? Yes, but you’ll quickly find there are some limitations with what you get here. Our previous blog on Cognos Audit Extensions noted that the sample reports provided with this can be helpful up to a point but have limited dimensionality and measures. Moving beyond this requires some work unless you decide to purchase a tool that does this for you.

The key thing to keep in mind here is that the value of going beyond the basics of what’s available is that the benefits are cumulative. The more you are able to do with the audit data the more you’ll be able to extend this into new areas of use and build upon what you have created.

Adding dimensionality and measures can greatly enhance the basic audit data to be able to identify problems or opportunities. One or more organizational dimensions can add a lot of value to analyzing what content is being used, with what frequency and by whom. More granular measures on usage such as sessions, activity types and content will make it easier to add value and precision to your audit data for usage analysis and reporting.

Form Factor

Standard reports or OLAP? – In many Cognos environments the audit data is used in multiple reports with different dimensions while others use OLAP cubes. Reports are best for standard metrics on things like utilization of content, frequency of usage, Pareto analyses, etc. OLAP cubes have an advantage when exploring and analyzing the data because of the ease with which this can be done on the fly.

Drill-through reports are the example where both are used together. Thus, most environments will usually have a mix of offering audit data using mulitple formats. The decision should be made on what makes it easiest to consume the information.

Figure 1 - Average User Session LengthGetting Creative Cognos Audit Data

Some examples of using Cognos audit data creatively include the following items:

  1. Average User Session Length –(Figure 1 - click image to view full size) This example shows average session length by users from high to low for a given time period.
  2. Report Run Count by Dispatcher and Run Type – This is useful for looking at the load by dispatcher and run type (interactive or batch) for a given time period.
  3. Object Type Count by Package – Makes it easy to see which FM packages are actually being used and weed out those that have no usage.
  4. Figure 2 - Reports Average Run TimesReport Average Run Times – (Figure 2 - click image to view full size) Identifies reports with consistently long run times that may indicate problems. Can also drill down on weeks/days/hours.
  5. New Content Adoption Rates by Organization and User – Measures subsequent usage of new content developed in a given period. Useful to see if new content is actually being used and how quickly it’s being adopted.
  6. Test Validation – Shows run counts for reports in Test or UA environment to validate that testing is actually being done.
  7. Figure 3 - Reports Run But Never ViewedReports Scheduled & Run but Never Viewed –(Figure 3 - click image to view full size) This can be a problem in any environment but especially in large ones where users may schedule and run reports but seldom, if ever, look at them. Getting control of this can free up lots of platform resources.
  8. Figure 4 - Run Count by Hours Run Count by Hours/Days –(Figure 4 - click image to view full size) Shows run activity by week broken down into hours and days. Helpful for identifying peak activity periods.
  9. Users With No Activity – This can be useful in identifying user licenses that can be redeployed.
  10. Reports Ranked by Success/Failure Rate – Identifies reports that consistently have problems.
  11. Top Active Reports – Useful for identifying most popular reports by organization, package, user, etc.

Once you’ve created the appropriate dimensions and measures to use with the Cognos audit data the creative opportunities are limited only by your imagination and real needs. Focus on those that represent real opportunities to expand user adoption and remove problems that get in the way of download expert guide to cognos audit data

© 2014 Envisn, Inc. – All rights reserved. Advanced Tips for Cognos Audit Data


Returns a calculated value using the appropriate aggregation function, based on the aggregation type of the expression.

Syntax: aggregate ( expr [ auto ] )
aggregate ( expr for [ all | any ] expr { , expr } )
aggregate ( expr for report )


Returns the number of selected data items excluding NULL values. The keyword distinct is available for backward compatibility of expressions used in previous versions of the product.

Syntax: count ( [ distinct ] expr [ auto ] )
count ( [ distinct ] expr for [ all | any ] expr { , expr } )
count ( [ distinct ] expr for report)

Example: count ([gosales_goretailers].[Products].[Product name]) - (o/p: 41, 15, 21)
Count ([Product type] for all [Product line]) ------------ (o/p: 5, 4, 4)
Count ([Product type] for any [Product line]) ----------- (o/p: 5, 4, 4)
Count ([Product type] for Report) ------------------- (o/p: 21 for all)


Returns the maximum value of selected data items. The keyword distinct is available for backward compatibility of expressions used in previous versions of the product.

Syntax: maximum ( [ distinct ] expr [ auto ] )
maximum ( [ distinct ] expr for [ all | any ] expr { , expr } )
maximum ( [ distinct ] expr for report )

Example: maximum ([Quantity]) -------------------------------- (o/p: 962)
maximum ([Quantity] for all [gosales_goretailers].[Products].[Product type])
(o/p: 344,436…)
Maximum ([Quantity] for any [gosales_goretailers].[Products].[Product name])
(o/p: 44,78,….)
Maximum ([Quantity] for report) ------------------- (o/p: 1,646)

Returns the minimum value of selected data items. The keyword distinct is available for backward compatibility of expressions used in previous versions of the product.

Syntax: minimum ( [ distinct ] expr [ auto ] )
minimum ( [ distinct ] expr for [ all | any ] expr { , expr } )
minimum ( [ distinct ] expr for report )

Example: minimum ([Quantity]) -------------- (o/p: 43,330, 142,150)
Minimum ([Quantity] for all [Product type]) ---- (o/p: 6, 4, 4, ….)
Minimum ([Quantity] for any [Product type]) – (o/p: 6, 4, 4...)
Minimum ([Quantity] for report) ----------------- (o/p: 2)


Returns the value by adding the values to the previous one based on the numeric_exp.

Syntax: moving-average ( numeric_expr , numeric_expr [ at exp {, expr } ] [
  1. Hestia familia
  2. Bluetooth speaker cnet
  3. Spectra staffing agency
  4. Merrimack nh movie
  5. Times gazette classifieds

Contact US


Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!

  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Us!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Tek-Tips Posting Policies

Contact US

Running Count function

Running Count function



I have a Cognos 7.5 report where I want to apply the Running Count function to each value in the many table
of a one to many relationship. However I encounter the error "Duplicate Value on Sort Key'. When Count is
used instead of Run Count there is NO problem.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Power Query Running Count with Grouping \u0026 Add Index - Excel Magic Trick 1589

The concept of a running-total is relational concept.  Since a relational table can be visualized in only two dimensions it is very easy to understand and visualize running and moving aggregates.

A multidimensional data source, on the other hand, is more complex.  You might want the running aggregate on one dimension but not another. When attempting to use the running aggregate functions on a cube you cannot guarantee consistent results, drilldowns might behave unexpectedly, and the entire processing time is increased as the resultset must be processed locally.

Fortunately running and moving aggregates can be handled with purely dimensional functions.

Consider the following request. A line graph that shows the running total of sales for the current and previous years.  The ordinal axis shows all months of the current year.

To start create a new line graph report.  Create a Query Calculation in the Category area with the following expression (replacing with correct values from your own cube):

descendants ([Cube].[Date].[Date].[Year]->:[PC].[@MEMBER].[20100101-20101231],[Cube].[Date].[Date].[Month] )

This will return the Month descendants of 2010  (see a previous post on a better way of finding the current year).

Create a Query Expression in the series with the following expression:

total([Cube].[Measures].[Sales] within set periodsToDate([Cube].[Date].[Date].[Year],currentMember([Cube].[Date].[Date])))

Run the report. If all goes well you should see the line growing as expected.

It is important to understand the mechanism. The expression in the series is processed for each item in the ordinal axis.  The currentMember function will take the member that’s being evaluated. So far January 1 the expression would appear as:

total([Cube].[Measures].[Sales] within set periodsToDate([Cube].[Date].[Date].[Year],[Cube].[Date].[Date].[Month]->:[PC].[@MEMBER].[20100101-20100131]))

When it gets to the October category it will appear as:

total([Cube].[Measures].[Sales] within set periodsToDate([Cube].[Date].[Date].[Year],[Cube].[Date].[Date].[Month]->:[PC].[@MEMBER].[20101001-20101031]))

The periodsToDate function will take the member and create a set of members starting with the first member in the specified level to the member being evaluated.  October/2010 would return the set: January/2010, February/2010, March/2010, April/2010, May/2010, June/2010, July/2010, August/2010, September/2010, October/2010.

The next demand is to see the running total for the previous year.  In a relational database you might do this with an outer join. Here it’s just a logical extension of the same expression.

total([Cube].[Measures].[Actual] within set periodsToDate([Cube].[Date].[Date].[Year],parallelPeriod([Cube].[Date].[Date].[Year],1,currentMember([Cube].[Date].[Date]))))

This is essentially the same expression, with the parallelPeriod function wrapping the currentMember.

The same concept can be used for moving totals. Instead of the periodsToDate function, you would use lastPeriods().

total([Cube].[Measures].[Sales] within set lastPeriods (5, currentMember([Cube].[Date].[Date])))

This will always create a total for a set of 5 members ending with the member being evaluated.

Here’s a small challenge. Knowing this, how do you do a running-total in reverse?



Count cognos running

I’ve nicked the term “Multi-Line” model out from some cognos best practices presentations. Never known it was called that way )

Multi-Line is only way to go when you have a potentially huge dimension only a tiny bit of which should be available to end-user at a time. Like employee planning, whole dimension of 10k people, strictly less than a 100 in department. So you create a fake dimension of 1..100 and add an Employee name column, decreasing cube volume by 100. Access tables + cut-down might help, but it’s sometimes better to allow people pick up any employee,client, product given up that there won’t be more than fixed number of rows.

Main problem with this modeling technique arises when it’s time to import data in such cube. That data usually doesn’t have an 1..100 running count attached, so it’s your task to add it.

In this post I’ll sum up the ideas of how to calculate running count split by elist (that’s usual, ain’t it?).

As a example, I’ll use this simple table:

And you have departments as an elist, so you have to number rows so that numbering will restart for each dept. Numbering requires an order so let’s alphabetical order of employee names.

So this is what we want to get:


SQL code for table:

So what are our choices?

0 Use a framework package as source and a running-count calculation in query subject

Only since Cognos 8.2, slower than some variants, but definitely most cognos-style.

Create a query subject and add an expression of running-count like

1 Add sub query to count this rows position within elist slice.

That’s “works everywhere, but terribly slow” solution. Easy to do, use it if you’re about to import a 100-1000 rows table and really don’t care about time it takes.

Sample code:

2 Use SQL'99 standard rownum() function

Way much better optimized (in Oracle ) ) than subquerying variant above. Problem is: works only on Oracle, Ms SQL 2005+ and DB2. Ms SQL 2000 import – sorry, no SQL'99 there. See original syntax

Sample code:

3 Use cursors to calculate rownumber**

Brute-force solution, non portable, as fast as possible. Only way to do serious lifting in MS SQL 2000.

I’ll place sample code for Ms SQL 2000 if somebody needs it :)

PS You’re on Oracle 10g+? Lucky you, you can also try to do running_counts it via oracle model by clause.

comments powered by Sours:
Running Count vs True Count Explained (Blackjack Card Counting)

How to convert the tabular report into graphical report?Is this possible in cognos 8 report studio?

3 Answers   IBM, TCS,

How can we find out cardinality between two tables?

3 Answers   Dominion,

i have a sales project interviewe asked tel me the how many facts&dimensions used in ur project.

2 Answers   IBM,

what are ERP systems?

1 Answers  

Cognos Online Training will be provided. prior training with all real time aspects. Comprehensive training will be given in Datawarehousing concepts , cognos 8.4 and cognos 10 versions. Contact [email protected]

0 Answers  

What actually a project contains?

0 Answers  

What DB View and Bus View?

0 Answers  

suppose a value prompt contains 100 values and i want to select all values in that prompt....i want ALL option in the value prompt? how can u keep this option in value prompt?

5 Answers   TCS,

------------------- What is Busschema?

1 Answers   Cap Gemini,

wht is the reason for using sql queries in the report 1. inorder drag the items to conatiner we can use use packages but wht is the neccessity of using sql queries to drag the items into the container

0 Answers  

What's the differance between each studio in cognos 8?

1 Answers   TCS,

i have one file i saved in csv how can i take these file into framework manager

0 Answers  


You will also like:

IBM Cognos Running-Total on a Distinct Count?

This is for Cognos v.8 and I am unable to use SQL at my permission level.

I've spent almost two whole days trying to figure out how to make this running-total function work in Cognos, but for some reason it will not work in this case. I've tried running-counts, running totals, every combination of the two...

I'm trying to figure out specific count of type A items.

Each item has their specific item code, and so I have a data item called [Special Indicator]:

CASE WHEN [Item Code] IN ('A','B') THEN 'Special' WHEN [Item Code] = ('C') THEN NULL ELSE NULL END

I have another data item used, [Special Items] to calculate the distinct count by type using a concatenation of classifying factors called [Item SuperID].

IF ([Special Indicator] = 'Special') THEN ([Item SuperID]) ELSE NULL END

Finally, I have [Special Item Count]:

count(distinct [Special Items] for [Inventory Date], [Warehouse])

This gives me the correct count per day for each item.

However if I do this, I do not get a running total of these counts:

running-total([Special Item Count] for [Warehouse])

My initial count for the first date is correct, but all counts for each consecutive date seems to be multiplying the initial count in multiples of random integers like 11, 19, or 23.

Any idea on how I can get an accurate running count/total for each warehouse?

My counts should say 40, 68, etc... but it's coming out as 40, 440, etc..

Any help is GREATLY appreciated!!

asked May 11 '17 at 3:34


265 266 267 268 269