CA Service Management

Expand all | Collapse all

CABI datasource does not retrieve columns (fields)

Jump to Best Answer
  • 1.  CABI datasource does not retrieve columns (fields)

    Posted 02-06-2019 08:02 AM

    Hi!

     

    I created a Datasource conected to the mdb server, his conection works and i can retrieve all the tables, but some fields does not appear.

     

    For example in call_req does not appear assigned, or costumer or any fields that had a relation with other table.

     

     

    Pleas help!

    CABI 6.2

    SDM 14.1



  • 2.  Re: CABI datasource does not retrieve columns (fields)

     
    Posted 02-06-2019 08:17 PM

    Hello!

    By the look of that listing, I'd say you have created a datasource accessing the MDB database using a MS-SQL JDBC driver.  In order to access linked fields without doing a lot of manual configuration, you need to create an SDM datasource using the SDM OpenAccess driver, which interfaces with the SDM object layer rather than going directly to the underlying database.  Please review the steps listed here: Install and Configure CABI JasperReports Server r6.2.0 for CA Service Management - CA Service Management - 14.1 - CA Tec…  and specifically Step 3 - Configure CABI JasperReports Server for CA SDM.

    I'd also suggest that you consider upgrading from CABI 6.2 to a more recent release.

    Hope that helps... If you're still having problems please get back to us.

    Regards,

    James



  • 3.  Re: CABI datasource does not retrieve columns (fields)

    Posted 02-07-2019 07:47 AM

    Hi!

    Thanks for the reply.

     

    I had a datasource with the SDM OpenAccess drive, and it datasource retrieve objects from the SDM object layer.

     

    But it datasource does not filtering date time fields, in fact before it did not retrieve date fields, so i found a modification to the file.\jasperserver-pro\WEB-INF\applicationContext-semanticLayer.xml file. described on another post of the community. But even with this modification the date fields cannot be filtering in dashboards, so i want to connect directly to the mdb for achieve this filtering.

     

    The target is filter datetime fields in dashboards.



  • 4.  Re: CABI datasource does not retrieve columns (fields)
    Best Answer

    Posted 02-07-2019 01:59 PM

    If you're connecting to the MDB, it might be easier to create a SQL View and then use that to create the domain, otherwise you need to set up all the Joins in Jaspersoft.  Here is an example (sorry for the long reply).

     

    Steps would be:

    1. Create a SQL View that has the data you need.  (I can send you an example if you need one, let me know)

    2. Use the SQL View to create a domain. 

    The following url documents how to create a SQL datasource in CABI:

    https://docops.ca.com/ca-service-management/17-0/en/integrating/cabi-jasperreports-server-r6-3-0-for-ca-service-management/configure-cabi-jasperreports-server-r6-3-0-for-ca-service-management#ConfigureCABIJasperReports®Serverr6.3.0forCAServiceManagement-Step1:DownloadandInstallCABIJasperReports®r6.3.0Server

    Launch CABI: http://yourserver:port/jasperserver-pro/login.html

    Login with: superuser/<password> 

    Select Create under Data Sources 

    Complete the fields as noted below.

    For MS SQL Server, define the following datasource details:

    • JDBC Driver: MS SQL Server (tibcosoftware.jdbc.dqlserver.SQLServerDriver)
    • Host (required): The hostname of your SQL Server where the MDB is located.
    • Port (required): Define the SQL Server port number. Default value is 1433.
    • Service (required): MDB
    • URL (required): jdbc:tibcosoftware:SQLServer://<sql_server_hostname>:<port>;SID=MDB
      Where,
      <sql_server_hostname> is the name of the SQL server where the MDB resides.
    • User Name: Provide a database user that has read access to the MDB database.
    • Password: Enter the password for the MDB database user.

    Test the connection, save the datasource, and give it a name.

    3. Now create the domain

    Select Create under Domains

    Enter:

    Name : CR_SQL_Domain 

    Description: SQL View Domain

    Save Location: /organizations/servicemanagement/capability/domains

    Select your data source

    Select dbo, and OK

    In the Data Source pane, scroll down to dbo_View_SDM_Tickets_JS

    Select the Add button to bring it over to the right

     

    (Optional Step) Select Calculated Fields tab

    Enter: 

      Field Name: Ticket_Count 

      Type: Integer 

      Expression: count(dbo_View_ SDM_Tickets_JS.Ticket_Num)

    Click Save Field

    Select the Display tab, and add any or all fields to your Domain

    Select OK

    Select Submit

    Now test your domain. 



  • 5.  Re: CABI datasource does not retrieve columns (fields)

    Posted 02-08-2019 09:35 AM

    Great post! i'll work on it, thanks!