Difficult Grouping Question 1

[smilbert]

I'm using CR XI with MS SQL 2000

I've got a table in a database that lists possible rights that a group of users can have. The rights have a hierarchy--up to 6 layers deep.

Top Level Policy
Sub-Policy Layer1
Sub-Policy Layer1
Layer2
Layer3
Layer2
Layer3
Layer4
Layer5
Layer2

The tbPolicies table contains:
PolicyID
PolicyName
PolicyAlias
ParentAlias

Here is where it gets hard to describe:
* The TOP layer policies have a value in PolicyAlias but NULL ParentAlias.
* The other policies have an entry in ParentAlias that matches the PolicyAlias entry of the parent policy.
* The policies also have an entry in the PolicyAlias that MAY be a parent category to other policies (if there I a layer below).

I’d like to be able to build a report that shows these in hierarchical order. Is there any way to do this using groups?

I can post an example of the table values if it will help.

 

[BobSuruncle]

On your report, create a group on Policy Alias.

On the Report menu, select Hierarchical Grouping.

Select your group on Policy Alias on the left side of the screen under "Available Groups" and click the "Sort Data Hierarchically" checkbox.

For the Parent ID Field, choose ParentAlias.

Enter a vlaue for Group Indent if you wish.

Bob Suruncle

 

[smilbert]

BRILLIANT! Thank you BobSuruncle! What a great resource this forum is!

I added selection criteria to this report to show only a specific group's rights, and a True/False indicator to show if the group has rights.

However, ONLY the Policies which the group HAS rights show up (so all indicators are TRUE). Is there any way to show ALL policies--with a FALSE indicator on the ones that the group doesn't have rights to?

 

[BobSuruncle]

What does the data look like?
Are we reading data from more than 1 table?
Please show some sample data and also indicate the Tables and Joins that you're using.


Bob Suruncle

 

[smilbert]

Is there any way to attach a document or screenshot images to this thread?

I’ll try to describe:

I’ve got two main tables I’m using:
tbPolicies
tbGroupsPolicies

The tbPolicies table contains:
PolicyID
PolicyName
PolicyAlias
ParentAlias

This table contains all of the possible Policies (rights) that a group could have. The data contains a Hierachy, (using PolicyAlias and ParentAlias).

The tbGroupsPolicies table contains
GroupID
PolicyID

So, the table contains many entries for each GroupID—one for each PolicyID that that group has rights to. There is no hierarchy in this table.

I link the tbGroupPolicies.PolicyID to tbPolicies.PolicyID table with a Left Outer Join.

I don’t think the problem is related to the hierarchy grouping. Even if I take that out, I’m still not able to show a list of ALL policies (sorted by PolicyID) for a GroupID—with a True/False indicator to show IF that group has the rights.

 

[BobSuruncle]

I think the problem is that you're selecting from the right hand table. This tends to negate the Left-Outer join.
Try changing your record selection to be something like the following:

(
IsNull({tbPolicies.PolicyID}) 
or 
<Whatever other selection you had on the tbPolicies table>
)

Bob Suruncle