Author Archives: mayankp

About mayankp

I’m a Dynamics CRM Consultant based in United Kingdom. I have been working with Dynamic CRM system since last four years (Since CRM 3.0 be

CRM AUDITING: SQL Database Standard to Enterprise version

So on MS CRM Audit Log screen we could not see any partition details available even though (as shown in screen print below) even though we are using enterprise version of SQL.

B102082016_1

We also realized that our audit table grown very large and was started impacting performance as well, during investigation it is revealed that MS CRM organization was created from back up which was taken from standard SQL version.

So when new organization import process run then CRM system did not created these partition so we contacted Microsoft support and they provided following SQL script for this.

After running script we started seeing CRM Audit partition in the system now.

B102082016_2

CRM AUDIT: SQL enterprise to standard version

if your requirement is other way around so your current CRM SQL DB is enterprise version and if you want to migrate to standard SQL version then follow the steps provided in this KB article to restore DB and then after this perform normal import organization operation in CRM for this.

Hope this helps…

Cheers,
MayankP:)

 

 

CRM 2011 Workflow Entity View records are not sorted alphabetically

I just recently come across customer reported CRM issue related workflow view displaying on demand workflow list random manner and not displaying them in alphabetically manner.

This issue mainly hits you if you got lots of workflow related to particular entity and while trying to run specific on demand workflow you will see lots of workflow in view and needs to manually sort the list to find your workflow.

B6510

Well looking at issue it seemed really easy issue to fix, so just open relevant view and apply sort on name column and publish the changes BUT this view is called “On Demand Workflows” is not customizable and hence we have to do unsupported change to fix this issue. Following steps done to fix this issue using unsupported manner.

Step 1: first find existing view and view definition from the data base. So run following query for this.

select savedqueryid,name,fetchxml from savedquerybase where name like '%On Demand Workflows%' order by name

Please note down savedqueryid as we will be using this in next steps.

B65112

Step 2: So following is the Original FetchXML definition for this view. As can be seen there is default sort order is not defined in this definition.

<fetch version="1.0" mapping="logical"><entity name="workflow"><attribute name="workflowid" /><attribute name="name" /><attribute name="createdon" /><attribute name="modifiedon" /><attribute name="statecode" /><attribute name="owningbusinessunit" /><attribute name="ownerid" /><filter type="and"><condition attribute="type" operator="eq" value="1" /><condition attribute="ondemand" operator="eq" value="true" /><condition attribute="statecode" operator="eq" value="1" /><condition attribute="category" operator="eq" value="0" /></filter></entity></fetch>

We will be Modifying above fetch xml with following to include sort order related to this. We refer highlighted change in fetchxml below for this.

<fetch version="1.0" mapping="logical"><entity name="workflow"><attribute name="workflowid" /><attribute name="name" /><attribute name="createdon" /><attribute name="modifiedon" /><attribute name="statecode" /><attribute name="owningbusinessunit" /><attribute name="ownerid" /><order attribute="name" descending="false" /><filter type="and"><condition attribute="type" operator="eq" value="1" /><condition attribute="ondemand" operator="eq" value="true" /><condition attribute="statecode" operator="eq" value="1" /><condition attribute="category" operator="eq" value="0" /></filter></entity></fetch>

Step 3: now update this specific view definition using SQL query against CRM database, please replace the SAVEDQUERYID with value noted down in step 1.

update savedquerybase
Set fetchxml ='<fetch version="1.0" mapping="logical"><entity name="workflow"><attribute name="workflowid" /><attribute name="name" /><attribute name="createdon" /><attribute name="modifiedon" /><attribute name="statecode" /><attribute name="owningbusinessunit" /><attribute name="ownerid" /><order attribute="name" descending="false" /><filter type="and"><condition attribute="type" operator="eq" value="1" /><condition attribute="ondemand" operator="eq" value="true" /><condition attribute="statecode" operator="eq" value="1" /><condition attribute="category" operator="eq" value="0" /></filter></entity></fetch>'
where name = 'On Demand Workflows'
and savedqueryid = 'SAVEDQUERYID';

After applying above changes log in to CRM and verify this view is displayed record correctly now.

B65113

****Important Notes regarding above change****
above change is unsupported so make sure you apply this change to Dev, Test environment before applying this to Live environment. Also make sure you take back up of database before doing this
change.

