none
Union all alternative in fetch xml for getting data from dynamics crm v9.0 RRS feed

  • Question

  • Hi Guys,

    Hope you're all are doing great. I am trying to convert a union all sql query to fetchxml (as crm v9.0 doesn't allow you to upload sql query based reports).

     

    Select total_income, name as "Monthly Expenses" from income

    Union All 

    Select commitment_income, name as  "Commitment Expenses" from income

    Union All 

    Select disposible_income, name as  "Disposable Expenses" from income

    Union All 

    Select return_income,  name as  "Return Expense" from income

     

    The thing is that this query 's data is used in a stacked bar chart. What I've managed to do created three separate data-sets from the queries but from there I don’t know how to merge three data-sets into a single data-set for the chart category groups ,series groups and values sum .

     

    Any ideas? Thanks.


    • Edited by Cornelius11 Wednesday, August 21, 2019 8:46 PM more elobaration
    Wednesday, August 21, 2019 8:31 PM

All replies

  • Hi Cornelius11

    Still get little confused about your issue .

    Seems that you could not use the sql in the fetchxml type ,right ?

    Seems you got the 3 datasets , which have the common column name , does the name refer to the one column ?

    If so, you could try to use the lookup function to get the correspond value in dataset2 from dataset1 .(during the processing, there are some details about the relationships like 1 to n ,or 1 to 1 , n to n , you should consider about this ,otherwise you could not get the correct mapping value )

    See: Lookup Function    LookupSet Function

    Does the 3 datasets are all from one data source ? which I mean does they come from the same file or dataset in the crm?

    If so , why not get the all the columns in one fetchxml query ?

    If the data is not change often , you could try to export it from the original source via different types .(original export way from crm ,ssis, powerbi ,….)

    Then use the new data to create the chart .

    You could offer more detailed information about your issue , like the original data sample , the fetch xml query , the datasource ….etc . to us for more further research .

    Hoping for your reply.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.





    • Edited by Eric Liu001 Thursday, August 22, 2019 3:05 AM
    • Proposed as answer by Eric Liu001 Friday, August 23, 2019 8:59 AM
    Thursday, August 22, 2019 3:03 AM