Cognos/Framework Manager: Double Counting

Question

I have query subjects, Case and Person. A person belongs to a case if the case id appears in the person record. I'm trying to get a count of people by case id without double counting.

Person Fields: personID (PK), caseID (FK)

Case Fields: caseID (PK)

I can create a query item in Case that looks something like this: count( [Person].[personID] for [Case.caseID] )

When I test it, I see my count and the caseID appear once for each person on the case. If three people are on a case, I get the corresponding caseID and "3" three times.

Answer 1

Try count(distinct [Person].[personID] for [Case.caseID] ). You may also be able to get away with count(distinct [Person].[personID]) depending on how your list is configured.

---

Alright, try this. It sounds more and more like a display issue instead of a query problem.

I'm assuming you're on Cognos BI 8.3, 8.4, 10.1, or 10.2. I'm also assuming you're using Report Studio and not Query Studio. I'm used to 8.4.1, but these are all pretty similar from what I can tell. If you're on Cognos Insight, then we're not going to communicate at all. I translated some reports from that, but that's all.

Make a new list report with a new query. Add three data items: CaseID, PersonID, and PersonID a second time (I'll refer to it as PersonID1, as that's what Cognos will probably call it). Just add them from the Person table for now, and don't modify the data item to add the count() function at all.

Now, add this query as a list to your report and run it. You should see a pretty basic list that would match a very simple query in SQL.

Go back to your query, and edit the data item for PersonID1 to be count (distinct [PersonID] for [CaseID]).

Go back to the list on Page1. This time, group the list by CaseID. (Click CaseID on the List (not the list header), then go Structure -> Group). Now when you run the report, you should see that the CaseID field will span multiple rows where it makes sense to, but the PersonID and PersonID1 fields don't.

That is what I'm assuming you see. I'm assuming you don't like that PersonID1 repeats over and over again.

Now, the real easy thing is to say, "Oh, I'll group by PersonID1 and that will work!" Well, no, that's not what you want to do. It might work, but it won't work reliably. Instead, go back once more to Page1, and click on the PersonID1 data item (not the list header). Look at the properties window for this item. Under "Data", you should see "Group Span". Click that, and set the drop-down menu to what should be the only available group, CaseID. Now when you run the report, you should get a single item with a cell that spans rows.

Is that what you're looking for, or am I going too simple now?

For more complex reports, you'll need to have more control over Grouping and Sorting, controlling the order of both. You can get back to that by clicking the List, then going Data -> Advanced Sorting. Here you can specify how data is grouped and sorted, and make corrections or adjustments as necessary.

Answer 2

Cognos' default behavior is to only show rows that have distinct combination of attribute values and aggregate any measure values. If my data looks like so:

PersonID | Person | Sale
1 | Bob | 100
2 | Frank | 600
1 | Bob | 300

Cognos will find all of the distinct combinations of non-measure columns, in our case PersonID and Person, and display each only once. Any measures will be aggregated to those combinations. The result of running the query should be:

1 | Bob | 400
2 | Frank | 600

This happens if the measure column has an Aggregate Function property of 'Total' which is the case for most measures. This behavior is called 'Auto-Group & Summarize'. It's enabled for queries by default.

In your case you are creating a custom aggregate function. Custom aggregate functions should have a Aggregate Function property of 'Calculated'. This tells Cognos not to try and roll-up the column values as you are creating your own custom aggregate function. In your count() function you have 'for [Case].[caseID]'. This tells Cognos to rollup the value to a level you specify and not the default level determined by looking at the unique attribute combinations in the non-measure columns. Thus, the only way your count column is going to appear once and only once for each caseID is to only include caseID, or any columns from the same grain in your column list. If you include any columns that are at a lower-level of granularity than caseID, Cognos will further break out each individual caseID value into multiple rows, each having some column that varies. This will cause your aggregate column to repeat. If all you are looking at is sample data generated in Framework Manager, this may be inevitable as a query subject with two sources with a 1-n relationship is bound to have attribute items from both the 1 and n sides.