Breaking news from around the world
Get the Bing + MSN extension
The views and opinions expressed in this blog are those solely of the author(s) and do not necessarily reflect Microsoft’s current policy, position, or branding. For official announcements and guidance on Dynamics 365 apps and services, please visit the Microsoft Dynamics 365 Blog.
Choose your path Increase your proficiency with the Dynamics 365 applications that you already use and learn more about the apps that interest you. Up your game with a learning path tailored to today's Dynamics 365 masterminds and designed to prepare you for industry-recognized Microsoft certifications.
Visit Microsoft Learn
2019 release wave 2 Discover the latest updates and new features to Dynamics 365 planned through March 2020
Release overview guides and videos Release Plan | View virtual launch event
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Talent TechTalks | Upcoming TechTalks
I was working with a client the other day that requested help with an SSRS report. You see, this client wanted to create Microsoft Dynamics CRM Record Count Reports. This is accomplished in a Dynamics CRM on-premise environment by creating a SSRS report using an aggregate query to pull data.
This will yield the user and the record counts in the report Dataset as needed. This is nice, but it only works for on-premise. On-premise reports are easy. You just write a query to pull the data you need, format the report and there you have it.
However, this question was regarding a Microsoft Dynamics CRM online instance. We must use FetchXML to pull our data. The ChartGuy has a nifty solution to create a chart of multiple entities with FetchXML. The article shows you the tricks to create a chart of entities by owner. The only issue is the limited number of records returned from the FetchXML. FetchXML queries have a limitation of 50,000 records. On-premise SQL queries do not have this limitation.
I’ve run across a few instances with more than 50,000 combined records of Accounts, Contacts, Opportunities and Leads. I know of two Microsoft Dynamics CRM instances with Leads of well over 100,000 and over 500,000 Contact (we’ll not talk duplicates in this article). The ChartGuy’s solution will not work in these instances.
But, we’re trying to find the number of records owned by users. Let’s hope the users/owners do not have 50,000 records. How would they manage all those contacts and leads anyway?
Let’s start by creating a report to count the Account, Contact, Lead and Opportunitiy entities by owner. For this will create a standard SSRS project -- a new report. We’ll want to display this report based on the user record.
Here is the fetch for our User DataSet:
<fetch mapping="logical" aggregate="true" >
<entity name="systemuser" >
<attribute name="systemuserid" groupby="true" alias="User" />
<attribute name="fullname" />
This is our primary search. This will pull all the users in an instance. We can layout the report as needed. But we’re going to add a sub-report to the row. The sub-report will display the records per user. The sub-report will accept a parameter from the parent report of @ownerid. The sub-report will display aggregate values for each entity specified.
This is a sample layout. But all you need is a cell/text box to display the values. Above I have a column for Accounts, Contacts, Leads, Cases, Opportunities and a final column with the total of the row. The “Header” row and the fullname column are hidden during rendering. Below is the fetch used to pull the count of accounts per user with the parameter as the filter.
<entity name="account" >
<attribute name="accountid" alias="count_accountid" aggregate="countcolumn" />
<filter type="and" >
<condition attribute="ownerid" operator="eq" value="@ownerId" />
Each entity above is a separate DataSet in the sub-report. The expression in the box is:
The row total is the addition of the cell values.
The parent report displays the User’s name and a merged cell holding the sub-report. The column headings are on the parent report.
To get the headings to line up, ensure your cells are the same size by looking at their properties.
The resulting report would look something like this:
Again, this is just a quick report to show user record counts. Export the report into a spreadsheet to sort and manipulate the data as you see fit.
If you have questions regarding this report or other SSRS reports for your instance, give us a shout.
By Sanford Mosby, Microsoft Dynamics CRM Technical Consultant with xRM³, a Microsoft Dynamics CRM Partner located in San Diego, California Southern California.
The post Microsoft Dynamics CRM Record Count Reports appeared first on CRM Software Blog.
Business Applications communities