125
Hello,
I want to evaluate average number of products being quoted in our organization in a fiscal period. I assume that it will have to be a fetchxml query or ssrs report but just not sure where to start. I will appreciate any tip/guidance in this direction.
Best,
G
*This post is locked for comments
I have the same question (0)
Hello,
This FetchXml would give you an overview of all 'extendedamount' ((Unit Price * Quantity) +Tax) of all product lines in the current fiscal year:
Share
Report
All responses (
Answers (
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="quotedetail">
<attribute name="extendedamount" />
<filter type="and">
<condition attribute="modifiedon" operator="this-fiscal-year" />
</filter>
</entity>
</fetch>
You could already include this in an SSRS report and average the numbers yourself.
In FetchXml you can also directly do aggregates, like AVG and SUM, which you can do in SQL.
This averages all values of 'extendedamount' ((Unit Price * Quantity) +Tax) for the current fiscal year
<fetch aggregate="true" >
<entity name="quotedetail" >
<attribute name="extendedamount" alias="avg_extendedamount" aggregate="avg" />
<filter type="and" >
<condition attribute="modifiedon" operator="this-fiscal-year" />
</filter>
</entity>
</fetch>
Please take a look at this article on how to use FetchXml to build an SSRS report:
https://docs.microsoft.com/en-us/dynamics365/customer-engagement/analytics/create-a-new-report-using-sql-server-data-tools
My examples all use the current fiscal year as a filter, you can also remove the filter from the FetchXml and enable it for prefiltering. This allows users in Dynamics to select the period themselves:
https://docs.microsoft.com/en-us/dynamics365/customer-engagement/analytics/improve-report-performance-by-using-filters
<fetch aggregate="true" >
<entity name="quotedetail" enableprefiltering="1" prefilterparametername="CRM_FilteredQuoteDetail">
<attribute name="extendedamount" alias="avg_extendedamount" aggregate="avg" />
</entity>
</fetch>