Hope this helps…

Cheers,
MayankP:)

Book Review: Packt Microsoft Dynamics CRM 2011 Reporting

I have been asked me to  review the Microsoft Dynamics CRM 2011 Reporting written by Damian Sinay

Image

The reviewers for the book are:

Overview and Structure of the Book

The book is 308 pages and the chapters are:

  • Preface
  • Chapter 1: Introduction to Reporting in      Microsoft Dynamics CRM
  • Chapter 2: Database Basics
  • Chapter 3: Creating Your First Report in CRM
  • Chapter 4: SQL Server Report Builder
  • Chapter 5: Creating Contextual Reports
  • Chapter 6: Creating Inline Reports
  • Chapter 7: Using Reports and Charts in      Dashboard
  • Chapter 8: Advance Custom Reporting and      Automation
  • Chapter 9: Failure Recovery and Best Practices
  • Chapter 10: Mobile Client
  • Appendix : Expression Snippets

 

Preface

This book covers all different options available for CRM reporting and provides good grounding in CRM reporting aspects so any users/developers want to learn/understand CRM reporting better this is book for them.

Chapter 1: Introduction to Reporting in Microsoft Dynamics CRM

The first chapter explain different types of reports available in CRM and also explains installation procedure for CRM 2011 connects and Reporting authoring extension.

SQL reporting service version sections (page 15) explains different SQL server version compatibility with SQL Server and CRM server and states that SQL Server 2012 is not compatible with Visual studio 2012 for CRM 2011 Reports. I guess this was correct at time of book writing but since then Microsoft released update of SQL Server Data tools and after this update Visual studio 2012 can also be used to develop CRM reports for SQL Server 2012.

Chapter 2: Database Basics

This chapter explain basic and advanced commands of SQL Language and also FetchXML Language. All the dynamics CRM views are created with this FetchXML language and users can now write reports in CRM 2011 using this language as well.

 Chapter 3: Creating Your First Report in CRM

This chapter takes you through step by step guide on how to create report in CRM using CRM reporting wizard and then how to modify that report in SQL Reporting Service Project.

Chapter 4: SQL Server Report Builder

This chapter explains step by step guide to create report using SQL Report Builder which comes with SQL Reporting services. SQL Report Builder can be used by intermediate user to develop CRM reports.

Chapter 5: Creating Contextual Reports

This chapter looks at advanced tools (visual studio) to create CRM report and this chapter also reviews CRM report parameters and chart controls.

Chapter 6: Creating Inline Reports

This chapter looks ate crating embed report on CRM entity form. It creates HTML web resource to achieve this functionally and chapter also explains how to create/update this HTML web resource. 

Chapter 7: Using Reports and Charts in Dashboard  

This chapter explains process of integrating report in CRM dashboard and CRM charts (i.e. new feature in CRM 2011) basics.

 Chapter 8: Advance Custom Reporting and Automation

This chapter explains how one can integrate a custom ASP.NET or Silverlight application to show a dynamic or more sophisticated report inside Dynamics CRM 2011

Chapter 9: Failure Recovery and Best Practices

This chapter looks at how one can troubleshoot different issue arising while working with reports in CRM 2011. Quick rightly these details were need as user will tend to hit these issues while developing CRM 2011 report. This chapter also focuses on performance enhancement and SQL Reporting Service Best practices.

Chapter 10: Mobile Client

This chapter explores CRM reports on different mobile devise like iPhone, iPod and Microsoft Surface and also explains process of configuring IFD authentication with CRM.

Appendix: Expression Snippets

This section explores basic expression and their usage in reports and how can you add external code, variable and assemblies in report.

Final Verdict

Provided that we already have Dynamics CRM books but I guess each book generally dedicates chapter or two for CRM reporting only while this book is specifically for CRM reporting which is really good.

And although this does not cover all types of reports (i.e. matrix reports, SQL advanced chart type reports) related to CRM it does cover lots of details related to CRM report So if you are just started work in CRM reports or working in CRM reports but want to know more about it this is definitely book for this.

If you are interested in buying above book, here is link for this.

http://www.packtpub.com/microsoft-dynamics-crm-2011-reporting/book

Cheers,

MayankP:)

 

 

 

 

CRM : Rescheduling the Update Contract States job

Update Contract States job is CRM 2011 (also for CRM 4.0) system job which runs daily and change the contract status based on contract start date and end date (i.e. change contract from Invoiced to active and from Active to expired).

One of customer’s CRM organization has this job running 10 PM at night and wanted to change this job on particular time (very early in the morning) to make sure contract status are up to date when users starts using CRM system.

There is job editor provided by MS to reschedule CRM system jobs but this does not include this specific job.

so following are steps we undertook with the help of CRM parter/MS support to reschedule this particular job in CRM system.

Step 1: run the following query to find the current Jobs in the relevant CRM organization. (note down the AsyncOperationid returned from this query as we will be using this in next step)

select AsyncOperationid,RecurrenceStartTime,postponeuntil,StatusCode, RecurrencePattern from AsyncOperation WHERE
Name = 'Update Contract States job'
and
StatusCode =10
and RecurrencePattern is not null

following quick details regarding important columns in this table.

RecurrencePattern is used to describe frequency and interval of the system jobs and please refer this article for more information on regarding this.

Postponeuntil is datetime field and indicate when this job will run next time. And it is UTC date meaning if this field contains “2012-10-23 09:08:00.000” and if your time zone is GMT + 1 then this job will run next time on “2012-10-23 10:08:00.000”

RecurrenceStartTime is also UTC datetime field which will be used to set next runtime of job, date part of this field is not important as long as it is set in the past.

Step 2: so using noted down AsyncOperationid in step 1 run following query to update recurrencestarttime, postponeuntil for this Update Contract States job .


update AsyncOperation set
RecurrenceStartTime = '2012-10-22 09:08:00.000',
postponeuntil = '2012-10-23 09:08:00.000'
WHERE AsyncOperationid = '5EB24ECB-60ED-4F59-801D-A6C19465C4D8'

So as per above update, this job will run at 9:08 AM (GMT time) in the morning every day.after job run if you verify the details then it would look as follows. So as you can see system will update postponeuntil (next run time) to next day (24th October) after this job run today (on 23th October).

****Important Notes regarding above change****
above change is unsupported so make sure you apply this change to Dev, Test environment before applying this to Live environment. Also make sure you take back up of database before doing this change.

Hope this helps..

Cheers,
MayankP:)

CRM 2011: Reassign or Transfer Personal Views

Recently come across scenario where one of CRM user left the company and that user owned specific views which need to be transfer to his replacement CRM users.

These views created by old user using CRM advanced find screen and hence they are personal view of that user and as per CRM security settings even system administrator can not able to view and transfer this views to new user.

So I decided use impersonation to resolve this issue and created small standalone application for the same.

This application is shared below.
CRM2011_ReAssignViews_EXE

Following is few more details regarding this application for the same.

Step 1: after downloading, CRM2011_ReAssignViews_EXE.zip and extract the files.

Step 2: after extracting run the CRM2011_ReAssignViews_EXE.exe from extracted files.

Step 3: provide CRM server name, relevant port number, organization name and provide relevant credential for the same and then click on “Retrieve List of User” button.

For further steps and information please refer to following screen print for the same.

Note: – Please note that both users needs to active in CRM to perform above actions.

Hope this helps..

Cheers,
MayankP:)

How to find records created on weekends or created on after working hours

Recently one of our customer asked how to find cases or emails created on Saturday/Sunday or cases created after normal working hours (i.e. after 18 PM?).

Of course this CRM system had email router which were creating those email and automatic case were created based on this.

I did try to so this using advanced find but it is not possible to get the desired result and try to do this using Excel features and it was really easy, following quick steps for the same using Excel.

Step 1: Export data to excel, so do advanced find on CRM (CRM 4.0 or CRM 2011) to retrieve relevant data and then export that to excel.

Step 2: once exported add new columns, let’s say those columns are called “DAY”, “Time HOUR” and “Time MIN”.

Step 3: put formula to retrieve values for those columns, as shown below. Please note that column G is created on date and based on this column G following formula are created. Please change column name as per your requirement.

COLUMN I (DAY) :

