01-31-2019 07:54 AM
So I am trying to acquire some data regarding our usage of Document Types in a specific Document Type Group and the usage of the keywords attached to all those document types. So here are the details of what I would like to do: (NOTE: The first two steps reports are done in the Report Services client.)
I have looked through the Database Report Guide, but due to the fact that we don't have our keywords setup right now in Keyword Type Groups (which would make it so much easier) it makes it hard to create a report on this. So right now our keywords are loose keywords which does make them potential multi-instance (and we definitely try to remove them when we find them).
So has anyone created a report like this or had experience doing so?
Thanks.
01-31-2019 01:21 PM
Here you go:
SELECT
hsi.itemtypegroup.itemtypegroupnum AS [Doc Type Group #],
hsi.itemtypegroup.itemtypegroupname AS [Document Type Group],
hsi.doctype.itemtypenum AS [Doc Type #],
hsi.doctype.itemtypename AS [Document Type],
hsi.keytypetable.keytypenum AS [Keyword #],
hsi.keytypetable.keytype AS [Keyword]
FROM
hsi.keytypetable INNER JOIN
hsi.itemtypexkeyword ON hsi.keytypetable.keytypenum = hsi.itemtypexkeyword.keytypenum INNER JOIN
hsi.itemtypegroup INNER JOIN
hsi.doctype ON hsi.itemtypegroup.itemtypegroupnum = hsi.doctype.itemtypegroupnum ON hsi.itemtypexkeyword.itemtypenum = hsi.doctype.itemtypenum
ORDER BY
[Document Type Group],
[Document Type],
[Keyword]
01-31-2019 01:32 PM
Sorry I kind of ignored #4 in my answer because I'm not exactly sure what data you want.
But this is what I use (sorry, it's kind of ugly), to tell me the Doc Type Group, Doc Type and a specific keyword's value and how many times that value has been used.
You'd have to use the specific keytable number from the results you get in the above:
SELECT
hsi.itemtypegroup.itemtypegroupname AS [Document Type Group],
hsi.doctype.itemtypename AS [Document Type],
hsi.keytableXXX.keyvaluechar AS [Comments Keyword],
COUNT(hsi.keytableXXX.keyvaluechar) AS [Comment Count]
--hsi.itemdata.itemnum AS [Document Handle]
FROM
hsi.itemtypegroup INNER JOIN
hsi.doctype ON hsi.itemtypegroup.itemtypegroupnum = hsi.doctype.itemtypegroupnum INNER JOIN
hsi.itemdata ON hsi.doctype.itemtypenum = hsi.itemdata.itemtypenum INNER JOIN
hsi.keyxitemXXX ON hsi.itemdata.itemnum = hsi.keyxitemXXX.itemnum INNER JOIN
hsi.keytableXXX ON hsi.keyxitemXXX.keywordnum = hsi.keytableXXX.keywordnum
WHERE
-- you can specify document type here, or comment out the whole WHERE clause to pull this back for anything with that keyword #
(hsi.doctype.itemtypenum = '')
GROUP BY
hsi.itemtypegroup.itemtypegroupname,
hsi.doctype.itemtypename,
hsi.keytable148.keyvaluechar
--hsi.itemdata.itemnum
ORDER BY
[Comment Count] DESC
Find what you came for
We want to make your experience in Hyland Connect as valuable as possible, so we put together some helpful links.