You didn’t want to write code to get insight into your business? Alright, that’s fair. Check out CRM 4.0 because the CRM investment will really pay off – business users can now build their own reports with the report building wizard.
Since it’s a wizard there may be an assumption that there’s no explanation needed around it – that’s partially true: it’s easy to use and most people can move right though it and get exactly what they want. But you can get way more than you think out of it. Read on and you’ll get a walk through of how to build reports on processes that are being managed in CRM; reports like this really boost the value of CRM because they can show movement through the system, not just a static view of data.
But first, let me provide a little foundation on the choices in the Report Wizard. The report wizard asks which record types you want to include in the report and offers Primary and Related records. The “Primary” list is a list of all entities that are valid for Advanced Find. The Related records are the entities that have a many to one or a many to many relationship back to the Primary record. You can access properties of objects with one to many relationships to the Primary or Related records in a later screen when you choose columns for your tables. In any case, selecting a secondary object here will allow you to access properties and of that object in a later screen. If that isn’t quite clear, don’t worry, you can still create great reports without understanding the underlying data model.
In order to report on process you need a process, or more precisely a workflow, to report on. Without going into details on how to define a multi stage process using CRM workflow let’s just say that you have one, it has some objects running against it and that, within the editor, it looks something like this:
Note the stage names, those are the items in white text on a darker blue background, I’ve named them and included a number in the stage name this will be important later when we sort the results of the report.
The idea with reporting on a process is that we want to see what items are within a given stage of the process. In the Lead Distribution workflow above we have a lead qualification stage, a distribution stage and follow-up stage. Each stage has a different set of people responsible for it so knowing where leads are in the process is pretty important. So let’s go create a report showing a listing of leads grouped by the process stage that they’re in. Head to the Reports area and click the New button to create a new report, indicate that you want to create a Report Wizard based Report (the default) and then click the “Report Wizard” button on the form then within the wizard:
1. Indicate that you want to create a new report and click Next.
2. Type a report name and description if you like.
3. Choose the record with the process you want to report on. In my example that’s a Lead.
4. For the Related record type choose System Jobs; System Jobs have a many to one relationship to Leads (and anything else that can engage in workflow).
System Jobs are the generic term we use for any sort of operation that occurs in our asynchronous event pipeline. That includes workflows but a ton of other things like duplicate detection, bulk editing, bulk deletion – the list goes on and includes some interesting items. If you want some insight into the list of things that the CRM application does behind the scenes look at that list.
5. Once you’ve chosen System Jobs as your related record type click Next.
6. You now get to filter the records that will show up in your report.
This information gets converted into a SQL WHERE clause but all you need to know here is that the UI is just like the Advanced Find UI in the application. It’s a little different from the normal Advanced Find UI in that you have two different entities that you can filter – the primary as well as the related entity type. The key thing here is to make sure that you filter the System Jobs records so the next step is:
7. Within the System Jobs section click “Select” and choose “System Job Type”, “Equals” and “Workflow”. This will limit the System Jobs listing to just workflow items.
The next page of the wizard lets you define the columns and groupings for your report.
8. Choose to add a Grouping
9. In the dialog that pops up indicate that the record type you want to use is System Jobs
10. In the column selector choose Workflow Stage
11. Add which ever columns you want to the report like the Lead Name and Owner but include some other columns from the System Jobs object – Started On is one of the more interesting.
12. Skip the charts for now and click Next through the wizard to confirm the report definition and then to publish the report to SQL Server Reporting Services.
When you are returned to the Report form you can click on the Run Report button on the top of the Report form. That should open a new page and let you run the report. Depending on the columns that you’ve chosen you’ll get something like this:
And there you have a report on process. Note that the “Started On” column contains the date and time that the lead entered that workflow stage. Using that data you can see how long a given lead has been sitting at a particular stage – pretty useful. You can certainly do this sort of thing by having a picklist on the Lead and having users change the picklist based on stage. But this just gives users more work to do and requires that they remember to change yet another thing while managing their work.
Building processes that are triggered off of the work itself is far more likely to meet success – users will be more likely to comply and your data will reflect the reality of what is happening within the business process, not just the data that’s been entered into the system.