Windchill report for reworks within tasks AND change notices

[Dobi]

Version: Windchill 13.0

Use Case: In our change notice flow I have options to "Rework" at the ECN workflow level and also within the change activities as part of the ECN. I want to get a report to count the reworks overall per ECN but seem to be able to get either just the activities, or just the ECN reworks and not both.

Description:

There are couple of really useful references on Windchill ECN reporting:

CS48714 - How to use Query Builder to generate a report to show Change Notice related information in Windchill PDMLink

CS336467 - How to link work items of an upstream change object to downstream ones (CR to CN, etc.)

CS153902 - Data model

In our ECN flow there are rework routes within the ECN workflow and also within the Change Activity workflows:

All of the above work but they end up only showing the reworks within the ECN workflow and don't display the change activity reworks. 

If I go this route with looking at assigned activities, I get the workflow processes for both the ECN and the activities, but as soon as I add the change notice table, the activity data drops off. 

With change notice added:

What am I missing to consolidate reworks within an ECN that counts both the ECN reworks AND the task reworks within it? 

[Marco_Tosin]

I think you need to put an outer join to the link to the ECNs table, so that the report extracts data even if the assigned activity is NOT linked to this table.

[Dobi]

Once I add that outer join I get the change tasks with their reworks and the ECNs with theirs... but there's no relationship between the ECNs and Tasks. In other words, I can get the task number for an ECN and then look for the number of times that task shows up. That's a secondary operation though. 

What's the best way to get the ECN to show both the number of reworks within it's flow AND to tie in the associated tasks as part of it's implementation plan for the total sum of reworks? 

[Marco_Tosin · Accepted solution]

This is one of the most comprehensive reports I have prepared for change-related objects.

You will find linked to it. Change Requests (ECRs), Change Tasks, Change Notice (ECNs), workflow processes, assignees, etc... (I apologize for the Italian of some of the entries but it should be fairly easy to trace back to the relevant name in English) and, as you can see, there are many outer joins.

You can retrieve the reassignments from the work item table.

[avillanueva]

No one should ever apologize for their native language not being English.

[Marco_Tosin]

Certainly!
After all, in Italy we had Dante, the supreme poet.

[Dobi]

@Marco_Tosin, Grazie mille!

This, I think, covers the bases. It gives me the work items and - in my case - counts the rework votes within the tasks themselves. Now I just need to combine this with the rework votes within the activities of the ECN itself and I have what I need ... I think. 

I'll try to work it out but I'm wondering if our ECN flow is just set up to not want to yield this information easily. 

[Dobi]

After much trying, I convinced myself that two queries is the way to go. 

@Marco_Tosin this method gives me the rework counts within the Change Tasks that are implemented by the ECN. My original report does the reworks within the ECN workflow itself. 

I combine the results of both (separately, as two Excel files) and then post processing between Excel and PowerBI. 

Even there, I struggled a bit. 

The ECN number doesn't always show up on both lists. Sometimes there are only ECN reworks, only change task reworks and sometimes there are both... sometimes there are many change tasks, each with reworks, on the same ECN. The merging of the columns ultimately was where I think the single Windchill report was struggling. 

What I needed it to report out is a full list of ECN numbers that had ANY rework regardless of whether they occurred within a task or within the ECN or both and then sum all of those. 

This will now be a problem for later.