Introduction

In this article we will delve deeper into Salesforce and SSRS integration, and will learn how to test connection. We are continuing from where we left off. By this time, you must have installed ODBC PowerPack, created ODBC Data Source, and configured authentication settings in your Salesforce account .

So, let's not waste time and begin.

Use Query Builder to generate SQL query

1. The first thing you have to do is open Query Builder:

   

   ZappySys API Driver - Salesforce

   Amazon Ads Connector can be used to get Amazon advertisements using Amazon Advertisements API, download various advertisement-related reports.

   SalesforceDSN

   

2. Then simply select the Test Connection endpoint (action).

3. Continue by configuring the Required parameters. You can also set optional parameters too.

4. Move on by hitting Preview Data button to preview the results.

5. If you see the results you need, simply copy the generated query:

   Test Connection

   This Endpoint has no parameters.

   SELECT * FROM test_connection

   

6. That's it! You can use this query in SSRS.

Let's not stop here and explore SQL query examples, including how to use them in Stored Procedures and Views (virtual tables) in the next steps.

SQL query examples

Use these SQL queries in your SSRS data source:

How to Test connection

SELECT * FROM test_connection

Test Connection in SSRS

1. Open Visual Studio and create a new SSRS project.

2. Then add a new Shared Data Source (you can create a non-shared data source inside report too):

   

3. Continue with creating the Shared Data Source. Select Microsoft SQL Server as Type and hit Build button to proceed further:

   

4. Once a window opens, configure it similarly. Configure "SalesforceDSN" as database name. Finally, hit Test Connection and OK:

   SalesforceDSN

   

5. Another window opens, and it should look similarly to this one below which ends the creation of a Data Source:

   DataSource=localhost,5000;Initial Catalog=SalesforceDSN

   

6. Now it's time to create a Dataset. If you don't have a report created, in one of the wizard's steps it will look like this:

   SELECT * FROM Orders

   

7. Finally, once you complete the report, similar results will show up:

   

More actions supported by Salesforce Connector

Learn how to perform other actions directly in SSRS with these how-to guides:

• Get Beta Report
• Get Profiles
• Get Report
• Make Generic API Request
• Make Generic API Request (Bulk Write)

More integrations