Display a CRM 4.0 PickList Display-Value in Reports and Queries

Microsoft CRM 4.0 stores picklist display values within the MetadataSchema in the StringMap table.  When creating custom reports or quieries a custom Scaler-Function is needed to show the picklist display vale.  To do this I wrote a simple function shown below called MapPickList that can be called in a SQL query to return a picklist displayvalue.

This example shows the CRM Contact table with a custom attribute called ShoeSize

SELECT     LastName, FirstName, dbo.MapPickList(‘contact’, ‘ShoeSize’, ShoeSize) AS DisplayValue

FROM  Contact

The output would look like this:

LastName            FirstName           DisplayValue
—————————————————————-
Smith                    Mark                     12.5
Estes                     Lowell                   10
Sue                        Jones                    7

And here is the SQL function.

CREATE FUNCTION MapPickList

(

/******Name of the CRM Entity we are querying******/

@entity varchar(50),

/******Name of the CRM Attribute we are querying******/

@attribute varchar(50),

/******The picklist int value stored by CRM******/

@value int

)

RETURNS varchar(500)

BEGIN

DECLARE  @return varchar(500)

SET @return = (SELECT StringMap.Value AS DisplayValue

FROM StringMap INNER JOIN MetadataSchema.Entity ON StringMap.ObjectTypeCode = MetadataSchema.Entity.ObjectTypeCode

WHERE (StringMap.AttributeName Like @attribute) AND (StringMap.AttributeValue = @value) AND (MetadataSchema.Entity.Name Like @entity))

return @return

END

Advertisements

Microsoft CRM 4.0 SSRS 2008 Integration

The following document contains a proposed  Microsoft CRM 4.0 SSRS 2008 Integration architecture I worked with Microsoft to create along with a number of roadblock solutions for various errors encountered while attempting to integrate SSRS with Microsoft CRM 4.0.  To simplify the integration concepts I have limited the server configuration to 2 servers.

Overview of Microsoft CRM 4.0 integrated with SSRS 2008

crm_ssrs20081

Setup CRM Data Connector for SSRS 2008 Scaled Out Deployment

The CRM Data Connector acts as a IFD authentication service for SSRS.  The setup wizard is straight forward but there are two potential snags

The first snag is that the Data Connector setup can’t automatically handle a Scaled Out deployment which you almost certainly will be using.  To work around this you must run the Data Connector from the command prompt and specify the install-config.xml file and path.  The Data Connector install-config.xml file tells the setup program where the instances of SSRS are located.  Were you to run the setup program without specifying the install-config.xml file you would get the following error:

Error message in the Environmental Diagnostic wizard when you try to install the Microsoft Dynamics CRM Connector for SQL Server Reporting Services: “Unable to validate SQL Server Reporting Services Report Server installation”


Here are the instruction for modifying the install-config.xml and running setup from the command line.

1.     Copy all the files in the SrsDataConnector folder on the installation CD to a folder on drive C. For example, copy the files to the following folder:

C:\SrsDataConnector

Note The SrsDataConnector folder is located in the following folder on the installation CD:

drive:\Server\i386

The drive placeholder represents the CD drive.

2.     Locate the folder in which you copied the installation files. Right-click the Install-config.xml file, and then open the file in Notepad.

3.     Add the following information to the Install-config.xml file:

The reportserverurl tags

The URL for the server that is running SQL Server 2005 Reporting Services

For example, if the server that is running Reporting Services has a named instance of Titan, the reportserverurl tags resemble the following example.

<reportserverurl>http://servername/Reportserver$Titan</reportserverurl&gt;

If the server that is running SQL Server 2005 Reporting Services has a named instance, modify the instancename tags.

For example, if the server that is running Reporting Services has a named instance of Titan, the instancename tags resemble the following example.

<instancename>Titan</instancename>

After you complete steps 3 and 4, click Save on the File menu, and then close the Install-config.xml file.

Open a Command Prompt window, and then locate the folder in which you copied the installation files in step 1.

At the command prompt, type the following command, and then press ENTER:

C:\SrsDataConnector> SetupSrsDataConnector /CONFIG path of the folder that contains the Install-config.xml file\install-config.xml

