Calculated fields formulas

Objective

On this page you could find interesting expressions to create formulas for calculated fields in domains. Hope this could be populated by many good and useful formulas.

How to insert a formula for a calculated field

• Stop JasperReports Server
• Edit applicationContext-semanticLayer.xml (could be found in jasperserver-proWEB-INF)

• Find the SQLGenerator that matches the database engine used by the domain.

  For MySQL locate this line:

  <bean class="com.jaspersoft.commons.semantic.dsimpl.SQLGenerator"
        id="defaultSQLGenerator"
        scope="prototype">
  

  Then add your expression formula in this bean : it should be represented like this :

  <entry key="MagicFunction">
      <value> "your expression" </value>
      <!-- Note :use sqlArgs[0] for first value in your formula, then sqlArgs[1] for the second and so ... -->
  </entry>
  

  The expression formulas

  Conditions : commission calulation MySQL

  <value>
      "IF(" + sqlArgs[0] + " > 100," + sqlArgs[1] + "*1.33," + sqlArgs[1] + "*0.66)"
  </value>

  Dates : Differences between dates MySQL

  <value>
      "datediff('" + sqlArgs[0].value + "','" + sqlArgs[1].value + "')"
  </value>

  UNIXTIME : Convert unix times to timestamps MySQL

  <value>
      "FROM_UNIXTIME(" + sqlArgs[0] + ")"
  </value>

• save and restart server
• Now, in domains, create a calculated field using your fomula, MagicFunction(field1,field2...)

See Also

• Section 8.2 of the JasperReports Server User Guide for a more concise description of DomEL