Report listing all Change Notices with associated Change Requests

[PhilK]

I try to create a report using Query Builder, that gives me all Change Notices and their related Change Requests.

We have an one to many relationship between CR and CN, thus one CN can only have one CR or none.

From this article, I saw, that CN and CR are linked with "Adressed By": https://www.ptc.com/en/support/article/CS63595

I attached a Screenshot that shows my setup.

I selected Change Notice.number and Change Request.number as my output.

When I run my report and select the CN number only, I get every CN number. When I select both numbers, I get less results.

When I modify the join to be an outer join againt CR, I get every CN number for both, but some CR numbers are empty. When I open a CN with an empty CR number, there is a CR related to that CN in the Associated Process Objects table of the CN.

Results where the CR number is not empty look exactly the same like the one with an empfy filed, when I open the CN.

I want a list of CN where the CR number is empty only, when there is no related CN.

[aaronjlarson]

Per the WC help:

Essentially, selecting a value for Outer Join Against means “allow null entries for the unselected table when joining against the selected table”.

So, the table you select in the join settings should be the CR table.  I wasn't clear in your description if you selected the CN in that join setting.

[PhilK]

Thank you. I read about that in the help already, and yes, I am expecting to have null values for the CR number, but only if there is no CR related to a CN. I am getting null for relationships that exist, and that is my issue.

In the table and join tab I added the CN and CR as a table. Then I created a join between them, like shown in the screenshot. When I created the outer join and when I select the CN to outer join against, I get less results. When I select the CR, I get the correct amout of CN results (I think), but for the CR I receive three scenarios:

1. CN number with CR number (which is fine)

2. CN number without CR number (which is fine, if there is no relation to a CR)

3. CN number wihtout CR number (which is my issue, as there is a reatlion to a CR)

If I select neighter CR nor CN, it acts like CN was selected, with less results (it is kind of WHERE relationship IS NOT NULL).

Is that selection for outer join against like a left or right join?

In plain SQL, I'd look for something like: SELECT * FORM CN LEFT JOIN CR on CR.key = CN.key

[aaronjlarson]

Ahhh yes... that's one of the lovely quirks about Query Builder and the reporting interface.  If you only select the CR number as an output to your report it essentially ignores the join requirement and reports just what you have available from the CR table.... which is everything!  If you're using the Report Builder interface for your users, you could set the CR Number field to always be included (configured on the Select tab for the Reportable Item) which will not allow the users to choose to select it or not.  That way, the join criteria will always be considered when the query executes.

[PhilK]

Unfortunately that doesn't solve my issue. I am not reporting the CR number only.

I selected multiple values of the CN and CR is the only attribute I selct from the CR.

I atteched a screenshot of my selection.

I tried setting the CR number to be always included, but that doesn't change anything. I tried adding more values of the CR (state, name) but still, I receive null values for all selected CR.

I atteched a screenshot of the results as well.

When I open the CN from the first line, you can see, it has a related CR (screenshot attached).

In the results table there are no null results for relationship as well.

[aaronjlarson]

Okay, sorry I misunderstood that about the CR.  Last think I can think of is that you are missing part of your data due to the implementation of the flexible change association. This is mentioned in the article you originally posted also.  When I want to report on ALL of my CRs and CNs I have to use both types of links, addressed by for the old ones and flexible change for the new ones.  You could test this easily with the example you found by creating a different copy of the QB report and switch the addressedby join with the flexible change association and run the report for that specific CR.

[PhilK]

That's it! I exported the results with the adressed by join and after that I switched to the processing link join. Every single cr number that is null in the adressed by report exists in the processing link report.

Now there is only one question: How do I combine these two, to have both results in one column?

Like: If adressed by join is null return processing link join and if that is null return null

[aaronjlarson]

I haven't spent time trying to do that in QB yet.  It would be plausible that you could somehow create a concatenated field for the CR made up of the CR from two CR tables which would only ever return a single value (because a CR can't belong to both links).  Otherwise, it would be recommended to convert old and completed change objects to the flexible link using the utility discussed in the help here:

http://support.ptc.com/help/windchill/wc110_hc/whc_en/index.html#page/Windchill_Help_Center%2FChgMgmtChgAssocConvertUtility.html%23wwID0EDM1V 

[PhilK]

I don't have administrative credentials and can use the QB only.

How can I concatenate fileds? I need to implement some logic to show one link or the other.

Reagrds

[aaronjlarson]

Concatenation is just a database function. http://support.ptc.com/help/windchill/whc/whc_en/#page/Windchill_Help_Center%2FQBSelectOrConstrainDatabaseFunction.html
Was thinking you could perhaps do the outer join to two CR tables. So both tables would be joined to your one CN table, and one being the AddressedBy Join and one the Flexible Change Link. Then you can concatenate the ECR number from both tables, since only one of them would ever have a value it would return which ever ECR is actually linked to the ECN.