The second item to note is that it’s important to choose the “Update installation files”, when the setup wizard first runs.  In my experience the process will fail if you don’t choose this option.

For further information visit Microsoft

http://support.microsoft.com/kb/947060


Map SSRS2008 Web Service URL to work with a named instance

When you install Microsoft Dynamics CRM 4.0 together with Microsoft SQL Server 2008 and SQL Server 2008 Reporting Services all on the same server, you may receive the following error message:

“The specified path is not a metabase path.” Platform Error: System.Exception: Action Microsoft.Crm.Setup.Server.RSConfigAction failed. —> System.ArgumentException: The specified path is not a metabase path.

This issue occurs when the path of Reporting Services does not exist on the server.

To work around this issue, create a Web site that runs on the same port and the same URL that the Reporting Services server is using. In SQL Server 2008 Reporting Services, IIS is not used for accessing reports. However, if Microsoft Dynamics CRM 4.0 is installed on the same server, Microsoft Dynamics CRM 4.0 must find a Web site that has the same URL to bypass the error. To verify the URL of Reporting Services, follow these steps:

1.     Click Start, click All Programs, click Microsoft SQL Server 2008, click Configuration Tools, and then click Reporting Services Configuration Manager.

2.     Click connect to connect to your report server instance.

3.     Click the Web service URL, and see what the report server Web service URL is. For example, the URL may be http://SQLServername:8181/ReportServer.

In Windows Server 2008, follow these steps:

1.     Click Start, click Run, and then type inetmgr.

2.     Expand the server name, right-click Sites, and then click Add Website.

3.     Enter a name for the site, and then select a physical path.

4.     Enter the port number of the report server Web service. For example, enter 8181.

5.     Click OK.

In Windows Server 2003, follow these steps:

1.     Click Start, click Run, and then type inetmgr.

2.     Expand the server name, right-click Web Sites, click New, and then click Web Site.

3.     Enter a description for the site, and then click Next.

4.     Enter the port number of the report server Web service. For example, enter 8181, and then click Next.

5.     Enter a path for the Web site, and then click Next.

6.     On the Permissions page, click Next.

7.     Click Finish.

Configure Microsoft Dynamics CRM with Deployment manager

1.     Configure the Microsoft Dynamics CRM Application to point to the new SQL Server and SQL Server Reporting Services. To do this, follow these steps:

2.     On the computer that is running Microsoft Dynamics CRM Server, click Start, point to All Programs, point to Microsoft Dynamics CRM, and then click Deployment Manager.

3.     Click Organizations.

4.     Right-click the organization that you moved to the new computer that is running SQL Server, and then click Disable.

5.     Right-click the organization that you disabled, and then click Edit Organization.

6.     Type the new name of the SQL Server and the new URL for the SQL Server Reporting Services server.
Note This step publishes the reports that are in Microsoft Dynamics CRM to the new computer that is running SQL Server Reporting Services server that you specify.

7.     Click Next two times, and then click Apply.

Right-click the organization that you disabled in step 4c, click Enable, and then click Yes.

Use embedded data-sources

Microsoft CRM 4.0 does not support shared data-sources which is basically the default for SSRS2008.  Be sure to change any shared data-sources to embedded data-sources as follows:

1.     Open the report in Report Designer.

2.     On the Data tab, click the lookup button to the right of the Dataset list.

3.     Click the lookup button to the right of the Data Source list.

4.     Click to clear the Use shared data source reference check box.

5.     Click Edit to the right of the Connection box.

6.     In the Server name box, type the server name. Then, select the authentication method and the correct database name.

7.     Click OK two times.

8.     On the File menu, click Save as.

9.     Save the report on the Microsoft Dynamics CRM server, and then add the report.

Rollup 2 is required if you are using stored procedures in your reports

When you attempt to run a report from CRM 4.0 you likely will receive the following error:

“Query execution failed for data set dsNorthwind”.

There are two possible solutions for this.  Either apply the hotfix found in the link below or apply Rollup 2 for Microsoft CRM 4.0.  Since Rollup 2 for Microsoft CRM 4.0 includes the aforementioned hotfix I recommend this approach.  Also to get the hotfix files separate from Rollup 2 you will have to contact Microsoft.

http://support.microsoft.com/kb/956852