One of the great features of ExcelWriter is Grouping and Nesting feature of ExcelTemplate. Grouping and Nesting allows you to easily format your data in order to make it more readable. This feature is especially handy when applied to a common report, such as a sales pipeline report. Internally at SoftArtisans, we use ExcelWriter with Grouping and Nesting when generating our own sales pipeline report! By taking advantage of this feature, you can get fancier looking reports with little additional effort – ExcelWriter does the formatting work for you!
Before we begin, let me say that I will not attempt to tell you how to query your data as I’m sure everyone will be using different CRM systems. However, no matter how you’re querying your data (be it SQL, or a web service like SOAP or REST) the process is generally the same.
To start, you need to decide how you want to group your data. In most cases you’ll want to group first by month or quarter; then you could group by salesperson. These can be whatever you want, but you must have a column in your result set that will represent each.
For example, we group our own sales report data by:
- Estimated Close Date (month), descending
- Salesperson, alphabetical descending
Once you understand how you want to display your data, you will need to build your query into a single (sorted) result set. If you are querying data from a web service like SOAP and your results are strictly typed, just loop through your results and build a simple DataTable. ExcelWriter can accept a DataTable as its data source.
Next, you will need to build your template (or modify an existing template). This is where you will provide instructions to ExcelWriter about where to place data and how to group it. This will really depend on your data and how you wish to group your data, so I will just give a simple example.
The following is an excerpt of a template using Grouping and Nesting:
This image illustrates the usage of grouping by Month (EstimatedClose in this example), then by Salesperson (Owner in this example). The tag %%group instructs ExcelWriter to group all of the output in that column. The tag %%header tells ExcelWriter that the cell below is the header for each group. In addition, you can use the %%footer tag, which allows you to summarize each grouping and provide nice totals and subtotals using regular Excel formulas. For detailed explanations on all of the data markers and tags, please see our advanced Grouping and Nesting example in ourdocumentation.
When the template is populated with data, ExcelWriter provides the grouped and nested output from a single set of data:
While this is not a comprehensive step-by-step example of building a sales pipeline report, hopefully you have been able to see that it is a great feature which can help improve the formatting and output of your reports. With just a little reworking of your report template, you can start taking advantage of this great feature.
Share the post "Creating a Sales Pipeline Report with ExcelWriter"
Follow