Introducing to reports with filtering

When you execute a report from OpenKM the view might be shown a form requiring the user to introduce values for filtering purposes. The most common case is filtering by a range of dates.

Understanding filtering

To understand the form filter we will use the UsersLogin.rep report (download the UsersLogin.zip ).

Params XML file

In the sample has been set two filtering fields:

The params.xml file:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE report-parameters PUBLIC "-//OpenKM//DTD Report Parameters 2.1//EN"
                                   "http://www.openkm.com/dtd/report-parameters-2.1.dtd">
<report-parameters>
  <input label="From" name="from_date" type="date">
    <validator type="req"/>
  </input>
  <input label="To" name="to_date" type="date">
    <validator type="req"/>
  </input>
</report-parameters>

Using fields in report

In the report are created two parameters:

The parameters are also used in SQL or Java reports.

Java sample

• Click on  report query button.

The from_date and to_date variables are injected in code execution and can be used directly as a sample below.

import java.util.Calendar;
import com.openkm.db.service.ActivitySrv;
import com.openkm.db.bean.ActivityFilter;
import com.openkm.db.bean.Activity;
import com.openkm.util.ContextWrapper;
import org.springframework.web.context.WebApplicationContext;

WebApplicationContext cc = (WebApplicationContext) ContextWrapper.getContext();
ActivitySrv activitySrv = cc.getBean(ActivitySrv.class);
ActivityFilter filter = new ActivityFilter();
Calendar begin = Calendar.getInstance();
begin.setTime(from_date);
begin.set(Calendar.HOUR, 0);
begin.set(Calendar.MINUTE, 0);
begin.set(Calendar.SECOND, 0);
begin.set(Calendar.MILLISECOND, 0);
filter.setBegin(begin);
Calendar end = Calendar.getInstance();
end.setTime(to_date);
end.set(Calendar.HOUR, 0);
end.set(Calendar.MINUTE, 0);
end.set(Calendar.SECOND, 0);
end.set(Calendar.MILLISECOND, 0);
filter.setEnd(end);
filter.setAction("LOGIN");
List al = new ArrayList();
for (Iterator it = activitySrv.findByFilter(filter).iterator(); it.hasNext();){
    Activity act = (Activity) it.next();
    Map actMap = new HashMap();
    actMap.put("user", act.getUser());
    actMap.put("date", act.getDate().getTime());
    al.add(actMap);
}

return al

SQL sample

 Click on  report query button.

In the sample there is a parameter named "name" of class "java.lang.String" used for filtering the users list.

select * from OKM_USER where USR_ID like concat('%',$P{name},'%')

select * from OKM_USER where USR_ID like concat('%','okm','%')

select * from OKM_USER where USR_ID like '%okm%')