BIDS Report: Can I combine column groups in a Matrix Report? RRS feed

  • Question

  • I am sure this is a common issue.

    I have a DataSet which includes 3 lookup fields: Topic1, Topic2 and Topic3.  They all point to the same entity: Topic.

    I have built a Matrix report based on Topic1 and this works fine.  Now I am scratching my head trying to work out if I can incorporate the counts from Topic2 and Topic3.

    To clarify:

    • My rows are Customer;
    • My columns are a Matrix which show the Topic in the column header and a count for each customer in the cells.

    Is it possible to combine Topic1, Topic2 and Topic3 counts under each Topic header?

    Thursday, December 3, 2015 10:50 AM


  • I could not find a way to do this so abandoned this.
    Thursday, January 28, 2016 3:40 PM

All replies

  • To further clarify - is it that you want the totals for each topic to be a sum of the records that are linked to that topic in any of the fields Topic1, Topic2 or Topic3 ?

    If that's the case, then you can do this if using a SQL query, like the following. I can't immediately think of a way to this via FetchXml though

    select customer, topic1 as topic
    select customer, topic2 as topic
    select customer, topic3 as topic
    In the above query, if one customer were linked to the same topic in more than one field, they would be counted more than once. If you change the 'union' clause to 'union all' they'd only get counted once

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Thursday, December 3, 2015 12:17 PM
  • Alas I am within the confines of CRM Online so I cannot use SQL.

    The original On Premise SQL Report that I am attempting to replicate in FetchXML did, as you suggest, use unions to stack the totals.

    Sorry, I should have said that.

    Thursday, December 3, 2015 12:25 PM
  • I could not find a way to do this so abandoned this.
    Thursday, January 28, 2016 3:40 PM