Creating an Audit Report for User Logins in Dynamics CRM

If you’ve ever tried to create an audit report for Dynamics CRM, you may have noticed that you can’t create one with the Report Wizard. The Audit Summary Report in the Settings area gives you some information, provided you enable auditing for the correct entities. However, if you need detailed information on your report, or you wish to share this report to others in CRM, you quickly realize that your options are limited.

Fortunately, there is another option that you can take to overcome these challenges. This option involves writing a custom SQL Server Reporting Services (SSRS) report that can be uploaded into CRM and shared with others to provide auditing information. I will describe how I created a report to provide detailed login information for CRM users. This article assumes the reader understands how to create a custom SSRS report for Dynamics CRM.

The first challenge I encountered was deciding how to write the query for my custom SSRS report. I have 2 choices: a SQL query or a FetchXML query. Writing a SQL query will work if the report is grabbing data from an on-premises version of CRM, but what would I do if I wanted to write this report for CRM Online? Since I can’t write a SQL query for CRM Online, I am forced to use FetchXML for the report.

My first thought was “Can I access the audit tables via FetchXML like I can with SQL?”. After a bit of searching, I found a GitHub project that had a couple of SSRS reports that accessed the audit tables via FetchXML (credit to BrettRojas: https://github.com/BrettRojas/CRM2011UserAuditReport). Once I examined the query, I realized that with a bit of SSRS report manipulation I could use this to provide a detailed login report in CRM Online.

 

FetchXML Query

The query for this report is listed here:

<fetch distinct="false" no-lock="false" mapping="logical">

<entity name="audit">

   <attribute name="createdon" />

   <attribute name="action" />

   <attribute name="userid" />

   <attribute name="objectid" />

   <attribute name="objecttypecode" />

   <order attribute="createdon" descending="true" />

<filter type='and'>

   <condition attribute='action' operator='eq' value='64'/>

</filter>

<link-entity name='systemuser' from='systemuserid' to='objectid' link-type='outer' alias='SystemUser'>

   <attribute name="fullname" />

   <attribute name="windowsliveid" />

   <attribute name="businessunitid" />

   <attribute name="isdisabled" />

</link-entity>

</entity>

</fetch>

 

There are a few things to note about this query:

  • Enabling Auditing – Auditing in CRM must be enabled for this report to function properly. Since we are creating a login report, we need to enable auditing for the SystemUser entity in CRM.CRMAuditReport2
  • Query Entity – The entity used in the query is titled Audit, which is an internal entity in Dynamics CRM. This means it is not displayed in the Advanced Find dialog, nor the query generator for the Report Wizard. You still have access to this entity, but you need to have System Administrator privileges to do so.
  • Action Attribute – This attribute in the Audit entity describes the type of record being recorded. We are only interested in entries with the integer value of 64, which corresponds to a successful access via a web page. Since only CRM users would access the system via a web page, we can safely assume that these are real CRM users logging in to the system. As a side note, the original FetchXML query included entries with the value of 65, but this corresponds to a successful access to CRM via a webservices call (such as a plugin) and we do not need these records for our report.
  • Linked Entity – The query also performs an outer link to the SystemUser entity, in order to get the details we need for our report. Since the Audit entity only has the GUID for the main entity record being tracked (SystemUser in our case), we need to perform this link so we can see information such as the name of the user, their business unit, Windows Live ID, and their status (active/disabled).
  • Ordering of Results – The query performs an ordering of the results, and uses the CreatedOn field from the Audit entity. Since we are tracking SystemUser records, this field corresponds to a successful login by a CRM user, so ordering the query results in descending order assures us that the first record return for each CRM user (if we group by CRM users) will be the latest login.

 

Creating and Publishing the Report

You can now use the above query to create your SSRS report (and publish to CRM) using the normal techniques and processes to do so. Here is an example of the layout of the report that I created – you can display information about specific users, such as Last Login or Number of Logins over the last 30 days. Please note that the data displayed on this report will start when you enable auditing (as mentioned above).

CRMAuditReport3

I hope this is helpful – Happy reporting!

 

 

Creating Custom Edit Forms with InfoPath in SharePoint 2010

Users have the ability to display and edit properties (known as metadata) for items in SharePoint document libraries and lists. This is standard functionality that comes with SharePoint, and SharePoint provides a standard form when displaying/edit an item.

SharePoint also provides the ability for power users or developers to customize these display/edit forms, using InfoPath. I am going to run through the steps to customize a SharePoint 2010 list, to create a custom display and edit form. I will be using InfoPath 2010 (along with Visual Studio 2010) to demonstrate how you can call a web service method from the custom InfoPath form.

 

Customizing a SharePoint 2010 List

When you create a list in SharePoint 2010, a standard display/edit form is used, so the first step in customizing a list to use your InfoPath form is to navigate to the SharePoint list, and click on the List tab in the ribbon. This will display ribbon items for the list, and you can click on the Customize Form button, in the Customize List section (Figure 1).

InfoPath_Figure1

Figure 1: Customize Form ribbon button for a SharePoint list

Once you do this, SharePoint creates a custom template with InfoPath 2010, based on the current columns defined for your list. You can use SharePoint Designer 2010 to verify whether a SharePoint list is using a customized list, as seen in Figure 2 (Figure 3 shows a list that does NOT have a customized InfoPath form):

InfoPath_Figure2

Figure 2: SharePoint list with a Customized InfoPath 2010 Form

InfoPath_Figure3

Figure 3: SharePoint list without a Customized InfoPath 2010 Form

When you click the Customize Form button in the ribbon, InfoPath 2010 Designer will open up with the customized form template created for you. At this point, you should make your modifications to the form (we will discuss this in more detail in the next section) and publish the form back to the SharePoint list. One thing to remember is that if you do not publish the form back to the list at this point (you will be prompted if you exit the form), SharePoint will not save the form template as a customized form, meaning your list will continue to use the default form.

For my example list (named TestCustomEditForm), I just published the custom InfoPath 2010 form template back to the list without making any modifications. Once this was completed, clicking on an item in the library displayed my custom form, as seen in Figures 4 and 5. The next section will discuss how to design more advanced changes to the customized form template.

InfoPath_Figure4

Figure 4: Customized View Item Screen for a SharePoint list

InfoPath_Figure5

Figure 5: Customized Edit Item Screen for a SharePoint list

 

Designing a Custom Display/Edit Form with InfoPath 2010

Once you have created a custom form template using InfoPath 2010, there are a lot of options available to you to design the form to your specifications. I am going to show you three things you can do to customize your form template: Adding a custom field that is a lookup to another SharePoint list, creating different views for the Display, Edit and New forms, and calling a web service method to retrieve data in a column when the form loads.

 

Adding Custom Fields

Adding a custom field is done in InfoPath 2010 Designer, and is very easy to do. Design your form template and in the lower right panel, there is an Actions section. You can click on the Add Field link, and a dialog to add your field information will be displayed (Figure 6).

InfoPath_Figure6

Figure 6: Adding a Custom Field

 

Enter your information about the field you are adding, and press OK to add the field. In my example, I am adding a Genre field, which is a lookup to a SharePoint list called Genre. Selecting a value of Lookup (Information in a SharePoint list) in the Data Type entry field will configure the field to be a lookup to a SharePoint list. I also selected Genre from the Get Information From field, which appears after you select the Lookup data type.

After you have entered all the information for your field and clicked OK, the field will appear at the bottom of the Fields section. You can now drag and drop the custom field onto the form design surface, and InfoPath will create the appropriate control for you. In my example, since the Genre field is a lookup to a SharePoint list, InfoPath creates a dropdown control when I drag and drop the field to the design surface (see Figure 7).

InfoPath_Figure7

Figure 7: Adding a Control for a Custom Field

 

You can now publish the modified form template to SharePoint, and you will see the Genre column added, as well as the field displayed when viewing/editing an item. Figures 8 and 9 show the list after these changes.

InfoPath_Figure8

Figure 8: Update SharePoint list with a Custom Column

InfoPath_Figure9

Figure 9: View Item with a Custom Column

 

Customizing Different Views

If you need to customize the different views (View/Edit/New Item) for a SharePoint list, you can accomplish that by creating different views in the form template in InfoPath. For my example, I created three (3) views in InfoPath: Display Item, Edit Item and New Item. The only difference between these views is the label for the Title field (of course, in a real-world scenario, your changes would be more extensive).

After these views are created, publish the form back to SharePoint, and in order to “wire up” the different views to the custom pages being used, you need to modify the web part in the different custom forms created (when you originally clicked Customize Form for the list). This is done by clicking on the Modify Form Web Parts (under the Customize List section in the ribbon) to display the different custom forms created (See Figure 10).

InfoPath_Figure10

Figure 10: Modifying Form Web Parts

 

The web part properties for each Item form (not the Default forms) needs to be modified to display the proper view. For example, Figure 11 shows the custom form for New Items. Under the Edit tab in the ribbon, select the current view to display that view in the page.

InfoPath_Figure11

Figure 11: Selecting the View for the Web Part

 

You also need to modify the web part properties for the web part on the page, as seen in Figure 12. Click on the Options tab in the ribbon (under Web Part Tools), and select the appropriate view you created (from InfoPath), under the Views dropdown. Apply the change and Stop Editing to finish with your changes.

 

InfoPath_Figure12

Figure 12: Modifying Web Part Properties for the Web Part

Once you make these changes for each of the views, you can see the changes when you display, edit or create a new item, as shown in Figures 13 and 14. Notice that SharePoint automatically makes the controls read only (or editable), depending on which view is being displayed.

InfoPath_Figure13

Figure 13: Customized View Item Form

InfoPath_Figure14

Figure 14: Customized Edit Item Form

 

Calling a Web Service Method from a Custom Form

Another powerful customization is the ability to call a custom web service method (to retrieve data for a field, for example). Before you can allow your custom InfoPath form to call a web service method, you need to verify the Cross-Domain Access for User Form Templates option is selected (Figure 15). This setting is found in Central Administration, under the Configure InfoPath Forms Services area (look in the General Application Settings section).

InfoPath_Figure15

Figure 15: Customized Edit Item Form

 

In order to call a web service method, a data connection must first be configured in the InfoPath form template. Make sure you select a connection to receive data, and select SOAP Web service in the Data Connection Wizard (Figures 16, 17).

InfoPath_Figure16

Figure 16: Receive Data Connection in Data Connection Wizard

InfoPath_Figure17

Figure 17: Data Source selection in Data Connection Wizard

To complete the data connection for your web service, enter the URL to the web service and select the web service method you are using. For my example, I’m using a method titled HelloWorld, which takes no input parameters and returns a simple string value.

Once you’ve created this connection, you need to convert it to use a data connection file (UDCX file). This is needed for security purposes, so that the user is not prompted for credentials. Click on the Convert to Connection File button in the Data Connections window (Figure 18) for your newly-created web service connection. A dialog will appear, prompting for a URL to the data connection file (Figure 19). Enter the path and file name to a data connection file that will be created when you click the OK button. For my example, I created a Data Connection library earlier named DataConnections, and used the URL to that library when creating the UDCX file.

 

InfoPath_Figure18

Figure 18: Converting a Data Connection to use a Connection File

InfoPath_Figure19

Figure 19: Convert Data Connection Dialog

 

At this point, your web service method is configured for use in the template, so the final step is to wire up the method to a field on the form. For my example, I created another string field (named HelloWorld) and added a text box on the design surface. I then set the default value of the text box to be the string returned from the HelloWorld web service method. This was done by entering the Formula Editor and clicking the Insert Field or Group button to allow selection of the return value from my web service method. Please make sure you select your web service method in the Fields dropdown (it will be marked as a Secondary Data Source), and that you select the appropriate field from the dataFields node in the schema (for my example, the string being returned is title HelloWorldResult, under the HelloWorldResponse node).

InfoPath_Figure20

Figure 20: Selecting Output from Web Service Method

Click OK to save the changes, save and republish the form template, and when you display/edit an item in the list, your web service method will be called and displayed on the dialog (Figure 21).

InfoPath_Figure21

Figure 21: Displaying Customized Form with a Web Service call

 

Moving a Customized Display/Edit Form to Another Environment

 

So far, we’ve discussed how to create a customized display/edit form, and I’ve shown you some powerful customizations you can make to your form. You may be wondering how to deploy this custom form from a development environment to a different environment (staging and/or production). I have found that the process is a bit different than you would expect.

Normally, when InfoPath form templates are created in a development environment, it’s a simple matter to “repoint” the publishing URL to your target environment and republish the template. Unfortunately, since this form template is going against a SharePoint list, we do not have access to repoint the publishing URL to a list in the target environment. Due to this limitation, the only way to get your custom form to a different environment is to save your customized list (which will include the form template and any content you might want to include) to a list template. You can then install this list template on your target environment, and create a list in that environment, based on the list template. The only other changes you would need to make is to repoint your web service method call (if you’re using one) to the web service in your target environment. You would do this by editing and republishing the InfoPath form template in your target environment.

Conclusion

Customizing the display/edit forms for SharePoint 2010 lists and document libraries is simple and provides a new level of customizing your SharePoint environment. Adding a bit of custom code can take this customization even further, if you need additional functionality not available with just SharePoint lists. I hope this article has helped you understand how to customize the display/edit forms using InfoPath 2010, and if you have any additions, I would love to hear about them.

 

How to Use BCS in SharePoint 2010

Introduction

Business Connectivity Services (BCS) in SharePoint 2010 is a robust and powerful technology that is part of SharePoint 2010. Microsoft has enhanced BCS with new features, services and tools in order to streamline the development of solutions with deep integration of external data. This article will run through the steps (end to end) to use BCS to use data in SharePoint coming from an AS/400 mainframe system.

Imagine the scenario where a company has line-of-business data stored in a mainframe (AS/400) system, and they want to utilize this data in their new SharePoint 2010 environment. BCS provides the mechanism for reuse of this data, thereby solving the problem of migrating data from the AS/400 system, maintenance of duplicate sets of data. We are going to use this scenario as our example for demonstrating what BCS can do for businesses.

There are a number of steps that will be followed to accomplish this, and are listed here:

  1. Database Configuration
  2. Creation of a Secure Store Application
  3. Setup of an External Content Type
  4. Creation of the External List in SharePoint

For this example, SQL Server 2008 R2 was used for the database server, and SharePoint 2010 Server Enterprise was used for the SharePoint site.

Database Configuration

Accessing the AS/400 data can be done through SQL Server via a linked server. Once a linked server is setup, a normal SQL database can be created to access this linked server through views. In order to create a linked server, follow these steps:

  1. Open SQL Server Management Studio and connect to the server you are working with
  2. In the Object Explorer, navigate to the “Server ObjectsàLinked Servers” node
  3. Right mouse click on “Linked Servers” and create a new linked server. You can use whatever name you choose (I am using the GBCUSTOMERDATA name).
  4. Follow the prompts to create the linked server. Make sure you use the correct remote login and password when appropriate.

Once complete, your linked server should look similar to this:

DB_LinkedServerSetup1_new

After you have setup your linked server, you can now create a separate SQL database and create views that reference the linked server. I have created a database called CustomerData that will be used throughout this article.

Creating a Secure Store Target Application

The next step is to create and configure a Secure Store Target Application. A Secure Store Target Application is used to control authentication to the external data system, in order that the end user in SharePoint is not continually prompted for authentication credentials. This component will be used when configuring an External Content Type.

Creating a Secure Store Target Application is done in Central Administration, under the Security area. Before a new Target Application can be created, a new encryption key must be created for that application. This is done by navigating to the Application Management section, and clicking on the Manage Service Applications link under Service Applications. This takes you to a list of the service applications. Scroll down to the Secure Store Service and click on it to access the area to create new secure store target applications. To generate a new key, click the Generate New Key icon in the ribbon. A dialog will appear, asking you to enter a passphrase. This passphrase is used if you need to edit the target application, so make sure you save this in a secure spot for reference (it is not saved anywhere in the system).

SecureStoreSetup2

SecureStoreSetup4

At this point, you are able to create a new target application for the secure store. Clicking on the New icon in the ribbon will begin this process.

The first screen allows you to enter information such as the Target Application ID, Display Name, Contact E-mail, Target Application Type, and whether the target application has a page that redirects users when they incorrectly enter credentials. Target Application Type should be set to Group, as this allows configuration of a group of users for the target application. Other selections here are Individual, Individual Ticket, Individual Restricted, Individual, Group Ticket, and Group Restricted.

SecureStoreSetup5

SecureStoreSetup6

Please note that here is where you name the target application, along with the Target Application Type, which should be selected as a Group type, so that we can map a group of users to this Secure Store Target Application. If you wish, you can select Individual as the Target Application Type, which will only allow individual users to be mapped to this Secure Store Target Application.  For the Target Application Page URL, I selected None, as I do not want any screen to prompt the user for credentials. You can select a default or custom page if you wish your users to be prompted for credentials.  Clicking on the Next button takes you to the following screen, which allows configuration of authentication fields.

SecureStoreSetup7

SecureStoreSetup8

Please note that you have a lot of freedom in what you configure here for fields names, but the default field names of Windows User Name and Windows Password should be selected. Clicking on the Next button takes you to the next screen, which allows selection of the administrator accounts for the target application, as well as the members who are mapped to the target application.

SecureStoreSetup9

Enter the administrative user(s) for Target Application Administrators, as these users will be the ones managing the Secure Store Target Application. For Members, you can enter any users or groups defined in your Active Directory environment. I selected All Authenticated Users, as I want any user who is authenticated in the AD domain. Click OK to complete the configuration of the Secure Store Target Application.

Now that your Secure Store Target Application is created and configured, you need to set the credentials that will be used. This will be the account that has access to the external data system.

SecureStoreSetup10

Selecting the Target Application and clicking on the Set button in the ribbon (under Credentials) will display the following dialog, allowing entry of the username and password you wish to use with this Secure Store Target Application.

SecureStoreSetup11

Setting the credentials for the target application (which you configured as a group type) is essential, as SharePoint uses these credentials when accessing the external system, instead of prompting the user for credentials each time the external system is accessed.

External Content Types

In order to provide access to external data in SharePoint, an entity called an External Content Type must be configured in SharePoint. An External Content Type (ECT) defines the connection information on where to access the external data, as well as the operations to perform when retrieving (or updating) the external data.

Creating an ECT is done with the Microsoft SharePoint Designer tool. There is a link on the left-hand side panel called External Content Types, that will display the information about the ECTs defined in the system.

ECTSetup1

Click on the External Content Type button in the ribbon, under the New section. This will create a new External Content Type, as seen below:

ECTSetup2

From here, you can configure the ECT, such as setting the name, the Data Source Type,  and creating the operations that the ECT performs. For this example, I set the name as CustomerData.

ECTSetup3

Notice that the new External Content Type does not have any operations defined initially. In order to do this, click on the Click here to discover external data sources and define operations link, under the External Content Type Operations area. This will display the following page, which allows you to define operations for the ECT.

ECTSetup4

Before operations can be created for the External Content Type, you must first create an external data source. Click on the Add Connection button to create a new external data source. You will be asked what type the new external data source is. For this example, I selected SQL Server, as I am connecting the linked server previously created in SQL Server.

ECTSetup5

Once you select the external data source, another dialog will be displayed, so you can enter the details of the data source. For a SQL Server data source, you are prompted to enter the database server name, database name, and how the ECT will connect to the database. We are connecting to this database with impersonated Windows identities, so select the Connect with Impersonated Windows Identity selection. Selecting this option will enable the Secure Store Application ID entry box, which allows you to specify what Secure Store Application is used (for credentials) when accessing the data source. For this example, I entered the NorthwindSecureStore secure store application.

ECTSetup6

When you are finished, pressing the OK button will cause the system to validate the data connection. You will be prompted to enter the administrator credentials for the Secure Store application (NorthwindSecureStore) as part of this validation. Enter the credentials you configured when you configured the secure store application.

ECTSetup7

Once the system is finished, you will be returned to the Operation Designer page for your ECT in SharePoint Designer. You will now notice that there is a data connection in the Data Source Explorer tab that corresponds to the connection you just created. For this example, the SQL connection is to the Northwind database. At this point, you are able to create operations for the external content type. To do this, expand the Northwind data connection and navigate to the table you wish to create operations for. I created Read List operations for the Customers database table by right mouse-clicking the Customers table, and selecting the New Read List Operation selection from the menu.

ECTSetup9

The system will now take you through the steps to create read list operations for the Customers database table. This includes naming the operations name, as well as creating any filters on the data that you need, and any return parameters. Clicking Next progresses you through the different dialog screens, and clicking Finish completes the creation process.

ECTSetup10

ECTSetup11

When configuring filter parameters, you select the Data Source element that the filter uses to filter the data.

ECTSetup12

Selecting return parameters specifies what columns from the data connection are returned when executing the operation. For this example, I want all the columns in the Customers database to be returned, so I selected all the columns available. I also want the CustomerID column to be mapped to the CustomerID column identifier in the database, so I check the Map to Identifier checkbox and select CustomerID, under the Identifier dropdown. You can also configure columns to be required or read-only, as appropriate.

ECTSetup13

Once you press Finish, you are returned to the Operation Designer, and you will notice that the operations you just created are listed under the External Content Type Operations section.

ECTSetup16

As a final step in creating the ECT, go back into Central Administration, and verify that the external content type has proper permissions configured. Under Service Applications, click on the Business Data Connectivity Service.

BDCPermissions1

Right-mouse click the CustomerData external content type and select Set Permissions.

BDCPermissions2

The dialog that is displayed shows what accounts have permissions for the external content type. Verify that the administrative account you configured previously has the proper permissions to the external content type. For this example, I gave the WINGTIP\Administrator account full permissions to the CustomerData external content type.

BDCPermissions3

SharePoint External List Configuration

The final step in configuring the SharePoint system for external data is to go into the SharePoint site and create SharePoint lists that access the external data. These lists can then be used in SharePoint, and will access the data from the external system (AS/400).

To create a list that accesses external data, go to All Site Content and create an External List. When prompted for an External Content Type, select the appropriate ECT that was configured previously.

SharePointSite2

SharePointSite3

At this point, you will be able to configure a new external list based off of the external content type. When you press the Create button, the system will create the external list.

SharePointSite4

SharePointSite5

Conclusion

Business Connectivity Services is a great way to leverage external data in SharePoint, but can be challenging to configure. I hope that this article helps in understanding what steps are needed to configure BCS, and provides a single reference for end-to-end configuration of accessing external data in SharePoint.

 

Using SSIS to Import Data into Dynamics CRM

I am constantly being asked to import data into Dynamics CRM from various sources, and over time I have discovered that, even though the sources of data that I am importing vary, the process of performing the import is similar. Which tool I choose to use depends heavily on the source of the data, along with the format it comes in, and whether or not any manipulation needs to be performed prior to the actual import.

Even though 3rd party tools like Scribe <http://www.scribesoft.com/ > are great for doing these data imports, sometimes it isn’t the best fit for a client. There are times when SQL Server Integration Services (SSIS) are a better choice, and I will describe the steps to create an SSIS package to import data into Dynamics CRM. Using the out-of-the-box data import feature in Dynamics CRM is another option, but I would like to focus on the SSIS package in this article.

Data Source Definition

Before we can create an SSIS package to perform the import, we need to define a data source. This may already be defined for your scenario, but for this article I created a SQL database that represents account information.

SSISData1

Figure 1: Data Source for Accounts

 

Creating the SSIS Package

Once the data source has been defined, we can now use SQL Server Data Tools (SSDT) to create an Integration Services project. This project will define our SSIS package, which has the steps we need to perform to do the data import.

SSISData2

Figure 2: Integration Services Project

When a package is created in the project, it starts you out on the Control Flow tab (Figure 2). I can add either a Data Flow Task or an Execute SQL Task. I’m using a Data Flow Task in the package, as this will allow me to define the data source connection, the destination to import the data to, as well as any steps I want to perform.

The Data Flow tab displays a designer for the Data Flow Task(s) you define in the package (you can have more than one). This designer surface allows you to build out your source and destination connections, along with any manipulation steps you need to perform before the data is imported.

SSISData3

Figure 3: Data Flow Task in SSIS Project

For my package, I don’t need to manipulate the data before I import it into Dynamics CRM, so the only items I need to define in the Data Flow task are the source and destination. For the source, I’m using an OLE DB Source object, and this has been configured to go directly against the Accounts table in the database (Figure 4). I also mapped all the columns, as I want to import all this information, as seen in Figure 5.

SSISData4

Figure 4: Source Connection Definition

SSISData5

Figure 5: Column Mapping

 

Adding the Script Component

For the destination, we need to setup a Script Component as we will be using c# code (using the Dynamics CRM SDK assemblies) to import the data into our Dynamics CRM organization, as shown in Figure 6.

SSISData6

Figure 6: Script Component in SSIS

I then mapped the columns coming from the source data, so that my code can access this to perform the import. Figure 7 shows how I mapped the columns – you can select only the data columns you want to import, and also have the ability to rename the column names (if you want friendly names, for example).

SSISData7

Figure 7: Script Component Column Mapping

 

Adding Code to Perform the Data Import

Now we are ready to add the code that does the actual import of data into Dynamics CRM. Clicking on the Edit Script… button (Figure 6) will open Visual Studio with a special project type (VstaProjects) that contains a “shell” project which you can add code to.

There are 2 methods that we need to add code to, in order to connect to a Dynamics CRM instance and perform the data import. The first method is PreExecute() and we override this method so that we can first connect to a Dynamics CRM instance using our credentials. Figure 8 shows the format for connecting to a Dynamics CRM Online organization, along with the code (commented out) on how to connect to an on-premises Dynamics CRM organization. Please note that the UserName you use when connecting to CRM Online must be a valid CRM user in the organization you are connecting to.

SSISData8

Figure 8: Visual Studio Project from Script Component

The other method we need to add code to is Input0_ProcessInputRow(), and this method is executed for each row of data coming from the source. We want to create a new Account record in Dynamics CRM for each row, so Figure 9 shows the code that does this. Note that this code can get a lot more sophisticated, based on what you need to do during the import.

SSISData9

Figure 9: Code to Import Data

 

Final Thoughts

I came across a couple of issues when developing the Script Component for my SSIS package and would like to mention them here.

  • In order to use the Dynamics CRM SDK, I needed to add references to the Microsoft.xrm.sdk.dll and Microsoft.crm.sdk.proxy.dll files. I found that I received an “Assembly not found” error when running the SSIS package, and this was due to the fact that I needed to copy these DLL files to a specific directory under the local SQL Server installation directory location. Here is a link that describes the problem:

http://ssisdevelopers.wordpress.com/2013/04/09/ssis-script-task-filenotfoundexception-could-not-load-file-or-assembly-while-refering-to-third-party-dll/

  • I also ran across problems when attempting to use early binding in the Script Component Visual Studio project. When I added the cs file generated from the CrmSvcUtil.exe program to the project, I would get the “Assembly not loaded” error when running the project. Adding the DLL files (as described above) didn’t resolve the problem, so I resorted to use late binding.

So, if you are familiar with SSIS and need greater control over your data import process, the process explained here may be an option for you. I hope this article is helpful in providing another option for importing data into Dynamics CRM.

Cheers!

 

PowerView Reports in Dynamics CRM 2013

Dynamics CRM does a really good job of hosting and tracking a company’s critical business data, and the PowerBI technology stack does a great job of modeling and displaying business data to end users. While Dynamics CRM does a decent job of providing dashboards to surface this business data, I thought “wouldn’t it be great if CRM users had native access to PowerView reports while using Dynamics CRM”? Well, it turns out that with a little configuration you can provide these types of reports right in Dynamics CRM! Let me explain the process I took to do this:

First off, you need to use the PowerBI stack to extract data from a source (Dynamics CRM, for example), manipulate it in a PowerPivot (or SQL Server Analysis Services) data model and create a PowerView report. I did this for the Top 100 Global Companies and created a PowerMap report hosted in a SharePoint Online instance, as shown in Figure 1. Please refer to this post <<link to my other blog on surfacing LOB data into PowerView reports>> for further details on creating PowerView reports from LOB data sources.

PVR1

Figure 1: PowerMap Report in SharePoint Online

Once this is setup, you can then create a system dashboard in Dynamics CRM to host the PowerView report (Figure 2). Please note that this won’t work with Personal Dashboards, due to the fact that you need to configure the IFrame without restricting cross-domain scripting.

PVR2

Figure 2: Creating a System Dashboard in Dynamics CRM

You will use an IFrame on the dashboard to reference the PowerView report in SharePoint, so you can open the report in a separate window and copy the URL into the IFrame properties, under the URL field. You need to make sure the Restrict Cross-Domain Scripting checkbox is cleared, and should also verify that the URL you copy has the &action=embedview in the querystring (so it renders properly within the page).

PVR3

Figure 3: Creating an IFrame in Dynamics CRM

Once you configure this and publish to Dynamics CRM, you will then have a fully-functioning dashboard that displays PowerView reports! One final note to mention: authentication is an issue here, as the CRM user should also have a login to the SharePoint site that the PowerView report is hosted in, otherwise you will get a nasty error when displaying the dashboard (Figure 5).

PVR4

Figure 4: PowerMap Report in Dynamics CRM Dashboard

PVR5

Figure 5: Authentication Error with PowerView Reports in Dynamics CRM

I hope this blog helps others to quickly get these awesome reports working in Dynamics CRM. Enjoy!

 

Microsoft Dynamics CRM Use Rights (IUR) Program Changes

If you work for a Microsoft Partner and use Dynamics CRM or CRM Online, the current Internal Use Rights (IUR) program is changing. I recently spent some time planning for these changes in my organization, and I’d like to details these experiences so other partners can save some time and headache moving to the new IUR program.

 

Microsoft announced the changes to the IUR program a few months ago, so I won’t cover the details of those changes. More information can be found here (you will need login credentials to PartnerSource to access the page):

https://mbs.microsoft.com/partnersource/northamerica/news-events/news/msdcrmiurdisclosureofupcomingchanges

The preparation for the new IUR program is to have a migration plan in place prior to signing up for this new program, so you can be assured your users will be migrated successfully to the new plan. For myself, all this meant was to verify our tenant was upgraded from Commerce Transaction Platform (CTP) to Office 365 (which it was previously), and then check that the new program had enough CRM licenses to handle the number of users my organization had in our current system. Since we are a Dynamics CRM Gold partner, we receive 80 CRM Online licenses (down from the 250 in the retiring IUR program). Fortunately, the number of users we have in our current CRM system is below 80, so all I had to do was sign up for the new program and migrate over the users, and I should be good to go.

The instructions I received from Microsoft were very detailed and were easy to follow, so I proceeded to step through them. The starting point was to login to the Partner Digital Download site and navigate to the Microsoft Online Services tab:

MPNPage

This area displays the product keys that can be used to redeem for the IUR benefits that my organization is eligible for:

MPNOnlineServices

Once I clicked the link to redeem, I was taken to our Office 365 administrative panel where I logged in and followed the steps to redeem using the eligible product key token. The webpage then gives you an opportunity to purchase additional licenses but I didn’t need to do that.

At this point I was able to see the new IUR subscription listed in our Office 365 admin panel, alongside the IUR program that is being retired. The next step I took was to migrate the CRM licenses for the users to the new IUR program. I did that and verified in CRM that users with licenses in this new IUR program were listed as enabled users, while users that didn’t have a license were listed as disabled users in CRM.

There was only one issue I experienced when performing these steps and it really threw me for a loop, as it affected our CRM users in our production environment. We purchase additional storage space for our CRM Online environment, as the size of our database is larger than the 5GB provided when you provision a CRM environment.

When I completed the steps of signing up for the new IUR program and migrating the CRM user licenses, I began receiving messages that our production CRM environment has run out of space and that users cannot add records. This puzzled me and when I went to the Resources In Use screen (in Settings) I saw that our instance was only allocated the initial 5GB of space:

CRMOutOfStorage

I went back into Office 365 to verify that we were still purchasing the additional space for CRM and when I verified that, I felt it was time to bring in Microsoft Support to help.

In Microsoft’s defense, I usually have a good experience when reaching out to their support teams for help, but this issue was such a strange one that I think Microsoft was unclear how to help me resolve it. I won’t bore you with the details of each support team I talked to (I spoke with more than a few) but the bottom line is that the support team I spoke with that was able to help me was the Billing Support team. Let me repeat this for emphasis: Microsoft Billing was able to resolve a production-level technical problem I had.

As it turns out, if you previously purchased additional space for your CRM system (like we did), signing up for the new CRM IUR program somehow doesn’t automatically move your additional space purchase to the program, and thereby the CRM system doesn’t recognize that you are already paying for that additional storage space. MS Billing was able to provide me with a one-time signup web page that allowed me to request additional storage space that the new CRM IUR program does recognize, so once I did that our CRM environment recognized the additional space I purchased.

CRMPurchaseAddtlStorage

I hope this post helps if other partners experience issues migrating to the new IUR program, and also to show that Microsoft Support has a lot of great people that are dedicated to help partners solve their problems.

 

 

 

 

 

Integrating Yammer into Dynamics CRM 2013

Social selling is becoming more important these days, as companies are looking to find creative ways to generate more opportunities. Microsoft provides support for this in the Dynamics CRM space by providing integration with Yammer and CRM 2013. I’m going to show you how easy it is to setup Yammer to work with Dynamics CRM 2013, so your sales force can begin using its social features.

 

Prerequisites

  1. Yammer Enterprise. Integrating Yammer with CRM 2013 requires the enterprise version of Yammer
  2. The user performing the integration with Yammer requires system administrator privileges in CRM 2013
  3. The user performing the integration with Yammer requires system administrator privileges for your organization’s Yammer account
  4. Install the most recent product updates for Dynamics CRM 2013

 

Steps

  1. Login to Dynamics CRM 2013 and navigate to the Settings area. This is done by clicking on Microsoft Dynamics CRM –> Settings
  2. Navigate to the Administration section by clicking on Settings –> Administration
  3. Click on the link titled “Yammer Configuration” to begin the wizard steps to enable the integration. Please note that you will not see this link if you do not have System Administrator privileges in Dynamics CRM.

0

  1. A Yammer Disclaimer page will display, asking you to agree to the disclaimer. Click on Continue to proceed.

1

  1. A configuration page will appear, with steps listed on how to connect your Yammer environment to Dynamics CRM 2013. Notice that only the first step (“Authorize Microsoft Dynamics CRM Online to connect to Yammer”) will be enabled at this point. Click on this link to perform the authorization step.

2

  1. A dialog will display requesting you enter your Yammer credentials for the authorization. Enter these credentials and click the “Log In” button.

3

  1. Another dialog will be displayed that asks you to confirm the connection between Yammer and Dynamics CRM 2013. Click on the “Allow” button to allow this connection.
  2. At this point the integration is performed and complete, but the process takes you back to the Yammer configuration page, and you will notice that steps 2 and 3 are now enabled. These are optional steps that you can configure for specific items/behavior with the Yammer integration.

4

  1. (Optional) Select a Yammer Group ID to control conversation access. This optional step allows you to configure whether all the conversations initiated from Dynamics CRM should be placed in a specific group in Yammer. If you don’t specify a group, the All Company Group is used for this.
  2. (Optional) Set the level of security for Yammer activity stream messages. This optional step allows you to configure whether the conversations in Dynamics CRM are visible to everyone (Public). Selecting Private for this setting will require users in Dynamics CRM to follow the appropriate record in order to see the Yammer conversations.

 

Configuring Entities

Once Yammer has been connected to Dynamics CRM 2013, you need to specify which entities in CRM you wish to enable for use with Yammer. This can be done by navigating to the Post Configuration section in Settings (click Settings –>Post Configurations).

In order to activate an entity for use with Yammer, select the desired entity and click the Activate selection in the top navigation bar. Deactivating an entity for use with Yammer is done by clicking the Deactivate selection in the top navigation bar. Please make sure you Publish All Customizations to verify that the changes you made take effect in Dynamics CRM.

6

Reactivating an inactivated entity for Post Configurations will restore any conversations in Yammer (they are not deleted when you inactivate an entity as the messages are stored in Yammer, not Dynamics CRM).

 

Issues to be Aware Of

There is only one issue I encountered when configuring the Yammer integration with Dynamics CRM 2013. You need to make sure you have your URLs for your Dynamics CRM organization as well as the URL for Yammer in your Trusted Sites zone in Internet Explorer options. Since my Dynamics CRM environment is online, the URL I used was https://*.crm.dynamics.com, and https://www.yammer.com. If you forget to do this, you may run into an issue when you complete Step 6 (I encountered a blank screen that sat there forever).

5b

Conclusion

That’s it! You now have the social power of Yammer integrated into Dynamics CRM 2013, and can begin to leverage the benefits of social tools for your Dynamics CRM environment!

 

What’s New in Dynamics CRM 2013

With the newest release of Microsoft Dynamics CRM, there have been some interesting enhancements that I’m excited to show you today! This blog is the first in a series that will detail out the new features and functionality in Dynamics CRM 2013. I will describe in more detail the new 2013 functionality in later blog posts.

Dynamics CRM 2013 has been improved in the following areas:

UI Changes

The user interface (UI) in Dynamics CRM 2013 has been redesigned to provide a cleaner and more efficient experience for CRM users. Navigation has changed, along with “flattening” out the screens displayed (i.e. no popup dialogs). Auto-saving, integrated Bing maps, and inline editing of information on forms are just a few of the changes in this redesigned UI experience.

A brief overview video can be found here: http://www.youtube.com/watch?v=EScDpwxRuiI&feature=youtu.be

Business Processes

Guided business processes can now be created in Dynamics CRM 2013 that allows users to follow a clearly outlined set of process stages. These stages help define the tasks to be completed before moving to the next stage in the process. Processes are configurable, and are able to be designed for multiple types of processes, based on desired needs.

A brief overview video can be found here: http://www.youtube.com/watch?v=9cXoivs0ZMA

CRM 2013 for Tablets

The new mobile support created in Dynamics CRM 2013 is designed to be an immersive, touch-first experience for CRM users who need to stay connected to their customers, team members, and data. This mobile application is designed for use with a Windows 8 tablet device. The new mobile support allows CRM users to update data or work their business processes in CRM directly while in the field, when the information is still fresh in their minds.

A brief overview video can be found here: http://www.youtube.com/watch?v=1H23cYvQcys

Yammer

Yammer is now integrated into Dynamics CRM 2013, allowing users the ability to collaborate socially in the context of the task they are working on. Opportunities, Cases, or Campaigns are examples of entities that you can have specific, contextual Yammer conversations with. This “social CRM” capability allows users to put their business strategies into action more quickly and effectively.

More information can be found here: http://www.microsoft.com/en-us/dynamics/crm-social.aspx

Functionality present in Dynamics CRM 2011 is intact (for the most part), so if you are familiar with that version of CRM, a lot of what is in CRM 2013 will already be familiar. Be on the lookout for future blog posts that dive deeper into the functionality areas listed above.

 

SharePoint 2010 Web Parts and CRM 2011

Recently I was asked to create a web part in SharePoint 2010 that would allow users to create and update entities in CRM 2011. There were some interesting challenges to overcome, so I thought I’d detail the process I went through, so others could benefit from my experience.

Initially, I thought this task would be easy, as the CRM 2011 SDK provides sample code that calls into the CRM 2011 web services to perform CRUD (Create, Read, Update or Delete) operations. However, I quickly discovered that the CRM 2011 SDK code requires references to the .NET Framework 4.0 DLLs, and since SharePoint 2010 web parts do not support .NET 4.0, I encountered compilation errors in my web part project.

Pic1

The solution I came up with was to create a “middle tier” WCF web service that would perform the calls to the CRM 2011 web services. This WCF web service can be created using the .NET Framework 4.0 to get past the compilation errors, and the ServiceContract can be defined with methods that mirror what is provided in the CRM 2011 web services.

I started by creating a new WCF Services project in Visual Studio 2010, selecting the WCF Service Application project template, and making sure I selected .NET 4.0 as the framework:

Pic2

Once the new project is created, I was able to define the ServiceContract for the web service, as shown below:

namespace CRM2011MiddlewareService 
{ 
   [ServiceContract] public interface IService1 
   { 
      [OperationContract] string GetMessage(); 
      [OperationContract] void AddCRMAccount(string strAccountName); 
      [OperationContract] void AddCRMCase(string strCaseName, string strAccountName); 
   } 
}
Figure 1: Code for ServiceContract

I also needed to add service references in the project to the CRM web services, as listed here:

http://<<server name>>/XRMDeployment/2011/Deployment.svc

http://<<server name>>/XRMServices/2011/Discovery.svc

http://<<server name>>/XRMServices/2011/Organization.svc

Pic3

 

Once this “middle tier” web service is published to IIS, it can be consumed by a typical web part project in Visual Studio 2010. I created this visual web part project and added a service reference to my “middle tier” web service, so I can call the methods that will create the CRM entity I am interested in creating.

namespace VisualWebPartProject1.VisualWebPart1 
{ 
   public partial class VisualWebPart1UserControl : UserControl 
   { 
      protected void Page_Load(object sender, EventArgs e) { } 
      protected void Button1_Click(object sender, EventArgs e) 
      { 
         Service1Client proxy = new Service1Client(); 
         string strCaseName = txtCaseName.Text; 
         proxy.AddCRMCase(strCaseName, ""); 
      } 
   } 
}
Figure 2: Code for Visual Web Part Project

 

From here, creating a simple visual web part for SharePoint 2010 is a piece of cake, and once it is deployed and activated in SharePoint, I was able to test it out to make sure it works properly.

 

In conclusion, creating a web part in SharePoint 2010 to consume methods from the CRM 2011 web service requires that you create a “middle tier” WCF web service to call the CRM 2011 web service. This “middle tier” web service can then be called by your web part in SharePoint 2010. My example here just scratches the surface of what the CRM 2011 web service provides, but shows the process I took to get a web part in SharePoint 2010 working with CRM 2011.