How do I report on count of knowledge articles by category?

Shelly Easley
Mega Contributor

‎12-12-2017 12:46 PM

I'm trying to create a report that shows ALL of my Knowledge categories with a count of the number of articles within each category. I'm using this to monitor the usage of the categories and remove or consolidate categories that have few or no articles within them.

I have been able to see all articles with their associated categories, a list of categories and their number of updates (but not number of articles with this category), view counts, use counts, etc. But I just need a simple report that shows:

- Knowledgebase

- Parent ID (would be helpful but not mandatory)

- Category (Preferably "Full Category" but just "Label" would work)

- Count or Number of articles within this category

Anyone have any ideas?

Labels:
0 Helpfuls
  • 4,382 Views
12 REPLIES 12

Josh Petrick
Kilo Sage

‎12-07-2018 09:15 AM

If you use the new report creator you can find categories with no articles by using a Related List Condition.  I think you'd have to run a separate report to get this info, but it's better than exporting to Excel and using vlookup.

find_real_file.png

Preview file
35 KB

KSpencer
Giga Contributor

‎02-13-2019 09:18 AM

To show categories with 0% you need to set the Max number of groups on the configure tab to "Show all"

 

find_real_file.png

Preview file
19 KB

ytrottier
Tera Contributor

‎01-05-2021 02:01 PM

Here is a script I did to provide the information you were looking for.

If it can be useful.

var tc = 0
var tcgr = new GlideRecord('kb_category')
tcgr.addQuery('parent_table=kb_knowledge_base^parent_id=05ff44289f011200550bf7b6077fcfa3') // Customer Service KB
tcgr.query()
while (tcgr.next()) {
    var acga = new GlideAggregate('kb_knowledge')
    acga.addQuery('kb_category', tcgr.sys_id)
    acga.addAggregate("COUNT")
    acga.groupBy('workflow_state')
    acga.query()
    var asbd = ''
    while (acga.next()) {
        var csep = ', '
        if (asbd == '')
            csep = ''
        asbd += csep + acga.workflow_state.getDisplayValue() + ' = ' + acga.getAggregate("COUNT")
    }

    if (acga.getTotal("COUNT") != 0)
        asbd = ' (' + asbd + ')'
    gs.print(tcgr.label + ' '.repeat(40 - tcgr.label.toString().length) + acga.getTotal("COUNT") + asbd)
    tc++

    getChildCat(tcgr.sys_id, 1)
    gs.print('')
}

gs.print(tc + ' Categories')

function getChildCat(parent, level) {
    var ccgr = new GlideRecord('kb_category')
    ccgr.addQuery('parent_table=kb_category^parent_id=' + parent)
    ccgr.query()
    while (ccgr.next()) {
        var acga = new GlideAggregate('kb_knowledge')
        acga.addQuery('kb_category', ccgr.sys_id)
        acga.addAggregate("COUNT")
        acga.groupBy('workflow_state')
        acga.query()
        var asbd = ''
        while (acga.next()) {
            var csep = ', '
            if (asbd == '')
                csep = ''
            asbd += csep + acga.workflow_state.getDisplayValue() + ' = ' + acga.getAggregate("COUNT")
        }

        if (acga.getTotal("COUNT") != 0)
            asbd = ' (' + asbd + ')'
        gs.print('-'.repeat(level) + ' ' + ccgr.label + ' '.repeat(40 - ccgr.label.toString().length - level - 1) + acga.getTotal("COUNT") + asbd)
        tc++

        getChildCat(ccgr.sys_id, level + 1)
    }
}
0 Helpfuls