Report help on specific DTG regarding they keywords and their usage

[Ryan_Wakefield]

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.)

1. Query to get all of the Document Types in a specific Document Type Group. (Easily done and already done.)
2. Query to get all of the Keyword Types attached to those Document Types returned in step 1. (Done as well.)
3. Query to get all of the keywords values for those Keyword Types that are attached to those Document Types. (Need help here.)
4. Be able to take all of the data returned in step 3 and put in to Excel to give a visual/data representation of each keyword and how many times it is filled in and how many times it is not.

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.

[Jonna_Pantelis]

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]

[Jonna_Pantelis]

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