This is a multi-part message in MIME format.
--020206060805020100000401
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
It seems First is returning the first row returned by the Dataset, not
the first row according to the sorting of the dataregion or grouping (as
the First function's documentation implies). This ends up with some
mysterious results in my reports. I created a simple example using the
SQL Northwind that shows the problem and attached it. Is this a bug or
intended behavior? It makes some of my reports using sorted groups have
seemingly bogus results when viewed.
Thanks,
Aaron
--020206060805020100000401
Content-Type: text/xml;
name="FirstLastAnomaly1.rdl"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="FirstLastAnomaly1.rdl"
<?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>
<Table Name="table1">
<Height>1in</Height>
<Style />
<DataSetName>Northwind</DataSetName>
<Top>0.125in</Top>
<Width>3.25in</Width>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>#8080ff</BackgroundColor>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox10</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!FirstName.Value & " " & Fields!LastName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>#8080ff</BackgroundColor>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox16">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>#8080ff</BackgroundColor>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox16</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="1st:" & First(Fields!City.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>#8080ff</BackgroundColor>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox17</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="1st:" & First(Fields!OrderID.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>#8080ff</BackgroundColor>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<CanGrow>true</CanGrow>
<Value>="Last:" & Last(Fields!City.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>#8080ff</BackgroundColor>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="Last:" & Last(Fields!OrderID.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!FirstName.Value & " " & Fields!LastName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!FirstName.Value & " " & Fields!LastName.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox19">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>#80ff80</BackgroundColor>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox19</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!City.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox20">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>#80ff80</BackgroundColor>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox20</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!OrderID.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group2">
<GroupExpressions>
<GroupExpression>=Fields!City.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!City.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</TableGroup>
</TableGroups>
<TableColumns>
<TableColumn>
<Width>1.5in</Width>
</TableColumn>
<TableColumn>
<Width>1.75in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.25in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>b54bf116-08cc-4b39-aa96-57013b5de15d</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>3.375in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="LastName">
<DataField>LastName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="FirstName">
<DataField>FirstName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="EmployeeID">
<DataField>EmployeeID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Customers.CompanyName, Customers.CustomerID, Orders.OrderID, Employees.LastName, Employees.FirstName, Employees.EmployeeID,
Customers.City
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN
Employees ON Orders.EmployeeID = Employees.EmployeeID</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>c31d746f-9224-4fcd-8568-e5376409e635</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>
--020206060805020100000401--Yes, you're right. Do your sort in the dataset query not in the dataregion
Sorting tab.
Use ORDER BY in the query, that is.
Charles Kangai, MCT, MCDBA
"Aaron S." wrote:
> It seems First is returning the first row returned by the Dataset, not
> the first row according to the sorting of the dataregion or grouping (as
> the First function's documentation implies). This ends up with some
> mysterious results in my reports. I created a simple example using the
> SQL Northwind that shows the problem and attached it. Is this a bug or
> intended behavior? It makes some of my reports using sorted groups have
> seemingly bogus results when viewed.
> Thanks,
> Aaron
>|||Thank you for the reply. I have added an order by to the SProc in my
production report and it works, this is not ideal for me as I'd rather
not have the SProc's sort dependent on a report. So is this a bug we
should expect to be corrected?
Thanks again for responding.
Charles Kangai wrote:
> Yes, you're right. Do your sort in the dataset query not in the dataregion
> Sorting tab.
> Use ORDER BY in the query, that is.
> Charles Kangai, MCT, MCDBA
> "Aaron S." wrote:
>
>>It seems First is returning the first row returned by the Dataset, not
>>the first row according to the sorting of the dataregion or grouping (as
>>the First function's documentation implies). This ends up with some
>>mysterious results in my reports. I created a simple example using the
>>SQL Northwind that shows the problem and attached it. Is this a bug or
>>intended behavior? It makes some of my reports using sorted groups have
>>seemingly bogus results when viewed.
>>Thanks,
>>Aaron|||I just want to confirm that SRS ignores the Sort order of the table and
uses the sort order of the SP query. Is version 2 out yet ?!!
Aaron S. wrote:
> Thank you for the reply. I have added an order by to the SProc in my
> production report and it works, this is not ideal for me as I'd
rather
> not have the SProc's sort dependent on a report. So is this a bug we
> should expect to be corrected?
> Thanks again for responding.
>
> Charles Kangai wrote:
> > Yes, you're right. Do your sort in the dataset query not in the
dataregion
> > Sorting tab.
> > Use ORDER BY in the query, that is.
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "Aaron S." wrote:
> >
> >
> >>It seems First is returning the first row returned by the Dataset,
not
> >>the first row according to the sorting of the dataregion or
grouping (as
> >>the First function's documentation implies). This ends up with some
> >>mysterious results in my reports. I created a simple example using
the
> >>SQL Northwind that shows the problem and attached it. Is this a bug
or
> >>intended behavior? It makes some of my reports using sorted groups
have
> >>seemingly bogus results when viewed.
> >>
> >>Thanks,
> >>
> >>Aaron
> >>
没有评论:
发表评论