• SQL Report in iReport

  • We tried to make OpenKM as intuitive as possible, but an advice is always welcome.
We tried to make OpenKM as intuitive as possible, but an advice is always welcome.
Forum rules: Please, before asking something see the documentation wiki or use the search feature of the forum. And remember we don't have a crystal ball or mental readers, so if you post about an issue tell us which OpenKM are you using and also the browser and operating system version. For more info read How to Report Bugs Effectively.
 #45658  by dferguson
 
I have really struggled to get a filtered view of some SQL query results to put in a iReport for one of our users.

We are using the subscription feature AND the 'GET_DOCUMENT_CONTENT to verify that users have self-trained on required documents. Using iReport I built this query...
Code: Select all
SELECT
     OKM_ACTIVITY.`ACT_DATE` AS OKM_ACTIVITY_ACT_DATE,
     OKM_PROP_SUB_RECEIVED.`PSR_USER` AS OKM_PROP_SUB_RECEIVED_PSR_USER,
     OKM_NODE_BASE.`NBS_NAME` AS OKM_NODE_BASE_NBS_NAME,
     OKM_NODE_DOCUMENT_VERSION.`NDV_NAME` AS OKM_NODE_DOCUMENT_VERSION_NDV_NAME
FROM
     `OKM_PROP_SUB_RECEIVED` OKM_PROP_SUB_RECEIVED INNER JOIN `OKM_NODE_BASE` OKM_NODE_BASE ON OKM_PROP_SUB_RECEIVED.`PSR_NODE` = OKM_NODE_BASE.`NBS_UUID`
     INNER JOIN `OKM_NODE_DOCUMENT_VERSION` OKM_NODE_DOCUMENT_VERSION ON OKM_NODE_BASE.`NBS_UUID` = OKM_NODE_DOCUMENT_VERSION.`NDV_PARENT`
     INNER JOIN `OKM_ACTIVITY` OKM_ACTIVITY ON OKM_NODE_BASE.`NBS_UUID` = OKM_ACTIVITY.`ACT_ITEM`
WHERE
   PSR_FROM = 'jmcnaughton'
 AND ACT_ACTION = 'GET_DOCUMENT_CONTENT'
 AND NDV_CURRENT = 'T'

ORDER BY PSR_USER, NBS_NAME, NDV_NAME, ACT_DATE DESC
sql query.png
sql query.png (60.1 KiB) Viewed 5400 times
It does great, except it lists every time they have viewed the current version. I would like to filter by ACT_DATE to only show the last 'GET_DOCUMENT_CONTENT'.

Since this example joins so many tables it has been hard for me to use things like MAX(ACT_DATE). When I try to do that I get the following error
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'okmdb.OKM_PROP_SUB_RECEIVED.PSR_USER'; this is incompatible with sql_mode=only_full_group_by
I figure there has to be a simple solution to this, but I am very new to all this and it has eluded me so far. Any help would be greatly appreciated.

Doug
 #45669  by lnovoa
 
Hi!,
try this.

I hope it's what you need
Code: Select all
SELECT
     MAX(OKM_ACTIVITY.`ACT_DATE`) AS OKM_ACTIVITY_ACT_DATE,
     OKM_PROP_SUB_RECEIVED.`PSR_USER` AS OKM_PROP_SUB_RECEIVED_PSR_USER,
     OKM_NODE_BASE.`NBS_NAME` AS OKM_NODE_BASE_NBS_NAME,
     OKM_NODE_DOCUMENT_VERSION.`NDV_NAME` AS OKM_NODE_DOCUMENT_VERSION_NDV_NAME
FROM
     `OKM_PROP_SUB_RECEIVED` OKM_PROP_SUB_RECEIVED INNER JOIN `OKM_NODE_BASE` OKM_NODE_BASE ON OKM_PROP_SUB_RECEIVED.`PSR_NODE` = OKM_NODE_BASE.`NBS_UUID`
     INNER JOIN `OKM_NODE_DOCUMENT_VERSION` OKM_NODE_DOCUMENT_VERSION ON OKM_NODE_BASE.`NBS_UUID` = OKM_NODE_DOCUMENT_VERSION.`NDV_PARENT`
     INNER JOIN `OKM_ACTIVITY` OKM_ACTIVITY ON OKM_NODE_BASE.`NBS_UUID` = OKM_ACTIVITY.`ACT_ITEM`
WHERE
   PSR_FROM = 'jmcnaughton'
 AND ACT_ACTION = 'GET_DOCUMENT_CONTENT'
 AND NDV_CURRENT = 'T'
 
GROUP BY NDV_NAME, PSR_USER
ORDER BY PSR_USER, NBS_NAME, NDV_NAME DESC
 #45671  by dferguson
 
Thank for that, but I still get this error...
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'okmdb.OKM_NODE_BASE.NBS_NAME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Does anyone know if changing this global for the database would have adverse effect?
 #45677  by lnovoa
 
Missing in the group by adding the NBS_NAME

...GROUP BY NDV_NAME, PSR_USER, NBS_NAME
 #46312  by dferguson
 
Our quality director would like to see a history of all subscribed document versions (not just current = 'T') and when users looked at the document (ACT_ACTION = 'GET_DOCUMENT_CONTENT'). Obviously I'd have to identify the date the version was created and then find a single instance of the user getting the document content where the date is greater than the created date. For cases where there are multiple revisions of a document I would need to loop through all the 'GET_DOCUMENT_CONTENT' entries and find a single instance that fell within that revision's created date and the next revision's created date. I can think of how to do this in other programming languages, but can't think of way to SQL query this information and then get it into a iReport.

Ideas?

We are using subscriptions and 'GET_DOCUMENT_CONTENT' as our method to verify training on a document. I'm not sure why he would like to keep a training history of old revisions, but he asked. Is this the best way or am I missing something more obvious?
 #46319  by jllort
 
We have some customer with something similar, basically they have an ISO, and they distribute the document to several users. Then they want to get a report based on what specific document version and when the users reviewed the document. For it is better an specific customization rather trying to play only with current database data, what might be some pain on it.

About Us

OpenKM is part of the management software. A management software is a program that facilitates the accomplishment of administrative tasks. OpenKM is a document management system that allows you to manage business content and workflow in a more efficient way. Document managers guarantee data protection by establishing information security for business content.