Click through to CRM records from SSRS custom report for Microsfot Dynamics CRM 4. Linking information on SQL Reporting Services custom CRM reports to Dynamics CRM 4 records and opening them up from within the report.

If you are writing a custom report – SQL Server Reporting Services (SSRS) report for Microsoft Dynamics CRM 4, you might want to add the functionality (sql expression) to click through to a record. This can be applied to some or all information shown on the report, so that if you click on the information text box, you open up the corresponding CRM record in a new window such as: click on sub-account in the sub-accounts list to open this sub-account CRM record, or the same for contact, opportunities and so on.

To do this, right click on the text box in where you will be putting the expression and the value to be clicked on, and click on Texbox properties. On there click on Actions on the left, select last option “Go to URL” and in the expression box type the following query expression

=IIF(IsNothing(Parameters!CRM_URL.Value),system.dbnull.value,Parameters!CRM_URL.Value & "?ID={"&Fields!Accountid.Value.ToString()&"}&OTC=1")
/*Replace "&Fields!Accountid" with the ID of the corresponding record */
/*you want to be open, so contactid, opportunityid or any other custom entity id field*/
/*Also replace OTC value above with the Entity Object type Code (OTC) value*/

You can get any entities OTC value from the “EntityView” view or for the full list of all system entities type codes, read this MSDN article: http://msdn.microsoft.com/en-us/library/bb887791.aspx. Obviously you will need to get the OTC value for your custom entities from the “EntityView” view from your Dynamics CRM database. You can use a query similar to the following:

select entitytypecode from entityview where entityname = 'account'

This sql expression can work with any entity either system (cusomisable) or custom entity.

Return and display picklist value (display value) in custom sql report reporting services report in Microsoft Dynamics CRM 4 entity attribute and StringMap picklist values table

Normally, you should be able to get the picklist display value and picklist item id in the filtered view of any entity. In some cases, you may need to work with StringMap table directly to get the picklist values for a specific field of a given entity. This could be the case if you are not using the filtered view for some reason or if you want to write a report based on the table or the non-filtered view, the following post is providng the query (queries) needed to do so.

To get the display value of any picklist, you need to use the StringMap table. If you are writing a custom ssrs (SQL Server Reporting Services) report for Dynamics CRM 4, you usually need to return the picklist display value not the picklist item id.

For example: in the opportunity entity, you have a picklist attribute “Rating” (attribute name opportunityratingcode). If you are writing a custom SQL report to add to CRM and you want to display the values of the attribute, you will find that the display value of the picklist is not stored in the filtered view of Opportunity (FilteredOpportunity). To get the display value of any picklist, you need to use the StringMap table.

There many ways to get the information from the StringMap table, I will mention two ways here:

1) using Inner Join (simple - one picklist only)
This way, you just simply join the two tables: your entity filteredview and the StringMap table
/* the query uses out of the box attributes, so should run successfully on all CRM organisations*/
SELECT opp.name, opp.estimatedvalue_base, opp.closeprobability, StringMap.Value
FROM Opportunity as Opp INNER JOIN
  StringMap ON Opp.opportunityratingcode = StringMap.AttributeValue
WHERE (StringMap.ObjectTypeCode = 3) AND (StringMap.AttributeName = N'opportunityratingcode')
AND (opp.accountid = 'input your accoung GUID here'

This query will return list of all Opportunities for the specified account display the picklist display value rather than the id.

2) using Temp table (my prefered way for multiple picklists)

This query is longer and uses/declares temp tables but it is the simplest way in case you have several picklists and you want to return the display value of multiple picklists. You just create several temp tables just like @TempSM, where each temp table returns the list of display values for each picklist and then join them together.

1 Picklist query:
/* the query uses out of the box attributes, so should run successfully on all CRM organisations*/
Declare @TempOpp table(OpportunityID uniqueidentifier, OpportunityName nVarchar(max),
Estimatedvalue float, Probability float, Rating nVarchar(max))
Insert into @TempOpp
select opp.opportunityid,opp.name ,
opp.estimatedvalue_base,
opp.closeprobability,
opp.OpportunityRatingCode
From Opportunity opp
where opp.accountid = '97F97A5A-2622-DF11-A1C3-005056BA6B8A'
Declare @TempSM table(AttributeValue nVarchar(max), Value nVarchar(max))
Insert into @TempSM
select AttributeValue, Value
from StringMap
WHERE (StringMap.ObjectTypeCode = 3) AND
(StringMap.AttributeName = N'opportunityratingcode' )
select t1.OpportunityName, t1.Estimatedvalue, t1.Probability, t2.Value as RatingDisplayValue
from @TempOpp as t1 left outer JOIN @TempSM as t2 ON
(t1.Rating = t2.AttributeValue)

