Grouping results by matrix dataset

  • Hi,

    I am struggling on a requirement that involves grouping matrix rows with the same resultant dataset.

    For example:

    Accnt MatrixColA MatrixColB MatrixColC

    1 0 1 0

    2 0 1 0

    3 2 1 0

    4 0 0 1

    Is there a way to group accnt 1 and 2 as they share the same values for matrixColA, matrixColB, and matrixColC? This would mean that for those values {0,1,0}, there is a count of two. For the other values {2,1,0} and {0,0,1}, there is a count of one respectively.

    As it is a matrix there could be any number of matrix columns and I'd need the solution to work.

    The underlying dataset for the above would look like:

    Accnt MatrixCol

    1 B

    2 B

    3 A

    3 A

    3 B

    4 C

    Can anybody see a solution to this? I have tried in the SQL to group the records by making use of "STUFF" to create a new column such as A,A,B (for accnt 3) but this takes too long when I have a large dataset.

    Thanks in anticipation,

    Stu

  • SOLVED 😀

    I solved this by adding an additional column to my report called, for example, "relationship set".

    The value of relationship set is determined by an expression:

    [font="Courier New"]=JOIN(LOOKUPSET(Fields!Accnt.Value,Fields!Accnt.Value,Fields!matrixCol.Value,"Main"),", ")[/font]

    Where "Main" is the dataset that you are using (I am only using one dataset so looking up itself).

    The performance seems to be great too even with large datasets.

    The results for this example would be:

    Accnt Relationship Set

    1 B

    2 B

    3 A, A, B

    4 C

    The relationship set can then be used to group common values within the matrix.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply