How to Build a Microsoft Dynamics 365 Sales Pipeline Report Using .NET

A sales pipeline is a way of tracking potential customers in an organized manner. It lets you observe the progress of each prospect as they move through different sales stages. In Dynamic 365 Sales, a sales pipeline is divided into four sale stages: Qualify, Develop, Propose, and Close, and the prospects are monitored by creating a Sales Pipeline Report.

The information in Sales Pipeline Report is used to forecast future revenue and set goals for the sales team. The report displays a chart of potential sales grouped by user, sales territory, customer territory, date, products, rating, or sales stage, and a user can click on a Show All or similar option to view the detailed report.

In this blog, we will learn how to create a Sales Pipeline Report using FlexReportDesigner by binding to Dynamic 365 Sales data using C1.AdoNet.D365S data provider. We will be creating a Sales Pipeline Report in which the Sales Pipeline Chart will be grouped based on Sales Stage and Close Date, followed by a detailed report which is hidden by default and can be viewed by clicking on View Detailed Report option. Here is a quick multi-page view of the report we will be creating:

Follow along the detailed steps below to create a Sales Pipeline Report.

Download Now!

Create a Sales Pipeline Report using FlexReportDesigner

FlexReportDesigner is a standalone application available at the following location: C:\Program Files (x86)\ComponentOne\Apps\v4.5.2, provided Studio for Winforms is installed on your system. The FlexReportDesigner has two versions, 32 bit and 64 bit, you can work with either of these, based on your requirement. For more details on FlexReportDesigner, refer to following documentation link.

Let's begin by opening an instance of the FlexReportDesigner application, which will be used to create the report using the below-mentioned steps.

Step 1: Create a New Report

The first step to creating a Sales Pipeline Report is to create a new empty report, following the below steps:

1. To create a new report, click on the “New Report” dropdown button in the Reports section in the left panel of the FlexReportDesigner. From the dropdown menu choose “Empty Report”. Observe it adds an empty report with name Report1.

2. Change the report name by setting the ReportName property in Properties window, here we set it to Sales Report.

Step 2: Bind Report to DataSource

When creating a report, the core purpose is to represent data in a presentable format which helps the report users analyze different aspects of data. Sales Pipeline Report lets users analyze the sales opportunities available in the near future and accordingly plan the sales tasks ahead. In this step, we will learn how to fetch data from Dynamic 365 Sales and bind to the report.

Switch to Data tab available next to Reports tab. You will notice that a datasource named Main is added to the report by default and has been set to the DataSource property of Sales Report. However, Main DataSource is still not bound to any data. The steps below will guide you on how to bind this default datasource to data.

1. Select Main datasource and click Edit option in the Menu bar at the top. It displays the Data Sources window.
2. In the Data Sources Window, from the Data Provider dropdown menu choose C1.AdoNet.D365S, as we are going to create Sales Pipeline Report by binding to Dynamic Sales 365 data. C1.AdoNet.D365S data provider is used to fetch Dynamic 365 Sales data and is provided by C1DataConnector which is supported by FlexReportDesigner. Refer to C1DataConnector documentation, for more details.

3. In the Connection string field, you can directly add a connection string for Dynamic 365 Sales data. Or you can click the ellipsis next to Connection string field and set individual attributes for connection string using Data Link Properties window which would later be integrated and displayed as a Connection string.

You may refer to the connection string documentation topic for further detail on the same. When integrating FlexReport and C1DataConnector in a Winforms application, you can enable cache settings for the Data Provider using connection string. Refer to the cache settings documentation topic for details. Here is the expected connection string for binding to Dynamic 365 Sales data:

Url=https://xxx.xxxx.com/api/data/v9.1/;Use Etag=true;OAuth Client Id=*****;Username=*****;Password=*****;OAuth Token Endpoint=*****;OAuth Extend Properties={'resource':'https://xxx.xxxx.com/'};Max Page Size = 100;

