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.
OK I have a report that needs an interactive sort on a calculated field. I get the message: "Report items cannot be used in sort expressions"
That's the whole reason we purchased SS*S and are putting up a Data Warehouse, so we can rank and analyze our data. Surely there is a way to do this??
Thanks for any advice!
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
I have a quick question that i have just a simple calc field.
Field.Value-Field.value then i want to sort by the calculated results. The expression is fine i know it is written wrong here but i cannot figure how to sort by the result of the expression. Thanks.
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
does any one have and example of how to embedd a flash swf file onto a report.??? Is it possable? any examples would be helpful.
View 1 Replies View RelatedHow do I get data on my linked report based on my grouped subtotal and grand total from the main report. The subtotal and grand total are calculated columns.
I have a 3 columns in my matrix in the SSRS summary report. Actn_COAST, ActnCITY and NumbOfAccts.
The following is code for my summary report. The results are shown below.
SELECT Distinct ActnCITY, Count(ACCT) as NumbOfAccts,
CASE WHEN ActnCITY in ('NY', 'OH', IN, 'NJ', 'SC', 'NC') THEN 'EAST COAST'
WHEN ActnCITY IN ('CA'. 'NV', 'UT', 'WA', 'OR') THEN 'WEST COAST'
ELSE 'OTHER'
END AS Actn_COAST
FROM tbl1
where ACTNDATE between @STARTDT and @EndDT
Code for my detail report contains the following SQL
SELECT * FROM tbl1 where ACTNDATE between @STARTDT and @EndDT AND @ActnCITY = ActnCITY
I have linked my report based on the NumbOfAccts column. I am able to get data if I click any of the NumbOfAccts values related to the state I want. However when I am not sure how to make the subtotal and grand total work. I want when I click on the subtotal of either coast, I should be able to see records of that coast e.g., if I select 37 I should be able to see all the records in East Coast. If I click on the Grand Total, I only want data related to those 2 coasts.
Hi,
I've created dsv that contain all fields from table database. in the smdl I've remove some fileds due to security. All fields in the smdl do not contain drill.
Issue: When I created calculated field in the report builder the field has a link. When I clicked the drill I saw all the record data including field that not in the smdl.
Questions:
1) Can I remove the link from the calculated fields?
2) Can I prevent from users drill to fields that not in the smdl?
Thanks,
Assaf
I am creating a report using report builder and I am using several fields from various entities. Is there a way to sort the data in the report using a field that is not being displayed in the report? The "sort and group" option lists only fields that are selected for output in the report... but is there a way to do sorting using a field that is not displayed in the report?
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
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 RelatedInteractive 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.
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>
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
Hi 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.