Cognos Summary Counts in Workspace Advanced

​Cognos Summary Counts without the Detail in Workspace Advanced

With many companies realizing the bang for their buck by purchasing and training their end users on Cognos 10.2 Workspace Advanced, we are starting to see at Lodestar an influx of Workspace Advanced “how to” questions. What is the best way to get a Cognos Summary Counts without the detail in Workspace Advanced. These “studios” combine the functionality of Query Studio, Analysis Studio, and Report Studio Express into one. However, the most frustrating part of learning any new software is understanding where to find things, what functions are built-in, and what syntax to use in calculations. Amazingly enough, sometimes the simplest question can be the hardest for which to find answers.

This week’s question is a simple one, but is also extremely useful. One of our clients asked, “What is the best way to get a summary report of counts without the detail. At the moment, we have to show all of the detail and create a count total.”

The steps to answer this question are:

  1. Drag a Query Calculation object from the toolbox to either your list or crosstab report. This will prompt you to name the column and open the Expression Definition pane.
  2. Drag the Count Summary function into the Expression Definition box. (For relational data, use the count found under the List Summaries folder. For dimensional data sets, use the Count function in the Crosstab/Chart Summaries folder.)
  3. Drag the query object you want to count from the source tab and close the parenthesis.
  4. Validate expression and click ok.

In the example below, I created a column that counted the number of sales orders per product for the given time period. From there, I was able to use the count to calculate the average quantity per order.

Cognos Summary Counts Expression

Cognos Summary Report of Counts

If you enjoyed this blog on Cognos Summary Counts in Workspace Advanced, check out this blog on Crosstabs and Dimensional Data in Report Studio by clicking here.

Who should use Cognos Workspace Advanced?

Cognos Workspace Advanced (known as Business Insight Advanced in 10.0) is an IBM Cognos product that encompasses Query Studio, Analysis Studio and Report Studio Express capabilities into one application. Now ad-hoc meets slice and dice, and can be wrapped up in a pretty package too.

Unlike Analysis Studio, end users no longer have to predetermine whether a data package is relational or dimensional because Query Studio functionality is also included in the application. The Cognos Workspace Advanced user interface and program capabilities automatically change to match the data source; reducing confusion and increasing end user satisfaction.

Query Studio and Analysis Studio require a very low skill set to master.  However, when advanced users outgrow the capabilities of these two studios and try to make the leap to Report Studio, they often find the distance to be too great.  The Report Studio Express functions available in Cognos Workspace Advanced are a perfect stepping stone.  Some of the functions that these advanced users will enjoy include creating calculated members and measures based on tuples, and creating sets in order to determine top and bottom members.  Additionally, since the expected skill level is lower, Workspace Advanced only requires a BI Advanced Business Author license; whereas, Report Studio requires a BI Professional Author license.  This is the beauty of Cognos Workspace Advanced; end users are trained once, on one application, and have room to grow without increasing cost.