Search code examples
sqlreportcognoscognos-10

Sum column in Cognos

I'm making a report that compares the production of a cell to the average production (roughly)

enter image description here

So a cell (cellule) is in a room (salle), and there are many cells in each room. We have for each day the production (Production brute) and the standard production in the database. The rows are grouped by week (Semaine). The "production moyenne" column is meant to display the average for the week and the room.

I only want to display values for one cell so I made a filter on "Nom de salle" and "Nom de cellule". The problem is that the "production moyenne" column calculates the week average for the cell, and not the room, which seems kinda logical.

So I deactivated the filter on the "Nom de cellule" column, and added an if condition in the "Production brute" and "Production standard" columns, so these columns would be filtered, but not the "Production moyenne" one.

It doesn't work, because now, all the cells are displayed, and the ones that I don't need just have zeroes in their columns, the last column is also incorrect because it's the average only for this cell.

enter image description here

I hope you understood my problem, feel free to ask for more details.

Solution

  • This is a common problem. You want an aggregate calculated based on detail rows that you want to exclude from the output.

    I'm assuming you are using a relational source. If so, the solution is to use a derived query. A derived query is one which uses another query as its source, instead of items from a model. This is what a derived query looks like in Report Studio:

    Derived Query

    Query1 doesn't contain items that point directly to any data model. Instead it gets its items from Query2. Query2 will contain items that directly reference model objects. You can create this relationship by simply dragging a query to the right of another.

    Your average calculation should be the following:

    average([Production brute] for [Semaine],[Nom de salle])

    In the solution, Query2 will contain your existing data items, including the average calculation above. It will not contain the filter however as we need all of the detail rows present to calculate the average. Move the filters to Query1, making sure you reference the Query2 items:

    [Query2].[Nom de salle] = ?param1?
[Query2].[Nom de cellule] = ?param2?

    Now pull in all of the Query2 data items into Query1. Change your data container to point to the new derived query: Query1.

    If you look at the generated SQL the effect is the SELECT statment from Query2 is wrapped in another SELECT statement. The outer select's WHERE clause contains the filters, which aren't applied until after the aggregate calculation is completed.