locked
How to reference cells in dynamically created Matrix? RRS feed

  • Question

  • I've been mulling this problem over most of the past two days and I'm stuck.

    Consider a Matrix that displays performance stats for past quarterly periods.

    --------------------------------------

    Location        | [QuarterlyPeriod  |

    --------------------------------------

    [Location]     | [Performance]      |

    --------------------------------------

    Grouping happens on column [QuarterlyPeriod] and row [Location]. Performance is a short string code e.g. 'SAT', 'UD' or can be blank.

    I want to add another column after [QuarterlyPeriod] that will calculate for the Quarters involved, using the codes in the cells. So if for example, if the report is run for 5 quarters, the rows might appear as <blank>, SAT, SAT, <blank>, UD. How do I references those cells to get that data?

    The report can potentially be run up to 12 quarters back so I cannot guarantee how many columns will be present to scan through.

    Thanks in advance.

    Tuesday, December 5, 2017 4:25 PM

All replies

  • Hi Crow555,

    As I know, when you display records in Matrix, the column will expand according to column record like below

    dataset structure:

    matrix structure

    Whether you want to get actual records of quarter for location instead of expand? If so, you could use expression in cells(right click the [performance] to insert a column->outside group)

    = "actual count amount in dataset:" & Count(Fields!quarter.Value,"location")  &"      " &join(Lookupset(Fields!location.Value,Fields!location.Value,Fields!quarter.Value, "DataSet1"),",")

    By the way, I don't know which calculate function you want to use and what is your expecting output. If possible, could you please inform me in details? Then I will help you correctly.

    Thanks for your understanding and support.
    Best Regards,
    Zoe Zhi

    MSDN Community Support
    Please remember to click "Mark as Answer" 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. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 6, 2017 2:12 AM
  • Hi Crow555,

    May I know how is your issue going currently? If your issue is solved please mark the appropriate answer as Mark as answer.  This will help other members to find a solution if they face the same issue. If you still have any questions, please feel free to ask.

    Thanks for your understanding and support.
    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" 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. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 11, 2017 7:22 AM