locked
Corresponding ROW_NUMBER() in FetchXML RRS feed

  • Question

  • Hei,

    It seems like  have to create a FetchXML data source for my "Top-10-customer-with-Other" report. Earlier I created a new report datbase beside the CRM database (Organisation XXX below), and created a VIEW based on the following SELECT with the ROW_NUMBER() function to indentify each row sequencly:

    CREATE VIEW v_invoice_data AS
    SELECT ROW_NUMBER() OVER(ORDER BY sum(TotalAmount) DESC) AS RowNo, a.Name as CustomerName, year(b.createdon) as InvoiceYear, sum(TotalAmount) as TotalAmount FROM XXX_MSCRM.[dbo].[Account] a INNER JOIN XXX_MSCRM.[dbo].Invoice b on b.customerId = a.AccountId group by a.Name, year(b.createdon)
    SELECT CustomerName, SUM(TotalAmount) as TotalAmount
    FROM XXX_Reports.[dbo].[v_invoice_data]
    where RowNo <= 10
    group by CustomerName
    UNION ALL
    SELECT 'ANNET', SUM(TotalAmount)
    FROM XXX_Reports.[dbo].[v_invoice_data]
    where RowNo > 10
    The about SQL is the final data source for the report where customers outside Top-10 is put in the seconds SELECT where RowNo is greater than 10.

    I need the get the same ROW_NUMBER function i FetchXML, to be able to group all customers from row greater than 10 on the "Other" category. Current FetchXML (uncomplete - missing rownumber). Could someone help me with this row_number() function. Is there a similar construction in FetchXML?

    <fetch distinct="false" mapping="logical" aggregate="true">
      <entity name="invoice">
    	<attribute name='totalamount' alias='totalamount_sum' aggregate='sum' /> 
    	<attribute name='customerid' alias='customerid' groupby='true' /> 
     	<order alias='totalamount_sum' descending='true' />
      </entity>
    </fetch>

    This is what the report should look like (RDL), that shall be added as report in CRM:

    kunder = customer

    år = year

    ANNET (all customer outside Top 10)


    Roar Jørstad aka sveroa
    Senior Consultant, EVRY as
    Blog: Notebook, trick & tips
    Please mark as answer or helpful if my post is useful



    Wednesday, October 3, 2012 9:58 AM

Answers

All replies

  • I guess you need to use count attribute to return only 10 records...as shown below..

    To retrieve top 10 records from CRM 2011 using fetch xml,add count="10" into the first fetch tag.
    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" count="10">
      <entity name="account">
        <attribute name="name" />
        <attribute name="primarycontactid" />
        <attribute name="telephone1" />
        <attribute name="accountid" />
        <order attribute="name" descending="false" />
      </entity>
    </fetch>

    MayankP
    My Blog
    Follow Me on Twitter

    Wednesday, October 3, 2012 10:18 AM
    Answerer
  • What about the the customers/invoices outside Top-10? That is the whole point with this custom report. Showing all customer outside "Top-10" as "OTHER" (ANNET in norwegian) in the piechart. If you see my second SQL statement I'm having the "where RowNo <= 10" and "where RowNo > 10" to be able to differenciate between Top-10 customers and the rest of the customer.

    Am I able to use 2 FetchXML statements as data source for a report? Equally to my "UNION ALL" statement about


    Roar Jørstad aka sveroa
    Senior Consultant, EVRY as
    Blog: Notebook, trick & tips
    Please mark as answer or helpful if my post is useful

    Wednesday, October 3, 2012 10:26 AM
  • you can use " Not  In " operator to run query against all accounts that are not there in top 10 but you will have issue doing UNION in fetch xml as UNION are not supported, see following thread..

    http://social.microsoft.com/Forums/sv/crm/thread/21aa6dff-8fd7-4c0a-ac69-d6c6a032fa6ahttp://social.microsoft.com/Forums/en/crm/thread/ed0a9d0d-8ed3-42e1-9e8f-202017794694

    for these kind of reports, it is advisable to use direcly SQL query using filteredview to get CRM data and display in report.


    MayankP
    My Blog
    Follow Me on Twitter

    • Marked as answer by Roar Jørstad Wednesday, October 3, 2012 2:38 PM
    Wednesday, October 3, 2012 1:38 PM
    Answerer