1 Reply Latest reply on Jul 12, 2016 11:15 AM by Caton Guilbault

    Reporting and Analytics

    mrobinson

      I was asked today for reporting on items that have been tagged with a certain tag: "compliance"

      and any related activity to these documents and groups.  Example:  They want to know how many likes and unlikes items with that tag have.

       

      I know I can get the act of tagging and untagging, but not sure if the other is possible.

       

      Thanks!

       

      Susan Rubio

        • Re: Reporting and Analytics
          Caton Guilbault

          Hi Mike,

           

          If you have DB access I think this query will fit your needs. You can modify the constraints to focus on the various object types.

           

          select 
              doc.internaldocid, 
              docversion.title, 
              tag.tagname,
              doc.containertype,
              objtype.code,
              doc.containerid, 
              community.name,
              to_timestamp(doc.creationdate/1000) as CreationDate,
              to_timestamp(docversion.modificationdate/1000) as ModificationDate,
              doc.recommended,
              doc.meanrating,
              sum(objlike.score)as TotalLikes
              
          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 jiveobjecttag objtag on objtag.objectid = doc.internaldocid
          left join jiveacclaim objlike on objlike.objectid = doc.internaldocid
          left join jivetag tag on tag.tagid = objtag.tagid
          where 
              docversion.state = 'published' 
              and objtype.code = 'community' 
              and objtag.objecttype = '102' 
              and tag.tagname ilike '%compliance%' --Tag Filter - remove the % for exact matches
              
          Group By
              doc.internaldocid, 
              docversion.title, 
              tag.tagname,
              doc.containertype,
              objtype.code,
              doc.containerid, 
              community.name,
              doc.creationdate,
              docversion.modificationdate,
              doc.recommended,
              doc.meanrating
          

           

          I hope that helps.

          1 person found this helpful