Notice the tables are displayed in the Data source tab. We can either select the tables and fields from this list or define a SQL query in the SQL statement tab.

Fetch Data

The Dynamic 365 Sales typically stores the fields required to generate the Sales Pipeline Report in the Opportunity entity. We will be using the following SQL query to fetch the required fields from different tables by using Inner Join. You can alter this query as per your data and report requirements:

Select op.opportunityid, op. gc_renewaldate, opp.extendedamount, p.name, ps.stagename, su.fullname from opportunities op inner join opportunityproducts opp on opp._opportunityid_value = op.opportunityid inner join opportunitysalesprocesses osp on osp._opportunityid_value = op.opportunityid inner join products p on p.productid = opp._productid_value inner join accounts a on a.accountid = op._parentaccountid_value and a.name like 'account%' inner join processstages ps on ps.processstageid = osp._activestageid_value inner join systemusers su on su.systemuserid = op._ownerid_value where gc_renewaldate IS NOT NULL LIMIT 105

Add the above query to SQL statement tab and observe all the fields displayed under the Main datasource.

Step 3: Design Report Layout

Configure Header Section

The Header section of FlexReport is the first section to be rendered when rendering a report. It is rendered only once at the beginning of a report. We will add a Report title, a hidden TextField used to control the visibility of detailed report, a Sales Pipeline Chart, and an option to view the detailed report in this section.

We begin by setting the Height property of Header section to 10770. We will also write script to handle the OnFormat event, to add a page break when rendering the detailed report. This page break ensures that the Sales Pipeline Chart is rendered on one page, while the detailed report starts rendering from a new page. Add the following script to OnFormat event, using the VBScript Editor. Refer to the following documentation link which describes how to work with VBScript Editor in FlexReportDesigner.

If hiddenField.Text = "Hide Report"
Then
Header.ForcePageBreak = "None"
Else
Header.ForcePageBreak = "After"
EndIf

Add Report Title

In this step, we will add report title, by inserting a TextField in the Header section of the report and format the TextField by setting following properties:

Add a Hidden TextField

Here, we add a TextField, which will be used to control the visibility of the detailed report. Insert a TextField in the Header section and configure it by setting following properties:

Add Column Chart

Next is adding a Column Chart to create the Sales Pipeline Chart. We will add the column chart in the Header section of report, as the chart is expected to be rendered only once, at the top of the report. Follow these steps:

1. Insert a TextField in the Header section to serve as the chart title and format the TextField by setting following properties:

2. Insert a FlexChartField below the TextField with following configuration:

3. Add a series to the FlexChartField by configuring the Series property as described below using the Series Collection Editor, accessed by clicking ellipsis next to FlexChartField's Series property:
   • Remove the default series and add a new series by clicking on Remove and Add buttons respectively.
   • Set YExpression property of series to =sum(extendedamount) using Expression editor, where extendedamount is a database field.
   • Set Label property of series to =stagename, where stagename is a database field.

Here is a quick view of Series Collection Editor, after adding and configuring a series:

4. Next, add a Series group to group the chart series by Sales Stage. Add a series group to FlexChart by configuring the SeriesGroups property as described below, using the DataGroup Collection Editor, accessed by clicking ellipsis next to FlexChartField's SeriesGroups property:
   • Add a new SeriesGroup by clicking the Add button.
   • Set the GroupExpression property of SeriesGroup to stagename, where stagename is a database field.

Here is a quick view of DataGroup Collection Editor, after adding and configuring a series group:

5. Add CategoryGroups by accessing DataGroup Collection Editor using the ellipsis next to CategoryGroups property of FlexChartField. Add a new CategoryGroup and set its GroupExpression property to Month(gc_renewaldate) to group the dates displayed on XAxis by Month, where gc_renewaldate is a database field.

Here is a quick view of DataGroup Collection Editor, after adding and configuring a category group:

6. Set the XLabelExpression property of Chart to MonthName(Month(gc_renewaldate)) & " " & Year(gc_renewaldate), to plot the CloseDate on X axis grouped by Month and year.

