Difference between revisions of "Reports"
(→SQL Report) |
(→Script Report) |
||
| Line 29: | Line 29: | ||
== Script Report == | == Script Report == | ||
| + | Other times, the data is not extracted from a database but another source. In this case we will query the OpenKM API to obtain a list of registered users. | ||
| + | |||
| + | <source lang="sql"> | ||
| + | import com.openkm.dao.*; | ||
| + | import com.openkm.dao.bean.*; | ||
| + | |||
| + | List al = new ArrayList(); | ||
| + | for (User user : AuthDAO.findAllUsers(false)) { | ||
| + | Map usr = new HashMap(); | ||
| + | usr.put("id", user.getId()); | ||
| + | usr.put("name", user.getName()); | ||
| + | usr.put("email", user.getEmail()); | ||
| + | usr.put("roles", user.getRoles().toString()); | ||
| + | al.add(usr); | ||
| + | } | ||
| + | |||
| + | return al; | ||
| + | </source> | ||
| + | |||
| + | This means that not only database-managed user are show but also if you have configured OpenKM to use an external LDAP server to handle user management. | ||
| + | |||
| + | Full report is available at [[File:RegisteredUsers.jrxml]]. | ||
| + | |||
== XPath Report == | == XPath Report == | ||
If you want to create a report which uses an XPath query, use scripting to do the trick. This code will query for locked documents: | If you want to create a report which uses an XPath query, use scripting to do the trick. This code will query for locked documents: | ||
Revision as of 15:13, 25 January 2011
In report section you can create and use your own Jasper Reports. Reports are generated in PDF format. Starting from OpenKM 5.1 other formats can be generated.
Add new report
Then make click 
new report icon
Report can use internally Scripting or SQL. The file to uploading must be .jrxml JasperReport source file. Starting with OpenKM 5.1 you can also upload compiled reports (files with .jasper extension).
SQL Report
Usually you have a database and want to generate a report extracting data from it. In this case you should know the table relationship. Tools like Aqua Data Studio can help you to create a ER diagram. In this example, will query the database to generate a report with information related to workflow execution:
select ti.actorid_ as ti_actorid, pd.name_ as pd_name, ti.name_ as ti_name, ti.start_ as ti_start, ti.end_ as ti_end
from JBPM_TASKINSTANCE as ti, JBPM_PROCESSINSTANCE as pi, JBPM_PROCESSDEFINITION as pd
where ti.procinst_ = pi.id_ and pi.processdefinition_ = pd.id_
order by ti_actorid, pd_name, ti_name, ti_start, ti_end
Full report is available at File:WorkflowWorkload.jrxml.
Script Report
Other times, the data is not extracted from a database but another source. In this case we will query the OpenKM API to obtain a list of registered users.
import com.openkm.dao.*;
import com.openkm.dao.bean.*;
List al = new ArrayList();
for (User user : AuthDAO.findAllUsers(false)) {
Map usr = new HashMap();
usr.put("id", user.getId());
usr.put("name", user.getName());
usr.put("email", user.getEmail());
usr.put("roles", user.getRoles().toString());
al.add(usr);
}
return al;
This means that not only database-managed user are show but also if you have configured OpenKM to use an external LDAP server to handle user management.
Full report is available at File:RegisteredUsers.jrxml.
XPath Report
If you want to create a report which uses an XPath query, use scripting to do the trick. This code will query for locked documents:
import javax.jcr.*;
import javax.jcr.query.*;
import org.apache.jackrabbit.*;
import com.openkm.module.direct.*;
List al = new ArrayList ();
String statement = "/jcr:root/okm:root//element(*,okm:document)[@jcr:lockOwner]/@jcr:lockOwner";
String type = "xpath";
Session jcrSession = DirectRepositoryModule.getSystemSession();
Workspace workspace = jcrSession.getWorkspace();
QueryManager queryManager = workspace.getQueryManager();
Query query = queryManager.createQuery(statement, type);
QueryResult result = query.execute();
for (RowIterator it = result.getRows(); it.hasNext();) {
Map ld = new HashMap();
javax.jcr.query.Row row = it.nextRow();
javax.jcr.Value v = row.getValue(JcrConstants.JCR_LOCKOWNER);
ld.put("owner", v==null?"NULL":v.getString());
v = row.getValue(JcrConstants.JCR_PATH);
ld.put("path", v==null?"NULL":v.getString());
al.add(ld);
}
return al;
You can finde the whole report at File:LockedDocuments.jrxml and File:SubscribedDocuments.jrxml.
