Report Studio - Nulls Suppressed 1

[Drepso]

FRAMEWORK MANAGER 8.1
REPORT STUDIO 8.1

I have an issue where records are being suppressed from a report studio report.
The report is referencing two joined tables.

Table 1 – Individual
Table 2 – Individual Primary Subject

Tables are joined on primary subject code.

Cardinality for the tables as follows:
- Each individual has zero or one individual primary subject (outer join) (0..1)
- Each individual primary subject has one or more individual


Running a report with just the individual_id and primary subject code from the individual table returns those individuals with ‘null’ subject codes, however when I include the description from the Individual Primary Subject table it suppresses those individuals with null subject codes from the report. I need those individuals with no primary subject codes to appear in the report.

The generated SQL from the report is creating an INNER JOIN, when I change this to a left join in SQL Studio I get the right results.

Can anyone suggest how to resolve this?
Appreciate any help.

Thanks

 

[blom0344]

You did not by chance modify the join type within framework manager (including publishing) while keeping the report open?
It seems that one really needs to close and reopen tools for changes to take effect (lots of caching around)

Ties Blom

 

[Drepso]

Thanks for the reply blom0344.
No I didn't modify and publish with the existing report open. The relationship problem is just a part of a larger report. I created a new report from scratch with just these fields to try and identify the problem. Thanks for help anyway. Cheers

 

[mfgf]

Hi,

Do you have access to Framework Manager? If so, select the items in Framework Manager, then use the Test option to view the results. Look at the generated query and make sure that you are seeing a left outer join in the SQL - if not, go back and check the relationship(s).

Once you have made sure everything works OK in FM, republish your package, then try the same thing from Report Studio. You should then see the same results, hopefully.

If this doesn't help you to narrow down the problem, get back to us and we will try to assist further.

Regards,

MF.

 

[Drepso]

Thanks for your help mfgf, greatly appreciated.

I tested the items in FM and the results were correct but it would not generate the SQL.
I verified the sql in the relationship SQL in the relationship definition in context explorer and I did see an outer join.

Despite this an inner join was being passed to the report.
There were relationship copies in Explorer outside of the physical & business layers which were causing the problem. I didn't see these in the project viewer only in the explorer screen by chance. I do not know how these got there, but I removed them and the report now returns the correct results. Removing these also allowed me to see the generated sql from the test option in FM, these copies did not allow this to happen before.

Thanks again for your help.

Cheers

 

[mfgf]

Glad you got it sorted out. Thanks for the star!