This is the most likely type of report you will require. It's a report which takes it's data from a data source such as MySQL and returns the output. (Not all reports do this.) 

Prerequisites

For this to work you need several things:

1. What parameters you are going to use, and how to query them
2. A Query
3. Column configuration - if required
4. Database connection
   
   1. Jndi
      
      1. JNDI name
   2. JDBC
      
      1. URI
      2. Username
      3. Password

Intro

These need to be placed in a yaml file placed in the following location:

reports/<reportid>/reportconf.yaml

Please note the reportId is different to the report name.

A standard report configuration has a couple of required elements regardless of type:

• paramConfig
• columns (optional, defaults to everything)
• extraOptions (optional, no default)
• pageLimit (optional, default changes depending on client)

Additionally JDBC and JNDI both require:

• sql
• presqlsql (Optional)
• postsqlsql (Optional)

Also they have their own options on top of this:

JNDI:

• jndi - the JNDI name.

JDBC

• jdbc - The JDBC URI
• username
• password
• classLoaderPath - For when you need to insure that a JDBC Driver has been registered

Example

A basic configuration can look like any of the following:

JNDI which lists the questions for a date range:

type: "JNDI"
jndi: "dataSource"
sql: "SELECT question, answer FROM Answers <@where><@clause render=param.dateOfQuestion?has_content>(Date specific query here)</@clause></@where>"
paramConfig:
  - type: DateTime
    name: "Date of Question"
    help: "The date the question was asked."
    placeholder: "Any"
columns:
  - name: Question
    extraOptions:
      styleType: text
  - name: Answer
    extraOptions:
      styleType: numbers
extraOptions:
  image: image1.png
  reportName: "Answers DB"

A JDBC example report which queries the answer to a specific question:

type: "JDBC"
classLoaderPath: "com.mysql.jdbc.Driver"
jdbc: "jdbc:mysql://localhost:3306/test"
username: "test"
password: "test" 
sql: "SELECT question, answer FROM Answers <@where><@clause render=param.question?has_content>question=${method.value(param.question)}</@clause></@where>"
paramConfig:
  - type: String
    name: "Question"
    help: "Enter the exact question used."
    placeholder: "All"
columns:
  - name: Question
    extraOptions:
      styleType: text
  - name: Answer
    extraOptions:
      styleType: numbers
extraOptions:
  image: image1.png
  reportName: "Answer DB"

As you notice in the examples above there is a markup language. This is freemarker with an additional configuration. 

Components

The JDBC and JNDI report types each have their own set of fields; with one compulsory and a couple optional. 

JNDI requires a JNDI string and that's all.

JDBC can take:

com.mysql.jdbc.Driver

In addition to the specific components there are a couple of shared fields, both the JDBC and the JNDI connectors provide:

Freemarker

Kagura uses Freemarker with the JDBC and JNDI reports as a sort of pre-processor to be run with reports, it is responsible for inserting values into the query, making the report responsive to environment and allowing a greater degree of flexibility. Kagura as of writing uses Freemarker 2.3.19.

Some examples of Freemarker usage

It can be used to make conditional parameters: (However see below we have a better way of using it.)

SELECT id, active, username FROM User <#if param.User?has_content>WHERE username=${method.value(param.User)}</#if><@limit />;

This would select all users, unless a user has been specified as a parameter, if it has it makes it a where clause.

Doing evaluation of equations:

SELECT result FROM Lookup WHERE percent>=${param.percent / 100} and percent<${(param.percent+10) / 100}<@limit />;

Type conversions:

SELECT * FROM log WHERE entryDate >= ${method.value(param.startDate?date)} <@limit />

Generate SQL from lists: (Contrived, but an example.)

SELECT date AS 'Date'
<#list ["uniqueProperty1", "uniqueProperty2", "uniqueProperty3", "uniqueProperty4"] as x>
`${x}` AS '${x}'
</#list>
FROM dynamicTable WHERE category IS NULL
<#list param.selectedValues as x>
OR category=`${x}`
</#list>
<@limit />

Switching:

SELECT invoiceNumber, invoicePrice FROM invoice WHERE
<#switch param.mode>
  <#case "Test">
    category="TE"
  <#case "Customer 1">
    category="ON"
  <#default>
    category IS NOT NULL
</#switch>
<@limit />

Converting user input:

