CABI Jasperserver 6.2 with SDM - Domain with Derived Table Problem

[Anon Anon]

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

[Anon Anon]

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

[Legacy User]

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

[Jon_Israel]

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.

[Anon Anon]

Hello Jon,

 

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

[Jon_Israel]

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.

[Legacy User]

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?

[RicardoLimaNeves]

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.