Need LiveReport to display Department of User

I have the following LiveReport that I can use to list the names of all users within a group, but I also want to display the Department Name for each user. What do I need to add to this to be able to have the Department Name display? Thanks...

select unique kuaf.name "User ID", kuaf.firstname || ' ' || kuaf.lastname "User Name", kuaf.mailaddress "eMail", kuaf.contact "Contact" from kuaf, (select childid from kuafchildren start with id in (select kuaf.id from kuaf, dtree where lower(kuaf.name) = lower(%1) and kuaf.type = dtree.dataid (+) and (lower(dtree.name) = lower(%2) or dtree.name is null)) connect by ID = PRIOR ChildID) a where kuaf.id = a.childid and kuaf.type = 0 order by 2, 1

Find more posts tagged with

Comments

Bhupinder_Singh

From what I recall, GroupID in KUAF refers to the user's department.

Here is some SQL I once put together in response to the question "How do I list all active users and their departments?". "Active users" in this case meant those who have the "Login Enabled" privilege which is recorded as part of a bitmask in the UserPrivileges column in the KUAF table of the Livelink database. A value of 15 is "added" to the bitmask when this privilege is granted to users, and this is what the SQL checks for.

select a.name, a.lastname, a.firstname, b.name "Department", a.userprivileges from kuaf a, kuaf b where bitand(a.userprivileges, 15) = 15 and a.type=0 and a.groupid=b.id

- Bhupinder
----------------------------------------------
Bhupinder Singh, B.Math, B.Ed.
Systems Administrator, Cloud Services
Open Text, Waterloo, Ontario, Canada
----------------------------------------------

Karen_Morley

Thanks, Bhupinder -- this helps me get a little closer -- my original report gives me everything I need except does not show the department. I am trying to take what you have provided and add to my sql but no luck. I need to be able to select the group I want to view all the users for. I tried modifying your recommendation to include user inputs but it won't allow for me to input the name of the Group I want.

Bhupinder_Singh

The ability to select a group for a user input variable is a known limitation of the product. For details, and a workaround, please see my post and the subsequent replies in this thread:

https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objId=7646012&objAction=viewincontainer&ShowReplyEntry=7637720

- Bhupinder

Bhupinder_Singh

One more note: a colleague indicated that if you have third-party WebReports module, then "When defining a parameter you can decide whether a user is allowed to browse for users, groups or both."

- Bhupinder

Karen_Morley

Thanks,Bhupinder -- I don't have Web Reports. The current report I have (the sql statements is in the first thread) does allow me to select a group that I want to run the report on -- the only thing I have not been able to figure out is how to display the department field for each user; I get their login, name and email address, etc.

Attached is screenshot of inputs, etc.

UsersinGroupReport.png