interactivity in RS

Hi there!
I have a question about the interactive behavior in the reports.
Is it possible to add interactive sorting capability for the report.
By this I mean that a user can click on the column header of the
report and the report will be sorted in the appropriate way.
Another question is about drilldown reports.
Lets consider the situation when, we a have a group
and no data in that group. The group column ( e.g. Employee
name ) was selected as the field on which some other columns depend.
We have a drilldown and a "+" sign. So the question comes if there is no
data in the group, how we can disable "+" sign or not show it?
TIA
--
Vadym StetsyakVadym Stetsyak wrote:
> Hi there!
> I have a question about the interactive behavior in the reports.
> Is it possible to add interactive sorting capability for the report.
> By this I mean that a user can click on the column header of the
> report and the report will be sorted in the appropriate way.
> Another question is about drilldown reports.
> Lets consider the situation when, we a have a group
> and no data in that group. The group column ( e.g. Employee
> name ) was selected as the field on which some other columns depend.
> We have a drilldown and a "+" sign. So the question comes if there is
> no data in the group, how we can disable "+" sign or not show it?
> TIA
> --
> Vadym Stetsyak
There is a slightly grubby hack that will allow you to (sort of) sort
on the column headers. I want to add this to the sorting/grouping
article at
http://highlyobscure.net/howto/sorttablewithparameters/sorttable.htm
but haven't had time. Basically you add a rectangle to the column
header with 2 more textboxes in it, to link to ascending and descending
sorted reports. Note that in SP1 you can't put little images of arrows
in the textboxes because RS errors out if you do; you have to use text.
If you're familiar with parameter-based sorting (and it sounds like you
are) you can skip the article above and go straight to
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=6
a347b53-5594-40f9-861d-876beabeda16 , a great sample.
As to the second question, I dunno. Let us know if you figure it out
8-).
hth
Highly Obscure

Interactive Sorting/Execution of query

Does clicking interactive sort button in a column reporting services 2005 result re-execution of the query.

Or will it just re-print the rendered data in the layout and so perform better in comparison to the implementation which can be done using drill down to same report with the help of some extra parameters

Priyank

If the user session hasn't expired, interactive sorting doesn't result in re-executing of the query. The server simply re-uses the cached report.

|||I'd definately prefer the cache over generating another report. But if you feel so inclined, try both and time them to see which one is faster.

|||

Thanks, I tried this, rendered data got re-printed without execution of query.

Interactive Sorting -prevent group from collapsing

is there a way to configure interactive sorting so that a group that has
been expanded does not get hidden again when the sort arrow in a column
heading is clicked?
TIA
DeanHi Dean,
Have you got any resolution? If yes please share...I am also facing same
problem.
Thanks
-Shailesh
"Dean" wrote:
> is there a way to configure interactive sorting so that a group that has
> been expanded does not get hidden again when the sort arrow in a column
> heading is clicked?
> TIA
> Dean
>
>|||I'm looking for the answer to the same question.
--
FionaDM
"Shailesh K" wrote:
> Hi Dean,
> Have you got any resolution? If yes please share...I am also facing same
> problem.
> Thanks
> -Shailesh
> "Dean" wrote:
> > is there a way to configure interactive sorting so that a group that has
> > been expanded does not get hidden again when the sort arrow in a column
> > heading is clicked?
> >
> > TIA
> > Dean
> >
> >
> >|||I guess this is a flaw since there has been no MSFT replies since the post
started. MSFT, any solution or workaround to this?

Interactive sorting of a list

I have a list data region that has column headings outside the list. I would like to be able to click a column heading and sort the list. However I cannot do this because the scope of the header can't access the list. Has anyone sorted a list by clicking textbox column headings? Please help.

Hi Patrick-

In general, when applying a user sort, you can sort items that are at the same scope, or are in a child scope. You might try setting the textbox to sort on the data set wihch the list is using, and then set the sort expression scope to details. If you are using RS 2005, I've included a sample RDL below. Just copy the text to a file, rename the extension to .rdl and open it in report designer.

-Jon

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<DataSources>

<DataSource Name="DataSource1">

<ConnectionProperties>

<IntegratedSecurity>true</IntegratedSecurity>

<ConnectString />

<DataProvider>XML</DataProvider>

</ConnectionProperties>

<rd:DataSourceID>8d7185f8-cc23-497e-a4a0-cb6c5ec79844</rd:DataSourceID>

</DataSource>

</DataSources>

<BottomMargin>1in</BottomMargin>

<RightMargin>1in</RightMargin>

<rd:DrawGrid>true</rd:DrawGrid>

<InteractiveWidth>8.5in</InteractiveWidth>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<ReportItems>

<Textbox Name="textbox1">

<Left>1.25in</Left>

<Top>0.5in</Top>

<rd:DefaultName>textbox1</rd:DefaultName>

<ZIndex>1</ZIndex>

<Width>1in</Width>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Height>0.25in</Height>

<UserSort>

<SortTarget>DataSet1</SortTarget>

<SortExpression>=Count(Fields!Col.Value, "DataSet1")</SortExpression>

</UserSort>

<Value>SORT</Value>

</Textbox>

<List Name="list1">

<Left>1in</Left>

<ReportItems>

<Textbox Name="Col">

<Left>0.625in</Left>

<Top>0.375in</Top>

<rd:DefaultName>Col</rd:DefaultName>

<Width>1in</Width>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Height>0.25in</Height>

<Value>=Fields!Col.Value</Value>

</Textbox>

</ReportItems>

<DataSetName>DataSet1</DataSetName>

<Top>1in</Top>

<Width>2in</Width>

<Height>1in</Height>

</List>

</ReportItems>

<Height>2.75in</Height>

</Body>

<rd:ReportID>50318574-728b-4b7c-a2f9-82ab09948b39</rd:ReportID>

<LeftMargin>1in</LeftMargin>

<DataSets>

<DataSet Name="DataSet1">

<Query>

<CommandText>&lt;Query&gt;&lt;XmlData&gt;&lt;Root&gt;&lt;Col&gt;1&lt;/Col&gt;&lt;Col&gt;2&lt;/Col&gt;&lt;Col&gt;3&lt;/Col&gt;&lt;/Root&gt;&lt;/XmlData&gt;&lt;/Query&gt;</CommandText>

<DataSourceName>DataSource1</DataSourceName>

</Query>

<Fields>

<Field Name="Col">

<rd:TypeName>System.String</rd:TypeName>

<DataField>Col</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<Width>6.5in</Width>

<InteractiveHeight>11in</InteractiveHeight>

<Language>en-US</Language>

<TopMargin>1in</TopMargin>

</Report>

interactive sorting in sql reporting 2000

hi all

how can i get interactive sorting in sql reporting 2000 that is available in sql reporting 2005

plz suggest me the solution for same.

Most people approximated this behavior by using a series of parameters which allowed a user to select which columns to sort on. Then, you'd use the parameter values and inject their values into a custom built expression and/or expressions behind a data region. For example:

= "SELECT MyField, MyField1, MyField2 FROM MyTable ORDER BY " & Parameters!SortParameter.Value

...which would resolve to Select....From MyTable ORDER BY SomeColumn.

The example above is very simple...you'd have to make it fancier and use IIF statemetns to check for empty values, etc. etc..

Interactive sorting in matrix

How could I accomplish interactive sorting inside the matrix control? I have matrix like this:

=Fields!Time_Calculations.Value (Current, Prior Year)
Forecast Sale
=Fields!Month xxxxx xxx

And I need to sort by Forecast and Sale.

Thnaks

The matrix region is somewhat special when it comes to sorting due to the way it aggregates data. In short, you need to use the corner textbox to set up your user sort, as this report definition demonstrates.

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<DataSources>

<DataSource Name="northwind">

<ConnectionProperties>

<IntegratedSecurity>true</IntegratedSecurity>

<ConnectString>Data Source=localhost;Initial Catalog=Northwind</ConnectString>

<DataProvider>SQL</DataProvider>

</ConnectionProperties>

<rd:DataSourceID>66a72cd8-749c-4971-b5d6-05b2612a4d40</rd:DataSourceID>

</DataSource>

</DataSources>

<BottomMargin>1in</BottomMargin>

<RightMargin>1in</RightMargin>

<ReportParameters>

<ReportParameter Name="RowGroup">

<DataType>String</DataType>

<DefaultValue>

<Values>

<Value>ProductName</Value>

</Values>

</DefaultValue>

<Prompt>RowGroup</Prompt>

<ValidValues>

<ParameterValues>

<ParameterValue>

<Value>ProductName</Value>

<Label>By Product Name</Label>

</ParameterValue>

<ParameterValue>

<Value>SupplierID</Value>

<Label>By Supplier ID</Label>

</ParameterValue>

<ParameterValue>

<Value>CategoryID</Value>

<Label>By Category ID</Label>

</ParameterValue>

</ParameterValues>

</ValidValues>

</ReportParameter>

<ReportParameter Name="ColumnGroup">

<DataType>String</DataType>

<DefaultValue>

<Values>

<Value>ReorderLevel</Value>

</Values>

</DefaultValue>

<Prompt>ColumnGroup</Prompt>

<ValidValues>

<ParameterValues>

<ParameterValue>

<Value>ReorderLevel</Value>

<Label>By Reorder Level</Label>

</ParameterValue>

<ParameterValue>

<Value>UnitsInStock</Value>

<Label>By Stock</Label>

</ParameterValue>

<ParameterValue>

<Value>SupplierID</Value>

<Label>By Supplier ID</Label>

</ParameterValue>

</ParameterValues>

</ValidValues>

</ReportParameter>

</ReportParameters>

<rd:DrawGrid>true</rd:DrawGrid>

<InteractiveWidth>8.5in</InteractiveWidth>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<ReportItems>

<Textbox Name="textbox3">

<Left>0.125in</Left>

<Top>0.375in</Top>

<ZIndex>2</ZIndex>

<Width>3in</Width>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Height>0.25in</Height>

<Value>="Matrix columns " &amp; Parameters!ColumnGroup.Label</Value>

</Textbox>

<Textbox Name="textbox1">

<Left>0.125in</Left>

<Top>0.125in</Top>

<rd:DefaultName>textbox1</rd:DefaultName>

<ZIndex>1</ZIndex>

<Width>3in</Width>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Height>0.25in</Height>

<Value>="Matrix rows " &amp; Parameters!RowGroup.Label</Value>

</Textbox>

<Matrix Name="matrix1">

<MatrixColumns>

<MatrixColumn>

<Width>1in</Width>

</MatrixColumn>

</MatrixColumns>

<Left>0.125in</Left>

<RowGroupings>

<RowGrouping>

<Width>2.125in</Width>

<DynamicRows>

<ReportItems>

<Textbox Name="CategoryID">

<rd:DefaultName>CategoryID</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields(Parameters!RowGroup.Value).Value</Value>

</Textbox>

</ReportItems>

<Grouping Name="matrix1_RowGroup">

<GroupExpressions>

<GroupExpression>=Fields(Parameters!RowGroup.Value).Value</GroupExpression>

</GroupExpressions>

</Grouping>

</DynamicRows>

</RowGrouping>

</RowGroupings>

<ColumnGroupings>

<ColumnGrouping>

<DynamicColumns>

<ReportItems>

<Textbox Name="ReorderLevel">

<rd:DefaultName>ReorderLevel</rd:DefaultName>

<ZIndex>2</ZIndex>

<Style>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields(Parameters!ColumnGroup.Value).Value</Value>

</Textbox>

</ReportItems>

<Sorting>

<SortBy>

<SortExpression>=Fields(Parameters!ColumnGroup.Value).Value</SortExpression>

<Direction>Ascending</Direction>

</SortBy>

</Sorting>

<Grouping Name="matrix1_ColumnGroup">

<GroupExpressions>

<GroupExpression>=Fields(Parameters!ColumnGroup.Value).Value</GroupExpression>

</GroupExpressions>

</Grouping>

</DynamicColumns>

<Height>0.25in</Height>

</ColumnGrouping>

</ColumnGroupings>

<DataSetName>DataSet1</DataSetName>

<Top>0.875in</Top>

<Width>3.125in</Width>

<Corner>

<ReportItems>

<Textbox Name="textbox4">

<rd:DefaultName>textbox4</rd:DefaultName>

<ZIndex>3</ZIndex>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<UserSort>

<SortExpression>=Fields(Parameters!RowGroup.Value).Value</SortExpression>

<SortExpressionScope>matrix1_RowGroup</SortExpressionScope>

</UserSort>

<Value>Sort rows</Value>

</Textbox>

</ReportItems>

</Corner>

<Height>0.5in</Height>

<MatrixRows>

<MatrixRow>

<Height>0.25in</Height>

<MatrixCells>

<MatrixCell>

<ReportItems>

<Textbox Name="ProductID">

<rd:DefaultName>ProductID</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Count(Fields!ProductID.Value)</Value>

</Textbox>

</ReportItems>

</MatrixCell>

</MatrixCells>

</MatrixRow>

</MatrixRows>

</Matrix>

</ReportItems>

<Height>2in</Height>

</Body>

<rd:ReportID>4614d21e-03f0-4b4b-8270-a40c31094d26</rd:ReportID>

<LeftMargin>1in</LeftMargin>

<DataSets>

<DataSet Name="DataSet1">

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>select * from products</CommandText>

<DataSourceName>northwind</DataSourceName>

</Query>

<Fields>

<Field Name="ProductID">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>ProductID</DataField>

</Field>

<Field Name="ProductName">

<rd:TypeName>System.String</rd:TypeName>

<DataField>ProductName</DataField>

</Field>

<Field Name="SupplierID">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>SupplierID</DataField>

</Field>

<Field Name="CategoryID">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>CategoryID</DataField>

</Field>

<Field Name="QuantityPerUnit">

<rd:TypeName>System.String</rd:TypeName>

<DataField>QuantityPerUnit</DataField>

</Field>

<Field Name="UnitPrice">

<rd:TypeName>System.Decimal</rd:TypeName>

<DataField>UnitPrice</DataField>

</Field>

<Field Name="UnitsInStock">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>UnitsInStock</DataField>

</Field>

<Field Name="UnitsOnOrder">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>UnitsOnOrder</DataField>

</Field>

<Field Name="ReorderLevel">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>ReorderLevel</DataField>

</Field>

<Field Name="Discontinued">

<rd:TypeName>System.Boolean</rd:TypeName>

<DataField>Discontinued</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<Width>3.375in</Width>

<InteractiveHeight>11in</InteractiveHeight>

<Language>en-US</Language>

<TopMargin>1in</TopMargin>

</Report>

InterActive Sorting Help (Brian Welcker Video)

I saw a demo of a report using interactive sorting about a week ago. I can
not figure out though how to get this feature in my report. I believe the
video was of Brian Welcker' Does anyone have any advice on how todo this?
I am running a W2K3 Server and RS with all the latest SP's.
Thanks for any helpYou will need SQL Server 2005 CTP June for using the built-in interactive
sort. It is not available on RS 2000.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andy Jones" <ajones@.rheemac.com> wrote in message
news:%23LRBAu3eFHA.2732@.TK2MSFTNGP14.phx.gbl...
>I saw a demo of a report using interactive sorting about a week ago. I can
>not figure out though how to get this feature in my report. I believe the
>video was of Brian Welcker' Does anyone have any advice on how todo this?
>I am running a W2K3 Server and RS with all the latest SP's.
> Thanks for any help
>|||Thank You for the help. So now I have a dumb question. What does CTP stand
for?
Thanks again!
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:ey92kc4eFHA.3040@.TK2MSFTNGP14.phx.gbl...
> You will need SQL Server 2005 CTP June for using the built-in interactive
> sort. It is not available on RS 2000.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Andy Jones" <ajones@.rheemac.com> wrote in message
> news:%23LRBAu3eFHA.2732@.TK2MSFTNGP14.phx.gbl...
>>I saw a demo of a report using interactive sorting about a week ago. I can
>>not figure out though how to get this feature in my report. I believe the
>>video was of Brian Welcker' Does anyone have any advice on how todo
>>this? I am running a W2K3 Server and RS with all the latest SP's.
>> Thanks for any help
>|||CTP = Community Technology Preview.
See also: http://www.microsoft.com/sql/2005/productinfo/ctp.mspx
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andy Jones" <ajones@.rheemac.com> wrote in message
news:OzcfckFfFHA.3912@.tk2msftngp13.phx.gbl...
> Thank You for the help. So now I have a dumb question. What does CTP
> stand for?
> Thanks again!
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:ey92kc4eFHA.3040@.TK2MSFTNGP14.phx.gbl...
>> You will need SQL Server 2005 CTP June for using the built-in interactive
>> sort. It is not available on RS 2000.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Andy Jones" <ajones@.rheemac.com> wrote in message
>> news:%23LRBAu3eFHA.2732@.TK2MSFTNGP14.phx.gbl...
>>I saw a demo of a report using interactive sorting about a week ago. I
>>can not figure out though how to get this feature in my report. I believe
>>the video was of Brian Welcker' Does anyone have any advice on how todo
>>this? I am running a W2K3 Server and RS with all the latest SP's.
>> Thanks for any help
>>
>

