End User, Assignee, Reported By are not showing in Jasper report

[Saravanakumar Kumbalingam]

Hi,

 

I am using CA SDM 17.1 and Jasper report.

 

I created Data source with MS SQL JDBC server for my new domain.

 

When i create new report with this new domain, i could not see any fields related to contacts like End user, Assignee, Reported by like that from Call_Req.

 

Please help in fixing the issue.

 

Thanks,

Saravanakumar Kumbalingam

[Legacy User]

Hi Saravanakumar,

Fields from linked tables are joined by default when you use an 'SDM' data source, which connects to the SDM object layer.  If you wish to use a direct connection to the MDB database - as you have created for your domain - you will have to add all of the necessary joins manually, for example, linking to the ca_contact table by contact_uuid to retrieve details such as last and first names for assignee, group, etc.

Regards,

James

[Saravanakumar Kumbalingam]

Thank you James for your quick reply.

 

Since the SDM data source is not having my custom fields. I have created data source with MS SQL JDC driver to directly connect MDB database. Connection is successful. As you said, to make joins manually, i could not able see customer, assignee, and log_agent fields (contacts related) under call_req to make join with ca_contact. (Remaining fields are showing under call_req table.

 

Please help.

 

Regards,

Saravanakumar Kumbalingam

[Cristi Mitrana]

The fields are there, in the call_req table:

 

They're binary type fields, so the driver might not understand those type of fields and doesn't show them as browsable/choosable. You can either do the join in your SQL interrogation and create a View with the results, or convert the binary fields to 'nvarchar' in your SQL query.

[Legacy User]

You might find the 'Attribute Alias' function useful here.  The attribute alias function enables custom fields to be added to the schema that is made visible by the SDM ODBC connector.

For example, my schema has a new field 'zEnvironment' in 'cr' that points to a new reference table:

In SDM, under Administration / Service Desk / Codes / Attribute Aliases, I can create a new alias 'zEnviro_sym' with value 'zEnvironment.sym', in the 'cr' object:

This field becomes visible to Jaspersoft in the 'cr' object through the SDM connector (I haven't tested whether a restart is required):

The new field can now be used in a report without adding a join to the 'zEnvironment' table:

 

Regards,

James

[Paul Coccimiglio]

Saravanakumar.Kumbalingam ..........

 

Did the information provided by camja06 address your issue?

Note:  direct MDB connection from JasperSoft is NOT supported/certified

[Saravanakumar Kumbalingam]

Sorry for the delay reply.

 

Yes. camja06 information was helpful. I have got what i expected.

 

Thanks a lot!

 

Regards,

Saravanakumar Kumbalingam