I have a report that is mapped directly to the database as I cannot produce what I need from export.

I have some constituents (Reps) that may or may not have a specific relationship type to other constituents (beneficiaries). These beneficiaries may or may not have certain attributes.

I have managed to show all the reps that I need with specific criteria (even if they do not have beneficiary relationships). The reps are a group.

I am having problems with showing the beneficiaries in the detail though.

I suppress the detail if they do not have the rep relationship type - this works well and means I get some reps without beneficiaries. The link between the rep and the consit_relationship table is a left outer join.

However when a beneficiary does not have one of three attributes it is not shown. I tried doing the same thing to the attributes as to the rep-beneficiary relationship i.e left outer join between the beneficiary (records table) and the three attribute tables and then again between each attribute table and their corresponding attrubutetype table. I also suppress the detail if the attribute type is not one of the specific categories but I don't seem to have any luck with this one.

Any thoughts? I realise that it is possible hard to envisage and would be happy to send the CR if you want to have a look for yourself.

Thanks

David

I have a report that is mapped directly to the database as I cannot produce what I need from export.

I have some constituents (Reps) that may or may not have a specific relationship type to other constituents (beneficiaries). These beneficiaries may or may not have certain attributes.

I have managed to show all the reps that I need with specific criteria (even if they do not have beneficiary relationships). The reps are a group.

I am having problems with showing the beneficiaries in the detail though.

I suppress the detail if they do not have the rep relationship type - this works well and means I get some reps without beneficiaries. The link between the rep and the consit_relationship table is a left outer join.

However when a beneficiary does not have one of three attributes it is not shown. I tried doing the same thing to the attributes as to the rep-beneficiary relationship i.e left outer join between the beneficiary (records table) and the three attribute tables and then again between each attribute table and their corresponding attrubutetype table. I also suppress the detail if the attribute type is not one of the specific categories but I don't seem to have any luck with this one.

Any thoughts? I realise that it is possible hard to envisage and would be happy to send the CR if you want to have a look for yourself.

Thanks

David

Since you are looking for a particular attribute, you need to do the filter before the join, otherwise anyone who has an attribute, but not the one you are looking for will be filtered out. This means that you have to filter in your SQL code. Typically this is accomplished by using a view or stored procedure to do the filtering, but you can also directly edit the SQL for the report. There is one big caveat: once you edit the SQL, opening the visual editor will wipe out any changes that it doesn't understand.

Drew