Request for help with Livereport on Users/Groups

How can I create a live report that will tell me: this [User] is a member of which groups? That is, the Livereport should prompt me to choose a user and then tell me all the groups to which that user belongs.Any help is greatly appreciated.- Bhupinder Singh (bsingh@opentext.com)

Find more posts tagged with

Comments

Jose_Garcia

I just happen to have a report that does that. It was given to me by the people at OpenText. You'll have to make some changes to suit you DB of course but it does exactly what your asking for.

Vera_Verbist_(dvvadmin_-_(deleted))

MAIN REPORT:Select in the inputs the type user.The select command to use is:select kuaf.id "ObjId", kuaf.name "Group" from kuafchildren, kuaf where kuafchildren.childid=%1 and kuafchildren.id=kuaf.idParam %1 = User input 1Sub report: the same report as the main reportSub report param 1 = ObjIdThis is the report we use.Best Regards,DVVVera Verbist

Alex_Kowalenko_(akowalen_(Delete)_2285456)

This type of request is simple enough, yet has a not-so-simple answer. The solution is a good example of three concepts that advanced SQL writers should be familiar with: (1) self-joins, (2) hierarchical queries, and (3) outer-joins.In Livelink, groups can contain users as well as other groups. It's the other groups part than can lead to many levels of nested groups. In order to fully answer the question, "this user is a member of which groups?", you have to follow the group lineage.The Livelink schema table that stores the user/group to user/group relationships is KUAFChildren which is a simple table of ID and ChildID columns. There is another table, called KUAFRightsList, that stores all groups for a user in a non-hierarchical format. However, this table cannot be trusted since it is built on-the-fly and just-in-time, meaning that it can be out of date for users who have not logged in for a while and will contain no entries for users who have not logged in at all.The child relationships in KUAFChildren are: (1) recursive, meaning that this table relates to itself, and (2) hierarchical, meaning that there is a parent-child relationship.The following examples use an Oracle SQL clause (START WITH ... CONNECT BY ...), that allows you to build queries on hierarchical relationships. In MS SQL, there is no such clause and you would have to write a custom procedure to do this. I don't have such a procedure. If any one else does, then please post it as a response to this Reply. Otherwise, I'll just have to write one some day.To select the user in question for these examples, I will use the LiveReport Input type of "User" that allows you to select a specific user ID by name. I will use parameter %1 as "User Input 1" to put the ID in an SQL query.Query 1:SELECT G.ID, G.NAME FROM KUAFChildren H, /* Rename to H for Hierarchy */ KUAF G /* Rename to G for Group */ WHERE H.ChildID = %1 AND /* The user you want */ H.ID = G.ID AND /* Relate H to G */ G.Type = 1 AND /* Group type */ G.Deleted = 0 /* Not deleted */ ORDER BY G.Name /* Sort by Name */This query gives you part of the answer. It only lists immediate parent groups for a user. It does not list grandparents, great-grandparents, etc. To do this you will have to "walk" up the hierarchy. I will use the Oracle hierarchy clause for this. However, this clause cannot be used in a multiple table query so Query 1 will have to be restated to a single table query with a subquery.Query 2:SELECT ID, NAME FROM KUAF WHERE Type = 1 AND /* Group type */ Deleted = 0 AND /* Not deleted */ ID IN ( /* Start of subquery */ SELECT ID FROM KUAFChildren WHERE ChildID = %1 /* The user you want */ ) /* End of subquery */ ORDER BY Name /* Sort by Name */ Now all we have to do is replace the subquery with an hierarchical query.Query 3:SELECT ID, NAME FROM KUAF WHERE Type = 1 AND /* Group type */ Deleted = 0 AND /* Not deleted */ ID IN ( /* Start of subquery */ SELECT ID FROM KUAFChildren START WITH /* Starting point */ ChildID = %1 /* The user you want */ CONNECT BY PRIOR ID = ChildID /* The hierarchical self relationship */ ) /* End of subquery */ ORDER BY Name /* Sort by Name */But we're not finished yet. There is a class of groups called roles that are special to Projects. These are named "Coordinators", "Members", and "Guests". The KUAF Type's for these groups are greater than 2000 and are actually the Project's DTree.DataID value. The following query will list these roles as well.Query 4:SELECT ID, NAME FROM KUAF WHERE (Type = 1 OR /* Group type */ Type >= 2000) AND /* Project role type */ Deleted = 0 AND /* Not deleted */ ID IN ( /* Start of subquery */ SELECT ID FROM KUAFChildren START WITH /* Starting point */ ChildID = %1 /* The user you want */ CONNECT BY PRIOR ID = ChildID /* The hierarchical self relationship */ ) /* End of subquery */ ORDER BY Name /* Sort by Name */ Of course, this query does not list the projects that the roles belong to. We can do this by adding project names to the list through an outer-join. This type of join recognizes that only some rows of one table are related to the other table.Query 5:SELECT G.ID, G.NAME "Group", /* Rename to Group */ DECODE(P.NAME, NULL, ' ', P.NAME) "Project" /* Change NULLs to blanks and rename to Project */ FROM KUAF G, /* Rename to G for Group */ DTree P /* Rename to P for Project */ WHERE (G.Type = 1 OR /* Group type */ G.Type >= 2000) AND /* Project role type */ G.Deleted = 0 AND /* Not deleted */ G.Type = P.DataID (+) AND /* Outer-Join to Projects */ G.ID IN ( /* Start of subquery */ SELECT ID FROM KUAFChildren START WITH /* Starting point */ ChildID = %1 /* The user you want */ CONNECT BY PRIOR ID = ChildID /* The hierarchical self relationship */ ) /* End of subquery */ ORDER BY G.Name, P.Name /* Sort by Names */There is one other type of group that are called Privilege Groups and have KUAF.Type equal to 4. For example, some Users have the special privilege to create LiveReports. I'll leave the extension of Query 5 to list Privilege Groups as an exercise for the reader.I have attached exports of LiveReports for the 5 queries.

Jeff_Morris

Great reports.One more request -- can you help me change the Project participation report to only show Projects for which I'm a 'member' or 'coordinator' of (not a guest).

Doug_Schreiber_(knopla01admin_-_(deleted))

How can I run the reports / have them run?The 5 lines in the attached files do not give me the data I need nor are they something I can paste into my LiveLink environment (b2bscene) and run.

Kevin_To

Great post! This was extremely helpful!