Introduction:
You have been tasked with creating a report related to your ServiceNow Knowledge Base. The operational report includes some specific KPIs related to its health and usage.
For this task, we are going to walk through how to use VividSheets to prepare the data, pulling from and working directly within your ServiceNow instance.
Part One: Know your requirements
Here is a list of 14 KPIs that your stakeholders expect to see in this report, broken into three categories:
Status of knowledge base articles
How many published articles are in the knowledge base?
How many articles are currently in draft?
How many articles are currently in review?
How many articles have been retired?
How many categories are used in the knowledge base?
Usage of articles over the past 7 days?
How many articles were used?
What percent of all articles were used?
How many times was an article used?
How many articles were viewed?
What percent of all articles were viewed?
How many times was an article viewed?
How often have articles been used with tasks over the past 7 days?
How many incidents had an article attached?
How many change requests had an article attached?
How many problems had an article attached?
A few highlights regarding these metrics and the tables required to get the data.
kb_knowledge
The table that will have all of the articles created, which also includes the 'workflow_state' field, allowing you to get the article's status. It will also provide the 'kb_category' field to understand the number of categories being used.
kb_use
This table has the information you'll need to get the values for 'used' and 'viewed' articles.
m2m_kb_task
This table will allow us to see which tasks have had an article attached to them, what article was attached, and when it was attached.
Part Two: Create and stage your VividSheet
You will need to have the VividSheets application installed in your instance
You will also need the VividSheets builder role
Step 1 - Go to the ServiceNow Navigator and search for 'VividSheets', click on 'VividSheets Home' once found.
Step 2 - Create your sheet by clicking on 'Create a New Sheet'
Step 3 - Click on the 'New Blank Sheet' option
Step 4 - Update the sheet's title so that it's easy to find, plus provides information about the purpose of the sheet.
Step 5 - Add headers and organize our sheet for all the KPIs you are going to create.
The example organizes all the KPIs defined in 'Part One: Know your requirements' from above.
Step 6 - Stage the information you will need within your functions. This includes any table you will need, along with input used to create your query.
One of the best ways to get the query information is to use the condition builder on the list view of your ServiceNow table, and then copy the query. Let's walk through this.
In this step I needed the query information that allows me to get data from the last 7 days, so I went into the 'kb_use' table, used the condition builder to get records that were created on the last 7 days and then copied the query. Here was the result:
sys_created_onONLast 7 days@javascript:gs.beginningOfLast7Days()@javascript:gs.endOfLast7Days()
I pasted that into cell J5, so that I can reuse this information in several of my VividSheets functions, via cell referencing.
I did a similar process to get the values to filter for incidents, change requests, and problem tasks. While those values will only be used one time, I wanted to be sure the result of those queries was what I expected.
Part Three: Build your KPIs using the VividSheets functions
Status of knowledge base article KPIs
How many published articles are in the knowledge base?
In cell A4, use the VIVIDCOUNT function
=VIVIDCOUNT($J$3, workflow_state=published )
How many articles are currently in draft?
How many articles are currently in review?
How many articles have been retired?
In cells B4, C4 and D4 the table is the same, the only change is the 'workflow_state' value, so I can easily copy/paste the above function to those cells and edit accordingly.
=VIVIDCOUNT($J$3,workflow_state=draft)
=VIVIDCOUNT($J$3,workflow_state=review)
=VIVIDCOUNT($J$3,workflow_state=retired)
How many categories are used in the knowledge base?
This question is a little different than the previous questions, as I do not want a total count, I only want the number of unique categories we have in our knowledge base. For this, I can use the VIVIDCOUNTDIST function, in cell E4.
=VIVIDCOUNTDIST($J$3, active=true, kb_category)
After adding this information, I can use the 'Collect Data' button in the upper right corner. If I've correctly added the information, the values should appear in each cell.
Now we are going to follow a similar pattern for the next two categories of KPIs, however, our function will be referencing multiple cells.
Usage of articles over the past 7 days
How many articles were used?
To answer this question we will be using the 'kb_use' table, where the record was created on the last 7 days, plus the 'used' field needs to be 'true'. The last piece to this question is that we only want to count an article once, in case it was used multiple times.
We can achieve this with the VIVIDCOUNTDIST function again, in this way:
=VIVIDCOUNTDIST($J$1, `used=true^{{$J$5}}`, article)
The main lesson to learn here is that the query value requires multiple pieces of information, including the 'created on last 7 days' query which we inserted into cell J5. Using the backticks allows us to build that query, along with the double brackets.
Read more on VividSheets function syntax here if you need it.
What percent of all articles were used?
Here is our first requirement that doesn't require any new data to be pulled in, we simply need to do a calculation with what we already have. Specifically, we need to take the count from the 'How many articles were used?' KPI and divide it by the total number of published articles. The DIVIDE function does this easily for you.
=DIVIDE(A9, A4)
How many times was an article used?
A similar question to the one we saw earlier, but this time we want a total count. This includes everytime an article was used, even if the article was used multiple times. We can use the same information we did before, but this time remove the 'count distinct' function and just count them all.
=VIVIDCOUNT($J$1, `used=true^{{$J$5}}`)
The next three follow the same logic and patterns, the only difference is that we will change the 'used=true' parameter to 'viewed=true'.
How many articles were viewed?
What percent of all articles were viewed?
How many times was an article viewed?
=VIVIDCOUNTDIST($J$1, `viewed=true^{{$J$5}}`, article)
=DIVIDE(D9, A4)
=VIVIDCOUNT($J$1, `viewed=true^{{$J$5}}`)
This is what my sheet now looks like after I ran the 'Collect Data' again.
I didn't format the percentage columns, so let's quickly fix that by formatting those.
We have one category of KPIs remaining.
How often have articles been used with tasks over the past 7 days?
All three of these KPIs are very similar, we will simply want to filter our result based upon the type of task an article was attached to. We also need to use the 'm2m_kb_task' table.
How many incidents had an article attached?
How many change requests had an article attached?
How many problems had an article attached?
Here are what those VIVIDCOUNT functions will look like.
=VIVIDCOUNT($J$2, `{{$J6}}^{{$J$5}}`) --> Incident tasksNotice how the query inside the backticks has multiple cell references. This is simply building the query from the information we added during the staging step above. This also makes it easy to get my other KPIs. I will just copy/paste and update the cell that references the type of task we are looking for.
=VIVIDCOUNT($J$2,`{{$J7}}^{{$J$5}}`) --> Change request tasks
=VIVIDCOUNT($J$2,`{{$J8}}^{{$J$5}}`) --> Problem tasks
After running the 'Collect Data' action for a final time, my sheet looks like this:
Part Four: Use your sheet for reporting
Congrats! You now have a VividSheet set up to collect your Knowledge Base KPIs when you need them.