Connecting Crystal Reports

The Aprimo Reporting API enables you to expose data from Aprimo list and summary reports to Crystal Reports and other external reporting tools.

The Reporting API responds to these types of HTTP(S) calls:

• XML – Returns report data in XML format
• XSD – Returns schema data (XSD) for the requested report
• JSON – Returns report data in JSON format

Crystal Reports requires XML/XSD endpoints.

Which Tasks do I Need to Complete?

The tasks you must complete depend on the action you want to perform:

To integrate Aprimo with Crystal Reports for the first time:

• Configure Aprimo Prerequisites
• Configure Crystal Reports Prerequisites
• Expose a Report to the Reporting API
• Configure Report Criteria and Parameters for Use with the Reporting API
• Configure URL Endpoints
• Create a Connection to the Aprimo Reporting API
• Create a Crystal Report from a Connected Aprimo Report

To expose a particular Aprimo report to the Reporting API:

• Expose a Report to the Reporting API
• Configure Report Criteria and Parameters for Use with the Reporting API
• Configure URL Endpoints
• Create a Connection to the Aprimo Reporting API
• Create a Crystal Report from a Connected Aprimo Report

To run a Crystal Report containing data from an Aprimo Report that has been exposed via the Reporting API:

• Create a Crystal Report from a Connected Aprimo Report

Before you can expose Aprimo reports to the Reporting API, make sure these prerequisites are configured:

• The mpm-web-service feature flag must be activated. If the flag is not activated, have Aprimo activate it for you. For more information, see Feature Flags in the Aprimo Help.
• Verify that you (and any other users who create reports) have these domain rights:
  • Analysis, Web Service Access Administration
  • Report, View
  • Report, Edit
    If you do not have these rights, have your system administrator give them to you.

Before you can create a connection between between Aprimo and Crystal Reports, configure these items on the computer where Crystal Reports is installed:

• Verify that Java 1.8 or later is installed.
• Add the Aprimo SSL certificates to the Crystal Reports Trusted Certificate Store

Install Java 1.8 or Later

Aprimo uses the TLS (SSL) 1.2 security protocol, which requires Java 1.8 or later. Install Java 1.8 or later on the Crystal Reports computer if necessary.

Add Aprimo Certificates to the Crystal Reports Certificate Store

Crystal Reports strictly enforces SSL, which requires establishing formal trust between Crystal Reports and each of your Aprimo environments. You establish this trust by adding certificates for each Aprimo environment to the Crystal Reports Certificate Store.

Download Aprimo Certificates (Internet Explorer)

1. Navigate to your Aprimo login page.

   If you use single sign-on (SSO) with Aprimo, navigate to the Aprimo login.aspx page, not your SSO login page.

2. In your browser’s URL bar, click the lock icon, then click View Certificates.
3. Click the Certification Path tab.
4. Double-click Trend Micro.
5. Click the Details tab.
6. On the Details tab, click Copy to File.
7. In the Certificate Export Wizard, click Next.
8. Select the DER encoded binary X.509 (.CER) format, and click Next.
9. Save the certificate, note its filename and location where it’s saved, and click Next.
10. Click Finish.
11. Close the certificate’s Details.
12. In the Certification Path tab, double-click AffirmTrust Certificate Authority-ov1.

    The name of your certificate might be different.

13. Repeat steps 5-10 to save this certificate, and then repeat for any additional Aprimo environments.

Download Aprimo Certificates (Chrome)

1. Navigate to your Aprimo login page.

   If you use single sign-on (SSO) with Aprimo, navigate to the Aprimo login.aspx page, not your SSO login page.

2. Open Chrome’s Developer Tools.
3. Click the Security tab.
4. Click View certificate.
5. Click the Details tab, and click Copy to File.
6. In the Certificate Export Wizard, click Next.
7. Select the DER encoded binary X.509 (.CER) format, and click Next.
8. Save the certificate, note its filename and location, and click Next.
9. Click Finish.
10. Close the certificate’s Details.
11. In the Certification Path tab, double-click AffirmTrust Certificate Authority-ov1.

    The name of your certificate might be different.

