4 Replies Latest reply on Mar 17, 2017 8:50 AM by Jennifer Kelley

    Report on Categories

    tbernard@lenovo.com

      Jennifer Kelley Billy Volpone

       

      Is there a way to report on categories?   Views by date in categories?  Views by document in categories?

       

      Thank you.

        • Re: Report on Categories
          Jennifer Kelley

          Hi Trevor, Categories in places are really just a local filter that gets applied, so there isn't metadata that gets appended to content or that shows up in Reports.  If you want to discuss your reporting goals and options further, come back to a future "Ask a Consultant" event or consider posting in Advanced Customer Measurement

          • Re: Report on Categories
            Caton Guilbault

            Hi Trevor,

             

            If you are hosted or on-prem and have access to the database you can query for which Categories are applied to what content. This won't give you views by Category necessarily as a user could find a piece of content via search or UI elements besides a Category filter.  But we have found it helpful to make sure content is categorized correctly. If you want to try and figure out how often the Category filters are used you would probably have to add an event to those actions and track via something like Google Analytics.

             

            Postgre Queries for Categories: These can be changed to focus on any content type.

             

            List of Docs & count per Category

            select 
                tagset.name as Catagory,
                count(doc.internaldocid) as Doccount, 
                string_agg(doc.documentid, '| ') asDocIDs
            
            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
                AND doc.containerid in (3555,2744) -- Focuses on specific container
            group by tagset.name
            

             

             

             

            Categories for each Doc

            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
            order by doc.internaldocid asc
            
            
            1 person found this helpful