06-15-2023 06:42 PM
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:
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?
06-16-2023 05:09 AM
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.
06-16-2023 11:29 AM
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)
Documents workflow transaction query (hyland.com)
------
Alternative solutions
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.