=IF(WEEKDAY(G2)=1,"SUNDAY",IF(WEEKDAY(G2)=2,"MONDAY",IF(WEEKDAY(G2)=3,"TUESDAY",IF(WEEKDAY(G2)=4,"WEDNESDAY",IF(WEEKDAY(G2)=5,"THURSDAY",IF(WEEKDAY(G2)=6,"FRIDAY",IF(WEEKDAY(G2)=7,"SATURDAY",)))))))

COLUMN J (Time Hour):
=HOUR(G2)
COLUMN K (Time Hour):
=MINUTE(G2)

Step 4: after apply this formula to all rows and you will get following result.

Step 5: you can apply standard excel filter to return only cases created on weekends (Saturday/Sunday) or cases created after 18 PM easily.

Hope this helps…

Cheers,
Mayank:)

CRM 2011 views: getting more than 5000 records

If you access any CRM views for example account page and if it got more the 5000 records you will see “5000+” as shown below.

You can update this limit by running following query again MSCRM_Config database, so IntColumn contains number of maximum records to be run for CRM. If you put value -1 it will retrieve all the records…


Update DeploymentProperties Set IntColumn=-1 Where ColumnName = 'TotalRecordCountLimit'

so IntColumn contains number of maximum records to be run for CRM. If you put value -1 it will retrieve all the records

****Important Notes regarding above change****:
1. Above change is unsupported so make sure you apply this change to Dev, Test environment before applying this to Live environment. Also make sure you take back up of database before doing this change
2. This change might impact performance as well since system will retrieve all the records.

There is another registry key called “TurnOffFetchThrottling” is also there which will allow you to retrieve more than 5000 records in CRM, refer following blog article for more information regarding the same.

http://www.interactivewebs.com/blog/index.php/server-tips/turn-off-microsoft-crm-2011-5000-limit-on-data-retrieval-via-sdk/

but above key is only when call is done in the external SDK (fetchXML) calls only, this will not change data retrieve to be display in Entity Grid, so for account grid will still display “5000+” after applied this registry key change and only above SQL script (i.e. unsupported) can change this behaviour.

Hope this helps..

Cheers,
MayankP:)

CRM 4.0 to CRM 2011 Queues Upgrades

There are lots of improvements and enhancement with regards queue in CRM 2011 and they are mentioned in this article

Now if you migrated your organization from CRM 4.0 system the since for each user CRM 4.0 has two queues as mentioned below
1. User’s Private Queue (i.e. Assign Queue)
2. User’s WIP Queue (i.e. In Progress Queue folder)

While upgrading User’s Private Queue is converted as default queue and renamed user’s full name so if user is name mark smith, in CRM 2011 this user will have only one queue named with full user name

All WIP queue is migrated as it is but on them worker Id set to owning user, so since these were the items in user’s In progress queue in CRM 4.0 they are migrated with working user set as owning user.

If you match your queue details with standard CRM 2011 organization (which was not upgrade from CRM 4.0) then you will know that they have got only one named queue and do not have this additional WIP queue.

If users want to match up this queue experience for CRM 2011 environment (migrated from CRM 4.0 system) to new install of CRM 2011 environment then following ate recommended steps for the sae which are mentioned in following article. this article provides the details but does not provide tool or script to do the job, this post will provide tool and script code to achieve this.

http://msdn.microsoft.com/en-us/library/gg327885.aspx

1. Move all items from WIP queue to user’s default queue and set worker id as well
2. Delete or Deactivate the WIP queue

Now each user can manually do above task but if you got large user base then I guess it is better if system admin can do above task for the same.

Following two ways to achieve this,

Option 1: using CRM API to update relevant queue records (Supported)

I have created application which will move all current WIP queue item to user’s named queue and then disables the WIP queue.

This application is shared below.
CRM2011_WIPQueues

Following is few more details regarding this application for the same.

Step 1: after downloading CRM2011_WIPQueues_Upgrades_EXE.zip,extract the zip file.

Step 2: after extracting run the CRM2011_WIPQueues_Upgrades.exe

Step 3: provide CRM server details and then click on “Move Items“ button. Following are example screen print for the same.

One advantage of this application is it is supported but disadvantage is that it could take more then 2-3 hours if your CRM system got really large records in WIP queues but since we have run this only once I prefer this approach.

However there is another faster (but unsupported) method is mentioned below to achieve the same result.

Option 2: using SQL script **UNSUPPORTED**

System administrator can build SQL script to update all relevant queue item records to change queue id from WIP queue to user’s named queue.

