Fetch-Xml based report (to count User Access) for Microsoft Dynamics CRM Online

(0) Share

Report

Posted on by Community Member

Hi,

I have created a User Access report for CRM on-premise using SQl query in the following format. One row corresponds to one user in organization. Currently, I am using Microsoft Dynamics CRM Online trial version and have two users in my organization.

6165.Capture2.JPG

I want to the same report for CRM Online environment. Only Fetch-Xml based custom reports are supported by CRM online environment hence this SQL query cannot be used.

I have already written fetch-xml query to retrieve user access records ("audit" entity records) in "last-x-months" (where x = 1,2,3,4,5,6) as below.

I am able to retrieve the records with "last-x-months" condition at a time, for example, the last-2-months in my fetch-xml query only.

For, example, when I retrieve the records in the last-2-months, it also includes the records in the last month. I want to count the records in the 2nd month only that is the difference between these two. The difference will be my 2nd column.

Similarly, I want the other columns.

For the case in the above example, I have created the two separate datasets to get the record-count for the last month and last-2-months. But, I am not able to use the two datasets in the same table in my report and hence not able to calculate the difference.

Is there any way to solve this problem?

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (1)

Bas van de Sande 4,383 on at

Like (0)

Report

Hi there,

can you post the SQL Query (you are running in the on prem version) and the FetchXML query you are running against the CRM online version?

You can use the Fetch XML testing tool which is part of the XRMToolBox. That way you can easily test your query just by adjusting it (xrmtoolbox.codeplex.com)

Bas

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks for your reply.

My current Fetch-Xml query works fine. I am using the following code to retrieve the records created in the last six months.

<filter type="and">

<condition attribute="createdon" value="1" operator="last-x-months" />

</filter>

But, I want the number of records created in each of the six months.

Was this reply helpful? Yes No
Bas van de Sande 4,383 on at

Like (0)

Report

you can achieve that if you want to. On msdn.microsoft.com/.../gg334216.aspx there is a good explanation of the use of count operator. You should do an aggregation.

Perhaps you need to add an additional column to your entity in which you put the month number

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I am able to retrieve the User Access, for example, in the last month only. I want to retrieve the count for last-2-months, last-3-months, last-4-months, last-5-months and last -6-months using fetch-xml.

When I query last-2-months records, it gives the count including the last month records. But, I need the difference actually.

Was this reply helpful? Yes No
Bas van de Sande 4,383 on at

Like (0)

Report

When you add an additional level of aggregation, e.g. month then you will get the count per month.

Can you post the fecth xml?

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

Hi,

I have modified my Fetch-XML query to retrieve all the required User Access records and removed aggregation and counting and grouping from the query as well. Did grouping and counting in SSRS. Now the report works fine as shown in the above picture.

Was this reply helpful? Yes No
suresh maurya 630 on at

Like (0)

Report

For testing of FetchXML, you can test here online http://msxrmtools.com

Was this reply helpful? Yes No
krishna 30 on at

Like (0)

Report

Hi Purva,

can you share the Expression used for getting month wise data

Regards,

Krish

Was this reply helpful? Yes No