CA Service Management

 View Only
Expand all | Collapse all

How to retrieve Date attributes on Domain Designer Jasperserver?

  • 1.  How to retrieve Date attributes on Domain Designer Jasperserver?

    Posted Apr 18, 2017 12:47 PM
      |   view attached

    How to retrieve Date attributes on Domain Designer Jasperserver?

     

    I'm trying to develop some Jaspersoft Reports, and during the configuration of my future Domain, i'm not able to retrieve any Date attribute (open_date, resolve_date, last_mod_dt, etc....) from ANY table...

    Despite of that, i'm able to get z_ custom fields.

     

    PS: Attached you may find a print screen from the "casd_cr" table.



  • 2.  Re: How to retrieve Date attributes on Domain Designer Jasperserver?

    Broadcom Employee
    Posted Apr 26, 2017 07:41 AM

    Hi Ivo,

     

    From the screenshot provided, I see other default object attributes like urgency, web_url etc. Could you let me know if this is the entire list of default attributes that are visible as of now?

     

    Please check what tables have been defined on the data source? Check the query that was defined which determines the structure of the table returned in your case?

     

    You create a derived table in a Domain by first building a custom query using the objects you selected on the Tables tab. Because Domains are based on JDBC and JNDI data sources, you write the query in SQL. You run the query, and then select columns from the query result for use in the Domain design.

     

    To define a derived table:

     

    1. Type a name for the table in the Query ID field.
    2. Enter a valid SQL query in Query. The query may refer to any table or column in Available objects. Only queries that begin with the select statement are allowed. Do not include a closing semi-colon (;)

    Expand the tables in Available objects. Double-click column names to add them to the query.

    3. When the query is complete, click Run Query to test it and choose the list of columns in the result.
    4. By default, all columns in the result are selected. Use Ctrl-click to change the selection. If you want only a few columns out of many, it is easier to specify the column names in the SELECT clause of the query.
    5. Click Save Table to add the derived table to Available objects. A distinctive icon, identifies it as a derived table.

     

    Let me know if this helps.



  • 3.  Re: How to retrieve Date attributes on Domain Designer Jasperserver?

    Posted Jul 10, 2017 03:57 PM

    I have the same problem here. I can't see fields like "open_date", "Close_date", "resolved_date" when creating a domain and using CASD_CR table.

    If I build a derived table using for example this query select ref_num, open_date, close_date from casd.cr I am able to save it as a domain but when trying to use it after to create an Ad How view I get this error: L'attribut ref_num référencé par la requête SQL Majic est introuvable dans le ou les objets . at ref_num ... FROM
     at com.ddtek.openaccess.ssp.Diagnostic.toSQLException(Unknown Source)

     

    (The system is in French but the message means something like the attribute is not found).



  • 4.  Re: How to retrieve Date attributes on Domain Designer Jasperserver?

    Posted Jul 11, 2017 12:20 PM

    Hi Benoit - this is a known issue and is the reason we dont currently support domains in jasper for CA SDM at this time.  Its a problem that we are currently working through with Tibco (the company that owns Jaspersoft) and are hoping to be able to deliver a domain in the near future.  Stay tuned on this!

    Thanks,

    Jon



  • 5.  Re: How to retrieve Date attributes on Domain Designer Jasperserver?
    Best Answer

    Posted Jul 12, 2017 06:06 PM

    Actually one can see dates in domains if you add the line highlighted in red below to the ...\jasperserver-pro\WEB-INF\applicationContext-semanticLayer.xml file.

     

     

    <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"/>

     

    Recycle Jasper Report Server after making the modification and date attributes like open_date, etc... should appear.

     

    Note 1:  You can't filter on dates.  We are currently working with the vendor to see if date filtering is possible.     

     

    Note 2:  As Jon mentioned earlier,  domains are not officially supported by CA for SDM at this time. 



  • 6.  Re: How to retrieve Date attributes on Domain Designer Jasperserver?

    Posted Jul 13, 2017 08:52 AM

    Thanks Steve!

    One additional note, if you attempt to then create an ad-hoc view using the domain against the object layer of SDM, and you attempt to enter a date field into the ad-hoc view or report, you will get an error if the ad-hoc view or report type is set to "crosstab" - you need to change it to table in order to add the date fields.  If you need a crosstab, you can change it back once you add the date fields that you want.   Just figured I would share this one as well

    Regards,
    Jon



  • 7.  Re: How to retrieve Date attributes on Domain Designer Jasperserver?

    Posted Dec 13, 2017 09:57 AM

    Hi folks, just to let you know, dates work with JasperSoft server. You can use them as a measure field. Pdm Functions don't work but they work in JasperSoft studio.



  • 8.  Re: How to retrieve Date attributes on Domain Designer Jasperserver?

    Posted Jul 14, 2017 09:04 AM

    Hi Steve and Jon
    Thanks for you help, I can now see dates.

     



  • 9.  Re: How to retrieve Date attributes on Domain Designer Jasperserver?

    Posted Jan 22, 2018 09:30 AM

    Hi Steve.

     

    I have 3 questions:

    1 - Have you already any solutions for date filter problem?

    2 - CA have tested the Jasper before offer to your clients? (By the amount of issues we have to fixed I think no).

    3 - If the previously version of CABI (BOXI) works with Universe, why CA don't support Domain in Jasper? (Why CA don't offer a domain template as offered in BOXI?)



  • 10.  Re: How to retrieve Date attributes on Domain Designer Jasperserver?

    Posted Jan 22, 2018 10:38 AM

    Hi Fabiano,


    If you use Steve's solutions concerning Dates it will work. I'm now using JasperSoft a lot and I don't know much about Boxi but the person in my office that was using Boxi a lot before told me about features and I'd say that CA SDM (casd) should be working with domains on JasperSoft Server, that would help to build simple report and ad hoc views.

     

    On the other hand, I tried using domains in JasperSoft server and it works somehow very well except for few things. Implementing "Pdm" functions in Jasperserver domain would be a great improvment as it was available in Boxi and having some joins being made more automatically. On my side, I did not figure out how to extract a list of users based on the existence of at least one special handling.



  • 11.  Re: How to retrieve Date attributes on Domain Designer Jasperserver?

    Posted Jan 22, 2018 11:07 AM

    Hi Blav..


    Thank for your attention.

    Have you tried create a filter by date using domain? Ex: cr.open_date > 01/01/2018. Like this:

     


    As I wrote, we have three question. One of them about this issue and others two about the quality of the tool and the strategy of CA to attend the customers needs.



  • 12.  Re: How to retrieve Date attributes on Domain Designer Jasperserver?

    Posted Jan 22, 2018 04:46 PM

    Hi Fabiano
    I know that CA are working on improving their functionnalities to use with the product. From what I heard about Boxi I can tell that there are things missing from Boxi in JasperSoft. But there are powerful Tools to use with JasperSoft that I know CA is aware of (like visualize.js).

    For your request, in Ad Hoc Views in Jasperserver it all work wells. By my side I only had to use PdmYear and PdmMonth directly in the SQL Query on CASD object layer. I haven't tried putting an exact date.

    Here is an example of a DataSet query that I used in a Jasper Studio report. It takes long time building a report in Jasper Studio, using Ad Hoc views on Jasperserver is a lot quicker for most use. They don't provide support for domains yet but it works.

    select ref_num, sla_violation, open_date,

    PdmSeconds(PdmDownTime('SupportService','Mon - Fri { 7:30 am - 5:00 pm }

    12/31/2013-01/02/2014

    04/18/2014-04/21/2014

    06/24/2014

    07/01/2014

    09/01/2014

    10/13/2014

    12/24/2014-12/27/2014

    12/31/2014-01/02/2015

    04/03/2015-04/06/2015

    05/18/2015

    06/24/2015

    07/01/2015

    09/07/2015

    10/12/2015

    12/24/2015-12/27/2015

    12/31/2015-01/04/2016

    03/25/2016-03/28/2016

    05/23/2016

    06/24/2016

    07/01/2016

    09/05/2016

    10/10/2016

    12/23/2016-12/27/2016

    12/30/2016-01/03/2017

    04/14/2017-04/17/2017

    05/22/2017

    06/23/2017

    06/30/2017

    09/04/2017

    10/09/2017

    12/22/2017-12/26/2017

    12/31/2017-01/02/2018

    03/30/2018-04/02/2018

    05/21/2018

    06/25/2018

    07/02/2018

    09/03/2018

    10/08/2018

    12/24/2018-12/26/2018

    12/31/2018-01/02/2019', open_date,resolve_date)) as TheDuration,

    open_date,

    resolve_date,

    category_symbol,

    group_name from casd.cr

    where

    PdmYear(open_date) >= 2016 order by open_date



  • 13.  Re: How to retrieve Date attributes on Domain Designer Jasperserver?

    Broadcom Employee
    Posted Feb 06, 2018 12:11 PM

    Since there has been some confusion on the steps needed to configure Jaspersoft Reports to recognize date fields and filter on date fields, I am posting the complete file edits needed here. 

    Update the applicationContext-semanticLayer.xml file:

    Locate the applicationContext-semanticLayer.xml, in the following directory:

    C:\Program Files\apache-tomcat-8.0.33-windows-x64\apache-tomcat-8.0.33\webapps\jasperserver-pro\WEB-INF

    Open the file using Notepad++, 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 the following lines:

    <!-- 11 is date fields in SDM, mapped to TIMESTAMP -->

    <entry key="11" value="TIMESTAMP"/>

     

    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>

    Paste the below text below the above section:

    <bean class="com.jaspersoft.commons.semantic.dsimpl.DBServerConfig">

    <property name="productName" value="OpenAccess"/>

    <property name="sqlGenerator" ref="sqlserverGenerator"/>

    </bean>

     

    Save and close the applicationContext-semanticLayer.xml file

     

    The following step is needed to avoid errors when date fields are used on reports along with combo name. It is a Known Issues that without the following file updates, date fields and combo names cannot be used on the same report without jaspersoft reports throwing an error.   

    Update the DatachooserDialogView.js file

     

    Locate DatachooserDialogView.js, in the following directory:

    C:\Program Files\apache-tomcat-8.0.33-windows-x64\apache-tomcat-8.0.33\webapps\jasperserver-pro\scripts\adhoc\datachooser\view

    Open the file using Notepad++, and locate the following section:

    var reportType = this._getParameterByName(ADHOC_REPORT_TYPE_PARAM_NAME);

    reportType = reportType || "crosstab";

    this.reportType = reportType === "chart" ? "ichart" : reportType;

    Update the following line as follows:

    reportType = reportType || "table";

    Save the file

    Update the js.config.properties file

     

    Locate js.config.properties in the following directory:

    C:\Program Files\apache-tomcat-8.0.33-windows-x64\apache-tomcat-8.0.33\webapps\jasperserver-pro\WEB-INF

    Open the file using Notepad++, and locate the following line:

     

    javascript.optimize=true

     

    Update that value to false, as follows:

     

    javascript.optimize=false

     

    Save the file

    Recycle the Jaspersoft ReportServer service

    Clear your browser cache



  • 14.  RE: Re: How to retrieve Date attributes on Domain Designer Jasperserver?

    Posted Jun 20, 2019 01:12 AM
    Hi All,

    How to get duration field like time_spent_sum?

    Regards,
    Saravanakumar Kumbalingam