Outer Join Issue 1

[smilbert]

I’m using CR XI with MS SQL 2000

I’m trying to build a report showing all possible Policies that a group can have along with a True/False value showing whether a group has that privilege. (I'll either group by group or use a parameter to select a single group once I get this working)

I’ve gottwo tables:
tbPolicies
tbGroupsPolicies

The tbPolicies table contains all possible Policies that a group could have:
PolicyID
PolicyName

The tbGroupsPolicies table contains
GroupID
PolicyID

IF a group has a Policy, there is an entry in the tbGroupsPolicies table with their GroupID and the PolicyID.

Seems pretty straightforward to me—do a Left Outer Join from tbPolicies.PolicyID to tbGroupsPolicies.PolicyID and I’m done, right?

I have a formula that looks to see if tbGroupsPolicies.PolicyID is NULL:

IF isnull ({tbGroupsPolicies.PolicyID})
then “TRUE”
else “FALSE”

For whatever reason, I cannot get the Polices that a group DOESN’T have to display. ONLY the Policies that a group DOES have are shown.

 

[lbass]

I think the problem is that you are trying to group on the the group field, and if the group doesn't have the policy, there is no corresponding record. You could do this a couple of ways.

1) If each policy has at least one group that subscribes, I think you could insert a crosstab in the report footer, using Policy as the row and Group as the column, with your formula as the summary field (use a maximum).

2) You could insert a group on Group and then use running totals displayed in the group footer, that do a running total distinct count of group ID, evaluates using a formula:

{table.policyName} = "Policy Name"

Reset on change of group. This would give you a 1 for those with the policy, or a 0 if not.

-LB