Windows Vista

I thought I'm a geek, but you really should watch these guys. These are the real ones! Watch the Windows Longhorn Productname Launch Video.

Anyway here's the logo which belongs to the new Windows version:

MS SQL Reporting Services Reports for MSCRM including basic security

Microsoft CRM is using Crystal Reports for reporting purposes. This works pretty fine as long as you do not want to modify reports or create your own reports. Crystal will then become very slow if you are working with thousands of records in your database. You could tweak Crystal, but instead, you could also take a look at MS SQL Reporting Services (SRS). Microsoft has released a report pack for MSCRM v1.2 which includes 6 reports. This report pack will give you a quick start on working with SRS Reports in MS CRM. But, when examining these reports, you will notice that the security rights which are set within MS CRM are completely gone when requesting these reports! In this posting I will show you how to create basic security for custom SRS reports.

The security in MSCRM is working with Security Descriptors. We cannot use these Security Descriptors in SRS. Therefore we will have to recreate the security settings. These settings are all stored in the database. Depending on how much security is required for your business, you can get the settings. In this example I will show you how to include settings on business unit level. Let's assume that you are working in an international company called Avanade. The headquarters are in the USA and there are several other locations in other countries including The Netherlands and France. In the end of the example, the users which reside in The Netherlands are able to see the Dutch accounts, the France users the France accounts and the headquarters will see all accounts.

For this example I do expect you to install Windows, Visual Studio, MSSQL and Reporting Services on your own. That shouldn't be too hard though :)

As soon as you have Reporting Services installed, we can start creating our report. We will do this in Visual Studio. There should be a new project type: "Business Intelligence Projects". From this project type we will select "Reports Project". Within this project we will chose to add a new report (Solution Explorer, right click on Reports and select "Add New Report"). Visual Studio will ask you to first create a Data Source. Please fill in a nice name, select SQL Server and enter your connection string. You will probably use this Data Source more often, so it would be useful to select the checkbox "Make this a shared Data Source".

Now its time for the real work. Let’s create the query. A user from the Netherlands should only see Dutch accounts. We will achieve this by using the userid. Based on this GUID we can find out in which Business Unit this person resides. If this is the top BU, then we should select all accounts, otherwise we select a filtered set of accounts. The first step is to select every account which resides in the same business unit as the user. This query will get us there.

DECLARE @owningBU UNIQUEIDENTIFIER
SELECT @owningBU = BusinessUnitId FROM SystemUserBase WHERE SystemUserId = @userid;

SELECT Name, Telephone1, Description
FROM Account
WHERE (OwningBusinessUnit = @owningBU)
ORDER BY Name

As you can see, I am using the view Account instead of the table AccountBase. This view has already the address data included, which saves me a lot of query coding.

As soon as you press enter after entering the query, you will get a lot of layout options. Currently I do not care too much about how the report looks, so let’s choose to finish this report. When viewing the report in the preview window, you should enter a guid of a user which does own some accounts. Just to be sure that you do get some results.

If you enter the userid of a user which resides in the USA Business Unit, then you will only find USA accounts. We can solve this by making sure that this query will also search for accounts which are in child business units. I have created a user defined (recursive) function which accepts a GUID and returns a table with all the child business units. Here is the function:

CREATE FUNCTION dbo.GetChildBU (@currentBU UNIQUEIDENTIFIER)
RETURNS @businessunitsTable TABLE(ID UNIQUEIDENTIFIER) AS
BEGIN
DECLARE @businessunitID UNIQUEIDENTIFIER
DECLARE BU_Cursor CURSOR FOR
SELECT BusinessUnitId FROM BusinessUnitBase WHERE ParentBusinessUnitId = @currentBU

OPEN BU_Cursor

/* Read current */
FETCH NEXT FROM BU_Cursor INTO @businessunitID

WHILE @@FETCH_STATUS = 0
BEGIN
/* Return current */
INSERT INTO @businessunitsTable
VALUES (@businessunitID)

/* Return children */
INSERT INTO @businessunitsTable
SELECT * FROM dbo.GetChildBU(@businessunitID)

/* Read next current */
FETCH NEXT FROM BU_Cursor INTO @businessunitID
END

RETURN
END

With this function in mind, you can modify your initial query to include these Business Units. This will be the result:

DECLARE @owningBU UNIQUEIDENTIFIER
SELECT @owningBU = BusinessUnitId FROM SystemUserBase WHERE SystemUserId = @userid;

SELECT Name, Telephone1, Description
FROM Account
WHERE (OwningBusinessUnit = @owningBU) OR OwningBusinessUnit IN (select * from GetChildBU (@owningBU))
ORDER BY Name

If you have added the function to the MSCRM database and modified the query for the report, then you should be able to see all the accounts in the users Business Unit and all the child Business Units.

OK, now the reports are fine, but where do we store them in CRM? It would not be logical to store some of the reports under the reports part in the Navigation bar and some modified reports in the Left pane or menu bar. You will probably want them to be in the same screen as the other reports. This you can solve by modifying the map_xml.aspx file in the Reports folder. You can copy and paste one of the other items and you will have a link to your reports. Just put them in a fancy -if possible looking like crm- datagrid and you have extended CRM with your own reports! Please keep in mind that the last modification is unfortunately not supported...

Happy reporting!

"Parser Error Message: Access is denied:

Every now and then I get this error message:

Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message: Access is denied: 'MyWebApp'.

Source Error:

Line 198: <add assembly="*">

This doesn't say much, but Harish Ranaganathan wrote a wonderful solution for this error message.

Keep up the good work :)

Full-Text Search is not enabled for the current database

After using the redeployment tools it is sometimes not possible to search for KB articles in CRM. The error which shows up (either on the screen or in the event viewer) is:

