How to retrieve Date attributes on Jasperserver Domain Designer?

Document ID : KB000125466
Last Modified Date : 29/01/2019
Show Technical Document Details
Introduction:

When trying to develop Jaspersoft reports during the Domain configuration, it is not possible to retrieve any Date attribute, such as "open_date", "resolve_date", "last_mod_dt", etc., from any table.

Question:

How to retrieve Date attributes on Jasperserver Domain Designer?

Environment:
JasperReports Server 6.3.0
JasperReports Server 6.4.3
Answer:

1. Locate the "applicationContext-semanticLayer.xml" file, which should be in the following directory:
{CABI 6.x Install Directory}\apache-tomcat\webapps\jasperserver-pro\WEB-INF

 

2. Create a copy of the file and rename it to “backup_ applicationContext-semanticLayer.xml”

 

3. Open the original file using a text editor, and locate the following section:

<property name="codeToJdbcTypeMapping">
<map>
<!-- entry key="<code>" value="<field name>" -->
<!-- -101 is TIMESTAMP WITH TIME ZONE in Oracle, mapped to TIMESTAMP -->
<entry key="-101" value="TIMESTAMP"/>

 

Update the section to add a line as shown here:

 

<property name="codeToJdbcTypeMapping">
<map>
<!-- entry key="<code>" value="<field name>" -->
<!-- -101 is TIMESTAMP WITH TIME ZONE in Oracle, mapped to TIMESTAMP -->
<entry key="-101" value="TIMESTAMP"/>
<entry key="11" value="TIMESTAMP"/>

 

IMPORTANT NOTE: Steps 4 and 5 are database-specific and only ONE of those two steps needs to be followed.
If your CA Service Desk Manager MDB database is running on MS SQL Server, then follow step 4, and skip step 5.
If your CA Service Desk Manager MDB database is running on Oracle, then skip step 4, and follow step 5.

 

4. (FOLLOW THIS ONLY IF YOUR MDB IS RUNNING ON MS SQL SERVER) Next, Locate the following section in the same file:

<bean class="com.jaspersoft.commons.semantic.dsimpl.DBServerConfig">
<property name="productName" value="Microsoft SQL Server"/>
<property name="sqlGenerator" ref="sqlserverGenerator"/>
</bean>

Copy and Paste that same section right below it, then change the copied and pasted section as follows:

<bean class="com.jaspersoft.commons.semantic.dsimpl.DBServerConfig">
<property name="productName" value="OpenAccess"/>
<property name="sqlGenerator" ref="sqlserverGenerator"/>
</bean>

 

5. (FOLLOW THIS ONLY IF YOUR MDB IS RUNNING ON ORACLE) Next, Locate the following section in the same file:

<bean class="com.jaspersoft.commons.semantic.dsimpl.DBServerConfig">
<property name="productName" value="oracle"/>
<property name="sqlGenerator" ref="oracleSQLGenerator"/>
</bean>

Copy and Paste that same section right below it, then change the copied and pasted section as follows:

<bean class="com.jaspersoft.commons.semantic.dsimpl.DBServerConfig">
<property name="productName" value="OpenAccess"/>
<property name="sqlGenerator" ref="oracleSQLGenerator"/>
</bean>


6. Save the "applicationContext-semanticLayer.xml" file.

 

7. Recycle Jasper Server service.

Additional Information:
Further information on this subject may be found at the CA Service Management communities, for instance:

How to retrieve Date attributes on Domain Designer Jasperserver?
Jasper Server - Date Items

Note that Jasper Domains created by customers are not supported, therefore it is not possible to have support regarding any issues while using custom Domains with JasperReports Server versus the CA Service Management solution.