How to Build a Leads to Opportunity Salesforce Report Using .NET
We added a .NET Standard service library, DataConnectors, to connect to online data sources like Microsoft Dynamics 365, OData, Salesforce, Google Analytics, and Kintone using an ADO. NET provider for each type. This library can be used to bind any data-aware control with the aforementioned data sources.
We recently published a blog post, “How to Bind FlexReport to Salesforce CRM Data,” which shows how to use the DataConnectors library to bind FlexReport to Salesforce data at runtime. At that time, working the same at design time via FlexReportDesigner was not supported.
With the 2021 v1 release of ComponentOne, we added a new feature to bind reports to data from online data sources like Salesforce, OData, Dynamics 365 Sales, Google Analytics, Kintone, and QuickBooks using ADO.NET DataConnectors with FlexReportDesigner.
This blog will illustrate how to bind a FlexReport to the Opportunity data table of Salesforce using FlexReport Designer converted into Opportunities from the available Leads. The complete steps from creating the connection string to rendering the report are as follows:
Step 1: Build a Connection String
Before connecting to Salesforce, we need to create an app and connect using the app's credentials. This Salesforce resource explains how to configure your application so that OAuth can authenticate your application:
The following information is required to connect to Salesforce:
- OAuth Endpoint Token (https://help.salesforce.com/s/articleView?id=sf.remoteaccess_oauth_endpoints.htm&type=5)
- API URL (https://help.salesforce.com/s/articleView?id=sf.remoteaccess_using_openid.htm&type=5)
- OAuth Client ID
- OAuth Client Secret
- Salesforce Username and Password
- Security Token
Most of the above credentials will be available on the application page in Salesforce, and a security token would have been sent to the registered email used for creating the Salesforce account. The image below displays the OAuth Client Id (Consumer Key) and OAuth Client Secret (Consumer Secret).
Now we have the required information for building the connection string to connect to Salesforce. The final connection string will be in the below-given format as per the documentation.
Username= username ;Password= password;
Security Token=your Token that you got on mail ;
OAuth Client Id= consumer Key;
OAuth Client Secret= consumer Secret;
OAuth Token Endpoint=https://ap16.salesforce.com/services/data/v45.0;
Url=https://ap16.salesforce.com/services/data/v45.0;
Use Pool = false; Max Page Size = 200
Step 2: Data Bind FlexReport to Salesforce Data
Please follow the steps to create a report via FlexReportDesinger and connect with the Salesforce using the connection string that we build in step 1:
- Launch the C1FlexReportDesigner.4.5.2.exe for the 64-bit platform and C1FlexReportDesigner32.4.5.2.exe for the 32-bit platform located at the following location on your computer:
C:\Program Files (x86)\ComponentOne\Apps\v4.5.2
- From the Reports tab on the left, click New Report. This opens the FlexReport Wizard, which walks you through the basic report setup.
- Select the ADO.NET.Salesforce data provider, AdoNet.Salesforce, from the Data provider drop-down.
- Enter your connection string.
- After entering the connection string, it will load the list of default tables, and you can select one from the list, or also you can write your SQL query to get the data from different tables and click Next.
Here we have selected the Opportunity table from the list of default tables.
- Follow the wizard and choose other settings for the report, such as the style, layout template, and title of the report.
By following the above-given steps, you can create a sales opportunity report from the Salesforce data source coming from the different lead sources.
Step 3: Design the Report
Whenever creating a business report, everyone has their own designs to make the report more interactive and using FlexReportDesigner. You are free to design the report as per your need. Please see the documentation to learn how you can modify the report by changing the fields' font, alignment, position, etc.
Here we have presented the data in the grouped format with modified font, alignment, and position of some fields to make the report look nice. Next, we added a FlexChart field in the Group Header section that shows the count of different Stage Names under the particular Lead Source group by setting the following properties:
- XLabelExpression: This property is set to generate the labels on the X-axis. We set it to StageName to show the different stage names on the X-axis.
- YExpression: This property is set to obtain the data of the Y-axis. To access this property, we need to click on the ellipsis of the Series property of FlexChartField and change its value in Series Collection Editor. We set this property to Count(*) to get the count of each label of the X-axis.
- GroupExpression: This property is used to group the labels of the X-axis. To access this property, we need to click on the ellip sis of the CategoryGroups property of FlexChartField and change its value in DataGroup Collection Editor. We set this property to StageName to group similar types of labels of the X-axis.
To learn more about applying grouping to the chart, you can go through the documentation here.
After completing all the modifications, the report looks like below:
You can download the attached PDF to view the generated report. However, you can also run the attached report by adding your connection string.
Similarly, you can connect to other data sources by creating the connection string suggested in the documentation.
Try it yourself! Ask questions in the comments section below. Happy coding!
Prabhat Sharma