Full-Text Search is not enabled for the current database. Use sp_fulltext_database to enable full-text search for the database.

Here is the resolution:

Ensure that the Microsoft search service is running and set to start automatically. This can be verified under the Control Panel > Administrative Tools > Services.
Enable (or re-enable) full text search on the databases. Open Query Analyzer and connect to the production databases. Run the following query, where is the database that ends with “_MSCRM”:

use <database>
go
exec sp_fulltext_database 'enable'
go

Repopulate the catalogs. This is done by going into the _MSCRM database, looking at the Full Text Catalogs under the database, and right clicking on the ix_* file and clicking on “Start Full Population”. Populating the index might take some time. However, there should be no impact on the users during this time.

Multi select box in MSCRM v1.2

The current controls which can be put on a CRM entity form are:
- String
- Integer
- Float
- Currency
- Date/Time
- Boolean
- Picklist
- Memo

As you can see, there is no such option like a multi select box. Even within CRM you do sometimes need this. For example: Several contacts are receiving business gifts every now and then. You do not want the enduser to fill in which gifts they received in by hand, because they might make typing mistakes. Then you can't use this field for searching. So you want to use a multi select box.

In this article I will describe a way to create a workaround for archieving this by using a combination of a picklist and a memo field.

First we will have to add the picklist and memo field to the deployment manager. We will add a picklist called CFPgifts and a memo field called CFMgifts. It probably wouldn't surprise you that the next step is to add these fields to the contact form of CRM. We will add them under eachother, the picklist on top, the Memo under. I have removed the label for the Memo field to make it look better.

Now we can fill the picklist with the values. I have added the values 'Christmas card', 'Wine', 'PDC tickets'. Now we need to copy the selected value to the memo field. This will be done by using Javascript. The properties window of a picklist field allows you to add some scripting to the onChange event. This field is very useful, but not really user friendly. First lets get the control working, later I will describe an easier way to modify the script.

We communicate with the picklist value by using:

crmForm.CFPgifts.value

The memofield is available by using:

crmForm.CFMgifts.value

That means that if we enter this script in the onChange event, that we can copy data from the picklist to the memo field:

crmForm.CFMgifts.value = crmForm.CFMgifts.value + crmForm.CFPgifts.value;

Don't forget to switch the event on with the checkbox!

Now we can extend this task by putting a separator between the values. We should also check if the new item is a new item or if it is already in the list. If we add all this to the onChange event, then that small box will not be clear anymore. It also removes the breaks you enter manually. Therefore I prefer to only call a function from this onChange event and store the real code in a separate file, although this is not support. To do this, we have to modify the edit.aspx located at: drive:/website/SFA/conts/edit.aspx. In this file there are several other javascript files linked. We add one for ourselves which will look like this:

<script language="javascript" src="/sfa/conts/PicklistEx.js"></script>

Here is the file which includes the separator and check for existing items.

// Functions needed by the multi select box in MSCRM
// Author: Ronald Lemmen
// Company: Avanade Netherlands

// function to check if an item exists and if not, add it to the item list
function checkAndAdd(item, itemlist){
if (item != ""){
if (checkItemExists(item, itemlist) == false){
itemlist = addItem(item, itemlist);
}
itemlist = clearup(itemlist);
}
return itemlist;
}

// function which adds a new item and clears up the item list
function addItem(newItem, itemlist){
return newItem + ";" + itemlist; //modified by ronald 05-11-04
}

//this function checks if the submitted item is in the list;
//this can be done by checking on ;item; ;
//an item like "wine" is not the same as "red wine";
function checkItemExists(item, itemlist){
itemlist = strReplaceAll(itemlist, " ", ""); // remove the spaces from the existing list;
item = strReplaceAll(item, " ", ""); // also remove the spaces from the new item
itemlist = ";" + itemlist + ";"; // also the first charactar without a semicolon will be recognized now.;
if (itemlist.indexOf(";" + item + ";") >= 0){
return true;
}else{
return false;
}
}

function clearup(itemlist){
itemlist = strReplaceAll(itemlist, ";;", ";"); // remove double semicolons;
if (itemlist.substring(0,1) == ";"){ //remove the first semicolon;
itemlist = itemlist.substr(1);
}
if (itemlist.substring(0,1) == " "){ //remove the first space;
itemlist = itemlist.substr(1);
}
return itemlist;
}

//function to search and replace all occurances of a string;
function strReplaceAll(str,strFind,strReplace){
var returnStr = str;
var start = returnStr.indexOf(strFind);
while (start>=0){
returnStr = returnStr.substring(0,start) + strReplace + returnStr.substring(start+strFind.length,returnStr.length);
start = returnStr.indexOf(strFind,start+strReplace.length);
}
return returnStr;
}

When this file is stored on the correct location (drive:/website/SFA/conts/PicklistEx.js), then you can modify the onChange event to:

crmForm.CFMgifts.value = checkAndAdd(crmForm.CFPgifts.value, crmForm.CFMgifts.value);

This keeps the CRM code readable and you will be able to modify this code a lot easier compared to that small box. Now you just have to publish the modifications and run an iisreset and you're done.

You might want to enhance this control. Now the users are still able to modify the memo field by hand. You could add another picklist which will remove values from the memo field and then disable the memofield upon opening of the form. This is up to you to create :)


Update: James indeed did take the time to enhance this control and to make this for CRM 3.0! See his blogpost at this address:
http://jameswilcox.ca/random/ms-crm-30-multi-select-boxes/

Hello world!

How original!

Starting the first post with a holy "Hello world".
On the other hand, I'm also not the first one starting a blog. But I do think that I help other people with this blog. Here you will find information about CRM, C# and a little bit of myself. Feel free to contact me for clarification or whatever you would like to contact me for.

See you soon again,

Ronald Lemmen