Here are some knowledge base article reports I’ve created in my environment as well as some modifications that you could make to them.
Title: Knowledge Base Articles with rating less than 4
Description: Report will contain a list of KB articles with a rating less than 4. Note less than 4 doesn’t include 4. Ordered By KB Article Views
SQL:
SELECT ADVISORY.TITLE,
ADVISORY_RATINGS.RATING,
ADVISORY_RATINGS.PAGE_VIEWS,
ADVISORY_RATINGS.LAST_VIEW
FROM ADVISORY
LEFT JOIN ADVISORY_RATINGS on ADVISORY.ID = ADVISORY_RATINGS.ID
WHERE ADVISORY_RATINGS.RATING < 4
ORDER BY ADVISORY_RATINGS.PAGE_VIEWS
If you wish to see all KB articles Ordered By their Page Views simply remove the “WHERE ADVISORY_RATINGS.RATING < 4”. This will list all KB articles.
One could also modify the WHERE statement to focus on page views instead of user ratings of the article. The example below is the same report but the WHERE statement is looking for articles with less than 100 views. You can change or adjust this as you wish.
EX SQL:
SELECT ADVISORY.TITLE,
ADVISORY_RATINGS.RATING,
ADVISORY_RATINGS.PAGE_VIEWS,
ADVISORY_RATINGS.LAST_VIEW
FROM ADVISORY
LEFT JOIN ADVISORY_RATINGS on ADVISORY.ID = ADVISORY_RATINGS.ID
WHERE ADVISORY_RATINGS.PAGE_VIEWS < 100
ORDER BY ADVISORY_RATINGS.RATING
SELECT
ADVISORY.TITLE as 'KB Article', USER.FULL_NAME as 'User Name', ADVISORY_RATINGS.Page_Views as 'Total Views', ADVISORY_RATINGS.Rating as 'Rating (1-5)'
FROM
ORG1.ADVISORY_RATINGS
INNER JOIN
(ADVISORY, ORG1.USER) ON ADVISORY_RATINGS.ADVISORY_ID = ADVISORY.ID
AND ADVISORY_RATINGS.USER_ID = USER.ID - cblake 7 years ago