Forum Discussion

madeline's avatar
madeline
New Contributor
12 years ago

Reporting Question for Code Collaborator

Hi ,


I have a request to report the grandparent/parent group id of a review which by the grandchild group id has been entered in the review.




Group structure Defintion



Product Engineering (PE) (Grand Parent- L1)

    Product Engineering (PE) Intergration (Parent –L2 )

            Product Engineering (PE) Custom Design(Child- L3)

            Product Engineering (PE) Porting (Child – L3)



Sample Data





















ID

Review Title

Group

GrandParent/Parent Group

Author Full Name

65387

Review 123

Product Engineering - Porting (L3)

Product Engineering (PE) (L1)

John


The groupgroups table allows me to find  parent id of the child but there is no way for me to get the grandparent info ( in this case it is Product Engineering PE


Based on what I checked in the code collaborator interface, the information can be accessed through the Groupancestry table. I have already tried with the prefix of username i.e ccgrp1. Groupancestry ,  it still says table or view not found. Do you know how can I get access to this table to get the child information?


Our administrator says this field is listed in the documentation as intentionally undocumented.  This means DO NOT USE this table. 


So what is the alternative available for me to get access to get the mapping of the grandparent/parent/child mapping without using the

Groupancestry table? Otherwise how to get the mapping information which involve multiple level?


Your kind suggestion and support is greatly appreciated.


Thanks,

Madeline

6 Replies

Sort By
  • rpaterson's avatar
    rpaterson
    Contributor
    12 years ago
    Hi Madeline,



    You've got the right idea - the information you want is in the GroupAncestry table.  The exact name of the table varies by your database back-end.  Just use your database's admin UI to inspect the schema - it's in there!



    The GroupAncestry table has two columns: ancestorId, descendantId.  Both of the columns store IDs of groups as listed in the GroupDescription table.  For the purposes of the GroupAncestry table every group is considered to be it's own ancestor and descendant.  In other words if all your groups were unrelated (the group hierarchy was "flat") there would be one row in the GroupAncestry table for every group.



    Examples:



    Find every group that is a descendant of an ancestor group id 12345 (note the result includes the ancestor group itself):

    select groupancestry_descendantid from groupancestry where groupancestry_ancestorid = 12345



    Find every group that is an ancestor of a descendant group ID 6789 (note the result incudes the descendant group itself):

    select groupancestry_ancestorid from groupancestry where groupancestry_descendantid = 6789



    Regards,

    Roy
  • madeline's avatar
    madeline
    New Contributor
    12 years ago
    Hi Roy,



    Thank you for the response. It is great to know I am on the right track.



    Even though  the CC Admin thinks I should be able to use the ADMIN UI from the account he already gave me, I still didn't  see the database admin UI while the account given to us to access directly the CC database didn’t allow us to login to access to the Code Collaborator tool.





    Our CC admin refuses to give me  the main admin account as that would allow me to edit the database as well which would not be safe.  I told him we don’t need access to the main admin as long as I can get help to find out the table name needed for the reporting requirement given by my users.



    Right now, I have accessing the CC Database through SQL developer. Is there way for me to inspect the schema and find out the exact name of the table which varies by database back end directly through SQL interface instead of your Admin UI?  I am attaching you a screenshot of some of the tables I can see today.



    In the CC database I also didn’t find some tables such as user (Which can be used to read the creator name), phase (Which can be used to retrieve the phase id description Ex: phase id 5 = Completed), comment which are listed in the CC documentation link.





    I would appreciate if you can help and  let us know where we can find these tables so that we can enable the CC reporting requirements based on our users specific business requirement.






    Thanks,

    Madeline








  • rpaterson's avatar
    rpaterson
    Contributor
    12 years ago
    Hi Madeline,



    In your database the user table is called "collabuser" - we can't use the name "user" because it is a keyword for your database implementation.  The group ancestry table is called "grpncstry".  Phase definitions are not in a table - they're in a view.  The view should be called "phase".



    Regards,

    Roy
  • madeline's avatar
    madeline
    New Contributor
    12 years ago
    Hi Roy,



    I tried the following SQL statement with the name you suggested and it still returns the error table or view not found. Any idea?



    SELECT * FROM ccgrp1.grpncstry;





    Also I don't see the phase view in my database. How can I get access to it?



    Thanks,

    Madeline
  • rpaterson's avatar
    rpaterson
    Contributor
    12 years ago
    Hi Madeline,



    Sorry I made a mistake earlier - the table should be named "groupancestry".  If that doesn't work for you then either the table is missing or perhaps you don't have access to it?



    It seems unlikely the table is missing - that would cause lots of errors on your server.  To verify have your admin run our "Verify Database Schema" diagnostic by going to "Admin"->"System Status"->"Debugging"->"Diagnostic Utility"->"Verify Database Schema" and send you the results.



    The phase view is definitely named "phase".  If you can't see it then you may not have access to it - contact your database administrator.



    Regards,

    Roy
  • madeline's avatar
    madeline
    New Contributor
    12 years ago
    Hi Roy,



    Thanks for the update. We finally got our CC administrator to grant us the access of the groupancestry tables and phase view. We have reviewed and confirmed that we have access to all the tables in CC database that are required for our report data extraction.



    Thank you so much for your kind support.



    Greatly appreciated,

    Madeline