Interactive sorting a list

Okay, I'm sure there is a way to do this. Basically what I have is a
List that contains a table. The list is grouping on a value so that
the table just shows some sub-details related to the grouping. What I
would like to do is sort the List. Here is an example of what I have
is the List grouping on the House Name (e.g. "My House"):
House Name:
My House
Rooms (table):
Bedroom 1 ...
Bedroom 2 ...
Living Room ...
House Name:
Bob's House
Rooms (table):
Bedroom 1 ...
Kitchen ...
So basically I want the user to be able to click on either "My House"
or "Bob's House" and change the list sort. Hope I'm making sense.
Thanks for any help!So I found one way to potentially accomplish this by setting up a
parameter, and then doing a "Jump to report" to reload the same
report, but changing the parameter that I use to control the sort of
the List. However, whenever I click on "My House" to cause the whole
jump to report thing to happen, I end up with the error "A data source
instance has not been supplied for the data source "House_Dataset"".
As some more info, this is all local processing, and I'm setting up
the data sources myself when the report is loaded for the first time
like so:
reportViewer1.LocalReport.ReportEmbeddedResource = "HouseInfo.rdlc";
reportViewer1.LocalReport.DataSources.Add(new
Microsoft.Reporting.WinForms.ReportDataSource("House_Dataset",
GetHouseDS()));
reportViewer1.LocalReport.DataSources.Add(new
Microsoft.Reporting.WinForms.ReportDataSource("Room_Dataset",
GetRooms()));
reportViewer1.RefreshReport();
So the initial load of the report is fine, but once the "jump to
report" executes, seems the reload loses (or never gets) the data
sources it needs.

Interactive Sorting

I know this is not available now, but is it plan in the near future:

I want to be able to set the initial sort order on the interactive sort.

for example, I have a report sorted Asc by Rank. If I click on the interactive sort button on the Rank column, it resorts ascending... totally pointless, and my customers point this out on a weekly basis...

Any plans to add this feature? If not, I will go to connect and suggest it.

Also, does anyone else find this annoying?

Thanks!

BobP

So you are saying that when you click the sort button on a report that is already sorted ascending, you want it to sort descending and vice versa?

Right click the field you want to interactively sort. Click Properties. Go to the interactive sort tab.

Is this not what you want?

By the way, how does this work? I'm trying to get one of my drilldowns to interactively sort by transaction amount. It won't seem to sort it. I think I just don't have it set up properly.

|||

It is exactly what i want, and it works great EXCEPT... If the report is sorted by default on Column A ascending, and I click on the sort button on column A, it tries to sort it asc first. I have to push it twice to sort desc.

To get it to sort, you have to select the correct grouping to sort, and the correct grouping for the sort expression.

Generally, if I have 1 group, I can sort on the table level, and leave the bottom selection default.

BobP

|||

BobP - BIM wrote:

It is exactly what i want, and it works great EXCEPT... If the report is sorted by default on Column A ascending, and I click on the sort button on column A, it tries to sort it asc first. I have to push it twice to sort desc.

To get it to sort, you have to select the correct grouping to sort, and the correct grouping for the sort expression.

Generally, if I have 1 group, I can sort on the table level, and leave the bottom selection default.

BobP

I got it to sort for me. But I see what you're talking about now. That is somewhat annoying. Especially when you go sort happy.

|||Is there any way to change the default order of the button? I need a report to display descending first, then ascending if clicked a second time. However, I am unable to find any setting in the report designer that allows me to change this.|||

That's what this thread is about. No, it does not exist now.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=249537

BobP

Interactive sort in matrix not working on 9.00.3054.00

I'm having problem with sorting in matrix. My report looks something like below.

Name* Avg.* | Test1 Test2 Test3

==============================================

Jim 50% | 100% 40% 10%

John 28% | 5% 40% 40%

The column with * need to be sorted. I can sort the name and average fine in Microsoft SQL Server Reporting Services Version 9.00.2047.00. BUt when I run this in another environment, sorting on Avg doesn't work. The version on that is Microsoft SQL Server Reporting Services Version 9.00.3054.00.

I put together a sample code against the northwinddatabase. Sorting works in 9.00.2047.00 but not 9.00.3054.00. Any ideas?

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<DataSources>

<DataSource Name="DataSource11">

<DataSourceReference>DataSource1</DataSourceReference>

<rdataSourceID>632d2cbd-3d9c-44fe-97ac-fbdef687ef47</rdataSourceID>

</DataSource>

</DataSources>

<BottomMargin>0.25in</BottomMargin>

<RightMargin>0.25in</RightMargin>

<PageWidth>11in</PageWidth>

<ReportParameters>

<ReportParameter Name="objecttype">

<DataType>String</DataType>

<DefaultValue>

<Values>

<Value>Student</Value>

</Values>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<Prompt>Object Type</Prompt>

</ReportParameter>

<ReportParameter Name="testid">

<DataType>Integer</DataType>

<DefaultValue>

<Values>

<Value>481</Value>

</Values>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<Prompt>testid</Prompt>

</ReportParameter>

<ReportParameter Name="groupby">

<DataType>String</DataType>

<DefaultValue>

<Values>

<Value>Element</Value>

</Values>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<Prompt>groupby</Prompt>

</ReportParameter>

<ReportParameter Name="usertestdoc">

<DataType>String</DataType>

<DefaultValue>

<Values>

<Value>6453bybbyq</Value>

</Values>

</DefaultValue>

<Prompt>usertestdoc</Prompt>

</ReportParameter>

<ReportParameter Name="dbserver">

<DataType>String</DataType>

<DefaultValue>

<Values>

<Value>Data Source=10.88.22.00;Initial Catalog=xxx;User ID=sa;Password=xxxxx</Value>

</Values>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<Prompt>dbserver</Prompt>

</ReportParameter>

<ReportParameter Name="copyright">

<DataType>String</DataType>

<Nullable>true</Nullable>

<DefaultValue>

<Values>

<Value>Copyright 2007 </Value>

</Values>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<Prompt>copyright</Prompt>

</ReportParameter>

<ReportParameter Name="levelby">

<DataType>String</DataType>

<DefaultValue>

<Values>

<Value>Teacher</Value>

</Values>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<Prompt>levelby</Prompt>

</ReportParameter>

<ReportParameter Name="objectid">

<DataType>Integer</DataType>

<DefaultValue>

<Values>

<Value>0</Value>

</Values>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<Prompt>objectid</Prompt>

</ReportParameter>

</ReportParameters>

<rdrawGrid>true</rdrawGrid>

<InteractiveWidth>8.5in</InteractiveWidth>

<rd:GridSpacing>0.0625in</rd:GridSpacing>

<rdnapToGrid>true</rdnapToGrid>

<Body>

<ReportItems>

<Matrix Name="matrix1">

<MatrixColumns>

<MatrixColumn>

<Width>1.73189in</Width>

</MatrixColumn>

</MatrixColumns>

<Left>0.5in</Left>

<RowGroupings>

<RowGrouping>

<Width>2.3125in</Width>

<DynamicRows>

<ReportItems>

<Textbox Name="shipcountry_1">

<rdefaultName>shipcountry_1</rdefaultName>

<ZIndex>1</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Left</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<BackgroundColor>Gainsboro</BackgroundColor>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=count(Fields!orderid.value)</Value>

</Textbox>

</ReportItems>

<Grouping Name="matrix1_RowGroup1">

<GroupExpressions>

<GroupExpression>=Fields!shipcountry.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</DynamicRows>

</RowGrouping>

</RowGroupings>

<ColumnGroupings>

<ColumnGrouping>

<DynamicColumns>

<ReportItems>

<Textbox Name="customerid">

<rdefaultName>customerid</rdefaultName>

<ZIndex>2</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<BackgroundColor>Gainsboro</BackgroundColor>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!customerid.Value</Value>

</Textbox>

</ReportItems>

<Grouping Name="matrix1_shipcountry">

<GroupExpressions>

<GroupExpression>=Fields!customerid.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</DynamicColumns>

<Height>0.4375in</Height>

</ColumnGrouping>

</ColumnGroupings>

<DataSetName>dataset</DataSetName>

<Top>0.625in</Top>

<Width>4.04439in</Width>

<Corner>

<ReportItems>

<Rectangle Name="rectangle1">

<ReportItems>

<Textbox Name="textbox1">

<Left>0.0625in</Left>

<Top>0.1875in</Top>

<rdefaultName>textbox1</rdefaultName>

<Width>1in</Width>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<UserSort>

<SortExpression>=count(Fields!orderid.Value)</SortExpression>

<SortExpressionScope>matrix1_RowGroup1</SortExpressionScope>

</UserSort>

<Value>SORTME</Value>

</Textbox>

</ReportItems>

<ZIndex>3</ZIndex>

</Rectangle>

</ReportItems>

</Corner>

<Height>0.88597in</Height>

<MatrixRows>

<MatrixRow>

<Height>0.44847in</Height>

<MatrixCells>

<MatrixCell>

<ReportItems>

<Textbox Name="textbox4">

<rdefaultName>textbox4</rdefaultName>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=count(Fields!orderid.Value)</Value>

</Textbox>

</ReportItems>

</MatrixCell>

</MatrixCells>

</MatrixRow>

</MatrixRows>

</Matrix>

</ReportItems>

<Height>2.5625in</Height>

</Body>

<rd:ReportID>98ed88ab-a457-4446-8b4c-ff275e2b5e03</rd:ReportID>

<LeftMargin>0.25in</LeftMargin>

<DataSets>

<DataSet Name="dataset">

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>select top 30 shipcountry, customerid, orderid from orders</CommandText>

<DataSourceName>DataSource11</DataSourceName>

</Query>

<Fields>

<Field Name="shipcountry">

<rd:TypeName>System.String</rd:TypeName>

<DataField>shipcountry</DataField>

</Field>

<Field Name="customerid">

<rd:TypeName>System.String</rd:TypeName>

<DataField>customerid</DataField>

</Field>

<Field Name="orderid">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>orderid</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<Code />

<Width>10.3125in</Width>

<InteractiveHeight>11in</InteractiveHeight>

<Language>en-US</Language>

<PageFooter>

<Height>0.1875in</Height>

<PrintOnLastPage>true</PrintOnLastPage>

<PrintOnFirstPage>true</PrintOnFirstPage>

</PageFooter>

<TopMargin>0.25in</TopMargin>

<PageHeight>8.5in</PageHeight>

</Report>

I ran into the same problem a while back but we ended up rebuilding our reports to use tables. I'm kinda curious as to what caused this issue and if anyone ever found a fix.|||

You know it looks like they changed it so that it won't sort based on an aggregate. For example prior to the SP update

I could have a report like this

SortBox Year

Carrier Sum(Passengers)

sort expression Sum(Passengers)

sort expression scope Carrier

Now that still sucks because you can't sort on the Passengers for a given year but at least it sorts on total pax....

Now after the SP(and I checked this in VS05 9.00.3054,VS08 beta 2 RDLC, and Katmai Designer) only non aggregate fields work

sort expression Fields!Carrier, or Fields!Passengers

sort expression scope Carrier

The tablix in Katmai allows you to put something like this together

Year

(Label with sort Passengers)

Carrier Sum(Passengers)

The sort label:

sort expression Fields!Passengers or Sum(Passengers)

sort expression scope Carrier

I'm hopeful that the fact that it doesn't give me an error indicates that this excel type sort will work in the future.

