posts - 88 , comments - 3 , trackbacks - 0

Tuesday, July 7, 2015

Use the CData ODBC Driver for NetSuite in SAS JMP

You can use the CData ODBC Driver to integrate NetSuite data into the statistical analysis tools available in SAS JMP. This article shows how to use NetSuite data in the Graph Builder and Query Builder.

You can use the CData ODBC Driver for NetSuite to integrate live data into your statistical analysis with SAS JMP. The driver proxies your queries directly to the NetSuite API, ensuring that your analysis reflects any changes to the data. The CData ODBC Driver supports the standard SQL used by JMP in the background as you design reports.

The NetSuite API supports bidirectional access. This article shows how to access NetSuite data into a report and create data visualization. It also shows how to use SQL to query and manipulate NetSuite data from the JMP Query Builder.

Access NetSuite Data as an ODBC Data Source

If you have not already done so, specify connection properties defined in the data source name (DSN).

Below is a typical connection string:

Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;

You can configure the DSN in the built-in Microsoft ODBC Data Source Administrator. This is the last step of the driver installation. See the "Getting Started" chapter in the help documentation for a guide to use the Microsoft ODBC Data Source Administrator to create and configure a DSN.

Import NetSuite Data with the Query Builder

After you have created the NetSuite DSN, you can use SQL to invoke the capabilities of the NetSuite API. Follow the steps below to execute some supported queries in the Query Builder:

  1. In SAP JMP, click File -> Database -> Query Builder. The Select Database Connection dialog is displayed.
  2. Click New Connection.
  3. On the Machine Data Source tab, select the DSN. In the next step, the Select Tables for Query dialog is displayed.
  4. In the Available Tables section, select a table and click Primary.
  5. As you drag Available Columns to the Included Columns tab, the underlying SQL query is updated.
  6. Click Run Query to display the data.
  7. To refresh the results with the current data, right-click Update from Database and click Run Script.

Manipulate NetSuite Data

You can execute data manipulation queries from JSL scripts such as the one below. To execute a script, click New Script in the toolbar. To connect, specify the DSN. You can then use the standard SQL syntax:

Open Database( "DSN=CData NetSuite Source;",
VALUES (''Furniture : Office'');");

Vizualize NetSuite Data

After importing, you can use the Graph Builder to create graphs visually. To open the Graph Builder, click the Graph Builder button in the toolbar.

  1. Drag a dimension column onto the x axis. For example, CustomerName.
  2. Drag a measure column onto the y axis. For example, SalesOrderTotal.
  3. Select a chart type. For example, a bar chart.

Posted On Tuesday, July 7, 2015 3:47 AM | Filed Under [ cdata ODBC data netsuite SAS JMP ]

Publish Reports with Salesforce Data in Crystal Reports

Use the Report Wizard to design a report based on up-to-date Salesforce data.

Crystal Reports provides built-in support for the JDBC standard. The CData JDBC Driver for Salesforce enables you to use the Report Wizard and other tools to access Salesforce data in Crystal Reports. This article shows how to create a simple report that features Salesforce data.

Connect To Salesforce Data

Follow the procedure below to use the Report Wizard to create the Salesforce connection.

  1. If you have not already done so, specify connection properties in a DSN (data source name). You can use the Microsoft ODBC Data Source Administrator to create and configure ODBC DSNs. Typical connection properties are the following:

    • User
    • Password
    • SecurityToken

    See the "Getting Started" chapter in the help documentation for a guide to setting the required properties in the Microsoft ODBC Data Source Administrator.

  2. In a new report, click Create New Connection -> ODBC.

  3. In the resulting wizard, click Select Data Source and select the DSN in the Data Source Name menu.

Design a Report

After adding a JDBC connection to Salesforce, you can then use the Report Wizard to add Salesforce data to your report.

  1. Configure the data source by selecting the tables and fields needed in the report. This example uses the Name and AnnualRevenue columns from the Account table.

  2. Configure the chart type. For example, create a Bar Chart that aggregates the values in the Name column. After naming the chart, select Account.Name from the 'On change of' menu. In the 'Show summary' menu, select the SUM function and Account.AnnualRevenue. As you complete the wizard, Crystal Reports builds the SQL query to be executed to Salesforce data. The driver executes the query against the live Salesforce data.

  3. Configure other filters and the report template, as needed.

Preview the finished report to view the chart, populated with your data. If you want to filter out null values, use a SelectionFormula.


Posted On Tuesday, July 7, 2015 3:36 AM | Filed Under [ salesforce cdata jdbc reports Crystal Reports ]

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.

Posted On Tuesday, July 7, 2015 3:30 AM | Comments (0) | Filed Under [ dynamicscrm cdata jdbc dynamics Jaspersoft ]

Powered by: