SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Interactive sort on matrix
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JAG7777777
Starting Member

United Kingdom
25 Posts

Posted - 12/05/2012 :  08:25:01  Show Profile  Reply with Quote

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

India
52309 Posts

Posted - 12/06/2012 :  01:56:27  Show Profile  Reply with Quote
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/

Go to Top of Page

JAG7777777
Starting Member

United Kingdom
25 Posts

Posted - 12/06/2012 :  04:04:17  Show Profile  Reply with Quote
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
Go to Top of Page

JAG7777777
Starting Member

United Kingdom
25 Posts

Posted - 12/06/2012 :  06:35:57  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/07/2012 :  08:28:43  Show Profile  Reply with Quote
whats problem with column grouping? did you try using PIVOT and doing crosstabbing?

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

Go to Top of Page

JAG7777777
Starting Member

United Kingdom
25 Posts

Posted - 12/07/2012 :  09:55:08  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000