How to retrieve Date attributes on Jasperserver Domain Designer?

book

Article ID: 125466

calendar_today

Updated On:

Products

SUPPORT AUTOMATION- SERVER CA Service Desk Manager - Unified Self Service KNOWLEDGE TOOLS CA Service Management - Asset Portfolio Management CA Service Management - Service Desk Manager

Issue/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.



How to retrieve Date attributes on Jasperserver Domain Designer?

Environment

JasperReports Server 6.3.0
JasperReports Server 6.4.3

Resolution

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.