Create Data Visualizations in Cognos BI with Salesforce Data

You can use the CData ODBC driver for Salesforce to integrate Salesforce data with the drag-and-drop style of Cognos Report Studio. While this article uses Salesforce data, you can follow this tutorial to connect to any CData ODBC Driver from Cognos BI and create data visualizations in Cognos Report Studio. This article describes both a graphical approach to create data visualizations, with no SQL required, as well as how to manually edit the SQL query supported by Salesforce.

Configure and Publish the Data Source

Follow the procedure below to establish a connection to Salesforce in the Cognos Administration tool. You can use this connection throughout Cognos BI. With the driver, all users can work with consistent data and metadata: the live Salesforce data.

1. If you have not already created an ODBC DSN for Salesforce, create one with the Microsoft ODBC Data Source Administrator tool. See the "Getting Started" chapter in the help documentation for a guide.

   If you are running Cognos from a 64-bit machine, you will need to open the 32-bit ODBC Data Source Administrator. You can open it with the following command:

   C:\Windows\sysWOW64\odbcad32.exe

2. In the resulting DSN Configuration wizard, create a new system DSN.

3. Open Cognos Administration and add a new data source. Click Data Source Connections, select the ODBC option, and enter the system DSN and a user-friendly name. Click Retrieve Objects and choose the Salesforce database object.

Add Data Visualizations to a Report

You can now create reports in Cognos Report Studio with Salesforce data by dragging and dropping table columns from the Source Explorer onto report objects. The sections below show how to create a simple report with a chart that shows up-to-date data.

As you build the report, Cognos Report Studio will generate SQL queries and rely on the driver to execute them. The driver will convert queries into requests to the Salesforce API. To execute queries to the live Salesforce data, the driver depends on the capabilities of the underlying API.

Create a Chart Based on an Aggregate

You can populate almost any report object in Cognos with Salesforce data by simply dragging and dropping columns from the Source Explorer onto the dimensions of the object. The column in the Series dimension of the chart is automatically grouped.

Additionally, Cognos sets a logical default aggregate function for the measure dimension based on the data type. For this example, override the default by clicking the Id data item in the Data Items tab and set the Aggregate Function property to COUNT. The Rollup Aggregate Function property must be set to Automatic.

Convert a Query Object to SQL

When you know the query you need, or if you want to adjust the generated query, convert a query object into an SQL statement. After a query has been converted to SQL, the UI controls are not available for the query object. Follow the procedure below to populate a chart with user-defined SQL.

Cognos will rely on the driver to execute the user-defined query. Using the driver's SQL engine ensures that queries will always return up-to-date results, as there is no cached copy of the data.

1. Hover over the Query Explorer and click the Queries folder to display the queries that correspond to objects in your report.
2. Drop an SQL object in-line with the query object. Or, to edit the autogenerated query, in the Query properties click the button in the Generated SQL property, and then click Convert in the resulting dialog.
3. Modify the properties for the SQL object: Select the Salesforce data source in the SQL properties and set the SQL Syntax property to Native.
4. Click the button in the SQL property and enter the SQL query in the resulting dialog. This example uses the query below:

   SELECT Industry, SUM(AnnualRevenue) FROM Account GROUP BY Industry

5. Modify the properties for the query object: Set the Processing property to "Limited Local". This value is required to convert a query object to SQL.

Fill a Chart with the Results of a Query

You can now access the results of the SQL query as objects in the Data Items tab. Follow this procedure to create a chart with the results; for example, the Annual Revenue for each Industry.

1. Return to the page by hovering over the Page Explorer and then clicking the page object.
2. Drag a pie chart from the toolbox onto the workspace.
3. In the properties for the chart, set the Query property to the name of the query you created above.
4. Click the Data Items tab and drag columns onto the x- and y-axes. In this example, drag the Industry column to the Series (pie slices) box and the AnnualRevenue column to the Default Measure box.
5. Modify the default properties for the Default Measure (the AnnualRevenue values): In the Aggregate Function box, select the "Not Applicable" option.

Run the report to add the results of the query: