The introduction of Excel Online within Dynamics CRM was one of those big, exciting moments within the applications history. For Excel heads globally, it provides a familiar interface in which CRM data can be consumed and modified, without need to take data completely off CRM in the process. It is also a feature that can easily be used by CRM Administrators in order to perform quick changes to CRM data. It’s also one of the benefits of using CRM Online over On-Premise, and probably something I should of included as part of my previous analysis on the subject.

As great as the feature is, like anything with CRM, it is subject to occasional issues in practice; particularly if you use bespoke security roles as opposed to the “out of the box” ones provided by Microsoft. We encountered an issue recently where one of our colleagues had a problem importing modified data from Excel Online back into CRM. Our colleague had no problem opening the data in Excel Online, with the problem only surfacing when they clicked the Save Changes to CRM button. The rather lovely looking error message looked something like this:

Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=8.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: Principal user (Id=0c6ff908-a6c9-e511-8144-c4346bac5e0c, type=8) is missing prvReadImportFile privilege (Id=fe46d775-ca5c-4a09-af93-99a133455306)Detail:

<OrganizationServiceFault xmlns:i=”http://www.w3.org/2001/XMLSchema-instance” xmlns=”http://schemas.microsoft.com/xrm/2011/Contracts”>

<ErrorCode>-2147220960</ErrorCode>

<ErrorDetails xmlns:d2p1=”http://schemas.datacontract.org/2004/07/System.Collections.Generic” />

<Message>Principal user (Id=0c6ff908-a6c9-e511-8144-c4346bac5e0c, type=8) is missing prvReadImportFile privilege (Id=fe46d775-ca5c-4a09-af93-99a133455306)</Message>

<Timestamp></Timestamp>

<InnerFault i:nil=”true” />

<TraceText i:nil=”true” />

</OrganizationServiceFault>

When approaching any type of error message for the first time, it can be quite daunting figuring out what it is saying. Fortunately, in this case, there is only one line we really need to be concerned about, which is the <Message>…</Message>. To translate to plain English, this line:

<Message>Principal user (Id=0c6ff908-a6c9-e511-8144-c4346bac5e0c, type=8) is missing prvReadImportFile privilege (Id=fe46d775-ca5c-4a09-af93-99a133455306)</Message>

Means:

I cannot complete this action for Joe Bloggs, because they are missing the Import Source File Read privilege!

(Note: To help translate the above, I made use of the Security role UI to privilege mapping table on MSDN – a handy link to have in your browser favourites)

Giving the user just this privilege did not resolve the issue, producing a completely different error message in the process. Rather then spend an inordinate amount of time replicating the action over and over again, we did a quick Google search to see if there if we could find a list of the minimum level of permissions required in order to complete We were directed towards this forum post, with an answer from CRM MVP Jason Lattimer on what permissions were required in order to resolve the error message:

Required permissions:

* Data Import (all)
* Data Map (all)
* Import Source File (all)
* Web Wizard (all)
* Web Wizard Access Privilege (all)
* Wizard Page (all)

Problem solved you’d think? Well, unfortunately, in this case not. Although at first we thought that things were working fine, as no error message cropped up. When we then monitored the data import job in background, however, it was stuck at Parsing. At this point, we were really beginning to struggle to think of how to resolve the problem. It was at that point we rather desperately took a look at other, successfully completed, System Jobs to see if there was anything obvious we could observe. We noticed that successfully completed Data Import jobs had 3 System Job Tasks associated with them, whereas our stuck had only 1. At this stage, we asked: Could we be missing privileges on the System Job entity? And, lo and behold, when we took a look at the users security role, there were no privileges configured for System Jobs. After a bit more trial and error, adding permissions one by one onto this role, we saw that the Data Import job ran successfully!

So just to confirm for those who may encounter the same problem in future, the full list of permissions required to get Excel Online Data Import working successfully are the ones highlighted above and the following additional privileges too:

Customization Tab

System Job

Create: Business Unit Level

Read: Business Unit Level

Write: Business Unit Level

Append: Business Unit Level

Append To: Business Unit Level

Assign: Business Unit Level

e.g.

SystemJobMinimumPrivileges

