posts - 88 , comments - 3 , trackbacks - 0

Connect to Dynamics CRM Data in Jaspersoft Studio

Create reports based on live data in Jaspersoft Studio.

This article shows how to use the CData JDBC Driver for Dynamics CRM to create a basic Jaspersoft Studio report that features Dynamics CRM data in a table and a chart. Each time you run the report, the chart and table will display the live data. You will use wizards in JasperSoft to build several SQL queries that will populate the report elements. The driver enables you to use standard SQL while skipping the process of copying the data into a relational database. Instead, queries are executed directly to the underlying Dynamics CRM API.

Connect to Dynamics CRM Data as a JDBC Data Source

You can use the Data Adapter wizard to connect to JDBC data sources in Jaspersoft Studio. Follow the steps below to connect to Dynamics CRM data from your project. You will a data adapter for Dynamics CRM and add it to the workspace.

  1. In the Repository Explorer, right-click the Data Adapters node and click Create Data Adapter.
  2. Select Database JDBC Connection.
  3. Enter a user-friendly name for the driver.
  4. On the Driver Classpath tab, click Add. In the resulting dialog, navigate to the lib subfolder of the installation directory. Select the driver JAR.
  5. On the Database Location tab, the following information is required:
    • JDBC Driver: Enter the class name of the JDBC driver, cdata.jdbc.dynamicscrm.DynamicsCRMDriver
    • JDBC URL: Enter the required connection properties in the JDBC URL. You must enter connection properties in name-value pairs separated by semicolons. Below is a typical JDBC URL for Dynamics CRM:

      jdbc:dynamicscrm:User=myuseraccount;Password=mypassword;URL=;CRM Version=CRM Online;

Create Reports with Dynamics CRM Data

After you create a data adapter for Dynamics CRM, you can add Dynamics CRM data to JasperReports. This section shows how to populate one of the included templates with Dynamics CRM data.

  1. Click File -> New Jasper Report. Select a template, select the parent project, and enter a name for the report.
  2. In the Data Adapter wizard, select the data adapter you created in the previous section.
  3. In the Diagram tab, you can build the query visually: Drag tables into the box and click the columns you want. You can also enter a custom query. For example:
    SELECT * FROM Account
  4. Select the fields you want to include in the dataset.

In the Preview tab, you can see the report as it would look with the current Dynamics CRM data.

Add a Chart

Follow the steps below to add a chart of Dynamics CRM data to an existing report. You will add a bar chart to the end of the report template you created in the previous section.

  1. Click the Design tab.
  2. In the Outline view, right-click the root node for the report and click Create Dataset.
  3. Enter a name for the dataset and click the option to create a new dataset from a connection or data source.
  4. In the Data Adapter menu, select the Data Adapter you created in the first section.
  5. Enter the following query:

    SELECT Contact.FirstName, SUM(Account.NumberOfEmployees) FROM Contact, Account GROUP BY Contact.FirstName
  6. Select the fields you want in the dataset. This example uses all fields.
  7. If you already used the GROUP BY clause when you entered the query, skip the Group By step. In this example, the driver's SQL engine aggregates the specified columns. So, no fields are selected in this step.

After adding the dataset, follow the steps below to create the chart:

  1. Click the Summary node in the Outline view. In the Properties view, set the height to 400 pixels. The summary band is printed at the end of the report.
  2. Drag a chart from the Palette onto the summary. The chart wizard is displayed.
  3. Select the chart type. This example uses a bar chart.
  4. In the Dataset menu, select the dataset you created for the chart.
  5. In the Dataset tab, select the option to use the same connection used by the master report. The chart wizard will enter the parameter for the connection and change the menu selection to Use Another Connection.
  6. Click the button next to the Series menu and delete the default series. Click Add.
  7. In the Expression Editor that is displayed, double-click a column to create a new series for each column value. For example, FirstName. When the series is set to FirstName, a new bar will be created for each FirstName.
  8. Click the button next to the Value box to open the Expression Editor for the measures of the chart. Double-click a column to add it to the y-axis, for example, NumberOfEmployees.
  9. Click the button next to the Label box to open the Expression Editor for the dimensions of the chart. Double-click a column to add it to the x-axis, for example, FirstName.
  10. In the Category box, enter "" for an empty string, as this example does not use a category.

After you create the chart, do some basic formatting to seamlessly add the subreport to the report, without any unused space.

  1. Right-click the chart and click Size to Container -> Fit Both.
  2. In the main report, right-click the subreport and click Size to Container -> Fit Both.

Save any changes to the report before previewing. The chart is displayed on the last page of the report.

Print | posted on Tuesday, July 7, 2015 3:30 AM | Filed Under [ dynamicscrm cdata jdbc dynamics Jaspersoft ]


No comments posted yet.
Post A Comment

Powered by: