Skip to main content
P
PhilK6-Contributor
6-Contributor
November 10, 2020
Question

Report listing all Change Notices with associated Change Requests

  • November 10, 2020
  • 1 reply
  • 7822 views

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.

1 reply

A
aaronjlarson14-Alexandrite
14-Alexandrite
November 10, 2020

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.

P
PhilK6-ContributorAuthor
6-Contributor
November 11, 2020

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

A
aaronjlarson14-Alexandrite
14-Alexandrite
November 11, 2020

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.

Terms & ConditionsCookie settingsAccessibility statement