After finishing off our ASP.NET, SQL, and ExcelWriter training, the Technical Services Interns were given a project to create a proof of concept involving the dynamic generation of internal reports. We would use our new SQL knowledge to pull data from a customer relations database (Microsoft CRM), our ExcelWriter knowledge to create a rich Excel report from that data, and our ASP.NET skills to create a Web Form whereby users could constrain the reports they received. The reports would then be ported into SharePoint webparts. The reports were split into three categories:
- Renewal Forecast
- Case Distribution
- Customer Activity
This post addresses the first of these reports. The Renewal Forecast report gives a company’s Sales and Support departments insight into customer contracts coming up for renewal.
Creating the Renewal Forecast Report
The purpose of this report is to provide a detailed overview of customers’ support contracts that are nearing expiration so employees can better manage the renewal process. The front-end in SharePoint is customizable so the report is generated completely dynamically and populated using queries from CRM. It is all ASP.NET code and uses a very sharp looking jQuery datepicker. Of course, the report generation is handled nicely using an ExcelWriter template. Not a current ExcelWriter user? No problem: you can download a free evaluation and follow along!
The web form below started as simple ASP.NET web controls – a calendar, ListBoxes, a textfield, and a lonesome button. Initially, the ListBoxes were hardcoded but later when I started digging into SQL, I dynamically populated the lists using the CRM fields. The ugly default calendar quickly got replaced too. The textfield with the date pops up a nice miniature calendar to pick the end date. And all of these controls without postback! Oh, the wonders of AJAX and jQuery.
SharePoint front end. Contract Owner names have been redacted for privacy.
Next, the body of the report with detailed views of all the queried contracts. It portrays all the needed information at a glance and even features clickable links direct to the contracts, opportunities, and contacts in CRM. Honestly, the hardest part about creating this page was the formatting and picking the necessary data. Grouping and nesting using ExcelWriter template made displaying all the information and totals grouped by month a very straightforward process. An outline of the steps taken to produce this report is as such:
- Make the ExcelWriter template in Excel to make the formatting nice and pretty.
- Write the query to pull the necessary data into a monstrous data table.
- Tie the data table into ExcelWriter with C#.
- Connect the backend code to the front end interface (make the button functional).
- Rinse and repeat steps 1-3 until the desired report is generated.
[Main report page. All names are fictional and data has been redacted.]
The final part of the Renewal Forecast was the summary sheet for the report itself. At first, it seemed easy using Excel’s built in functionality to reference and calculate based on the main body of the report but since the report is generated dynamically, this was not possible. So unfortunately, this required another query, but was very important for insight into renewals. After debugging some filtering issues and tracking down duplication culprits (legacy joins…) the summary was born:
[Renewal Forecast summary sheet. Again, all data has been redacted.]
Share the post "How to Create a Renewal Forecast Report with CRM, ExcelWriter and SharePoint"
Follow