In anycase one possible bypass for the .3054 change appears to be embedding a table in the matrix cell which allows you to sort the "row group" relative to that specific column group. While this doesn't work across all the columns, in some situations it might help out.

Interactive sort for a grouped table

I'm trying out the interactive sort feature (and have searched high and low for this answer), but can not make the sorting work if I have the data in the table grouped. If I remove the group, the sorting works fine but the output is not acceptable due to the duplicated records in the list.

Can someone steer me in the right direction?

I'm not really sure whether this is what you are looking for, but figured it is worth a try:

http://msdn2.microsoft.com/en-us/library/aa337431.aspx

See To add an interactive sort button on a group

Interactive column sort in Reporting services

Hi,

I have a report with fiive columns, I have implemented interactive column sorting on the report. I have added a group to the report based on Column 2 and there is a page break by group. Now if I am on the second page ( page break by column 2 ) and sort on column 3(there is no grouping on column 3), the sorting happens but after the sort, the first page is displayed.IS there any way to remain on the same page while sorting?

Thanks in Advance.

I do not think that is possible. Once you click on the sort it will sort all the pages in the report.|||

It is ok if it sorts all the pages. I wanted to know if there is any way i can stick to the same page even after sorting. ie. If I am on page 5 and I click on sort, it sorts that records and takes me back to Page 1. Is there any way I can remain on page 5 after sorting?

|||I agree... I don't think that is possible (not without some nifty trickery), but do you really want that anyways? What good does it do to remain on the same page if the data is sorted differently? The data being referenced on that page will not be the same so you might as well start from the beginning.

Interactive column sort in Reporting services

Hi,

I have a report with fiive columns, I have implemented interactive column sorting on the report. I have added a group to the report based on Column 2 and there is a page break by group. Now if I am on the second page ( page break by column 2 ) and sort on column 3(there is no grouping on column 3), the sorting happens but after the sort, the first page is displayed.IS there any way to remain on the same page while sorting?

Thanks in Advance.

I do not think that is possible. Once you click on the sort it will sort all the pages in the report.|||

It is ok if it sorts all the pages. I wanted to know if there is any way i can stick to the same page even after sorting. ie. If I am on page 5 and I click on sort, it sorts that records and takes me back to Page 1. Is there any way I can remain on page 5 after sorting?

|||I agree... I don't think that is possible (not without some nifty trickery), but do you really want that anyways? What good does it do to remain on the same page if the data is sorted differently? The data being referenced on that page will not be the same so you might as well start from the beginning.

Interactice Sorting confusion!

Well I have a data set with the following fields

1) Territory

2) Product Category

3) Sub-Product Category

4) Total Sales

I created a report that is a List with a Nested Table

The List have the Territory Field (and have a grouping on Territory)

And the nested Table Have the other 3 fields Grouped By Product Category

The table group header have the product category fields

And the table details have the sub-category and Total Sales fields

Now to the problem, I have no problem adding interactive sort on the two fields in the table details rows.

But I cannot add an interactive sort to Territory Field in the list or the Product Category in the table group header.

Is this a limitation in interactive sort, they dont sort on fields used for grouping other fields !!!

I also couldn't really understand the interactive field options, the help doesn't really explain them clearly

What does the Options

1- Data Region or Grouping to Sort

2- Evaluate Sort Expression in this scope

How are they really used I dont get it? I tried many options hoping something will work for the territory or product category fields mentioned above, but nothing work!

I hope I was clear

I will try to be more precise I have the following grouping

Territory | Category | Sub Category | Sales

USA

Bike

Bike Mountain 100

Bike Racing 200

Toy

Toy Barbie 50

Toy He-Man 74

UK

Bike

Bike Mountain 100

Bike Racing 200

Toy

Toy Barbie 50

Toy He-Man 74

UAE

Bike

Bike Mountain 100

Bike Racing 200

Toy

Toy Barbie 50

Toy He-Man 74

Terriroty is actually in a text field in the list, where I have a nested table with the other fields group on category in the table group header

I have easily add interactive sort to the subcategory and sales table files, using the default values for the options

I cannot field the correct option combination to add interactive sort for the territory or category fields