Interactive sort on matrix - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Interactive sort on matrix

Author

Topic

JAG7777777
Starting Member

25 Posts

Posted - 2012-12-05 : 08:25:01

Hi All,

I've read that this is not possible - but, am hoping there are others like me that ignore stuff like that and still try and find a way anyway!

I have a matrix report that is grouped both on the rows and columns:

Item |Description|Cust1Value|Cust1Units|Cust2Value|Cust2Units
10000001|Item 1 |100 |50 |150 |75
10000002|Item 2 |80 |40 |120 |90
10000003|Item 3 |90 |30 |200 |110

Item |Description|Cust1Value|Cust1Units|Cust2Value|Cust2Units
10000001|Item 1 |100 |50 |150 |75
10000003|Item 3 |90 |30 |200 |110
10000002|Item 2 |80 |40 |120 |90

Using the above as the example, the row group is Item and the column group is Customer (with a sum of value and unit for each customer). The columns 'Description' and 'Item' are not in the column group.

There's an interactive sort on the Description and Item text box that uses the Item group to sort by the Description or Item field - this works fine for these columns - however, the data in the column groups remains unchanged.

If I try and add an interactive sort to the columns in the column group (e.g. the Value text box) to sort the Item group I get nothing at all? No change whatsover to the rows.

I basically want to be able to sort the rows by any of the columns in the column groups - so in the example above - I have sorted on the Value column of the first customer on the lower table to re-arrange the Item group rows from that displayed in the upper table

Has anyone found a way of doing this?

Thanks,

JAG7777777

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-06 : 01:56:27

Alternate solution would be to use table container instead of matrix and do crosstabbing in t-sql. then you would be able to apply interactive sort over column group values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JAG7777777
Starting Member

25 Posts

Posted - 2012-12-06 : 04:04:17

Thanks visakh16,

I'm going to give the RunningTotal logic a go (based on what I found out worked on the alternate row colour expression problem I had) to see if this can be used somehow.....

Will let you know how I get on.

Thanks again,

JAG7777777

JAG7777777
Starting Member

25 Posts

Posted - 2012-12-06 : 06:35:57

Sadly, interactive sorts don't allow the use of RunningValue, otherwise something like:

RunningValue(Fields!ToBeSummedField.Value, Sum, "GroupName") would hopefully have worked :-(

I tried changing the report to a tablix before - but the issue is that we need grouping at both a row and column level. I even tried using a Group By SQL query to remove the row group issue....but it is the column grouping that is causing the problem.

Every where I read suggests it can't be done - but I'm sure there must be a way.......

JAG7777777

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-07 : 08:28:43

whats problem with column grouping? did you try using PIVOT and doing crosstabbing?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JAG7777777
Starting Member

25 Posts

Posted - 2012-12-07 : 09:55:08

Sorry - not sure what you mean...?

Its a matrix style tablix report in SSRS 2008. It has a column group, with three columns in the group summing various data by each column group. It also has a row group. So, in Crystal language, I guess you could call it a crosstab report and Excel you could call it a pivot if that is what you mean..?

SSRS is throwing an error if I try and use a SUM by a column group in a row group. I.e. if I set the sort to 'Sort on Row group' and then, in the expression, tell it something like SUM(Fields!Amount.value, "ColumnGroup") it states that the expression is essentially not within scope?

JAG7777777

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources