CRM Ad Hoc Reporting With Report Builder

Recently, I had a situation come up with a client where they needed Ad Hoc reporting in CRM.  Normally, I would point them to Advanced Find or the Report Wizard in 4.0.  The problem here was that they really wanted to get at data in the salesorderdetails entity and that just isn't visible from a normal Advanced Find.  They also wanted to do some particularly complex queries where they wanted to know customers that bought a particular product during a particular time period but did not buy a particular product during a different time period and that really could not be accomplished through Advanced Find.  They had so much data that exporting to Excel would have been too cumbersome to accomplish.  So, we decided to implement Report Builder. 

The benefits of using Report Builder are:

1. Can access any table or view in the CRM database in pretty much any way that you want to.  You can combine certain tables and views together to make it easier for the end user to access data that is related.

2. Report Builder does not currently require any installation on the client.  It will install itself the first time it is executed.

3. Allows end users to build reports

The purpose of this blog is to discuss the steps involved in making an ad hoc reporting environment using a report model and Report Builder.  I do assume that you have some knowledge of BIDS/Visual Studio 2005 so some steps will be summarized.

Step 1. Building The Report Model

Report Builder requires that data be available through a report model.  A report model is essentially a Data Catalog that is built using Business Intelligence Development Studio (BIDS) and allows developers to do all the heavy lifting of publishing data to end users in a meaningful way.  Tables or Views can be joined together to produce some great views of data with "English" field names that end users will understand.

The scenario I will use to demonstrate the building of the report model is the following:

A marketing person wants to monitor the activities occurring for a particular campaign to verify that people are making phone calls to actively drive the campaign.  In order to do that, I want to be able to show a matrix of activities associated with a Campaign so that I can see the number of phone calls made by sales rep.

To create the report model, open BIDS (Visual Studio 2005 with the SQL Server Reporting Services AddIn), create a new report model project and do the following steps:

Step 1a. Connect to The Data Source

Create a new data source using the wizard.  You should use windows authentication and connect to the MS CRM database.

Step 1b. Create a Data Source View

Create a new data source view using the wizard.  However, I usually do not simply add tables or views from that list.  To me, I like to build named queries after the Data Source View is created.  That way, I can get all the joins that really add value.  So, for this particular scenario, to show Activities for campaigns, I use FilteredActivityPointer as the main table and then if I need to pickup custom attributes for a specific type of activity, I need to join with the filteredview for that type of activity (e.g., FilteredPhoneCall, FilteredTask).  Although I did not use these other filteredviews in the actual select, I have included them here so you can see how the join works.  Note that I am using Filtered Views.  Filtered Views allow you to utilize the security features of CRM to only show the data that is appropriate for the user running the report.  You should always hit the FilteredViews where possible unless you have performance concerns.  The other thing to note in the query below is that I am looking at the regardingobjecttypecode to get the account associated with the activity.  If the regarding field has a contact in it then I get the parent account of the contact.  If the regarding field has the account then I simply get the account.  I also use the regardingobjecttype field to get the campaign id and campaign name.  Finally, in the where clause I filter out activity types like service activities as I am only interested in tasks, appointments, phone calls, faxes, and emails.

SELECT        apo.activitytypecodename AS 'Activity Type', apo.activityid AS 'Activity ID', apo.createdbyname AS 'Created By', apo.createdon AS 'Created On',
                         apo.description AS 'Description', apo.modifiedbyname AS 'Modified By', apo.owneridname AS 'SalesPerson', apo.ownerid AS 'SalesPerson ID',
                         apo.prioritycodename AS 'Priority', apo.regardingobjectidname AS 'Regarding', apo.regardingobjecttypecode AS 'Regarding Type',
                         apo.scheduledend AS 'Scheduled End', apo.scheduledstart AS 'Scheduled Start', apo.statuscodename AS 'Status Reason', apo.subject AS 'Subject',
                         apo.actualend AS 'Actual End', apo.actualstart AS 'Actual Start',
                         CASE WHEN apo.regardingobjecttypecode = 1 THEN apo.regardingobjectid WHEN apo.regardingobjecttypecode = 2 THEN
                             (SELECT        c.parentcustomerid
                               FROM            contact c
                               WHERE        c.contactid = apo.regardingobjectid) WHEN apo.regardingobjecttypecode = 3 THEN
                             (SELECT        o.accountid
                               FROM            opportunity o
                               WHERE        o.opportunityid = apo.regardingobjectid) END AS 'Account ID',
                         CASE WHEN apo.regardingobjecttypecode = 1 THEN apo.regardingobjectidname WHEN apo.regardingobjecttypecode = 2 THEN
                             (SELECT        c.parentcustomeridname
                               FROM            contact c
                               WHERE        c.contactid = apo.regardingobjectid) WHEN apo.regardingobjecttypecode = 3 THEN
                             (SELECT        o.accountidname
                               FROM            opportunity o
                               WHERE        o.opportunityid = apo.regardingobjectid) END AS 'Account Name',
                         CASE WHEN apo.regardingobjecttypecode = 3 THEN apo.regardingobjectid END AS 'Opportunity ID',
                         CASE WHEN apo.regardingobjecttypecode = 3 THEN
                             (SELECT        o2.campaignid
                               FROM            filteredopportunity o2
                               WHERE        o2.opportunityid = apo.regardingobjectid) WHEN apo.regardingobjecttypecode = 4402 THEN
                             (SELECT        ca.regardingobjectid
                               FROM            filteredcampaignactivity ca
                               WHERE        ca.activityid = apo.regardingobjectid) END AS 'Campaign ID', CASE WHEN apo.regardingobjecttypecode = 3 THEN
                             (SELECT        o2.campaignidname
                               FROM            filteredopportunity o2
                               WHERE        o2.opportunityid = apo.regardingobjectid) WHEN apo.regardingobjecttypecode = 4402 THEN
                             (SELECT        ca.regardingobjectidname
                               FROM            filteredcampaignactivity ca
                               WHERE        ca.activityid = apo.regardingobjectid) END AS 'Campaign Name'
