Hyland Connect

Denis_Hands · ‎06-15-2023

Hi All,

I was wondering if anyone out there had any SQL queries for the following as we are having a little trouble getting something that fits our requirements:

The top 50 documents viewed while in a specific workflow
The top 50 documents keywords revised while in a specific workflow - documents are flat emails that won't be revised
Unrelated to the 1st two queries - Number of items in a queue at a specific date (and if possible, the actual items)

Essentially what we are looking to create is a simple dashboard that contains a list of documents that are high touch but not actually progressing/exiting a specific workflow

Are there any specific places in community that have a structured query library as such where I can search for such things?

Juan_Trevino · ‎06-16-2023

So this may be a bit tricky with hsi.transactionxlog queries given the number of documents it will have to traverse to get the data you want. So the first question I would ask is what is the business value we are adding by running this query? I am all in favor of pushing back on the business a bit if something does not make a lot of sense. What I am saying is that these queries may be taxing on your servers - so you need to justify the business value before executing.

But if the business is adamant about this I would refer to the OnBase Database Reporting guide as it gives you the general schema of the tables to query which should help you build out this report. I do not believe this is in the MRGs but should be available here on Community or provided by your FLOS.

Eric_Beavers · ‎06-16-2023

I don't have an answer here, but I did want to comment as I have had to research this in the past.

I think you are going to have to invent/innovate these very specific requirements. The warning about the performance hit messing with the transaction log tables is still really important too! This feels like one of those cases where a snapshot of the database has to be taken so a reporting version can be set up without affecting end users.

There are some good discussions on the "Database Technical Community" that may have some of the building blocks.

The 2013 discussion document viewed within date range of being created (hyland.com) might be a good starting point.

Other Doc Type Transation examples:

Is there a SQL for generating a Document Audit Report? (hyland.com)

SQL for User Transaction History (hyland.com)

This 2019 discussion on Workflow could help too..

sql to get document IN workflow (hyland.com)

Other Workflow gems:

SQL Query for hsi.itemlc does not appear to include unity lifecycles (hyland.com)

Query for docs in workflow (hyland.com)

Workflow Log (hyland.com)

Documents workflow transaction query (hyland.com)