Windchill report "Group by" rows function

[Dobi]

Version: Windchill 12.0

Use Case: I have a report that lists out a number of ECNs and how many reworks (trip count) per ECN there were. The table view of the report shows a new row for each incrementing trip count value. "Group by" doesn't seem to do the grouping by rows to consolidate the results.

Description:

The report template lists out the ECN number for a time range with it's trip count (number of reworks). The table itself has a row for each incremental value of the trip count. 

Something like this:

ECN Number	Date	Trip count
ECN-12345	1/2/23	1
ECN-12345	1/3/23	2
ECN-12345	1/5/24	3

Is there a way in the report template to group the rows by the ECN number so that for the example above I just get a single row like this:

ECN Number	Date	Trip count
ECN-12345	1/5/23	3

Ideally I'm looking for a way of doing this without exporting out the table and then doing this in PowerBI (which works just fine). 

[Marco_Tosin]

I have a report that counts the number of Windchill accesses of various users in real time.

The report uses the database COUNT function.

Obviously the count is impacted by the range used as the time period

Below are some examples of existing reports that use the COUNT function

How to Generate a report to get a count of users exist

How to create a report that counts WTParts in a specific context

Report listing all content counts by data format

Report for counting the Affected Objects in a Problem Report

Report to count the number of documents by different document types

[joe_morton]

Looks like you just want the maximum trip count? Try using the database function maximum. You'll need to define that in a sub-select if you go that route.

[Dobi]

That's what I'd like. When I create the criteria, though, I get a "this table cannot be modified from a sub-select" for all of the tables". 

[Marco_Tosin]

In the sub-select you have to add the table from which you want to extract the criteria one more time.

That table will be colored white and not blue like the ones in the image you attached

[Dobi]

I think that's the part I was missing - adding a new table in that sub-select. So step 1 done and now working... but failing in terms of result provided. Looks like that's on me (Work Item or Wf Assigned Activity is a unique ID each row so perhaps stepping up to the ECN level will give me what I want). 

[Marco_Tosin]

This is an example

Query Builder Report(QML) that lists the latest versions of documents