What this problem (and the solution) I think demonstrates is the best way in which to approach day-to-day problems that may crop up within CRM:

  • It is reasonable to assume from the outset that the problem is due to a lack of security permissions problems; try not to over complicate matters early on by assuming it could be something completely different. For example, if the task or action that you are trying to perform CRM can be performed using an account/security role with greater permissions, then this will tell you straight away what the problem is.
  • Having good “under the hood” knowledge of CRM is always helpful in a scenario like this, but this may not always be possible for those of you work with CRM sparingly. To help you in this scenario, we can refer to some of the fantastic resources online by the CRM community. Ben Hosking has a great blog post on the importance of thinking in entities when it comes to working within CRM, something which I think applies great in this particular example. By understanding that the System Job is a system entity, we can logically assume that it has its own set of required permissions.
  • In diagnosing the issue in this case, we were able to refer to some of the previous System Jobs records in the system. As part of this, we observed that a System Job that completed successfully had 3 job records related to it. This immediately told us that there was something wrong with the users access to the entity in question (going back to the above, System Job is, after all, a system Entity). Sometimes, being able to understand the difference between an action, when it works and doesn’t work, can give you the information you need to make the logical next step jump on what to investigate further.
  • And, last but not least, access to and the ability to use a search engine is always very helpful 🙂

CRM2016Update1

It was a very nice post-bank holiday treat in the UK to be treated to news on Tuesday morning that the CRM 2016 Spring Wave has now been released, and can now be accessed as part of a Sandbox Reset or a new CRM Online Trial. 🙂 Microsoft will be targeting organisation updates on Office 365 tenants starting from the 1st week of June, and it’s a better time then ever to upgrade to CRM 2016 if you are still using 2013/2015 within your Online environment.

So what can we look forward to as part of the new release? I have taken a look at what’s new (both obvious and under the hood), and have highlighted the 5 new features that I am most looking forward to as part of the new release:

Characteristics

I’ve been getting myself more and more familiar with the service side of CRM (partly for exam preparation, and also because the new interactive service hub looks really cool!) and I have been impressed with the breadth of options that are available within CRM for service management, scheduling and resource management. One thing that is lacking though is some easy method of recording detailed information regarding a particular resource. For example, if you have a Network Engineer that you need to schedule out for a site visit and you need to ensure that he has a particular certification or qualification in order to complete the job, there is no (easy) way to record this against the resource in CRM.

I am therefore pleased to see that new entities called Characteristics Bookable Resource Characteristics have been added as part of this CRM release, with the following descriptions:

Characteristic – Represents the skills, education, and certifications of resources.

Bookable Resource Characteristic – Associates resources with their characteristics and specifies the proficiency level of a resource for that characteristic.

The entity has fields that records a Characteristic Type and Rating Value, which would seem to be perfect for scenarios similar to the example provided above.

Unfortunately, after digging around in a trial version of the Spring Wave update, I cannot seem to find any obvious means of accessing these entities via the user interface. I assumed that you would be able to add this information from the User form, but no dice. Perhaps this entity has been added in preparation for the next big release of CRM, but there is nothing (in theory) stopping system customisers from exposing this entity manually themselves and start using it. If anyone else manages to find this within CRM, please let me know in the comments below!

Email Signatures

Buried within the What’s New announcement page for the release, is the following small, but potentially significant, new feature announcement:

Save time on your email correspondence and add a professional touch by adding an email signature. Use a default signature for everyday replies and new email messages, and then create other signatures for those special cases. You can also assign a default signature to a queue. When you change the From field from a user to a queue, the default signature changes automatically.

This has been one of my major headaches in attempting maintain consistent email signatures between our Outlook and CRM clients. The problem is compounded somewhat by the fact that the Email Template functionality does not offer a HTML editor. You therefore have to go through a number of different hoops in order to replicate this within CRM, and even then it doesn’t look 100% perfect when compared to our Outlook signatures. If there is therefore a better way of managing this in CRM moving forward, then I am very interested in finding out more.

One problem I’ve noticed straight away though – similar to Characteristics, there doesn’t appear to be any way to access this through the CRM interface! There is a new entity in the system called Email Signature though, which can be accessed via Advanced Find, but looks as if its not something that can be used yet. A real shame, as I was looking forward to seeing this in action.