Following example screen print for the script for the same for one user, this script can be downloaded from here and change the USER DOMAIN NAME for the relevant user in the following script. Of course this needs to be run for all users in the system.

-- find out all items present in relevant user's WIP queue
-- update user's domain name in query mentioned below
select * from filteredqueueitem
inner join filteredqueue on
filteredqueueitem.queueid = filteredqueue.queueid
inner join filteredsystemuser on
filteredsystemuser.systemuserid = filteredqueue.ownerid
where queuetypecode = 3
and filteredsystemuser.domainname = 'USER DOMAIN NAME'

--Update items in WIP queue to named queue
-- update user's domain name in query mentioned below
Update queueitembase
set queueid = (select filteredqueue.queueid from filteredqueue
inner join filteredsystemuser on
filteredsystemuser.systemuserid = filteredqueue.ownerid
where queuetypecode = 2
and filteredsystemuser.domainname = 'USER DOMAIN NAME')
where queueid = (
select filteredqueue.queueid from filteredqueue
inner join filteredsystemuser on
filteredsystemuser.systemuserid = filteredqueue.ownerid
where queuetypecode = 3
and filteredsystemuser.domainname = 'USER DOMAIN NAME')

One advantage of this approach is that it will complete the moving with few minutes even if million records needs moving but big disadvantage is that we are making change to SQL directly which is considered as *UNSUPPORTED CHANGE * so if you are going for this route make sure you test this script fully on your development/UAT environment thoroughly before running them in to Live environment.

Hope this helps..

Cheers,
MayankP:)

CRM 2011 Chart: Drill Down enable/disable

Recently we noticed few charts in CRM 2011 displays drill down while other does not display drill down for the same.

For example standard Sales Pipe line chart report does not display drill down, after little investigation found that chard is displaying drill down if field used to display data set as Searchable to YES (as shown in the screen print below)

So your chart will display drill down if axis displays a field which got value searchable set to Yes.

Hope this helps..

Cheers,
MayankP:)

CRM 2011 Issues/Enhancements Feedback

Recently few of our customer and CRM user’s reports few issues/enhancement requested which I have posted them on Microsoft Connect Web site at https://connect.microsoft.com/dynamicssuggestions/Feedback

Following details regarding them and if you feel they need to be address please vote for them as Important on relevant feedback link provide below.

Feedback 1: CRM 2011 Outlook client Views are not sorted alphabetically

CRM 2011 Outlook client Views are not sorted alphabetically while IE views are sorted alphabetically. For example below screen print shows CRM 2011 IE Views sorted alphabetically working okay.

While CRM 2011 Outlook Views are seems to be sorted on created on date rather than alphabetically which is problematic if users got more than 10 views.

If you agree with me that outlook views should be sorted alphabetically then please Vote for this on the following page (Note: – you need to login with your Window Live/Hotmail account for the same)

https://connect.microsoft.com/dynamicssuggestions/feedback/details/742518/crm-2011-outlook-client-view-not-sorted-alphabetically

Feedback 2: Green Refresh button is missing on outlook client view.

If user want to refresh outlook client view then user have to View tab and then hit refresh button, it is just un-necessary one more click and also this behaviour is not same as IE version. In IE version we have got this green button in all views.

Of course we can do workaround to have Views -> Refresh button added in Quick Access toolbar as workaround but ideally it would be better if both integration works the same way.

For more information regarding this please refer following blog articles.
http://mscrmonline.wordpress.com/2011/07/24/how-to-refresh-a-microsoft-crm-2011-outlook-client-view/

http://www.dowawado.com/2012/02/08/crm-2011-how-to-refresh-a-view-in-the-outlook-client/

You can vote for this feedback here

Feedback 3: CRM 2011 View Selector not filtering custom views, only filters system views in Dashboard.

If user create new dashboard and adds list component and wants to view certain views only as shows in below screen print.

So we want system to display only selected one view in this example but when user checks out list it displays all the views as shown below so we want this feature to be corrected.

If you agree with me that above feature needs to be corrected then please Vote for this on the following page.

https://connect.microsoft.com/dynamicssuggestions/feedback/details/742536/crm-2011-view-selector-not-filtering-custom-views-only-filters-system-views-in-dashboard

Cheers,
MayankP