7. Set the Stacking property of FlexChart to Stacked.

8. Configure AxisX title by setting AxisX Title property to “Close Date”, TitleStyle Font property to Segoe UI, 11pt, Bold and LabelAngle to 30.

9. Configure AxisY title by setting AxisY Title property to “Sum of Amount” and TitleStyle Font property to Segoe UI, 11pt, Bold.

10. Configure the Legend by setting Legend Title property to Sales Stage and Legend → Style → Font property to Segoe UI, 10pt, Bold.

Add a Hyperlink to Display the Detailed Report

In this step, we will add a TextField, which when clicked displays the detailed Sales Pipeline Report. Insert a TextField below the FlexChartField and configure it to serve as a hyperlink by setting the following properties:

Next, add the script to be executed on clicking the hyperlink, using VBScript Editor. Open the VBScript Editor by clicking on ellipsis next to OnNavigate property of LinkTarget. Add following script:

hiddenField.Text = "View Report"

Here is quick view of the Header section of FlexReportDesigner, after adding all the above mentioned fields:

Configure Detail Section

Add TextFields to Display Detailed Sales Pipeline Report

We will begin by setting the Height property of Detail section to 1560.

1. Next, add four TextFields to the Detail section of Report to show detailed Sales Pipeline Report. Insert four TextFields in the detail section and configure them accordingly:

2. Handle the Detail section OnFormat event to control the visibility of detailed report, by adding following script to it:

If hiddenField.Text = "Hide Report" Then
Detail.Visible = False
Else
Detail.Visible = True
EndIf

Here is quick view of the Detail section of FlexReportDesigner, after adding all the above mentioned fields:

Configure GroupHeader Section

Add GroupHeader to Group SalesPipelineReport Data by Sales Stage

The detailed report by default displays the data as fetched from the datasource, but we will group this data by Sales Stages i.e. Qualify, Develop, Propose and Close. To accomplish the same follow below mentioned steps:

1. Add a Group to report by clicking the Group option under Data section of Home Tab. It will display the Groups Editor. Click on Add button to add a group.
2. Set the GroupBy property of Group to database field stagename. The Group Name automatically changes to Group_stagename.
3. Set the ShowGroupHeader property of Group to True and KeepTogether to KeepWholeGroup in Groups Editor. Here is a quick view of Groups Editor, after adding and configuring a group:

4. Close Groups Editor window, and see the GroupHeader and GroupFooter sections have been added to the report.

5. Add a subsection to GroupHeader using the Add Subsection option from the Insert tab. We require a subsection here, to show two different types of formatting in the Group Header.

6. Set first GroupHeader section, Height property to 1275 and second Group Header section Height property to 810.

7. In the first section of Group Header set Background to dark blue (31, 73, 125) and add a few TextFields as described below:

8. In the second section of Group Header, set Background to grey (217, 217, 217) and add four TextFields to serve as column headers for the details displayed through detail section. Insert four TextFields and configure them as:

9. Lastly, handle the OnFormat event of GroupHeader to add following script, which would control the visibility of detailed report:

If hiddenField.Text = "Hide Report" Then
Group_stagename_Header.SubSections(0).Visible = False
Group_stagename_Header.SubSections(1).Visible = False
Else
Group_stagename_Header.SubSections(0).Visible = True
Group_stagename_Header.SubSections(1).Visible = True
EndIf

Here is quick view of the GroupHeader section of FlexReportDesigner, after adding all the above mentioned fields:

Step 4: Save and Preview the Report

The report is now designed and ready to preview. Quickly save the report and click on the Preview button to preview the report. The GIF below depicts the rendered report. Initially it renders a Sales Pipeline Chart, clicking on the View Detailed Report option, renders the detailed report grouped by different sales stages:

Refer to the FlexReport documentation to gain insight on possibilities of creating other types of reports using extensive features of FlexReport and FlexReportDesigner.

Download Now!