LiveReport- Find Project(s) Name from Group or Users Name-ID#

1. Does anyone have an SQL LiveReport to identify the respective "Project Name" that a User belongs to, if you know the User's "Coordinator" &/or "Member" &/or "Guest" ID Number? 2. Also, an SQL LiveReport that identifies what "Projects" that a "User" or "Group (Individual Group Name, Coordinator, Member, Guest)" belongs to? We have LL Ver. 8.1.5 and use Oracle...Thanks in advanced for your assistance with this...Mike Higgins

Find more posts tagged with

Comments

Robert_Davies_(unlondonadmin_-_(deleted))

Dear MichaelI found the reply below marked with asterisks today. I am not sure if it addresses your problem but I have had to adapt it any way for Oracle (I think this person is using MySQL). This does not use their numeric ids. and I am not sure if it addresses your group requirements. Here is my adaptation:SELECT DTree.Name "Project",KUAF1.Name "Participant",KUAF.Name "Role" FROM KUAF,DTree,KUAFChildren,KUAF KUAF1 WHERE (KUAF.Type = DTree.DataID(+) and KUAF.ID = KUAFChildren.ID(+) and KUAFChildren.ChildID = KUAF1.ID (+)) and ( DTree.Name LIKE %1 AND KUAF.Name LIKE %3 AND KUAF1.Name LIKE %2 ) ORDER BY DTree.NameWhat I liked about the version below is allowing users to leave a blank prompt and within the SQL put in the double %%. But I was unable to do this (at least in the time available to me today). The user prompts and user inputs are as they are specified below. The only thing I have found that I don't like is that it does not tell you who the 'parent coordinators/members/guests' are.Here is the answer I used as a basis for the above report:******Yes - I don't like LiveReports either but it works and I can do just about anything with it. Here is a freeby. If I get feedback, I will put more online. I know many tricks. This LiveReport lets you query on project participants by prompting you for 1) Project Name 2) Participant and 3) Role. All prompts are optional. SELECT DTree.Name AS [Project], KUAF1.Name [Participant], KUAF.Name [Role] FROM KUAF INNER JOIN DTree ON KUAF.Type = DTree.DataID INNER JOIN KUAFChildren ON KUAF.ID = KUAFChildren.ID INNER JOIN KUAF KUAF1 ON KUAFChildren.ChildID = KUAF1.ID WHERE ( DTree.Name LIKE %1 + '%%' AND KUAF.Name LIKE %3 + '%%' AND KUAF1.Name LIKE %2 + '%%') ORDER BY DTree.Name Notice I am using "%%" for appending a wildcard to the end of the text string saving the user from entering in a wild card. Remember, if you are cut/pasting this add a space after you paste the string into the LiveReport after "where" and "from" (I never write LR inside of LR - more on this later....) Set the Prompt sequence: String Project Name String User (login name) String Role (Enter Role (Coordinator, Member, or Guests) Above SQL User Input1 User Input2 User Input3 Auto LiveReport Easy Easy Easy - You just need someone to show you the ropes.

Robert_Davies_(unlondonadmin_-_(deleted))

Dear MichaelThe SQL I gave you had (+) outer joins - remove them as they are not needed.I noticed someone else had a similar query and the reply they got was for a 'Livereport' not an 'AutoLiverport' so here you are - I am going to try it tomorrow and see what results it gives:Project Participants=A<1,?,'actions'={},'conf_names'='','conf_values'='','displaytype'='autoreport','fields'={},'headers'={},'inputs'={A<1,?,'inputType'='Project','label'='inputLabel1','prompt'='Select Project'>},'oldDataId'=27692663,'params'={'USERINPUT1',?,?,?,?,?,?,?,?,?},'recordlimit'=100,'sql'='select b.name type, d.name name from dtreeacl a, kuaf b, kuafchildren c, kuaf d where dataid = %1 and a.rightid = b.id and b.id = c.id and d.id = c.childid','subreport_id'=?,'subreport_parms'={},'subreport_path'='','title'='Project Participants'>

Michael_Higgins_(mhiggins_-_(deleted))

Anne, Thanks for your input. As soon as I resurface for air,I will try out your SQL & LR statements.I adapted your SQL statment dated 2/12/01 and found it to be very useful. I really like the idea of leaving the "...blank prompt and within the SQL put in the double %%...".Again, Thanks,Mike Higgins

Simon_Beal

Thanks Anne for your code it was Very Very useful, I wasn't sure where to start. I made a few adjustments to it thought so that the report was a little easier for people to use and understand. All it does is add the Wildcards and sort the report by Project and the User Name.Thanks again,SeanSELECT DTree.Name "Project",KUAF1.Name "Participant",KUAF.Name "Role" FROM KUAF,DTree,KUAFChildren,KUAF KUAF1 WHERE (KUAF.Type = DTree.DataID and KUAF.ID = KUAFChildren.ID and KUAFChildren.ChildID = KUAF1.ID) and ( DTree.Name LIKE %1 or DTree.Name LIKE '%%') AND (KUAF.Name LIKE %3 or KUAF.Name LIKE '%%') AND (KUAF1.Name LIKE %2 or KUAF1.Name LIKE '%%') ORDER BY DTree.Name, KUAF1.Name