12. Repeat steps 4-9 to save the certificate, and then repeat for any additional Aprimo environments.

Adding the Aprimo Certificates to the Crystal Reports Trusted Certificate Store

1. Close Crystal Reports if needed.
2. Open a command prompt and navigate to Crystal Reports’ jre\bin folder, where you will run the keytool.exe utility to import the Aprimo certificates.

   The location of the jre\bin folder might differ by the version of Crystal Reports you use and where it is installed. These are common default paths to the folder and keytool.exe utility:

   • C:\Program Files (x86)\Business Objects\javasdk\jre\bin
   • C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\jre\bin
   • Crystal Reports 2008 keytool.exe location:
     • C:\Program Files (x86)\Business Objects\javasdk\jre\bin
   • Crystal Reports 2016 keytool.exe location:
     • C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\sapjvm\bin
3. Use the following keytool.exe commands to import the certificates into the Crystal Reports trusted keystore (cacerts directory). Be sure to use your certificate names and the cacerts directory path for your Crystal Reports version and installation in the command.
   • Crystal Reports 2008 cacerts directory:
     C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\sapjvm\jre\lib\security\cacerts
   • Crystal Reports 2008 import command:
     keytool -import -alias cert1 -file “C:\temp\cert1.cer” -keystore “C:\Program Files (x86)\Business Objects\javasdk\jre\lib\security\cacerts” -storepass changeit
   • Crystal Reports 2016 cacerts direcotry:
     C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\sapjvm\jre\lib\security>
   • Crystal Reports 2016 import command:
     keytool -import -alias cert1 -file “C:\temp\cert1.cer” -keystore “C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\sapjvm\jre\lib\security\cacerts” -storepass changeit
4. Import the second certificate (AffirmTrust Certificate Authority-ov1):
   • Crystal Reports 2008:
     keytool -import -alias cert2 -file “C:\temp\cert2.cer” -keystore “C:\Program Files (x86)\Business Objects\javasdk\jre\lib\security\cacerts” -storepass changeit
   • Crystal Reports 2016:
     keytool -import -alias cert2 -file “C:\temp\cert2.cer” -keystore ” C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\sapjvm\jre\lib\security\cacerts” -storepass changeit

   The name of your certificate might be different.

   If you have additional Aprimo environments, make sure that the Aprimo certificates for those environments have unique filenames, such as cert3.CER. Also note:

   • In the command, modify these values: -alias cert(X) and C:\temp\cert(x).cer to match the certificates’ names
   • The Trend Micro certificate only needs to be exported/imported once. You do not need to export and import it for additional environments.
5. Start Crystal Reports.

Complete this task in Aprimo to expose a list or summary report to the Reporting API.

1. On the Report List or Report Folders page, find the report to expose, and click Edit Report Details.
2. Click the Web Service Access tab.
3. In the Allow External Web Service Access field, click Yes, and click Save.
   Aprimo saves your changes and generates the web service authentication token for the report
4. Note the report ID and authentication token.

Follow the guidelines in the following tasks to ensure that Aprimo report criteria and parameters function correctly with the Reporting API.

Configure Criteria in the Report’s Configuration

Complete this task in Aprimo.

1. On the Report List or Report Folders page, find the report to configure, and click Edit Report Configuration.
2. Click the Criteria tab.
   
3. Configure the criteria, keeping these points in mind:
   • You can select any attribute that is available to the report object as criteria, including attributes that do not appear as columns on the report.
   • You must define and edit criteria operators in Aprimo. The Reporting API does not provide a way to view or edit criteria.
   • The Relative Date option does not support these operators:
     • Is equal to
     • Is not equal to
     • Is before
     • Is before or on
     • Is after
     • Is after or on

   To use an operator that the Relative Date option does not support, select the Specific Date option instead.

4. Run and save the report.

Configure Parameters in the Report’s Details

Complete this task in Aprimo.

1. 1. On the Report List or Report Folders page, find the report to configure, and click Edit Report Details.
   2. Click the Parameters tab.

