Clarity Service Management

Expand all | Collapse all

CABI Jasperserver 6.2 with SDM - Domain with Derived Table Problem

  • 1.  CABI Jasperserver 6.2 with SDM - Domain with Derived Table Problem

    Posted 03-29-2017 08:50 AM

    Hello,

     

    we are using the CABI Jasperserver 6.2 with SDM 14.1 and we are trying to create a "TicketDomain" based on the "casd_cr" table.


    Additionally we want to map a derived table to enrich the information but this try ends in an exception.

     

    An example is to create a derived table from casd_alg table to map the amount of manual notifies per ticket to the "TicketDomain".

     

    The following steps have been done:

     

    • Create Domain and add table casd_cr

     

    • Create a derived table based on query: SELECT alg.request_ref_num  AS "ref_num",  COUNT(alg.request_ref_num) AS "notify_count" FROM alg  WHERE alg.type_symbol = 'Manual Notify' GROUP BY alg.request_ref_num

    • Create a left join between the casd_cr and the derived table

    • Add both tables 

     

    The error appears when i want to consume the created domain

     

    Add the fields

    casd_cr.ref_num

    notify_count.notify_count

    to the field list

     

     

    When i click on Refresh Preview Data the following error appears

     

     

    Is there anyone out there who has successfully implemented a similar thing?

     

    thanks a lot

    Sebastian

     

     

    net.sf.jasperreports.engine.JRException: javax.ws.rs.ProcessingException: Error reading entity from input stream.
    at com.jaspersoft.studio.server.domain.conn.DomainQueryExecuter.createDatasource(DomainQueryExecuter.java:165)
    at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1129)
    at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:696)
    at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:437)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:548)
    at net.sf.jasperreports.engine.fill.JRFiller.fill(JRFiller.java:163)
    at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:474)
    at com.jaspersoft.studio.data.reader.DatasetReader.fillReport(DatasetReader.java:242)
    at com.jaspersoft.studio.data.reader.DatasetReader.start(DatasetReader.java:301)
    at com.jaspersoft.studio.property.dataset.dialog.DataPreviewTable$5.run(DataPreviewTable.java:256)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55)
    Caused by: javax.ws.rs.ProcessingException: Error reading entity from input stream.
    at org.glassfish.jersey.message.internal.InboundMessageContext.readEntity(InboundMessageContext.java:866)
    at org.glassfish.jersey.message.internal.InboundMessageContext.readEntity(InboundMessageContext.java:810)
    at org.glassfish.jersey.client.ClientResponse.readEntity(ClientResponse.java:368)
    at org.glassfish.jersey.client.InboundJaxrsResponse$2.call(InboundJaxrsResponse.java:122)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:315)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:297)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:228)
    at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:399)
    at org.glassfish.jersey.client.InboundJaxrsResponse.readEntity(InboundJaxrsResponse.java:119)
    at com.jaspersoft.studio.server.protocol.restv2.RESTv2ExceptionHandler.handleErrorDescriptorList(RESTv2ExceptionHandler.java:99)
    at com.jaspersoft.studio.server.protocol.restv2.RESTv2ExceptionHandler.handleErrorDescriptor(RESTv2ExceptionHandler.java:123)
    at com.jaspersoft.studio.server.protocol.restv2.RESTv2ExceptionHandler.handleException(RESTv2ExceptionHandler.java:64)
    at com.jaspersoft.studio.server.protocol.restv2.ARestV2ConnectionJersey.toObj(ARestV2ConnectionJersey.java:57)
    at com.jaspersoft.studio.server.domain.protocol.RestV2.executeQuery(RestV2.java:147)
    at com.jaspersoft.studio.server.domain.protocol.DomainProxy.executeQuery(DomainProxy.java:41)
    at com.jaspersoft.studio.server.domain.protocol.DomainProxy.executeQuery(DomainProxy.java:46)
    at com.jaspersoft.studio.server.domain.DomainManager.executeQuery(DomainManager.java:92)
    at com.jaspersoft.studio.server.domain.conn.DomainQueryExecuter.createDatasource(DomainQueryExecuter.java:162)
    ... 10 more
    Caused by: com.fasterxml.jackson.databind.JsonMappingException: Can not deserialize instance of java.util.ArrayList out of START_OBJECT token
    at [Source: org.glassfish.jersey.message.internal.ReaderInterceptorExecutor$UnCloseableInputStream@1cd15125; line: 1, column: 1]
    at com.fasterxml.jackson.databind.JsonMappingException.from(JsonMappingException.java:148)
    at com.fasterxml.jackson.databind.DeserializationContext.mappingException(DeserializationContext.java:749)
    at com.fasterxml.jackson.databind.DeserializationContext.mappingException(DeserializationContext.java:745)
    at com.fasterxml.jackson.databind.deser.std.CollectionDeserializer.handleNonArray(CollectionDeserializer.java:275)
    at com.fasterxml.jackson.databind.deser.std.CollectionDeserializer.deserialize(CollectionDeserializer.java:216)
    at com.fasterxml.jackson.databind.deser.std.CollectionDeserializer.deserialize(CollectionDeserializer.java:206)
    at com.fasterxml.jackson.databind.deser.std.CollectionDeserializer.deserialize(CollectionDeserializer.java:25)
    at com.fasterxml.jackson.databind.ObjectReader._bind(ObjectReader.java:1232)
    at com.fasterxml.jackson.databind.ObjectReader.readValue(ObjectReader.java:676)
    at com.fasterxml.jackson.jaxrs.base.ProviderBase.readFrom(ProviderBase.java:808)
    at org.glassfish.jersey.message.internal.ReaderInterceptorExecutor$TerminalReaderInterceptor.invokeReadFrom(ReaderInterceptorExecutor.java:264)
    at org.glassfish.jersey.message.internal.ReaderInterceptorExecutor$TerminalReaderInterceptor.aroundReadFrom(ReaderInterceptorExecutor.java:234)
    at org.glassfish.jersey.message.internal.ReaderInterceptorExecutor.proceed(ReaderInterceptorExecutor.java:154)
    at org.glassfish.jersey.message.internal.MessageBodyFactory.readFrom(MessageBodyFactory.java:1124)
    at org.glassfish.jersey.message.internal.InboundMessageContext.readEntity(InboundMessageContext.java:851)
    ... 27 more

     

    USD Log entries

     

    ERROR        sqltrans.c            2150 Column NOTIFY_COUNT not found in table NOTIFY_COUNT (subquery 1.0 SELECTION)

    ERROR        vdbsql.c              4619 SQL parse error: Column NOTIFY_COUNT not found in table NOTIFY_COUNT (subquery 1.0 SELECTION) at . NOTIFY_COUNT

    SIGNIFICANT  vdbsql.c              4620 SELECT casd_cr.ref_num AS "casd_cr_ref_num", NOTIFY_COUNT.NOTIFY_COUNT AS "notify_count_notify_count" FROM Call_Req casd_cr

    ERROR        odbc_backend.c         153 ODBC error: AHD12002:Error in QueryHandler ServiceDesk-1450633106-11 in method fetch_response: Column NOTIFY_COUNT not found in table NOTIFY_COUNT (subquery 1.0 SELECTION) at . NOTIFY_COUNT



  • 2.  Re: CABI Jasperserver 6.2 with SDM - Domain with Derived Table Problem

    Posted 04-18-2017 07:37 AM

    Hello,

     

    really no one out there with the same problem, or in other words: is there anyone who has successfuly implemented a derived table using the openaccess jdbc SDM driver?

     

    thanks a lot



  • 3.  Re: CABI Jasperserver 6.2 with SDM - Domain with Derived Table Problem

    Posted 04-20-2017 04:37 AM

    CA's support and documentation for this framework, focused on others CA's products integration needs lots of improvments ...



  • 4.  Re: CABI Jasperserver 6.2 with SDM - Domain with Derived Table Problem

    Posted 04-20-2017 08:10 AM

    Hi Ivo, and Sebastian,

    Currently CA does not support the use of Jasper Domains with CABI 6.2 for CA Service Management products.  We are working on some new functionality that will deliver a domain along with an object layer inclusive of all three CASM products which will allow for the use of self service functionality with Jasper (ad hoc reports and view etc.).  We do not have an ETA as of yet, but it is actively being worked on.

    That said, there may be folks out here on the community who have been able to get a domain working with Service Desk, and they may be willing to share their info with you.
    Thanks,

    Jon I.



  • 5.  Re: CABI Jasperserver 6.2 with SDM - Domain with Derived Table Problem

    Posted 04-24-2017 06:21 AM

    Hello Jon,

     

    thank you very much. I am looking forward for the new functionality.



  • 6.  Re: CABI Jasperserver 6.2 with SDM - Domain with Derived Table Problem

    Posted 04-24-2017 07:55 AM

    Me too!!   I think it will be a huge help to customers as far as self-service reporting is concerned, plus the domain will already be created for you (you will have to add your custom tables/columns, but the base out-of-the-box domain will be delivered).  

    We dont have any ETA yet, but expect some further info coming in the near future!

    Thanks,

    Jon I.



  • 7.  Re: CABI Jasperserver 6.2 with SDM - Domain with Derived Table Problem

    Posted 12-13-2017 07:38 AM

    Jon, any development on this?
    I have noticed that when creating a new domain, some fields are not even being shown (eg. id for casd_nr factory).
    I am assuming that jasper cannot translate those field types (UUID).
    Also, I didn't see the possibility of using custom Pdm functions (PdmString, PdmToday, etc.) to make calculated fields.

    So, what is the recommendation when making new reports?
    Develop them using only the datasource passing out a custom SQL query using those custom PDM functions?

    We wanted to have this layer in order to restrict our report developers a bit.
    Without this layer, we might have some troubles in doing so.

    6.3 version has the same issue?



  • 8.  Re: CABI Jasperserver 6.2 with SDM - Domain with Derived Table Problem

    Posted 05-23-2018 12:10 PM

    Hi Jon,

     

    Do you have more information about this issue?

     

    I've created a JOIN between the CR table and a derived table and I am getting an error when I try to generate an Ad-doc view with field from both tables (CR and Derived Table).

     

    Does anyone had the same issue?

     

    Thanks.

    Best Regards,

    Ricardo Lima Neves.