Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Grouping results by matrix dataset Expand / Collapse
Author
Message
Posted Thursday, June 6, 2013 2:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 1:46 AM
Points: 2, Visits: 95
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
Post #1460571
Posted Friday, June 7, 2013 1:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 1:46 AM
Points: 2, Visits: 95
SOLVED :D

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:

=JOIN(LOOKUPSET(Fields!Accnt.Value,Fields!Accnt.Value,Fields!matrixCol.Value,"Main"),", ")

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.

Post #1460984
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse