locked
interactive sort doesn't do anything RRS feed

  • Question

  •  

    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?

    Wednesday, April 16, 2008 11:40 PM

All replies

  • Here is the simplified report (which still doesn't do anything). I also noticed that I have to Order By in the SQL query instead of  in the Edit Group>Sort when I right click on the detail line. I don't know what the difference is between the two. 

    I did try another report from the same data source, but just using one table, and the interactive sort did work on that one. But most of my reports including the one below are based on SQL views set up in SQL, with multiple tables joined.

     

    <?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="datasource">

    <DataSourceReference>datasource</DataSourceReference>

    <rdBig SmileataSourceID>b6ee6a5f-2024-41a4-a07b-9d77a7a9357c</rdBig SmileataSourceID>

    </DataSource>

    </DataSources>

    <BottomMargin>0.75in</BottomMargin>

    <RightMargin>1in</RightMargin>

    <rdBig SmilerawGrid>true</rdBig SmilerawGrid>

    <InteractiveWidth>8.5in</InteractiveWidth>

    <rdTongue TiednapToGrid>true</rdTongue TiednapToGrid>

    <Body>

    <ReportItems>

    <Table Name="PRODMDTABLE">

    <Footer>

    <TableRows>

    <TableRow>

    <TableCells>

    <TableCell>

    <ReportItems>

    <Textbox Name="textbox5">

    <rdBig SmileefaultName>textbox5</rdBig SmileefaultName>

    <ZIndex>3</ZIndex>

    <Style>

    <PaddingLeft>2pt</PaddingLeft>

    <PaddingBottom>2pt</PaddingBottom>

    <PaddingRight>2pt</PaddingRight>

    <PaddingTop>2pt</PaddingTop>

    </Style>

    <CanGrow>true</CanGrow>

    <Value />

    </Textbox>

    </ReportItems>

    </TableCell>

    <TableCell>

    <ReportItems>

    <Textbox Name="textbox17">

    <rdBig SmileefaultName>textbox17</rdBig SmileefaultName>

    <ZIndex>2</ZIndex>

    <Style>

    <BorderStyle>

    <Default>Solid</Default>

    </BorderStyle>

    <PaddingLeft>2pt</PaddingLeft>

    <PaddingBottom>2pt</PaddingBottom>

    <FontFamily>Tahoma</FontFamily>

    <BorderColor>

    <Default>LightGrey</Default>

    </BorderColor>

    <PaddingRight>2pt</PaddingRight>

    <PaddingTop>2pt</PaddingTop>

    </Style>

    <CanGrow>true</CanGrow>

    <Value />

    </Textbox>

    </ReportItems>

    </TableCell>

    </TableCells>

    <Height>0.25in</Height>

    </TableRow>

    </TableRows>

    </Footer>

    <Left>0.5in</Left>

    <DataSetName>dataset</DataSetName>

    <Top>0.375in</Top>

    <TableGroups>

    <TableGroup>

    <Grouping Name="table1_PRODUCT_ID">

    <GroupExpressions>

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

    </GroupExpressions>

    </Grouping>

    </TableGroup>

    </TableGroups>

    <Width>3in</Width>

    <Details>

    <TableRows>

    <TableRow>

    <TableCells>

    <TableCell>

    <ReportItems>

    <Textbox Name="PRODUCT_ID">

    <rdBig SmileefaultName>PRODUCT_ID</rdBig SmileefaultName>

    <ZIndex>1</ZIndex>

    <Style>

    <BorderStyle>

    <Default>Solid</Default>

    </BorderStyle>

    <PaddingLeft>2pt</PaddingLeft>

    <PaddingBottom>2pt</PaddingBottom>

    <FontFamily>Tahoma</FontFamily>

    <BorderColor>

    <Default>LightGrey</Default>

    </BorderColor>

    <PaddingRight>2pt</PaddingRight>

    <PaddingTop>2pt</PaddingTop>

    </Style>

    <CanGrow>true</CanGrow>

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

    </Textbox>

    </ReportItems>

    </TableCell>

    <TableCell>

    <ReportItems>

    <Textbox Name="textbox16">

    <rdBig SmileefaultName>textbox16</rdBig SmileefaultName>

    <Style>

    <BorderStyle>

    <Default>Solid</Default>

    </BorderStyle>

    <PaddingLeft>2pt</PaddingLeft>

    <PaddingBottom>2pt</PaddingBottom>

    <FontFamily>Tahoma</FontFamily>

    <BorderColor>

    <Default>LightGrey</Default>

    </BorderColor>

    <PaddingRight>2pt</PaddingRight>

    <PaddingTop>2pt</PaddingTop>

    </Style>

    <CanGrow>true</CanGrow>

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

    </Textbox>

    </ReportItems>

    </TableCell>

    </TableCells>

    <Height>0.25in</Height>

    </TableRow>

    </TableRows>

    <Grouping Name="PRODMDTABLE_Details_Group">

    <GroupExpressions>

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

    </GroupExpressions>

    </Grouping>

    </Details>

    <Header>

    <TableRows>

    <TableRow>

    <TableCells>

    <TableCell>

    <ReportItems>

    <Textbox Name="textbox2">

    <rdBig SmileefaultName>textbox2</rdBig SmileefaultName>

    <ZIndex>5</ZIndex>

    <Style>

    <BorderStyle>

    <Default>Solid</Default>

    </BorderStyle>

    <TextAlign>Center</TextAlign>

    <PaddingLeft>2pt</PaddingLeft>

    <PaddingBottom>2pt</PaddingBottom>

    <FontWeight>700</FontWeight>

    <FontSize>11pt</FontSize>

    <BorderColor>

    <Default>DarkGray</Default>

    </BorderColor>

    <BackgroundColor>DarkKhaki</BackgroundColor>

    <Color>MidnightBlue</Color>

    <PaddingRight>2pt</PaddingRight>

    <PaddingTop>2pt</PaddingTop>

    </Style>

    <CanGrow>true</CanGrow>

    <UserSort>

    <SortExpression>=Fields!PRODUCT_ID.Value</SortExpression>

    </UserSort>

    <Value>Product</Value>

    </Textbox>

    </ReportItems>

    </TableCell>

    <TableCell>

    <ReportItems>

    <Textbox Name="MD">

    <ZIndex>4</ZIndex>

    <Style>

    <BorderStyle>

    <Default>Solid</Default>

    </BorderStyle>

    <TextAlign>Center</TextAlign>

    <PaddingLeft>2pt</PaddingLeft>

    <PaddingBottom>2pt</PaddingBottom>

    <FontWeight>700</FontWeight>

    <FontSize>11pt</FontSize>

    <BorderColor>

    <Default>DarkGray</Default>

    </BorderColor>

    <BackgroundColor>DarkKhaki</BackgroundColor>

    <Color>MidnightBlue</Color>

    <PaddingRight>2pt</PaddingRight>

    <PaddingTop>2pt</PaddingTop>

    </Style>

    <CanGrow>true</CanGrow>

    <UserSort>

    <SortExpression>=Fields!MAJOR_CLASSIFICATION_ID.Value</SortExpression>

    </UserSort>

    <Value>Major Discipline</Value>

    </Textbox>

    </ReportItems>

    </TableCell>

    </TableCells>

    <Height>0.25in</Height>

    </TableRow>

    </TableRows>

    <RepeatOnNewPage>true</RepeatOnNewPage>

    </Header>

    <TableColumns>

    <TableColumn>

    <Width>1.625in</Width>

    </TableColumn>

    <TableColumn>

    <Width>1.375in</Width>

    </TableColumn>

    </TableColumns>

    <Height>0.75in</Height>

    </Table>

    </ReportItems>

    <Height>1.625in</Height>

    </Body>

    <rd:ReportID>79115c63-e223-40be-bce0-fb1fdf45cb8f</rd:ReportID>

    <LeftMargin>1in</LeftMargin>

    <DataSets>

    <DataSet Name="dataset">

    <Query>

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

    <CommandText>SELECT TOP (100) PERCENT PRODUCT_ID, MAJOR_CLASSIFICATION_ID, PostYear, NONSTOCK_IND

    FROM myview

    WHERE (NONSTOCK_IND = 1) AND (PostYear= '2007')

    ORDER BY PRODUCT_ID</CommandText>

    <DataSourceName>datasource</DataSourceName>

    </Query>

    <Fields>

    <Field Name="PRODUCT_ID">

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

    <DataField>PRODUCT_ID</DataField>

    </Field>

    <Field Name="MAJOR_CLASSIFICATION_ID">

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

    <DataField>MAJOR_CLASSIFICATION_ID</DataField>

    </Field>

    <Field Name="PostYear">

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

    <DataField>PostYear</DataField>

    </Field>

    <Field Name="NONSTOCK_IND">

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

    <DataField>NONSTOCK_IND</DataField>

    </Field>

    </Fields>

    </DataSet>

    </DataSets>

    <Code />

    <Width>8.125in</Width>

    <InteractiveHeight>11in</InteractiveHeight>

    <Language>en-US</Language>

    <TopMargin>0.75in</TopMargin>

    </Report>

    Thursday, April 17, 2008 6:01 PM
  • You want the SQL query in the report server project?

    I tried this against AW and it didn't work either.

     

    SELECT  Sales.vSalesPersonSalesByFiscalYears.SalesPersonID, Sales.vSalesPersonSalesByFiscalYears.FullName, Sales.vSalesPersonSalesByFiscalYears.Title,
                   Sales.vSalesPerson.EmailAddress, Sales.vSalesPerson.City
    FROM     Sales.vSalesPersonSalesByFiscalYears INNER JOIN
                   Sales.vSalesPerson ON Sales.vSalesPersonSalesByFiscalYears.SalesPersonID = Sales.vSalesPerson.SalesPersonID

     

     

     

     

    Thursday, April 17, 2008 9:01 PM