Guided Help

Eagle eyed CRM users will immediately spot, when first logging into the new CRM update, that the help button looks distinctly different on the top right corner of the screen:

GuidedTours3

The new release introduces the Guided Help feature, which aims to offer more detailed, context sensitive information for users who need help within CRM. Clicking the above button will pop open a sidebar on the left of the screen, that changes depending on where you within CRM:

GuidedTours1

Then, by clicking one of the green links that are listed (again, these change depending on where you are within CRM), you will then be given a tour of how to complete a specific action, such as creating a Lead record:

GuidedTours2

Rather then attempt to poorly emulate some of the existing information available online for this new feature, the video below provides a great summary of what Guided Tours is capable of:

I am really excited to see how this feature develops over the time, and hope that eventually you have the option to create your own guided tours that can then be tailored specifically to individual CRM systems. This feature is also a great example of how Microsoft is leveraging some of the benefits and features of the Azure platform to deliver better services to their online customers.

Feedback

Within previous versions of CRM, there was no out of the box way of recording post-sale information in regards to Products that are setup on the system. I would say that pretty much all businesses are not just concerned with how well their products sell, but also want to drill-down further and get feedback directly from their customers on a Product that has been sold to them.

Now, in the Spring Wave release, the existing Feedback entity (previously used for Knowledgebase Articles) has been extended for use by other entities. To enable for your system/custom entities, all you need to do is tick the Feedback option within the Entity Definition page:

CRM_Feedback

Just to advise you, once it has been enabled for an entity, it cannot be disabled. Once enabled, you can then access it from the entity record page:

CRM_Feedback2

So now, system customisers can modify their CRM systems in order to enable Product feedback and feedback for any type of record. For example, you could configure Feedback for the Competitors entity as well, in order to allow sales users to capture feedback when speaking to potential customers about a supplier they are currently using.

CRM Portals

One of Microsoft’s most high-profile acquisitions last year was ADX Studio, a company that provides customer portal solutions for CRM – the great benefit being that you don’t need to be an ASP.NET whiz in order to setup highly functional, attractive portal systems that feed data into and out of CRM. Starting from the 2016 Spring Wave, Microsoft are starting to more tightly integrate the product with CRM, to the extent that portals will now be manageable from within CRM itself and integrated as part of the Office 365 subscription plans, effectively becoming an add-on product that can be purchased from the portal. Organisations who are considering to use ADX Studio may be advised to wait to see what happens with this, as it is likely that Microsoft may introduce price breaks on the product in order to entice CRM Online uses to try the product. In the meantime, trial ADXStudio account are still available on their website for those who are curious. I am hoping to start getting to grips with the product within the months ahead, so expect a blog post or two on the subject in future.

What are you most excited about as part of the Spring Wave release? Let me know in the comments below.

When I first started looking more closely at some of the new features within Dynamics CRM 2016, my initial thoughts was this was that this was a release for the fans (i.e. CRM Administrators, Customisers & Developers). Putting aside some of the big headline grabbing features such as the Interactive Service Hub and Word Templates, there looks to be a lot of subtle changes underneath the hood which those who work with CRM on a daily basis will be jumping for joy at.

One of the fundamental concepts that needs to be grasped as part of any CRM development work is Solutions. This includes understanding the differences between managed/unmanaged solution files, how to export/import solutions between different environments and how managed/unmanaged customisations are handled by CRM. There are many debates and discussions online regarding the subject, but the general rule of thumb is to use unmanaged solutions for the majority of your customisations, and only use managed solutions for when you are an ISV developing a bespoke solution to performs a specific function and/or links in with a separate application.

One of my personal bugbears regarding Solutions is the time and effort involved as part of doing an update. If, for example, you have just one Solution file for your entire businesses CRM customisations, then over time pushing out a solution update into your Production environment can take up to 30 minutes or more to complete – even if your update only contains, for example, a field name change! This can make pushing out hot-fixes or urgent updates more time-consuming and lead to delays in addressing problems within a live production system.

Whilst doing some digging around within CRM 2016, I was very excited to see the following 2 new buttons on the Solutions page:

CRM2016SolutionChanges_1

 

 

Could it be that we now have a better and more efficient way of pushing out small parts of our CRM customisations and urgent hot-fixes? Before we take a look at how these buttons work in practice, it is useful to first explain what each one does in more detail:

Clone a Patch

Clone a Patch creates a patch of your main solution, which can contain a selection of CRM customisations that you wish to export from the system. A solution can have multiple patch solutions. One thing to point out is that you must manually add the components you wish to include as part of your patch solution into the Patch solution file. When, for example, you add in the Entity which you wish to include in the patch, you can specify individual entity forms, views, charts etc. that you want to include, something which I really like:

CRM2016SolutionChanges_6

Whilst there is at least one patch of a solution present in your CRM system, you will not be able to edit the main solution file. CRM presents you with a yellow banner message to inform you of this when you enter the CRM Solution in question:

CRM2016SolutionChanges_5

 

In order for the solution to become available for customisation again, you will need to either a) delete all patch solution files that derive from the main solution or b) use the Clone Solution button, which will be explained in more detail next.

Clone Solution

Clone Solution enables you to quickly combine together a solution and all of its descendant patches back into one solution file. Say for example, you have 1 Main Solution file and 3 Patch Solutions from the Main Solution. Clicking the Clone Solution button will combine all of these together again as part of 1 solution file. As a result, this will then allow you to customise the main solution file again without any issues.

So how does this work in practice then? Let’s find out:

To start of with, here is a custom entity within a new solution file. Nothing has been customised for this entity at this stage:

CRM2016SolutionChanges_2

This is then exported out of the system as a solution file and imported into our target environment. Next, let’s say we want to add a newly created field to our entity as well as making some other changes as well within the main solution. These are in an incomplete stage, so therefore we just want to export our new field only as a patch solution for now.

On the Solutions page, we select our Main solution file and press the Clone a Patch button. We are greeted with a screen similar to the below where we can specify a few optional details, before then confirming:

CRM2016SolutionChanges_3 CRM2016SolutionChanges_4

Next, we then have to go into our Patch Solution and add in the elements that we wish to push out as part of a patch. In this case, we want to add a new field to our Test Entity. We therefore go into a Patch Solution file and go to Add Existing… in order to add in our Test Entity. You will be greeted with the Entity Assets window above and, in this example, we want to include all assets so we just press Finish to add this in.

Now we want to add in our new field:

CRM2016SolutionChanges_10

CRM2016SolutionChanges_9

As above, this is then imported into our target CRM environment, as you would do normally for any other solution file. It even looks the same!

CRM2016SolutionChanges_13 CRM2016SolutionChanges_14

 

Now, lets say we want to pull together all of our other changes as part of a traditional solution update. We also want to rename our Testing Field as follows:
CRM2016SolutionChanges_12

We select our Solution file and click on the Clone Solution button. At this stage, we can modify the display name of the Solution and also specify a new version:

CRM2016SolutionChanges_11

When we click Save, the patch solution file will vanish from our solution file, replaced by our one solution file which can now be edited and exported accordingly.

And that’s it! It takes a while to get your head around, particularly if you have been used to working with Solutions in previous versions of CRM, and I think it will take a while before this starts to become widely used (to be honest, I have not yet used it as part of some of the recent solution updates I have had to do 😳). Still, it is good to know that for those who are just coming into CRM Administration/Development, you can get to grips with this feature straight away without having to concern yourself too greatly with the “old” method. Just don’t be surprised if you hear from CRM veterans something like “Back in my day, we just had Solution updates – and we were lucky to have them!”

Let me know in the comments below if you have started to use Solution Patches yourself within your CRM 2016 environment.

This is the second part of my 2-part series, continuing our evaluation of the new Word Templates feature versus the traditional CRM/SSRS Reports route. Word Templates were recently introduced as part of CRM 2016, and are one of the big new features that has got me really excited about the future of CRM. What I am keen to discover is if they can be utilised as effectively as .rdl CRM Reports to produce high quality and professional looking documents.

Last week we took a look at the process and steps involved in setting up a Report. So now, let’s make a start and go through the step-by-step process of setting up a Word Template document on a CRM 2016 instance:

  1. From a setup point of view, there is much less that is required in order to start working with Word Templates:
    • CRM Online 2016: If you don’t have access to a CRM Online 2016 instance, you can either reset a Sandbox Instance or start a free 30 day trial. My understanding is that Word Templates have been introduced as part of 2016 On-Premise CRM, but I’m unable to confirm this.
    • Microsoft Word 2013/2016
  2. Log into your CRM instance and navigate to a supported record type. For this example, we are going to use Lead:WordTemplate_2
  3. On the Form Ribbon, click on the ellipse to expand the button Options and select ‘Word Templates‘:WordTemplate_4
  4. You will then be greeted with the ‘Create template from CRM data‘ window, enabling you to specify the template type you want to create (Excel or Word), confirm the entity data you wish to use and choose whether to upload an existing template or create one from scratch. We’ll click on Word Template and then press ‘Select Entity‘ to proceed:WordTemplate_5
  5. Finally, CRM will display an informational window which gives the user a quick summary of the different related record types and, therefore, what additional fields can be displayed on your Word Template. This can be quite useful for novice CRM users or for those who are unfamiliar with how a particular CRM system has been customised. When you are ready to continue, press ‘Download Template‘ and then save the document to your local computer:WordTemplate_6
  6. Once downloaded, open the template. You’ll be greeted with a blank Word document, similar to the below:
    WordTemplate_8

Don’t panic though! The document has everything we need to make a start, but first we need to ensure that the Developer tab is visible. To switch this on, you will need to:

  • Go to File -> Options to open up the Word Options window:WordTemplate_10
  • Go to Customize Ribbon and make sure that the Developer Tab check-box is ticked. Once this is done, press OK:WordTemplate_11
  1. On the Developer Tab, you should see a button called ‘XML Mapping Pane‘. Click this button to open up a new pane to the right of the screen:

WordTemplate_12 WordTemplate_13

  1. Under the ‘Custom XML Part’ dropdown, you should see an option similar to this (may be different depending on the entity that you are building the template for):

urn:microsoft-crm/document-template/lead/4/

Once selected, you should see all of your entity fields appear below:

WordTemplate_15

  1. With the XML Mapping configured correctly, our CRM data fields can be moved onto our Word Document. To copy across each field onto the Word Document, all you need to do is right click the field, select ‘Insert Content Control‘ and then ‘Plain Text‘. Your field will be added onto your empty Word Document into the cursor area:WordTemplate_16

WordTemplate_17

  1. Now we can start to build our report! Here’s one I made earlier, with the help of some of the existing templates that Word provides for Letters:WordTemplate_18

With our document completed, we can now save it and upload it into CRM. To do this, we first need to navigate back to the ‘Create template from CRM data‘ from step 4) and, this time, select ‘Upload‘ instead of ‘Select Entity‘ to be greeted with the document upload window:

WordTemplate_20

  1. Once we have uploaded our document, we can then select our newly uploaded document from the Word Templates button on our Lead form to download the document, populated with our specific record information:WordTemplate_21 WordTemplate_22

Conclusions

So is it time to ditch .rdl Reports in favour of Word Templates then? I would certainly say so for instances where you just want to create documents which require very little data manipulation and where the key focus is around presentation of the document. Microsoft Word is certainly a much more accessible tool than SSRS when it comes to quickly creating documents that look visually appealing. That’s not to say that .rdl Reports will not still have a role moving forward, particularly when requirements are a little more complex. For example:

  • You need to use a customised FetchXML report to return data that is filtered a certain way or is, for example, returning multiple <link-entity> fields.
  • You are wanting to develop a report that a user needs to be able to filter at report run-time.
  • You need to leverage some of the advanced functionality made available via SSRS Expressions.

I therefore do not foresee a massive exodus towards Word Templates in the near future. It is more likely instead that Word/Excel Templates become the “preferred” report building tool, whereas .rdl Reports instead are used for “advanced” scenarios. I certainly am looking forward to using Word (and indeed Excel) templates moving forward and, as part of this, ensuring that some of our CRM Super Users receive training on how to use the feature as well. Giving users the power to create their own reports, using the tools they know and use every day, is very exciting!