FROM            FilteredActivityPointer AS apo LEFT OUTER JOIN
                         FilteredPhoneCall AS pc ON apo.activityid = pc.activityid LEFT OUTER JOIN
                         FilteredEmail AS em ON apo.activityid = em.activityid LEFT OUTER JOIN
                         FilteredAppointment AS app ON apo.activityid = app.activityid LEFT OUTER JOIN
                         FilteredFax AS fax ON apo.activityid = fax.activityid LEFT OUTER JOIN
                         FilteredTask AS tsk ON apo.activityid = tsk.activityid
WHERE        (apo.activitytypecode IN (4201, 4202, 4204, 4210, 4212))

To create the named query, you can right click on the datasourceview and select New Named Query option.  Once you have created the named query, a table will display on the datasource view with the name of the named query and it will show a list of fields returned from the query.

One thing to remember when creating your data source view is that you should Alias your resulting field names to user friendly names that end users will understand.  So, for example, in the query above, I have the following in the select statement "apo.owneridname AS 'SalesPerson'".  This allows end users to select a field called SalesPerson which makes sense instead of owneridname which doesn't make sense to most end users.

After you create a named view, you need to set a logical primary key so I usually include the GUID for the table that makes sense and make that the logical primary key.  To set the logical primary key, right click on the field in the named query table that you want to be the logical primary key and select set logical primary key from the menu.

After setting the logical primary key, you need to set any relationships to other named queries that you may have.  This option is available by right clicking on a named query table and selecting New Relationship... from the menu.  This is critical to allow users to join information together in Report Builder.

My example data source view is shown in the picture below.

Step 1c. Create a Report Model

Once you have your data source view, you are really close to being done.  Creating the actual report model can be very easy depending on your needs.  I always try to use the wizard as it greatly simplifies things.  The wizard will look through the Data Source View and create the appropriate model entities and source fields based on the fields and relationships defined in the Data Source View.  After the wizard has created the report model, you can edit the results.  The main things I tend to edit are the format of the fields, the names of the relationships, and the default detail attributes.  All of these are available in the properties for a source field or entity.  You simply select the property and update the value.  The format for the fields are useful for money and decimal fields.  Use "c2" for money fields and "f2" for things like quantities.

Step 1d. Publish the Model

Publishing the model is very easy.  First, you have to specify the report server url in the solution properties.  After that is done, simply build the solution and deploy.

Step 2. Making Report Builder Easily Accessible From CRM

Report Builder is typically an icon on the report manager website.  This is very inconvenient for the CRM user who wants to access it.  So, to give CRM users easy access, I put a button on the CRM main application toolbar right next to Advanced Find using isv.config.  The entry in isv.config looks something like this:

<ImportExportXml version="4.0.0.0" languagecode="1033" generatedBy="OnPremise">
  <Entities>
  </Entities>
  <Roles>
  </Roles>
  <Workflows>
  </Workflows>
  <IsvConfig>
    <configuration version="3.0.0000.0">
      <Root>
       <MenuBar>
          <CustomMenus>
           ...
          </CustomMenus>
        </MenuBar>
          <ToolBar ValidForCreate="0" ValidForUpdate="1">
          <Button Icon="/_imgs/bar_bottom_ico_reports.gif" Url="http://cesql01/reportserver/reportbuilder/reportbuilder.application" PassParams="0" WinMode="0" WinParams="directories=0,height=150,width=300,location=no,menubar=no,resizable=no,scrollbars=no,status=no,titlebar=no,toolbar=no" Client="Web, Outlook" AvailableOffline="false">
            <Titles>
              <Title LCID="1033" Text="Report Builder" />
            </Titles>
            <ToolTips>
              <ToolTip LCID="1033" Text="Report Builder" />
            </ToolTips>
          </Button>
        </ToolBar>
     </Root>

Note: the url of course needs to be updated with the url of your SSRS server.  For IFD deployments, there may be some changes necessary to make the server accessible through the internet.

Comments

[Michael Dodd]

Where would the end user UI be located for the report builder. This would be a fantastic enhancement if it could be integrated into the CRM UI. Has this been/Can this be accomplished?

[Earle Oxner]

In step 2 above, we modified the isv.config portion of the CRM config. This will cause a button to appear on the toolbar next to Advanced Find. That makes it very easy for a user to access. Hope that helps.

[Coenraad Fick]

We are looking to do something similar with Report Builder 2.0, but we need to limit the fields the user will be able to select to fields that will be available in data that will be stored as XML data in SQL 2008. Is this possible doing this using the above mentioned technique, or do you maybe know of alternative solutions to achieve this.