Sorting Reports with ReportExecution2005 web service

Hot Topic

Appears to load 3.0 in emulator instead of 3.1
Script Component Error in BI Development Studio on Vista Install
Managing transactional replication
Do not have instance of SQL Express 2005 installed
Connecting to Report Services now broken
remote connection problem with sql server express
Do any ISP's really host SQL 2005 Express
Query large database time out in Query Analyzer
Report Builder Formatting Currency
Source Script Component using a flat file connection - System.ArgumentException: Empty pathname is not legal.

SQL Server

server groups
How Detect SMO is installed?
Importing Data from Oracle 8i/9i to SQL Server 2005 using SQL Server Import and Export Wizard (AKA DTS Wizard)
LOOKUP: specifying parameters for the SQL Query
HELP, error report, Setup Bootstrap\Log\SqlSetup0017.cab
saving xml to table
Removing Duplicate Entries from a Parent-Child Hierarchy
Accumulate values in an aditional column
doctors' appointments - please advise
How to create a report using multiple databases

Sorting Reports with ReportExecution2005 web service

I've been banging my head against this for a while now and have searched through the documentation and google and posted to the newsgroups with no luck.

The ReportExecution2005 namespace contains a sort method, but the documentation doesn't indicate how it's used. The method accepts a string for the sort item, but that string apparently has to be an integer. I've called the method and regardless of the input, the result is the same:

* The sort method returns 1 and the report item passed in is the report item returned in the 'out' variable.

* The sorting is not reflected in the output of the Render method.

I can't simply use interactive sorting because, in our deployment, the SQL Server and Reporting Services are not accessible through the firewall. Requests go to a web server which calls the SSRS web methods and renders the report to an HTML placeholder.

Answer this question

Sorting Reports with ReportExecution2005 web service

RufusLDK
Thanks again for the reply. That solution may work for others, but it still has the drawback of using Dynamic SQL (a big no-no) here and the Drill Through column headers contact the Report Server directly and our users will not have access to that server. Your solution did lead me to a technique that I think will work in our environment.

- Set up the report to accept a sort-by value from a drop-down list of columns that is specified at design-time

- In the report stored procedure, create an additional column (OrderBy) in the return dataset and populate that column with the value identified by the parameter

- Either ORDER BY [OrderBy] or set the sort property on the table control to sort by the "OrderBy" column.
Anil_shet