This query will return list of all Opportunities for the specified account display the picklist display value rather than the id.

2 Picklists query:
------------------
/* the query uses out of the box attributes, so should run successfully on all CRM organisations*/
Declare @TempOpp1 table(OpportunityID uniqueidentifier, OpportunityName nVarchar(max),
Estimatedvalue float, Probability float, Rating nVarchar(max), Priority nVarchar(max))
Insert into @TempOpp1
select opp.opportunityid,opp.name ,
opp.estimatedvalue_base,
opp.closeprobability,
opp.OpportunityRatingCode,
opp.PriorityCode
From Opportunity opp
where opp.accountid = '97F97A5A-2622-DF11-A1C3-005056BA6B8A'
/* Now we have @TempOpp1 with list of all opportunities for the specified account but with ID for picklist value not display names*/
/*Then we declare a temp table for Rating picklist*/
Declare @TempSM table(AttributeValue nVarchar(max), Value nVarchar(max))
Insert into @TempSM
select AttributeValue, Value
from StringMap
WHERE (StringMap.ObjectTypeCode = 3) AND
(StringMap.AttributeName = N'opportunityratingcode' )
Declare @TempOpp2 table(OpportunityID uniqueidentifier, OpportunityName nVarchar(max),
Estimatedvalue float, Probability float, Rating nVarchar(max), Priority nVarchar(max))
Insert into @TempOpp2
select t1.OpportunityID, t1.OpportunityName, t1.Estimatedvalue, t1.Probability,
t2.Value as RatingDisplayValue,t1.Priority as Priority
from @TempOpp1 as t1 left outer JOIN @TempSM as t2 ON
(t1.Rating = t2.AttributeValue)
/*tempopp2 now has opportunities with the rating display value*/
/*Now creating a temp table for Priority picklist*/
Declare @TempSM1 table(AttributeValue nVarchar(max), Value nVarchar(max))
Insert into @TempSM1
select AttributeValue, Value
from StringMap
WHERE (StringMap.ObjectTypeCode = 3) AND
(StringMap.AttributeName = N'PriorityCode' )
select t1.OpportunityID, t1.OpportunityName, t1.Estimatedvalue, t1.Probability,
t1.Rating as RatingDisplayValue,t2.Value as Priority
from @TempOpp2 as t1 left outer JOIN @TempSM1 as t2 ON
(t1.Rating = t2.AttributeValue)

--------------------------------------------

and so on. You create temp table for each picklist, join it with opportunity temp table and then carry on.

Note: This is one way of doing this. There are obviously many other shorter ways of doing this. I personally prefer temp tables a lot because although longer, they are simpler and easier to use. They also bypass any issues with null values in the right table of the join.

Not related Note: Always use filtered views when writing custom SQL reports on SSRS for CRM as they comply with CRM security.

Configure Microsoft SQL Server to allow remote connection and remote SQL Management Studio access

To get straight to the point. I have been trying to setup Microsoft SQL Server 2008 to allow remote connection and specifically I wanted the SQL server to allow SQL Server Management Studio on a different computer completely outside the network to be able to access this remote SQL Server and manage it fully without having to login on the remote Dedicated standalone SQL server. I also wanted to be able to access this server remotely from visual studio .net on the client machine. Please be aware that I am trying the extreme settings here of getting client (management studio or visual studio) to connect to the host server (SQL Server) where the client is on a completely different network from the host. i.e. This is a 100% Internet only remote connection not within the same network, lan, or even trusted domains, forests, etc..

There are a lot of setup steps that you need to do but remember there are two main parts of this setup:

  1. configure SQL Server to accept remote connection and,
  2. Setup and Configure the firewall running on this server machine to allow inbound incoming connections.