3. Click the check box for each criterion to pass to the Reporting API as a parameter, keeping these points in mind:
   • The Reporting API uses the value specified in the Attribute column for the parameter’s name.
   • The Reporting API can override values specified in the Values column.
   • The Reporting API does not use values specified in the Evaluation, Use as Default, and Allow ALL columns.
   • The Reporting API cannot override criteria that are not selected as parameters.
   • You must specify each parameter configured in Aprimo as a parameter in the Aprimo Reporting web service by configuring name-value pairs.

You must define XML and XSD (schema) URLs before Crystal Reports can consume data sent from Aprimo via the Reporting API. This task describes each component of an XML or XSD URL. You will add these URLs to Crystal Reports in the next task, Create a Connection to the Aprimo Reporting API.

An XML or XSD URL contains these parts:

• Endpoint: https://[CustomerURL]/
• Resource: /webapi/api/reports/<ReportID>/###.<Type>

  <Type> is either XML or XSD depending on the request type.

• Parameters: ?<Name=Value>

  For parameters, keep these points in mind:

  • The ? must precede the first name/value pair.
  • Separate multiple parameters with &. For example: ?Begin Date=01/01/2017;01/31/2017&Task Status=Draft.
  • Separate multiple values for a single parameter with semicolons. For example, ?Begin Date=01/01/2017;01/31/2017.
  • To pass multiple values of the same parameter, the name-value pair should be passed for each value. For example, ?Activity Type=Type 1&Activity Type=Type 2

Example XML URL
https://aprimo.com/webapi/api/reports/2203.xml/?Status=Open&Activity Type=Activity Type 1

Example XSD URL
https://aprimo.com/webapi/api/reports/2203.xsd/

Complete these steps in Crystal Reports to create a connection to an Aprimo report via the Reporting API.

1. Click Select File, click New, and click Standard Report.
   The Standard Report Creation Wizard appears.
2. In the Available Data Sources pane, expand My Connections.
3. Click XML and Web Services.
4. In the XML and Web Services dialog box:
   • Select Use HTTP(S) Data Source.
   • In the HTTP(S) XML URL field, enter the reporting API’s XML endpoint, and click Next.

   If the Reporting API requires report parameters, the parameters might be passed along with the web service endpoint at this time, or later in the process of configuring the connection.

5. Enter the HTTPS(S) XML User ID and Password, and click Next.

   The user ID and password are the same as those for the XML endpoint. The Aprimo report’s ID is the user ID, and the web service authentication token is the password.

   If you don’t know the web service authentication token, open the report’s details in Aprimo, and click the Web Service Access tab to view the token.

6. Select Use HTTP(S) Schema, enter the reporting API’s XSD endpoint, and click Next.
7. Enter the HTTP(S) Schema User ID and Password, and click Next.

   The user ID and password are the same as those for the XML endpoint. The Aprimo report’s ID is the user ID, and the web service authentication token is the password.

   If you don’t know the web service authentication token, open the report’s details in Aprimo, and click the Access tab to view the token.

8. Click Finish.
   Crystal Reports creates HTTP parameters from the XML endpoint.
9. If the report requires parameters that were not specified in the XML endpoint, click Add Property, and configure the appropriate name-value pairs.
10. Click OK.

After you create a connection to an Aprimo report via the Reporting API, complete these steps in Crystal Reports any time you need to generate the report.

1. Click Select File, click New, and click Standard Report.
   The Standard Report Creation Wizard appears.
2. In the Available Data Sources pane, expand My Connections.
3. Expand the connection from which to create a report.
4. If the report requires parameters, in the Enter Values dialog, select the parameters, and click OK.
5. In the Available Data Sources pane, click the collection’s Report Details/Row item, add it to the Selected Tables pane, and click Next.
6. In the Available Fields pane, add each field to include in the report to Fields to Display pane, and click Next.
7. On the wizard steps that follow, configure these optional items:
   • Grouping
   • Summary information
   • Sorting
   • Cart
   • Filter fields to limit the report to a subset of information
   • Apply a report template
8. Click Finish.
9. Enter parameter values if necessary, and click OK.
   The report appears.