CA Service Management

Expand all | Collapse all

How to Convert Date Field Attribute in Jaspersoft Report?

  • 1.  How to Convert Date Field Attribute in Jaspersoft Report?

    Posted Aug 09, 2016 05:59 AM

    Hi Team,

     

    On creating report in CABI Jaspersoft, the open date attribute is being displayed in numeric format as shown below:

    Jaspersoft.jpg

     

    Is it possible to convert it into standard date and time format? If yes, kindly provide the steps for doing that.

     

    Thanks & Regards,

    Balram Singh Deswal



  • 2.  Re: How to Convert Date Field Attribute in Jaspersoft Report?

    Posted Aug 09, 2016 09:00 AM

    Balram, are you using Jasper Studio connected to the sdm_ds data source?  Or are you going directly against the MDB?  Also which field did you put in the report specifically?   If you can share a screenshot of the report with the detail of the fields that you added, that may help as well.

    Thanks,

    Jon I.



  • 3.  Re: How to Convert Date Field Attribute in Jaspersoft Report?

    Posted Aug 10, 2016 12:38 AM

    Hi Jon,

    Thanks for your response.

    I am using CABI Jaspersoft with a customised data source which is connected directly to MDB database. I am using the following tables (as shown in jaspersoft):

    dbo_act_log and dbo_call_req with full join on persid (in call_req) and call_req_id (in act_log).

    I have applied the filter in which type of call_req is 'I' and type of act_log is 'SOLN'.

    Basically the objective is to show the details of all the incidents on which solution is provided by the analyst.

    Now one of the fields that is being shown in the report is open_date (out of box field in call_req table). The value that is being shown in the report output is in the same format (numeric) that is stored in the MDB database. I want that it get displayed in the date and time format. Attaching some screenshots for your reference:

     

    Custom Data Source->

    Custom Data Source.jpg

     

    Tables Used->

    Tables.jpg

     

    Field Detail->

    Field Detail.jpg

     

    Report Output->

    Report Output.jpg

     

    Kindly suggest how to convert the open date from numeric to standard date and time format (as in CA BOXI, there was a provision to change the output format).

     

    Regards,

    Balram Singh Deswal



  • 4.  Re: How to Convert Date Field Attribute in Jaspersoft Report?

    Posted Aug 11, 2016 02:32 AM

    Hi Jon_Israel,

     

    Kindly provide your valuable inputs on this issue.

     

    Thanks & Regards,

    Balram Singh Deswal



  • 5.  Re: How to Convert Date Field Attribute in Jaspersoft Report?

    Posted Aug 11, 2016 07:58 AM

    Hi Balram,

    Unfortunately we do not support the use of Jasper with a custom data source going directly against the MDB, nor are we allowed to assist in troubleshooting custom reports as that is outside our scope.  If you are able to use the data source provided  with the product, and configure it with SDM as per the instructions on the documentation site, it will connect to the object layer/domsrvr and you can pick the date fields from there which will show the time in the proper format automatically.   Being that most folks are very new to Jasper, we dont have much experience with the ins and outs of working with it just yet.   I dont know of a way to convert the time from epoch to standard off hand, but doing a bit of googling, I found the following links which may help:

    One from CA Communities: Converting unix time to date in Advanced reporting

    http://community.jaspersoft.com/questions/506117/epoch-date-conversion

    Convert unix time to Timestamp | Jaspersoft Community

    Hope this helps,

    Thanks,

    Jon I.



  • 6.  Re: How to Convert Date Field Attribute in Jaspersoft Report?

    Posted Aug 17, 2016 01:20 AM

    Hi Jon,

     

    Thanks for providing these useful links. As per the information provided, I should create a calculated field (let's say Calculated_Open_Date) of Date data type with the following expression (giving no error during parsing):

    EPOCH_TO_UTC_DATETIME(dbo_call_req.open_date)

     

    But when I am trying to add this calculated field from domain to Add Hoc View, it is showing the following error in the tomcat command prompt window:

     

    pastedImage_0.png

     

    I know it is out of scope for CA Support to answer but almost every report that is created for service desk has one or more date fields included in it. If the reports will display the date in Epoch format, the report will not be of any use.

     

    Thanks & Regards,

    Balram Singh Deswal



  • 7.  Re: How to Convert Date Field Attribute in Jaspersoft Report?

    Posted Aug 17, 2016 01:36 AM

    For the out of the box data source provided for CA SDM (sdm_ds), only single schema (casd) is available to choose. After selecting this schema, there is no date field (neither out of box nor customised) appearing under casd_cr table added during domain creation. Now to have those field, I create a customised data source that will directly fetch the data from MDB database. Even creating reports from Jasper Studio was displaying date fields in Epoch format.



  • 8.  Re: How to Convert Date Field Attribute in Jaspersoft Report?

    Posted Aug 17, 2016 10:44 AM

    Hi Balram,

    We do not support the use of domains (hence why we dont ship one with the product at this time).  In your previous post, you mentioned that the out of the box reports provided with the product have one or more date fields in them - which is true, are you saying that those reports ARE displaying epoch time?  I just tested a few and mine are displaying the normal time format.  As you mentioned, yes, it is out of the scope of support to assist in creating a domain, or creating custom reports.  This is why we advise customers to use Jasper Studio to create custom reports, basing them on the out of the box reoports.  Since the provided data source will go against the object layer in SDM and not the MDB directly, the data fields are properly converted and formatted in the out of the box reports.  If you use the appropriate data source, connected to the object layer, then the date will show correctly.

    Please follow the steps in the documentation to import the data source, and configure it properly, then import the out of the box content.  You can then use those reports as a base line to customize them for what you need.

    Thanks,

    Jon I.



  • 9.  Re: How to Convert Date Field Attribute in Jaspersoft Report?

    Posted Aug 18, 2016 03:37 AM

    Do you have steps to create new report?

    Form the Jasper homepage when i click create new Ad Hoc view, it will ask the domain? There is no Service Desk Domain from the list.



  • 10.  Re: How to Convert Date Field Attribute in Jaspersoft Report?

    Broadcom Employee
    Posted Aug 18, 2016 04:33 AM

    Hi Ahmad,

     

    You need to create a new domain for SDM in order for creating a new Ad Hoc View.

     

    Thank you,

    Kaori



  • 11.  Re: How to Convert Date Field Attribute in Jaspersoft Report?

    Posted Aug 18, 2016 05:41 AM

    HI Kaori,

     

    From the CA document, I can use the Japersoft studio to customize the report.

    Getting Started with CABI JasperReports Server r6.2.0 - CA Service Management - 14.1 - CA Technologies Documentation

     

    I 've installed the studio. But I can't connect to the datasource.

    Do you have any guide on how to connect the datasource from Jaspersoft studio?



  • 12.  Re: How to Convert Date Field Attribute in Jaspersoft Report?

    Posted Aug 18, 2016 11:37 AM

    I supplied directions on how to connect to a data source from JasperSoft Studio at Jasper Studio - Create Data Adapter for SDM



  • 13.  Re: How to Convert Date Field Attribute in Jaspersoft Report?

    Posted Aug 18, 2016 09:54 PM

    Hi,

     

    I've follow steps given but it return errorjasper studio.png

     

     

    The datasource is ok if I'm using Jaspersoft through browser.

     

    Jasper Web.png



  • 14.  Re: How to Convert Date Field Attribute in Jaspersoft Report?

    Posted Aug 19, 2016 06:07 PM

    I am unable to replicate the error you are seeing.  What OS are you did you install Jasper Studio on?  64 or 32 bit?  What version of Jasper Studio? 



  • 15.  Re: How to Convert Date Field Attribute in Jaspersoft Report?

    Posted Apr 26, 2018 08:45 AM

    I have the same issue reported by Balram: the ootb data source (sdm_ds) has only schema casd, and I can't find there any cr's date field, such us open_date or close_date.



  • 16.  Re: How to Convert Date Field Attribute in Jaspersoft Report?

     
    Posted Aug 15, 2016 07:19 PM

    Hi Balram.Deswal - Did Jon_Israel's response help answer your question? If so please mark as Correct Answer. Thanks!