Until now I've had a hard time in getting my mind around a real business case where you would be able to use the Microsoft Surface table. Today I got an email message of one of my colleagues who pointed me at a YouTube movie which does show a good business example for the Financial Market.
What ideas do you have for using Surface in conjunction with CRM?
If you are creating your own integration module for CRM, then it is very wise to make sure that the process is running only once. For performance reasons you can decide to split the process in multiple threads, but running a single process multiple times can cause quite some unforseen issues.
The following code snippet will allow you to check if an application with the same name is running already.
In my previous post I discussed a Mail-merge alternative: Reports exporting to Word files. The reports downloaded in that approach do not support any pre filtering as CRM reports would allow you to set. It would be great if that can be set as well. This post describes how to do that.
The reports created in CRM or for CRM do support the prefiltering. In custom created reports this is added by setting an alias for the filtered views which contain the CRMAF_ prefix. See Reporting PreFilter for details. Even though that post is written for CRM 3.0, it still is valid for CRM 4.0.
The reports which are created by using the Report Wizard do not have the CRMAF_ prefix though. Also reports which have been added to CRM and have been downloaded again do not have the prefix anymore. Instead, there is a report parameter added to the report. This parameter is @CRM_FilteredEntity. Entity would need to be replaced by the entity like @CRM_FilteredAccount or @CRM_FilteredNew_Entityname. This parameter will get the value of the filter in the format of a SELECT query. For Account the default filter would be "SELECT * FROM FilteredAccount". You can set this parameter in code and replace the default filter by your own filter. Here is a code example.
CRM 4.0 does offer a lot of new features regarding mail merge, but not all situations are supported by the new features. You can look into 3rd party tools like C360 / MSCRM-Addons or Temptus Wordconnect, but a totally different approach is to use the Reporting Services. This post will dive into how to use Reporting Services to generate word documents.
You can create any report in CRM by using the Report Wizard. Imagine you want a specific invoice which contains a list of the products that are included in the invoice. This report is easily created in the Report Wizard. You can also run the report from CRM, but when you look at the export button, you will see that word is no option. Now you can do two things:
1) Go to Aspose and buy their product. They will allow you to export a report to many text formats including .doc, .docx, .txt etc.
2) Create a piece of code which transforms your report to a .doc yourself. Of course we'll dive deeper on this approach.
You can create an extension to Reporting Services which does the real export to a word document, but this is relatively hard. A way easier approach is to access the ReportinService webservice and get the report in a byte array and send this byte array to the user with a content type set to "application/vnd.ms-word ".
How to do this, is to create a webform which does have nothing in the aspx itself. In the code behind, send instructions to the user that a word file will is approaching.
You can view the CRM reports in Reporting Services by browsing to http://YourReportingServer/ReportServer/. There you should select your organization, select 4.0 and select a report. At that moment you might be asked for a Log In Name and a Password as seen in the image.
This behavior appears when you have installed the SRS Data Connector because your deployment contains a separate server for SQL. The user name and password which are requested are not your windows credentials. Instead, the user name is your userid and the password is the id of the organization you're working with. You can find these values in SQL by using the following SQL query.
Today an article of mine has been posted to the Dynamics CRM Team blog as a guest post. This article discusses all the books which are currently available for Dynamics CRM 4.0. Next to these books you can of course get a lot of information in trainings, MOC's, the SDK and from the internet. Drop by at the team blog to look at which books are available and which matches your needs.
There are quite some reports in the out of the box installation of Dynamics CRM 4.0, but these sometimes do not match the exact business requirements. Also the reports which can be created by using the Dynamics CRM Report Builder Wizard cannot contain complex calculations or information of more than two entities. When you do export one of these reports and try to edit this in Visual Studio, then you will end up with errors. This post will guide you through these errors and modify a default report. For this example we will change the default “User Summary” report. This of course works for reports generated with the Report Wizard as well.
Exporting a report
The first step is to download a report from Dynamics CRM. You can do this by browsing to the Workplace and open the reports are. In the grid select the report which you want to modify and press “Edit Report”.
Clicking on “Edit Report” will open the Report Detail page. On this page click “Actions” and select “Download Report”. This will let you download the *.rdl file which is the definition of the report. Save this file to some location on your hard drive.
Opening the report in Visual Studio 2005
Keep in mind that the report must be opened in an editor which supports the rdl for SQL Server 2005. For Visual Studio this is version 2005. With 2008 you cannot change report files for CRM. When you directly open the report in Visual Studio you will see that the xml viewer will be opened.
Although you can modify a report in XML, there is an easier way to modify reports in Visual Studio. You will first need to set up a project for Reports. In Visual Studio select the “Report Server Project” from the “Business Intelligence Projects” group. If this is not available, install the “Business Intelligence Development Studio Add-In for Visual Studio 2005” from the SQL Server installation CD.
From the “Solution Explorer” right click on “Reports” and add an existing Report. From the report selection screen select the report which you have saved in the previous step.
The report will now be added to the solution under the reports tree view item. When you open this report now, you will get into a mode in which you can modify the report.
Connecting to Dynamics CRM
The report does have three tabs for defining the dataset, modifying the layout and previewing the report output. The first step in changing a report is to change the dataset. Every report which is exported from Dynamics CRM does have a hardcoded data source set. This will need to be changed to your current environment before you can modify the report. To do this click on the “Data” tab. You will get the following error message.
A connection cannot be made to the database. Set and test the connection string:
This is the message specifying the situation as I have just described. Just click “OK” and let’s change the data source. To do this click on the “…” next to the dataset pick list.
This will give you the “Dataset” detail form. On this form click on “…” next to the data source picklist.
This will give you the “Data Source” detail form. On this screen click on Edit next to the connection string.
This will give you the “Connection Properties” detail form. On this page verify that the server name is correct. Also select the correct database name. This should be in the format of “organizationname_MSCRM”.
After clicking on “OK” several times you will get back to the main screen. This will be refreshed and more information will be visible now.
Changing the Query
Now that a connection is created you can change the query. Unfortunately there is not just a single query. There are multiple Datasets with each a separate query. You’ll first need to find the correct Dataset before you start changing the query. By looking at the queries you will probably be able to determine which dataset you should use. Most likely the query starts with the declaration of a dynamic query like “Declare @SQL Varchar(4000)”.
Once you have found the query you can modify the query. I do expect that the people reading this article do have knowledge of how to change these queries, if not than you might want to find somebody else to change the query for you.
Once the query is modified and does return the attributes you want to use in your report, then there is an important step you should execute. If you miss the following steps, then you’ll get this error messsage “An error occurred during the local report processing. The definition of the report '/User Summary' is invalid.”. For some reason Visual Studio removes the “Fields” from the dataset. This basically is the mapping between the attributes returned by the query and the variables used in the report. You can verify that the list of Fields is empty by opening the Dataset detail form (click on the “…” next to the dataset pick list). On the Dataset detail form click “Fields”.
You can manually enter each of the fields and values, but you can also let Visual Studio regenerate this list. To generate this list you can click the “Refresh Fields” in the Dataset toolbar.
Clicking this toolbar button will ask you to define query parameters.
This screen doesn’t automatically fill the default values, but the values are available in the report though. You can find the values which you should fill in here on the “Report Parameters”. You can access these from the “Layout“ tab. Somewhere on the layout screen right click outside of the report somewhere on the yellow piece. This will give you a context menu where you can select the “Report Parameters”.
On the “Report Parameters” form you can select the property on the left side on the screen and at the right bottom you can find the default value for the selected property.
Copy the default value and past this into the “Parameter Value” for the “Parameter Name”.
Now click “OK” and the query is changed AND you can use the selected attributes in the report layout editor.
Changing the Layout
The layout can be changed on the “Layout” tab of the report. There are many possibilities to change the layout, but I won’t dive into the Reporting Services possibilities. I will just show how to add the field which is added to the query, but for more information you should look into Reporting Services trainings.
To add a field to the result table select the table and right click on the header. You’ll now get a context menu which will allow you to add a row group. This is the value you need to select to add a column. In the “Expression” field you should select your newly added attribute in this format “=Fields!address1_fax.Value”. For the label you could add a Textbox from the toolbox and change its properties to match the existing labels, but you could also just copy an existing label and change the text value.
Previewing the Report
To review the report, just click the “Preview” tab. If necessary you can go back to the Layout or Data tab to change the report. When you are ready, then save the report to an *.rdl file.
Updating the report in CRM
In Dynamics CRM, go back to the Reports area in the Workplace module. In the grid select the report which you have just modified. In the toolbar click on “Edit Report”.
On the Report detail form select the saved report definition file by using the browse button for the file location. Now press “Save” or “Save and Close” and your report is updated with your new definition file. From CRM you can now run the updated report.
