locked
Need help making a CRM Report RRS feed

  • Question

  • Hi guys,

    I'm new to CRM reporting, and when I tried to use the Report Designer to make it it did not function as expected.

    What I'm trying to do is make a report for "top 100 products sold" of a given year.  I want CUMULATIVE/TOTAL numbers, not just "the most sold at once"

    I set the entity as Sales Invoice Product, since that would only include items actually sold (my thinking is that Sales Order Product will include ones from cancelled or voided orders that were never invoiced).

    Anyway, I set the filters to have Date Created - On or After 1/1/2015 and On or Before 12/31/2015.  Those can be changed to different years before running the report.  I set Quantity to "sum" and had it sort descending.  But yet, I still end up with duplicates - it's looking at *each order* differently. So for example, we had two orders of the same part, 500 in one order and 400 in another order... rather than showing 900 on one line, there are two lines - 500 and 400 with an identical item number, price and so on.

    So I'm not quite sure what the "Sum" and "average" do (quantity and price respectively) if the report splits them up anyway when running it.

    What did I do wrong? I can give screenshots of my configurations in Report Designer if it would help.

    Friday, January 29, 2016 5:44 PM

All replies

  • Hello,

    Yes, please provide screenshots.


    Dynamics CRM MVP
    My blog

    Saturday, January 30, 2016 10:25 AM
    Moderator
  • See here:

    http://imgur.com/a/dnu9Y

    Sunday, January 31, 2016 2:13 AM
  • That's weird. Try to download report definition file, open it with some text editor and provide FetchXml query that is used for data fetching.

    Dynamics CRM MVP
    My blog

    Sunday, January 31, 2016 8:58 AM
    Moderator
  • OK, so here's what comes up when I open the file in Internet Explorer and search "fetch"

    <Value><fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"><entity name="invoicedetail"><all-attributes /><filter type="and"><condition attribute="createdon" operator="on-or-after" value="2015-01-01" /><condition attribute="createdon" operator="on-or-before" value="2015-12-31" /></filter></entity></fetch></Value>

    Later on,

    -<DataSets>


    -<DataSet Name="DSMain">


    -<Query>

    <CommandText><fetch distinct="false" no-lock="false" mapping="logical"><entity name="invoicedetail" enableprefiltering="1" prefilterparametername="CRM_FilteredInvoiceDetail"><attribute name="new_itemnumber" alias="new_itemnumber" /><attribute name="new_variantcode" alias="new_variantcode" /><attribute name="quantityshipped" alias="quantityshipped" /><attribute name="priceperunit" alias="priceperunit" /><attribute name="baseamount" alias="baseamount" /><attribute name="invoicedetailid" /><attribute name="productidname" /><attribute name="baseamount_base" /><attribute name="priceperunit_base" /><order attribute="quantityshipped" descending="true" /><link-entity name="transactioncurrency" to="transactioncurrencyid" from="transactioncurrencyid" link-type="outer" alias="LE_54d8dd5ab46c4955b7b66785b76023c8"><attribute name="currencysymbol" alias="LE_54d8dd5ab46c4955b7b66785b76023c8_currencysymbol" /><attribute name="currencyprecision" alias="LE_54d8dd5ab46c4955b7b66785b76023c8_currencyprecision" /></link-entity></entity></fetch></CommandText>

    <DataSourceName>CRM</DataSourceName>


    -<QueryParameters>


    -<QueryParameter Name="CRM_FilteredInvoiceDetail">

    <Value>=Parameters!CRM_FilteredInvoiceDetail.Value</Value>

    </QueryParameter>

    </QueryParameters>

    </Query>


    -<Fields>


    -<Field Name="new_itemnumber">

    <DataField>new_itemnumber</DataField>

    </Field>


    -<Field Name="new_variantcode">

    <DataField>new_variantcode</DataField>

    </Field>


    -<Field Name="quantityshipped">

    <DataField>quantityshipped</DataField>

    </Field>


    -<Field Name="quantityshippedValue">

    <DataField>quantityshippedValue</DataField>

    </Field>


    -<Field Name="priceperunit">

    <DataField>priceperunit</DataField>

    </Field>


    -<Field Name="priceperunitValue">

    <DataField>priceperunitValue</DataField>

    </Field>


    -<Field Name="baseamount">

    <DataField>baseamount</DataField>

    </Field>


    -<Field Name="baseamountValue">

    <DataField>baseamountValue</DataField>

    </Field>


    -<Field Name="invoicedetailid">

    <DataField>invoicedetailid</DataField>

    </Field>


    -<Field Name="productidname">

    <DataField>productidname</DataField>

    </Field>


    -<Field Name="baseamount_base">

    <DataField>baseamount_base</DataField>

    </Field>


    -<Field Name="baseamount_baseValue">

    <DataField>baseamount_baseValue</DataField>

    </Field>


    -<Field Name="priceperunit_base">

    <DataField>priceperunit_base</DataField>

    </Field>


    -<Field Name="priceperunit_baseValue">

    <DataField>priceperunit_baseValue</DataField>

    </Field>


    -<Field Name="LE_54d8dd5ab46c4955b7b66785b76023c8_currencysymbol">

    <DataField>LE_54d8dd5ab46c4955b7b66785b76023c8_currencysymbol</DataField>

    </Field>


    -<Field Name="LE_54d8dd5ab46c4955b7b66785b76023c8_currencyprecision">

    <DataField>LE_54d8dd5ab46c4955b7b66785b76023c8_currencyprecision</DataField>

    </Field>


    -<Field Name="LE_54d8dd5ab46c4955b7b66785b76023c8_currencyprecisionValue">

    <DataField>LE_54d8dd5ab46c4955b7b66785b76023c8_currencyprecisionValue</DataField>

    </Field>

    </Fields>

    </DataSet>

    </DataSets>

    Does that help?

    Monday, February 1, 2016 3:11 PM
  • Yes.

    You will have to fix your second FetchXml query to folllowing:

    <fetch aggregate="true" >
      <entity name="invoicedetail" enableprefiltering="1" prefilterparametername="CRM_FilteredInvoiceDetail" >
        <attribute name="new_itemnumber" alias="new_itemnumber" groupby="true" />
        <attribute name="new_variantcode" alias="new_variantcode" groupby="true" />
        <attribute name="quantityshipped" alias="quantityshipped" aggregate="sum" />
        <attribute name="priceperunit" alias="priceperunit" groupby="true" />
        <attribute name="baseamount" alias="baseamount" groupby="true" />
        <attribute name="productidname" alias="productidname" groupby="true" />
        <attribute name="baseamount_base" alias="baseamount_base" groupby="true" />
        <attribute name="priceperunit_base" alias="productidname" groupby="true" />
        <link-entity name="transactioncurrency" to="transactioncurrencyid" from="transactioncurrencyid" link-type="outer" alias="LE_54d8dd5ab46c4955b7b66785b76023c8" >
          <attribute name="currencysymbol" alias="LE_54d8dd5ab46c4955b7b66785b76023c8_currencysymbol" groupby="true" />
          <attribute name="currencyprecision" alias="LE_54d8dd5ab46c4955b7b66785b76023c8_currencyprecision" groupby="true" />
        </link-entity>
      </entity>
    </fetch>


    Dynamics CRM MVP
    My blog

    Monday, February 1, 2016 3:44 PM
    Moderator
  • So you're talking specifically about this line right here?

    <CommandText><fetch distinct="false" no-lock="false" mapping="logical"><entity name="invoicedetail" enableprefiltering="1" prefilterparametername="CRM_FilteredInvoiceDetail"><attribute name="new_itemnumber" alias="new_itemnumber" /><attribute name="new_variantcode" alias="new_variantcode" /><attribute name="quantityshipped" alias="quantityshipped" /><attribute name="priceperunit" alias="priceperunit" /><attribute name="baseamount" alias="baseamount" /><attribute name="invoicedetailid" /><attribute name="productidname" /><attribute name="baseamount_base" /><attribute name="priceperunit_base" /><order attribute="quantityshipped" descending="true" /><link-entity name="transactioncurrency" to="transactioncurrencyid" from="transactioncurrencyid" link-type="outer" alias="LE_54d8dd5ab46c4955b7b66785b76023c8"><attribute name="currencysymbol" alias="LE_54d8dd5ab46c4955b7b66785b76023c8_currencysymbol" /><attribute name="currencyprecision" alias="LE_54d8dd5ab46c4955b7b66785b76023c8_currencyprecision" /></link-entity></entity></fetch></CommandText>

    I change that to what you pasted?

    Monday, February 1, 2016 3:51 PM
  • Yes. But include CommandText as well as it was in original report.

    Dynamics CRM MVP
    My blog

    Monday, February 1, 2016 4:29 PM
    Moderator
  • Hey there,

    I figured out how to upload the .rdl file, but now I have another problem.

    I was able to upload it just fine (opposed to when I edited it incorrectly and it gave me an error about invalid FetchXML), but then when I try to RUN the report I get:

    If I click "try again",

    If I click "Try again" another time I get a 404 error.


    Tuesday, February 2, 2016 6:04 PM
  • Ok. Then you will have to install SQL Data Tools and FetchXml authoring extension to have possibility to edit report in normal way.

    Dynamics CRM MVP
    My blog

    Tuesday, February 2, 2016 6:24 PM
    Moderator
  • Are these backwards compatible? I went and installed Visual Studio 2015 so I could install SQL Data Tools, but we're using CRM 2011 - you linked me a thing for CRM 2015.

    If it works on older CRM versions though, I'll install it.

    Tuesday, February 2, 2016 10:31 PM