The Sort method only works in combination with reports that use the new "interactive sort" feature of RS 2005 (see e.g. http://msdn2.microsoft.com/en-us/library/ms157313.aspx).

I guess you could parse the generated HTML output on the client side to determine which sort item id was clicked on, but this is not trivial.

Have you actually looked into using the ReportViewer Webforms control of VS 2005 in LocalMode (see www.gotreportviewer.com) It can render reports without accessing a report server and still supports features such as interactive sort.

-- Robert
Shivangi
You don't need to have interactive sorting enabled on your report. If you modify the sort expressions for the table or group, you can achieve dynamic sorting based on parameters. The problem is that you can't set the sort direction in code, so you end up with duplicate code for Ascending and Descending. For one report, I allow users to select upto three columns to sort in either direction.

To get to the sort options, select a table, and right-click and select properties. You should then see and select the sort tab.

Ascending Code Snippet

=IIf(Parameters!blnSortAsc1.Value, Switch(

Parameters!SortOption1.Value = 1, Fields!FieldName1.Value,

Parameters!SortOption1.Value = 2, Fields!FieldName2.Value,

Parameters!SortOption1.Value = 3, Fields!FieldName3.Value,), "")

Descending Code Snippet

=IIf(Parameters!blnSortAsc1.Value = false, Switch(

Parameters!SortOption1.Value = 1, Fields!FieldName1.Value,

Parameters!SortOption1.Value = 2, Fields!FieldName2.Value,

Parameters!SortOption1.Value = 3, Fields!FieldName3.Value,), "")

Although this works, I would like to call to a function to return the sort field as shown in the code snippets below.

Custom Code Snippet

Public Shared Function GetSortFieldName(ByVal intSortOption as Integer) as String

Select Case (intSortOption)
Case 0 : GetSortFieldName = ""
Case 1 : GetSortFieldName = "FieldName1"
Case 2 : GetSortFieldName = "FieldName2"
Case 3 : GetSortFieldName = "FieldName3"
Case 4 : GetSortFieldName = "FieldName4"
End Select

End Function

Sorting Expression Code Snippet

=IIf(Parameters!blnSortAsc1.Value, Fields(Code.GetSortFieldName(Parameters!SortOption1.Value)).Value, "")

This works really well, except when the SortOption1 value = 0, and returns an empty string. You will get an undefined field exception because of the empty string (Fields("").Value).

I would really like to pass the sort option value into the custom code, and have it return the field. So far this hasn't worked. I have tried the fully qualified field object name, as well as variations of passing the field collection into the function, etc. None of these have worked properly. I always receive the exception "Reference to a non-shared member requires an object reference". This is too bad, since it would save a lot of duplicate code. The next step would be to call out to C# to attempt to achieve the same goal. If anyone has any other thoughts on this I would appreciate it. Oh, and if I could achieve this in SQL, I would, but due to dynamic filtering, CLR, and Stored Proc processing constraints, it is impossible to place the dynamic sorting in SQL.

Code Snippet

Public Shared Function GetSortFieldName(ByVal intSortOption as Integer) as Field

Select Case (intSortOption)
Case 1 : GetSortFieldName= Fields!FieldName1.Value
Case 2 : GetSortFieldName= Fields!FieldName2.Value

Case 3 : GetSortFieldName= Fields!FieldName3.Value
Case 4 : GetSortFieldName= Fields!FieldName4.Value
End Select

End Function

I hope these samples help. Good Luck. - Steve
shieldy

The report at the bottom provides one example of how to use report parameters to perform dynamic sorting. You are right, the sort order is a constant property.

-- Robert

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

< xml version="1.0" encoding="utf-8" >
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>Brown</BackgroundColor>
<BorderWidth>
<Bottom>3pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>18pt</FontSize>
<TextAlign>Center</TextAlign>
<Color>White</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.33in</Height>
<CanGrow>true</CanGrow>
<Value>DynamicSort</Value>
</Textbox>
<Table Name="table1">
<Header>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>21</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<Action>
<Drillthrough>
<Parameters>
<Parameter Name="SortBy">
<Value>CustomerID</Value>
</Parameter>
</Parameters>
<ReportName>DynamicSort</ReportName>
</Drillthrough>
</Action>
<CanGrow>true</CanGrow>
<Value>Customer ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>20</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<Action>
<Drillthrough>
<Parameters>
<Parameter Name="SortBy">
<Value>CompanyName</Value>
</Parameter>
</Parameters>
<ReportName>DynamicSort</ReportName>
</Drillthrough>
</Action>
<CanGrow>true</CanGrow>
<Value>Company Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>19</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<Action>
<Drillthrough>
<Parameters>
<Parameter Name="SortBy">
<Value>ContactName</Value>
</Parameter>
</Parameters>
<ReportName>DynamicSort</ReportName>
</Drillthrough>
</Action>
<CanGrow>true</CanGrow>
<Value>Contact Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>18</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<Action>
<Drillthrough>
<Parameters>
<Parameter Name="SortBy">
<Value>ContactTitle</Value>
</Parameter>
</Parameters>
<ReportName>DynamicSort</ReportName>
</Drillthrough>
</Action>
<CanGrow>true</CanGrow>
<Value>Contact Title</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>17</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<Action>
<Drillthrough>
<Parameters>
<Parameter Name="SortBy">
<Value>Address</Value>
</Parameter>
</Parameters>
<ReportName>DynamicSort</ReportName>
</Drillthrough>
</Action>
<CanGrow>true</CanGrow>
<Value>Address</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>16</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<Action>
<Drillthrough>
<Parameters>
<Parameter Name="SortBy">
<Value>City</Value>
</Parameter>
</Parameters>
<ReportName>DynamicSort</ReportName>
</Drillthrough>
</Action>
<CanGrow>true</CanGrow>
<Value>City</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>15</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<Action>
<Drillthrough>
<Parameters>
<Parameter Name="SortBy">
<Value>Region</Value>
</Parameter>
</Parameters>
<ReportName>DynamicSort</ReportName>
</Drillthrough>
</Action>
<CanGrow>true</CanGrow>
<Value>Region</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>14</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<Action>
<Drillthrough>
<Parameters>
<Parameter Name="SortBy">
<Value>PostalCode</Value>
</Parameter>
</Parameters>
<ReportName>DynamicSort</ReportName>
</Drillthrough>
</Action>
<CanGrow>true</CanGrow>
<Value>Postal Code</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>textbox10</rd:DefaultName>
<Action>
<Drillthrough>
<Parameters>
<Parameter Name="SortBy">
<Value>Country</Value>
</Parameter>
</Parameters>
<ReportName>DynamicSort</ReportName>
</Drillthrough>
</Action>
<CanGrow>true</CanGrow>
<Value>Country</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox11</rd:DefaultName>
<Action>
<Drillthrough>
<Parameters>
<Parameter Name="SortBy">
<Value>Phone</Value>
</Parameter>
</Parameters>
<ReportName>DynamicSort</ReportName>
</Drillthrough>
</Action>
<CanGrow>true</CanGrow>
<Value>Phone</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<Action>
<Drillthrough>
<Parameters>
<Parameter Name="SortBy">
<Value>Fax</Value>
</Parameter>
</Parameters>
<ReportName>DynamicSort</ReportName>
</Drillthrough>
</Action>
<CanGrow>true</CanGrow>
<Value>Fax</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="CustomerID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>CustomerID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CustomerID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>CompanyName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="ContactName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>ContactName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!ContactName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="ContactTitle">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>ContactTitle</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!ContactTitle.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Address">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>Address</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Address.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="City">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>City</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!City.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Region">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>Region</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Region.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="PostalCode">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>PostalCode</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!PostalCode.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Country">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Phone">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>Phone</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Phone.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Fax">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Fax</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Fax.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>Northwind</DataSetName>
<Top>0.33in</Top>
<Style>
<BackgroundColor>White</BackgroundColor>
<BorderWidth>
<Top>3pt</Top>
</BorderWidth>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
</Style>
<TableColumns>
<TableColumn>
<Width>1in</Width>
</TableColumn>
<TableColumn>
<Width>1in</Width>
</TableColumn>
<TableColumn>
<Width>1in</Width>
</TableColumn>
<TableColumn>
<Width>1in</Width>
</TableColumn>
<TableColumn>
<Width>1in</Width>
</TableColumn>
<TableColumn>
<Width>1in</Width>
</TableColumn>
<TableColumn>
<Width>1in</Width>
</TableColumn>
<TableColumn>
<Width>1in</Width>
</TableColumn>
<TableColumn>
<Width>1in</Width>
</TableColumn>
<TableColumn>
<Width>1in</Width>
</TableColumn>
<TableColumn>
<Width>1in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>0.75in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>c993082d-0f59-4be0-9978-38b03a17c08a</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=.;initial catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>11in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactName">
<DataField>ContactName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactTitle">
<DataField>ContactTitle</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Address">
<DataField>Address</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Region">
<DataField>Region</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PostalCode">
<DataField>PostalCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Phone">
<DataField>Phone</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Fax">
<DataField>Fax</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>="SELECT * FROM customers" & IIF(Parameters!SortBy.Value is Nothing, "", " order by " & Parameters!SortBy.Value)</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
<DataSet Name="CustomerColumns">
<Fields>
<Field Name="name">
<DataField>name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT null as name UNION ALL SELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name= 'Customers')</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>bd51006d-73ad-472b-8b4d-3d0bb595a843</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<ReportParameters>
<ReportParameter Name="SortBy">
<DataType>String</DataType>
<Nullable>true</Nullable>
<DefaultValue>
<Values>
<Value>=Nothing</Value>
</Values>
</DefaultValue>
<Prompt>Sort by</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>CustomerColumns</DataSetName>
<ValueField>name</ValueField>
<LabelField>name</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
</ReportParameters>
<Language>en-US</Language>
</Report>
SekharPC
Thanks for the response, I'll take another look at the report viewer's local mode. It seems on the surface however to present its own collection of issues. Instead of handling sorting, I'll have to write custom code to retrieve the report data. I'll also lose out on critical features such as CSV/XML export.

After running far to many what-if scenarios with the sort method, I came to the realization that what I really need to do is dynamically sort tables in the report according to a parameter passed into the report. I've tried placing a formula in the table's sort property with no luck so far. It also appears that the sort order (ascending/descending) can not be specified as a formula. Do you have any insights or pointers to dynamically ordering tables
Sorting Reports with ReportExecution2005 web service

Answer this question