SQL Report in iReport

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.
Post Reply
dferguson
Expert Boarder
Expert Boarder
Posts: 107
Joined: Thu May 18, 2017 12:48 pm

SQL Report in iReport

Post by dferguson » Mon Apr 16, 2018 8:18 pm

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

lnovoa
Expert Boarder
Expert Boarder
Posts: 102
Joined: Tue Mar 27, 2018 8:00 am

Re: SQL Report in iReport

Post by lnovoa » Wed Apr 18, 2018 9:47 am

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

dferguson
Expert Boarder
Expert Boarder
Posts: 107
Joined: Thu May 18, 2017 12:48 pm

Re: SQL Report in iReport

Post by dferguson » Wed Apr 18, 2018 12:47 pm

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?

lnovoa
Expert Boarder
Expert Boarder
Posts: 102
Joined: Tue Mar 27, 2018 8:00 am

Re: SQL Report in iReport

Post by lnovoa » Thu Apr 19, 2018 7:40 am

Missing in the group by adding the NBS_NAME

...GROUP BY NDV_NAME, PSR_USER, NBS_NAME

dferguson
Expert Boarder
Expert Boarder
Posts: 107
Joined: Thu May 18, 2017 12:48 pm

Re: SQL Report in iReport

Post by dferguson » Thu Jul 12, 2018 1:31 pm

This worked exactly how I needed it to, thanks!

dferguson
Expert Boarder
Expert Boarder
Posts: 107
Joined: Thu May 18, 2017 12:48 pm

Re: SQL Report in iReport

Post by dferguson » Thu Jul 12, 2018 3:27 pm

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?

jllort
Moderator
Moderator
Posts: 10351
Joined: Fri Dec 21, 2007 11:23 am
Location: Sineu - ( Illes Balears ) - Spain
Contact:

Re: SQL Report in iReport

Post by jllort » Fri Jul 13, 2018 4:01 pm

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.

Post Reply

Who is online

Users browsing this forum: No registered users and 3 guests