One observation I had in regard to Word Templates is that Word would occasionally hang on my computer for approx. 15 seconds when moving some of the fields from CRM around the document. I am guessing this delay might be caused by the fact the document is attempting to connect back to your CRM instance on regular intervals. Apart from that, there were really no issues in terms of usability and setup – everything was really straightforward, quick and familiar. These are most certainly the key experiences that Microsoft are aiming for as part of this new feature, and I am reasonably confident that any teething problems will be addressed swiftly so as to encourage as many people as possible to start using this feature moving forward.

For those who have done a lot of work previously creating bespoke document templates within CRM, the only effective way in which you would traditionally do this within CRM was via a Report. For the uninitiated, reports are .rdl files that are created within CRM (for very basic reports) or via SQL Server Data Tools (for more complex/bespoke reports).

Dynamics CRM 2016 has potentially flipped this approach on its head with the introduction of Word Templates. Now, you can use Microsoft Word to develop and customise a template that can then be populated with the information you need from a CRM record. Given that Word is a far more accessible and familiar tool for many people, this new feature could be a game changer and major boon to CRM Administrators and Developers.

Having worked myself with SSRS a lot previously to create .rdl reports (and developed quite a fondness for it as a result), I am really interested in seeing whether it is more efficient and easier to use Word Templates compared to a .rdl report. So let’s find out by creating a very basic custom introduction letter that a business could use to generate for a Lead record. We’ll attempt both methods to see the steps, effort and ease of use involved for each, and then decide which tool is the winner. Given the number steps involved, this will be split across two separate posts, with the first post focusing on SSRS,

The steps below assume that you have not previously authored any custom SSRS reports on your system and that you have a FetchXML query ready to return data from CRM. We’ll be using the following basic query to return Lead data that should work with any CRM instance:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="lead">
    <attribute name="fullname" />
    <attribute name="companyname" />
    <attribute name="telephone1" />
    <attribute name="leadid" />
    <order attribute="fullname" descending="false" />
  </entity>
</fetch>

Please note the enableprefiltering=”1” option above, if you are using your own custom FetchXML query, then this line we will need to be added to the <entity> node. Otherwise, your report will not upload correctly later on:

  1. First things first, you will need to download all of the software you need in order to build the reports. Technet has a great article that goes over what you need, the salient bits of which are as follows:

Both downloads are fairly small and shouldn’t take long to install. During the Report Authoring Extension Setup, you may be prompted to install Microsoft Online Services Sign-in Assistant, press Yes if this the case:

SSRS_1

  1. Once installed, open up SSDT using Start -> Search or in Program Files -> Microsoft SQL Server 2012:

SSRS_2 SSRS_3

  1. Once open, go to ‘File’ -> ‘New’ -> ‘Project…’ You’ll see a Window similar to below:

SSRS_4

Select the ‘Report Services Project Template’, give your Project a name and then press ‘OK’. An empty solution file will be created, with the following folders visible on the right under Solution Explorer:

SSRS_5

  1. Typically, at this point you would create your Shared Data Sources/Datasets. Unfortunately, CRM reports do not support these so we need to create our report first. Right click the Reports folder and go to ‘Add’ -> ‘New Item…’. Select Report, give it a logical name, and press ‘Add’:

SSRS_6

The report will open for you within VS:

SSRS_7

Now we can start to create our data sources 🙂

  1. On the left hand side, under ‘Report Data’, right click on the Data Sources folder and select ‘Add Data Source…’ You’ll then need to enter your CRM instance settings:
    • Tick the box where it says Embedded Connection
    • On the dropdown list for Type, select ‘Microsoft Dynamics CRM Fetch’
    • Under Connection String, enter the URL for your CRM instance. There are also two additional parameters that can be specified, but only really useful if your URL points to multiple CRM organizations and/or if you have Active Directory Federation setup.
    • Under Credentials, if you are connecting to an On-Premise CRM instance, select ‘Use Windows Authentication (integrated security)’; if your are connecting to CRM Online, then select ‘Use this user name and password’ and enter your CRM Online login details.

    We’ll test these connection string settings in a few moments

  2. Now that we have our Data Sources, we can create our Datasets (too many Data’s, eh?). Right click on Datasets and select ‘Add Dataset…’:

SSRS_8

Enter the following settings:

  • Enter a name for your Dataset, ideally something descriptive in terms of what data the report is returning
  • Tick ‘Use a dataset embedded in my report.
  • Under Data Source, select your newly created CRM Data Source
  • Ensure that under Query Type, Text is selected
  • In Query, copy + paste or (for bonus points!) manually type your FetchXML query
  • Click Refresh Fields. After a few moments, you should then be able to click on ‘Fields’ on the left pane and see all of the fields from CRM. This means its working! If you get an error message, then double check your connection string details

SSRS_9

SSRS_10

  1. Now the fun part – time to build the report! I will probably do a future blog post on some of the cool things you can do with SSRS. Suffice it to say, for the purposes of this post, we are just going to create a very basic report that displays some field data in a tablix. The report will be run from an individual record, thereby pulling in its data fields. First of all, we will add a tablix to the report. This is as simple as right clicking on the report area, selecting Insert -> Table. The tablix will then appear with a dotted line on the report area:

SSRS_11 SSRS_12

  1. Because there is only one dataset configured for the report, the tablix will automatically associate itself with this. We can therefore start to add in the field by click on the top left a column (where the small table icon appears) and then selecting each individual data field we want to add. SSRS will also add on the appropriate header text for each field you put onto the tablix:

SSRS_13

SSRS_14

For this report, we will add on the fullname, companyname and telephone1 fields. After adding on some customised text to make the report look like a letter, the report should look something like this:

SSRS_15

Very basic I know! But the report will illustrate well what .rdl reports can do within CRM.

  1. Now that the report is finished, we can upload it onto CRM. To do this, we will need to locate the .rdl file for the report first. Press “Build” on your Visual Studio solution (in order to ensure that you have the most up to date version saved to disk) and then open up your Project solution folder in Windows Explorer to find your newly created report:

SSRS_16

SSRS_17

 

  1. Moving across now into CRM, we need to either go into our target Solution or alternatively customise the system via the Default Solution (not recommended for development/Production environments!). On the left-hand bar, we will see a section called Reports. Click on it, and then on New in the centre area to open a pop-up window, which lets us start adding a report into CRM:

SSRS_18 SSRS_19 SSRS_20

It’s useful at this stage to explain what some of the different fields/options mean:

  • Report Type: There are three options here. The first is Report Wizard Report, which takes you through a wizard in CRM to enable you to create a basic report utilising Advanced Find-like filter criteria. You can even make a copy of an existing report and modify it, though you will be limited in your customisation options. The second option is Link to Webpage and enables you to specify a URL to a report that exists outside of CRM (e.g. SAP Crystal Report). The final option, and the one we will be using today, is Existing File and lets you upload a .rdl file into CRM.
  • Parent Report: Similar to what you can do within SSRS Server, you can setup Parent/Child Reports to link together similar reports (e.g. you could have a Parent Account report, that then has a Child Report which shows all of the Contact Details for the Account)
  • Categories: These are grouping options to indicate the type of report you are adding.
  • Related Record Types: Here you need to specify which CRM entities the report can be run from.
  • Display In: Indicates where the report will be visible from. You can select one or many of the options. Reports Area will display the report from the Sitemap Report area, Forms for related record types will make the report available from the Form of the entities you have specified in Related Record Types and, finally, Lists for related record types will do the same, but from the Entity View page instead.
  1. Because we want our report to run on Lead forms only, we need to ensure that the Related Record Types contains Lead, and to ensure our Display In options are configured accordingly. Finally, we need to upload a report by selecting Choose File and then populate the other details accordingly. Your pop-up window should look similar to the below when you are ready to save:

SSRS_21

  1. Save and the Publish your changes. Assuming no problems, you should now see your Report on the Run Report button on your Lead form:

SSRS_22

As you can see, creating a CRM .rdl report for the first time can be quite time consuming and then, depending on the complexity of the report you are trying to create, could take even longer on top of that. I am therefore really interested in finding out as part of next week’s blog post what the process is like for Word Templates, and whether it is a quicker and more effective means of creating reports.