Step 1 is straightforward and there are a lot of MSDN and Technet articles that explain it. But anyway, to make this post informative here are the main steps:

  • Go to Programs -> SQL Server -> SQL Server Configuration tools -> SQL Server Configuration Manager.
  • Make sure that the SQL Server Browser service is running under (SQL Server Services).
  • Go to SQL Server Network Configuration, open the SQL Server instance that you want to use. Make sure that the TCP IP Protocol is enabled.
  • right click on this TCP/IP protocol node and click properties. Go to IP Addresses tab. Make sure that the server IP Address is: 1) on the list of IPs, 2) is Active, 3) is enabled.
  • You can either: a) let your SQL server to listen on all server ports for any incoming SQL server connection OR b) you can specify which exact port to listen to. For a) set TCP dynamic Port to “0” and leave the TCP port value blank. To achieve b) set dynamic value to blank and the TCP port to the port number that you want the server to listen to.
  • Restart the SQL Server instance service from SQL server Services node (All this configuration is still done from within the SQL Server Configuration Manager).
  • Go to the SQL server instant in management studio ->right click -> properties -> connections -> Allow remote connections to this server.
  • Also, Go to the SQL server instant in management studio ->right click -> properties -> Security -> Allow SQL Server and Windows authentication mode.
  • Look at this article for more details: http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/ec77d275-99c7-4cc0-b60d-707bde6f8c67/

Step 2 is the step I struggled with. Basically, I was unable to connect and didn’t know whether it is a Firewall problem or a problem with my configuration of the SQL server (step 1). Best way to troubleshoot that, allow ALL incoming inbound connections on your firewall. Try to connect remotely to the server (using the SQL Server management studio or Visual Studio .NET), if it works, then your issue was the Firewall. If it doesn’t, then it is an issue with SQL server configuration. PLEASE REMEMBER to disallow incoming inbound connections after you did this test!!!

For step 2, you need to do the following to setup and configure your windows server Firewall to accept and allow incoming connections to the host server on which you got SQL Server. So, Host Server Firewall configuration is as follows:

  • Allow incoming TCP port number 1433
  • Allow incoming UDP port number 1433
  • Allow incoming UDP port number 1434
  • Allow incoming TCP port number 135
  • Add the program sqlservr.exe to the exceptions list. By default, sqlservr.exe is installed in C:\Program Files\Microsoft SQL Server\MSSQL10.InstanceName\MSSQL\Binn, where InstanceName is MSSQLSERVER for the default instance, and the instance name for any named instance.

Once I have added all these ports to my exception list, i.e. opening all these ports for incoming traffic allowing access to my server, once I have done that, all my problems were solved and I managed to connect to the SQL server remotely via the Internet (not an internal network) where the client and host have no direct connections what so ever.

Some articles and useful resources related to step 2 (Firewall setup):

One final note:  The format to input the server name in the SQL Management Studio connect to server object box is as follows:

<IP>,<port>\InstanceName

for example: 92.37.54.28,2345\InstanceName

If you selected the all ports option. i.e. dynamic port = 0 and TCP Port is blank (as in step 1 above), then the format will be something like that:

server name box should have: 92.37.54.28\InstanceName or just the IP address for the default instance (haven’t tried to setup a default instance though.. I have used named instances for added security.

That’s all for now. Hope this helps. Please write a comment if you have anything to add, correct me or if you have any related question. Please let me know if you need any help or support on this.

Thanks,

Mohamed Mostafa

Error with Report Server

We have been recently approached by a client who had a problem with their report server. If you try to access reports from the reports manager, reports run fine and you can view any report fully and in the right format. The problem arrises when a .NET application tries to open these reports, it just opens a new internet explorer window and uses a url to show the report. This was working fine until the client has probably made some changes to their report server. Since then, reports are not displaying in Internet explorer.

To cut a long story short, we eliminated all authentication issues by checking that security and access to all reports is properly configured and the right users has the right to run these reports. We also eliminated problems between SQL Server and the report server, as reports can run smoothly from the reports manager.

We finally came with the concolusion that the error must be related to Internet Explorer not able to render the report properly. We looked through IIS but could not find anything that looks wrong or not the way it should. I have then checked the security setting of Internet explorer (Tool –> Internet Options –> Advanced) and this all seemed fine but I ticked the box that says display all scripting errors. We also did something which I am not sure if it made any difference, which is that we added the server to the trusted sites in Internet Explorer. Then when we tried to open the report again, this time we started to get scripting errors (although they were not showing in the status bar before for some reason). Scripting error dialogs start to show and we finally started to understand what is happening. We got errors such as: “RS Controller is undefined”, “RSControl object reference not set to an object”. We quickly googled this error and we found an easy and simple solution for this (can’t find the original solution at the moment). The solution is to go to the report server itself, open IIS, right click on your reports server (usually under default website), go to directory –>configuration –> search for axd and double click to open its properties. On there uncheck the filter option (can’t remember the option on top of my head and I don’t have access to any IIS 6.0 at the moment to get the exact option wording). Finally click ok.

This has simply solved the problem, and we managed to get all reports back working from the browser and the .NET applicaiton now opens these reports externally with no issues at all.

I have created this post few days after the resolution, and I don’t have access to the client’s servers at the moment so if you have a similar problem and the post doesn’t answer your questions, please feel free to comment here and I promise I will get back to you as soon as I can with more details and may be a screenshot!

All the best.

Improving Microsoft Dynamics CRM Performance Enhancements & Optimization and CRM Server maintenance and improvements

Microsoft Dynamics CRM Performance enhancements, optimisation and maintenance can be divided into three main areas:

– Performance enhancements at Client level: networking issues such cache expiration times, latency and bandwidth
– Optimisation at application level: plugins, sdk code,
– and, improvements at Data layer: Indexes, (physical data layout)

This post is based on Microsoft recommendations and best practices for Microsoft Dynamics CRM 4.0 performance enhancement and improvements.

The first most important enhancement process and action that needs to be taken into account is to always to:  install and setup all the Latest hot fixes and update roll-ups (all CRM 4 updates link – KB article)

Now, let’s go through some of the possible enhancements for each area of those 3 main areas.

Client Level performance enhancement:
————————————————-

– Specify a value from 200 to 300 megabytes of disk space for temporary Internet files
– Web objects cache are usually 3 days by default, change it to make it 15. IIS –>CRM Website ->HTTP header.
– Consider using compression techniques: WAN acceleration appliances have been proved to cause excellent performance enhancements to Microsoft Dynamics CRM 4.0 .
– Make sure that your client machines bypass proxy server if you have one. Proxy servers could delay and affect performance between clients and server machine.
Application server performance enhancement:
———————————————————

– Some .NET code could cause potential issues: Ensure good .net coding practices and avoid bad practices such as memory misuse, improper use (and unessential use) of threading, no resource cleanup, implicit type conversion, Misuse of collections and inefficient loops.
– Increase the reserved port limit. Consider increasing reserved ports to be up to maximum of 5000. Only programs requesting specific ports can use reserved ports. To increase the limit do the following:

1. In the Registry Editor, navigate to the subkey:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
2. Click Parameters, and then, on the Edit menu, click New.

3. Create a registry entry by using the following information:
Value Name MaxUserPort
Value Type DWORD
Value data 65534
Valid Range 5000-65534 (decimal)
Default 0x1388 (5000 decimal)
Description Controls the maximum port number that is used when a program requests any available user port from the system. Typically, ephemeral (short-lived) ports are allocated between the values of 1024 and 5000, inclusive.
4. Close the Registry Editor, and then restart the computer to apply the new configuration.

– Always Monitor performance of your Microsoft Windows server. (don’t forget patches, updates, rollups, etc)
– Disable Tracing and debugging on IIS of your production environment as it usually causes performance issues and slows down performance.
– Thread pool settings: Creating threads on a per-request basis and not sharing threads using thread pools can cause performance and scalability bottlenecks for server applications.
– Installing server roles on more than one computer: In Microsoft Dynamics CRM 4.0 Enterprise Edition, you can specify to deploy one or both of the two server role groupings on one computer, or to have each one of them on a separate machine. Microsoft Dynamics CRM server roles are: Application server and platform server server roles.
– Another improvement is to Modifying the default view for entities that potentially has large number of records. In this case, instead of setting the default view to show all Active records, which in some cases can be too many recrods, you should set the default view of such entities to show “My records” only. This way, there will be no pulling of all active records on the server everytime you attempt to view this entity.
Data Layer performance enhancements:
————————————————-

– Row compression results in significant improvement in application performance.

– Page Compression is only good for 80% reduction in space requirements with no considerable change to performance (negligble performance decrease is possible with page compression).

– Consider using Sparce columns.

– Use Transparent Data Encryption.

 For further details and for a full list of Dynamics CRM performance improvements, please download and study this document by Microsoft:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=ba826cee-eddf-4d6e-842d-27fd654ed893&displaylang=en

Another document dedicated to data layer (SQL Server) performance enhancements and which includes some tests carried out by Microsoft as well as best practices can be downloaded here:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=b5bb47a4-5ece-4a2a-a9b5-5435264f627d&DisplayLang=en

Hope this help! Please comment with your experiences or if you found anything on this post useful.

Please note that as always, this post and all posts on this blog are provided as is, with no warranty and if you decide to try any of these steps, you will be doing so at your own risk. No liability will ever be on the author and owner of this blog.