${param.fullname?upper_case}
${param.fullname?capitalize}
${param.firstname?cap_first}
${param.firstname?trim}
${param.fullname?split[0]}
${param.fullname?split(", ")?reverse?join(" "}

Formatting and pattern  based conversions:

${param.fullname?date("MM/dd/yyyy")} - Convert a string date to a freemarker date.
${param.lastUpdated?string("yyyy-MM-dd HH:mm:ss zzzz"} - Date to string.
${param.typeA?string("yes", "no")} - Boolean to string 

Ideas:

• Using macros to avoid repetition: http://freemarker.org/docs/ref_directive_macro.html
• Using functions to generate values: http://freemarker.org/docs/ref_directive_function.html
• Throwing an exception: http://freemarker.org/docs/ref_directive_stop.html - Such as overly generic parameters selection to save loading times.

Customisations of Freemarker

As well as the impressive array of inbuilt functions Freemarker provides, Kagura provides some additional custom tags on top:

<@limit />

The where tag can be used as follows: (These are from the unit tests.)

SELECT * FROM table WHERE columnB=${method.value(param.test)} <@limit />

Renders:

SELECT * FROM table WHERE columnB=?  LIMIT 20 OFFSET 0 

<@where> <@clause>

Unlike <@limit /> this isn't require and provided for your benefit only.

Simple:

The where tag can be used as follows: (These are from the unit tests.)

SELECT * FROM table <@where><@clause render=true>columnB=${method.value(param.test)}</@clause></@where> <@limit />

Renders:

SELECT * FROM table  WHERE columnB=?  LIMIT 20 OFFSET 0 

Multiple

SELECT * FROM table " +
 <@where>
  <@clause render=true>columnB=${method.value(param.test)}</@clause>
  <@clause render=false>columnC=${method.value(param.test)}</@clause>
  <@clause render=param.test='ParameterOutput'>columnD=${method.value(param.test)}</@clause>
 </@where>
<@limit />

Renders:

SELECT * FROM table  WHERE columnB=? AND columnD=?  LIMIT 20 OFFSET 0 

Nested

SELECT * FROM table 
<@where>
 <@where type='or'>
  <@clause render=true>columnB=${method.value(param.test)}</@clause>
  <@clause render=false>columnC=${method.value(param.test)}</@clause>
 </@where>
 <@where>
  <@clause render=false>columnE=${method.value(param.test)}</@clause>
  <@clause render=false>columnF=${method.value(param.test)}</@clause>
 </@where>
 <@where type='and'>
  <@clause render=true>columnG=${method.value(param.test)}</@clause>
  <@clause render=true>columnH=${method.value(param.test)}</@clause>
 </@where>
 <@where type='or'>
  <@clause render=true>columnJ=${method.value(param.test)}</@clause>
  <@clause render=true>columnK=${method.value(param.test)}</@clause>
 </@where>
 <@clause render=param.test='ParameterOutput'>columnD=${method.value(param.test)}</@clause>
</@where>
<@limit />

Returns:

SELECT * FROM table  WHERE columnB=? AND columnG=? AND columnH=? AND (columnJ=? OR columnK=?) AND columnD=? LIMIT 20 OFFSET 0 

Notice how:

• it removes brackets from queries without the need
• that where clauses can be nested
• The were clauses denote usage
• <@where> tag adds the WHERE token

Render=

You can use any Boolean expression you like in the render tag, however I recommend sticking to simple expressions particularly: param.name?has_content. Here are some examples:

<@clause render=param.startDate?has_content>InspectionDate >= ${method.value(param.startDate?date)} </@clause>
<@clause render=param.test='ParameterOutput'>columnD=${method.value(param.test)}</@clause>
<@clause render=param.Employee?has_content>username=${method.value(param.Employee)}</@clause>

Notes

If can always have multiple clauses doing similar or the same thing, try keep your expressions themselves simple, freemarker can quickly become unreadable if you try do too much.

method.value()

method.value() is highly recommended on any external variable and some internal ones (extra.*). It properly handles escape characters and other issues you may encounter taking dynamic strings and using them inside a SQL statement. Some examples of ways you can use method.value():

SELECT * FROM table WHERE columnB=${method.value(param.test)} <@limit />

When the code runs, the ${method.value(param.test)} is replaced with a ?, and the value inside the function is put as part of the prepareStatement query. You can put anything as a query, including a constant string:

SELECT * FROM table WHERE columnB=${method.value("That's")} <@limit />

Notes

Values will be converted to strings inside freemarker if you are using dates or some other type, you will have to convert it as you use it, an example of that is:

      <@clause render=param.startDate?has_content>date >= ${method.value(param.startDate?date)} </@clause>
      <@clause render=param.endDate?has_content>date < ${method.value(param.endDate?date)} </@clause>

method.values()

method.values() has been with kagura since version 1.3. It supports an array / freemarker list as an input, and it allows you to use "IN". It's designed primarily for use with ManyCombo parameter types. The usage is as below:

SELECT * FROM table WHERE columnB IN ${method.values(param.test)} <@limit />

param.* and extra.*

These act like standard Freemarker variables, and you can use them in expressions. You can insert them into the SQL script by wrapping them in ${ .. } however, I would strongly recommend the use of method.value(...) when outputting the values into the SQL. 

Finally

Remember, the user needs to be setup with the Authentication Provider, or with the middleware you are using in order to see the reports.