Interactive sorting works just fine on my report until I set the
execution property to render from snapshot. When viewing a snapshot,
clicking on the column sort arrows will not sort the data I'm viewing. If I turn the snapshot feature off, then I get the interactive sort functionality back.
Is this by design or am I missing something fundamental here?
Using SQL 2005, RS 2005, VS 2005.
Thanks.
Hello,
I have a drill down report and would like to add interactive sort on two columns:- column 1 & column 2. These two columns should be in descending order. By adding this, when user's click on column 1 they will see the data based on the sorted order.
Does anyone help me on this issue.
Thanks,
BL
I am just starting with Sql Server reporting, but I can't get interactive sort to work for columns with aggregate fields.
I am using the following query for DataSet:
SELECT
Store.ID as StoreID,
Store.Name as StoreName,
COUNT(*) as NumReservations,
SUM(Appointment.TotalBeforeTaxes) as Revenue
FROM Store LEFT JOIN Appointment ON Store.ID=Appointment.StoreID
GROUP BY ALL Store.ID, Store.Name
ORDER BY Store.Name ASC
For report, I am using tabular data view. Interactive sorting works great for StoreID, StoreName, but doesn't work for NumReservations and Revenue fields. I turned it on for all 4 columns.
What could be causing this problem?
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>
Hi all,
Drill through bar charts work great in the report designer preview, but as soon as I deploy the report, and view the report in a webpage, drill through does not work smoothly.
I have to move the mouse around to be able to find a place on the bar to click through to the next report. Sometimes, I am able to click on the bar to go to next report, but then sometimes I have to click on the label, and other times somewhere in the vicinity of the label.
Any help will be much appreciated.
Thanks,
Kanwal
The Items Display is like this.
Project Name Acronym ID
7ked
SedRich Sed 123
Diercrom Dife 256
Fireup Fip 563
8IOP
Hiluke Hik 478
Jikerty Jer 125
Kilert Kre 458
The Report idea, u Got Right, now I want to implement Interactive sorting to Name ,Acronym,ID
Please help me
We have an issue with interactive sorting. Interactive sorting works in our Dev and test environments but does not work on our production server. We have deployed exactly the same report to each server. All servers are sp2 (9.00.3042.00). When the interactive sort buttons are clicked on production nothing happens. Has anyone seen this?
I can't get interactive sort to work on Report Server reports (but it works on reports I've done in Report Builder using one table per report).
I tried it on a different database in a Report Server report and it did work.
There was another post that talked about the collation, which is Latin1_General_CI_AS_KS_WS for the ReportServer and ReportServerTempDB, and SQL_Latin1_General_CP1_CI_AS for the two databases I reported on. But it seems like I'm not doing something correctly since it works in RB but not RS (though the one database report did work).
Any ideas?
We have run into a really bizzare problem after upgrading to SQL Server 2005 SP2. We have a bunch of matrix reports that have interactive sort in the upper left hand corner, the sort based on Numeric and Text fields. For Example
=Fields!City.Value
=Sum(Fields!Pax.Value)
Now for whatever reason after upgrading to SP2 and making NO CHANGES to the reports or under data sources, interactive sorts that use the =Sum(....) no longer work but interactive sorts on text values still work.
Any ideas???
Hi All,
I've designed a simple sales report , first grouped by Transport type and then by item.
Example
Category Item Sales
DHL A 2000
DHL C 1500
DHL E -1700
AmEx A 200
Amex E 20
-------------------------------
when click on sales under group DHL it shouild sort -say descending-
so result should be
DHL A 2000
DHL C 1500
DHL E -1700
instead I get results
DHL A 2000
DHL E -1700
DHL C 1500
this is just an example but this is the sort of results I get when I try to use interactive sorting.
Any Ideas?
thanks
Sonny
I have a table with WritingMode for the column labels set to tb-rl (vertical writing).
I want the text in each label to be right-aligned (i.e. aligned along the actual bottom of the cells), so I set TextAlign = Right.
This works fine for all columns until I enable Interactive Sort. When Interactive Sort is activated TextAlign ignores the value and always behaves as if General (or Center) was selected.
Is there a solution to this?
By the way: Is it possible to have the vertical text written upwards (bt-lr) instead of downwards?
Hi All
I'm Climbing the walls here, does anyone know how to do an interactive sort but keep one of the returned rows pinned at the end of the result set?
i've tried this
=iif(Fields!Item.Value <> "c", 1 ,2) & Fields!Item.Value
Doesn't work to well
Results to Sort
d
e
a
b
c
Sort should return
which works fine on the ascending sort
a
b
d
e
c
with c remaining at the bottom
But i get this on the descending sort
c
a
b
d
e
Thanks in advance
Dave
Hello,
I have a table with interactive sort and toggled to hide and unhide details (table group is the toggled)
when user click on the interactive sort the collapse back to default.
e.g.
in the report all the details on the table group are hide and the group have toggled.
user open one of the group. after that click on the sort all the collapses are close also the group user click to unhide.
any workaround ?
I'm setting 'interactive sort' property on some columns of my SSRS report. By some reason, this functionality works only for some columns. Settings do not produce any errors and everything is OK on preview level but when report is accessed through web-based Report Manager only some of the sorting arrows appear.
Hi,
I making a report using Matrix with 2 row groups and 1 column group. The group names are:
Row : CompanyName, TotalUsers
Column: UserType
Detail Group has a count of number of user in each type.
I want to use interactive sort feature on my column group so that when I click on my column header, it sort my detail group data ascending or decending and companyName and total users based on datail group.
Thanks a lot,
-Rohit
Hello All,
I have a subreport that has interactive sort on one of the columns in a table. The interactive sort works well when i run the subreport. But when i run the parent report that contains the subreport then the interactive sorting does not work.
Has anyone come across this issue, any help will be greatly appreciated.
Thanks a lot!
Mohit
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 looked around quite a bit but couldn't get around to the issue I've at hand.
I've a matrix report with one row group and one column group. I want to apply interactive sort so that when a user clicks on the column header based on the values in the column the report is rearranged.
The report shows:
Name 02/02/2008 01/31/2008
Test1 15 12
Test2 9 15
Now if the user clicks on 02/02/2008 then it will show
Name 02/02/2008 01/31/2008
Test2 9 15
Test1 15 12
And toggle if clicked again. Hope I'm clear.
Hi,
Im trying to do an interactive sort , one of the rows returned from my datasource called 'Total' i wish to display at the bottom always. is there a way i can do this?
I've tried the below on the column header but the total is either at the bottom or the top how can i check the ordering if Ascending or Descending? Then i cld swop the 1 and the 2 around.
=iif(Fields!Item.Value <> "Total", 1 ,2) & Fields!Item.Value
Otherwise doesnt anyone know how to palce a row from the detail section into the footer?
Many thanks
Dave
I have a report that has several headings for users to click on to toggle visibility of the data regions that the headings represent. Several of the columns in the data tables have interactive sort enabled. However, every time the user clicks the sort indicator, the region collapses again and is hidden. So, ultimately for users the interactive sort becomes a two-click action, after which they have to visually re-orient.
I tried putting the table into a rectangle and toggling visibility of the rectangle instead of the table, but it exhibited the same behavior.
How can I get it so the regions do not get hidden just because the user sorts?
Thanks,
Joel
Hello,
I have a report that calculates a field and therefore it does not exist in the dataset.
Can I perform an interactive sort on the textbox that contains this calculation?
Many thanks.
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.
Hello
I have a table with a RowNumber Group which shows 10 rows in each page.
it works fine.
in the next step i put an Interactive sorting on one column.
i expect that the sorting is on the table no regarding the presentation which shows only 10 rows.
in reality what happens is that the table was sorted seperated for each part of the 10 rows table,
and no for the whole table.
does any one have a suggestion how to force the Interactive Sort , sorting he Whole table first
and only later to present 10 rows each page (use the group expression)
Thanks,
In my Report, i have a grouping on project and Drill down on Project Cloumn only,now i need to implement interactive sort on rest of the columns,i implemented But it seems not working. can any one help me ..................... It is urgent requirement for me. rgds, kousik.
View 7 Replies View RelatedHi there,
I'm trying to implement Interactive Soring on the column headers in a matrix in a report. Is it actually possible to do this? I've read several internet posts and stories of implementing the Interactive Sorting in the upper-left corner of the matrix, but this is not what I want, I want to implement it on the column headers .
The goal I'm trying to achieve is to give the user the possibillity to click on a column header to sort the rows below in asc- or descending direction.
Please tell me if this is possible, because all my efforts have not been succesfull! If it's possible, please provide the solution to do this.
If you need more information, please don't hesitate to ask.
Thanks in advance,
Dave Ruijter
BI Consultant
We have a logo image in our report that is an external image that references a url similiar to: http://www.myserver.com/images/logo.gif.
In the same report we have a table with interactive sorting set on several column headings.
When we initially view the report the external logo image appears perfectly and the data in the table looks fine.
If we click on a column heading to sort the column then the logo image appears as a broken link as soon as the page refreshes.
Has anyone had a similiar experience with external images appearing normally and then appearing as broken when sorting a table?
We have configured the Execution Account and it is working normally and so do the reports except when we try to sort.
Any advice would be appreciated.
This must be a first, something that works in pdf, but not in html view....
I have 3 interactive sorts on 3 row groupings in a Matrix
When sorting the data in the 'preview' tab of report designer, all works well. On deploying the reports and trying to sort, I find that the report contains huge areas of white space (1 very wide empty column) and no data whatsoever, although the row grouping labels are present. Upon exporting to PDF all is ok.
Is this a known bug?
RS version = 9.00.3042.00
IE = 7.0.5730.11
I have a parameter for each field that lets me know if a field can be sorted or not.
What I want to be able to do is turn on or off interactive sorting for that column based on that parameters value.
In the dialog you have a check box that says enable interactive sorting. When that is clicked it appears that you get the two arrows no matter what you put in the expression.
I have tried
=IIF(allowSorting,Fields!myColumn.Value,"")
and
=IIF(allowSorting,Fields!myColumn.Value,Nothing)
but both result in the arrows still being there, just the sort doesn't work.
Is it possible to put an expression on the sort arrows appearing at all?
Has anyone experienced this? It's very odd. We have images that display fine in our headers but when you click on an interactive sort button, they become a small box with a red x in them. I'm accessing the reports from the reportviewer embedded in a custom application. Within Visual Studio's preview tabe it works fine. Any ideas?
View 4 Replies View RelatedI am trying to set sorting up on a DataGrid in ASP.NET 2.0. I have it working so that when you click on the column header, it sorts by that column, what I would like to do is set it up so that when you click the column header again it sorts on that field again, but in the opposite direction. I have it working using the following code in the stored procedure: CASE WHEN @SortColumn = 'Field1' AND @SortOrder = 'DESC' THEN Convert(sql_variant, FileName) end DESC,
case when @SortColumn = 'Field1' AND @SortOrder = 'ASC' then Convert(sql_variant, FileName) end ASC,
case WHEN @SortColumn = 'Field2' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, Convert(varchar(8000), FileDesc)) end DESC,
case when @SortColumn = 'Field2' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), FileDesc)) end ASC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'DESC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end DESC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end ASC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, FileDataID) end DESC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'ASC' THEN CONVERT(sql_variant, FileDataID) end ASC And I gotta tell you, that is ugly code, in my opinion. What I am trying to do is something like this: case when @SortColumn = 'Field1' then FileName end,
case when @SortColumn = 'FileDataID' then FileDataID end,
case when @SortColumn = 'Field2' then FileDesc
when @SortColumn = 'VersionNotes' then VersionNotes
end
case when @SortOrder = 'DESC' then DESC
when @SortOrder = 'ASC' then ASC
end and it's not working at all, i get an error saying: Incorrect syntax near the keyword 'case' when i put a comma after the end on line 5 i get: Incorrect syntax near the keyword 'DESC' What am I missing here? Thanks in advance for any help -Madrak
I have the following text field in SSRS report:
Version
2.2.32.7
1.0.0.0
2.0.0.0
1.2.0.0
2.1.8.8
1.4.11.0
I want to sort this field interactively.I have already sorted other fields, but as this field is text but has decimal data, its not sorting properly. How do I do this correctly? Once sorted ascending, report should show
Version
1.0.0.0
1.2.0.0
1.4.11.0
2.0.0.0
2.1.8.8
2.2.32.7
Hi Anyone,
I have a drilldown report includes three groups. I add the last group for pagination. But the details in each page doesn't accord to I specified and the detail record number is different in different page.
Another issue is the interactive sort always sort in the first page scope. I set the data region or grouping to the table, and evaluate expression scrope to Detail scope.
Anyone has experience on that?
Thanks a lot
Is there any example I can find a full feature demo of SSRS Web Service Rendering with Interactive Sort and other features enabled?
For MS: Indeed I think ReportViewer Control should be an open source component as we as developer needs the flexibility to customize the report viewer interface as well as can learn directly from the control source so to understand how can we integrate better with SSRS.