4 Replies Latest reply on Jun 1, 2015 2:04 PM by NCam

    How can I create a report with document IDs, document titles, and each document's tag(s)?

    ccarmichael

      I would like to create a report with document IDs, document titles, and a list of all the document's tags.  Does anyone know how to do this?

       

      In the system and analytics database (from JCA database extracts), the tables with "tag" in the table name do not indicate document or content ID, which is making it very difficult for me to surface the tags.

       

      If I can do this using the community manager reports, or data export service, that would be fine too.

       

      Any pointers would be much appreciated.

       

      Thanks,

      Curtis

        • Re: How can I create a report with document IDs, document titles, and each document's tag(s)?
          Caton Guilbault

          Hi Curtis,

           

          We use two separate queries for this information and combine with other internal data sources to provide a content inventory.

           

          Document Table Query:

          select 
              doc.internaldocid, 
              replace(docversion.title,',','') as title, 
              doc.userid as creatorID, 
              person.username as OriginalAuthor,
              doc.documentid,
              doc.containertype,
              objtype.code,
              doc.containerid, 
              community.name,
              to_timestamp(doc.creationdate/1000) as creationdate, 
              to_timestamp(docversion.modificationdate/1000) as Modificationdate,
              to_timestamp(doc.expirationdate/1000) as expirationdate,
              doc.recommended,
              doc.meanrating,
              doc.readcount,
              doc.commentstatus,
              doc.versionid
          from jivedocument doc
          left join jiveuser person on person.userid = doc.userid
          left join jiveobjecttype objtype on objtype.typeid = doc.containertype
          left join jivecommunity community on community.communityid = doc.containerid
          left join jivedocversion docversion on docversion.internaldocid = doc.internaldocid
          where 
              docversion.state = 'published' -- constrains to viewable docs 
              and objtype.code = 'community' -- constrainsts to docs in spaces 
              
          order by internaldocid asc
          

           

          All Tags for Each Doc Query:

          select jiveobjecttag.objectid, string_agg(jivetag.tagname, '| ')
          from jiveobjecttag
          left join jivetag on jivetag.tagid=jiveobjecttag.tagid
          where jiveobjecttag.objecttype=102 --constrains to tags on docs
          group by jiveobjecttag.objectid
          

           

           

          You also might want to think about adding in the Categories for each doc if you leverage them for content filtering or grouping.

           

          Categories for Each Doc Query:

          select 
              doc.internaldocid, 
              string_agg (replace(tagset.name,',',''), ' | ') as Categories
          from jivedocument doc
          left join jiveobjecttype objtype on objtype.typeid = doc.containertype
          left join jivecommunity community on community.communityid = doc.containerid
          left join jivedocversion docversion on docversion.internaldocid = doc.internaldocid
          left join jiveobjecttagset objtagset on objtagset.objectid = doc.internaldocid
          left join jivetagset tagset on tagset.tagsetid = objtagset.tagsetid
          where 
              docversion.state = 'published' -- constrains to viewable docs
              and objtype.code = 'community' -- constrainsts to docs in spaces 
              and objtagset.objecttype = '102' -- constrainsts to doc object
              
          group by doc.internaldocid
          

           

           

          I hope that helps and let me know if you figure out a way to tie all this together into one query. I've tried a couple of times but keep running into conflicting constraints that end up excluding the subset of docs that don't have a category or a tag.