I have a graph that filters a DataSet for itself. I want to display a message
when there are no rows after the filter.
I can't seem to use ReportItems! from a textbox to reference the graph and
the norows property on the graph does not seem to do anything.
Does anyone know how to do this?
DaveThis works for me. Just try the attached example at the bottom of this
posting, which filters e.g. on the inner column grouping and shows the
NoRows message. Also moving the filter to the entire chart works.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dlloyd" <Dlloyd@.discussions.microsoft.com> wrote in message
news:F6D266A0-4833-480E-82C9-97A24844F867@.microsoft.com...
> I have a graph that filters a DataSet for itself. I want to display a
message
> when there are no rows after the filter.
> I can't seem to use ReportItems! from a textbox to reference the graph and
> the norows property on the graph does not seem to do anything.
> Does anyone know how to do this?
> Dave
=============================================
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="Chart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>20</Inclination>
<Shading>Real</Shading>
<WallThickness>25</WallThickness>
<DrawingStyle>Cylinder</DrawingStyle>
<Clustered>true</Clustered>
</ThreeDProperties>
<Style>
<Color>Firebrick</Color>
</Style>
<Legend>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Excel</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Fields!UnitsInStock.Value</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style />
</DataLabel>
<Style>
<BackgroundColor>=Switch(Sum(Fields!UnitsInStock.Value) =Max(Fields!UnitsInStock.Value,"Chart1"), "Red",
Sum(Fields!UnitsInStock.Value) = Min(Fields!UnitsInStock.Value,"Chart1"),
"Yellow", true, "Green")</BackgroundColor>
</Style>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title>
<Style />
</Title>
<Style>
<FontSize>8pt</FontSize>
</Style>
<MajorGridLines>
<Style>
<BorderWidth>
<Default>1.5pt</Default>
</BorderWidth>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Dotted</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<MinorTickMarks>Outside</MinorTickMarks>
<MajorInterval>5</MajorInterval>
<MinorInterval>1</MinorInterval>
<CrossAt>0</CrossAt>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>55</PointWidth>
<Type>Bar</Type>
<Top>0.25in</Top>
<Title>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</Title>
<Width>6.375in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="ProductCategory">
<GroupExpressions>
<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
<Filters>
<Filter>
<FilterExpression>=Fields!SupplierID.Value</FilterExpression>
<Operator>Equal</Operator>
<FilterValues>
<FilterValue>=-1</FilterValue>
</FilterValues>
</Filter>
</Filters>
</Grouping>
<Label />
</DynamicCategories>
</CategoryGrouping>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="ProductName">
<GroupExpressions>
<GroupExpression>=Fields!ProductName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Fields!ProductName.Value</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>3.25in</Height>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Stock</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>OldLace</BackgroundColor>
</Style>
</PlotArea>
<NoRows>All rows are filtered out!</NoRows>
<Left>0.25in</Left>
<ValueAxis>
<Axis>
<Title>
<Caption>Amount</Caption>
<Style />
</Title>
<Style>
<BorderColor>
<Default>IndianRed</Default>
</BorderColor>
<FontSize>8pt</FontSize>
</Style>
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderColor>
<Default>Blue</Default>
</BorderColor>
<BorderStyle>
<Default>Dashed</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderColor>
<Default>CornflowerBlue</Default>
</BorderColor>
<BorderStyle>
<Default>Dotted</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MinorTickMarks>Cross</MinorTickMarks>
<Min>0</Min>
<MajorInterval>20</MajorInterval>
<MinorInterval>5</MinorInterval>
<Interlaced>true</Interlaced>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>3.75in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>32d95cbf-5e5b-4fb3-a37a-39b9506b8c80</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.75in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="ProductID">
<DataField>ProductID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="ProductName">
<DataField>ProductName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="SupplierID">
<DataField>SupplierID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CategoryID">
<DataField>CategoryID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="QuantityPerUnit">
<DataField>QuantityPerUnit</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="UnitsInStock">
<DataField>UnitsInStock</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="UnitsOnOrder">
<DataField>UnitsOnOrder</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="ReorderLevel">
<DataField>ReorderLevel</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="Discontinued">
<DataField>Discontinued</DataField>
<rd:TypeName>System.Boolean</rd:TypeName>
</Field>
<Field Name="CategoryName">
<DataField>CategoryName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="NullUnits">
<DataField>NullUnits</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT TOP 10 *, NULL AS NullUnits
FROM [Alphabetical list of products]
WHERE (UnitsOnOrder > 0)</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>4792d607-5639-4c89-ac36-2794e9e78a74</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>|||Maybe I am too new to this or something ... but having you post the rdl didnt
tell me how you solved the issue. I am having the same problem but I dont
have a chart. I have set an expression in the NoRows property but it is not
showing.
Can you put that in english
"Robert Bruckner [MSFT]" wrote:
> This works for me. Just try the attached example at the bottom of this
> posting, which filters e.g. on the inner column grouping and shows the
> NoRows message. Also moving the filter to the entire chart works.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Dlloyd" <Dlloyd@.discussions.microsoft.com> wrote in message
> news:F6D266A0-4833-480E-82C9-97A24844F867@.microsoft.com...
> > I have a graph that filters a DataSet for itself. I want to display a
> message
> > when there are no rows after the filter.
> >
> > I can't seem to use ReportItems! from a textbox to reference the graph and
> > the norows property on the graph does not seem to do anything.
> >
> > Does anyone know how to do this?
> >
> > Dave
>
> =============================================> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Chart Name="Chart1">
> <ThreeDProperties>
> <Rotation>30</Rotation>
> <Inclination>20</Inclination>
> <Shading>Real</Shading>
> <WallThickness>25</WallThickness>
> <DrawingStyle>Cylinder</DrawingStyle>
> <Clustered>true</Clustered>
> </ThreeDProperties>
> <Style>
> <Color>Firebrick</Color>
> </Style>
> <Legend>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> <Position>RightCenter</Position>
> </Legend>
> <Palette>Excel</Palette>
> <ChartData>
> <ChartSeries>
> <DataPoints>
> <DataPoint>
> <DataValues>
> <DataValue>
> <Value>=Fields!UnitsInStock.Value</Value>
> </DataValue>
> </DataValues>
> <DataLabel>
> <Style />
> </DataLabel>
> <Style>
> <BackgroundColor>=Switch(Sum(Fields!UnitsInStock.Value) => Max(Fields!UnitsInStock.Value,"Chart1"), "Red",
> Sum(Fields!UnitsInStock.Value) = Min(Fields!UnitsInStock.Value,"Chart1"),
> "Yellow", true, "Green")</BackgroundColor>
> </Style>
> <Marker>
> <Size>6pt</Size>
> </Marker>
> </DataPoint>
> </DataPoints>
> </ChartSeries>
> </ChartData>
> <CategoryAxis>
> <Axis>
> <Title>
> <Style />
> </Title>
> <Style>
> <FontSize>8pt</FontSize>
> </Style>
> <MajorGridLines>
> <Style>
> <BorderWidth>
> <Default>1.5pt</Default>
> </BorderWidth>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MajorGridLines>
> <MinorGridLines>
> <ShowGridLines>true</ShowGridLines>
> <Style>
> <BorderStyle>
> <Default>Dotted</Default>
> </BorderStyle>
> </Style>
> </MinorGridLines>
> <MajorTickMarks>Outside</MajorTickMarks>
> <MinorTickMarks>Outside</MinorTickMarks>
> <MajorInterval>5</MajorInterval>
> <MinorInterval>1</MinorInterval>
> <CrossAt>0</CrossAt>
> <Margin>true</Margin>
> <Visible>true</Visible>
> </Axis>
> </CategoryAxis>
> <DataSetName>Northwind</DataSetName>
> <PointWidth>55</PointWidth>
> <Type>Bar</Type>
> <Top>0.25in</Top>
> <Title>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </Title>
> <Width>6.375in</Width>
> <CategoryGroupings>
> <CategoryGrouping>
> <DynamicCategories>
> <Grouping Name="ProductCategory">
> <GroupExpressions>
> <GroupExpression>=Fields!CategoryName.Value</GroupExpression>
> </GroupExpressions>
> <Filters>
> <Filter>
> <FilterExpression>=Fields!SupplierID.Value</FilterExpression>
> <Operator>Equal</Operator>
> <FilterValues>
> <FilterValue>=-1</FilterValue>
> </FilterValues>
> </Filter>
> </Filters>
> </Grouping>
> <Label />
> </DynamicCategories>
> </CategoryGrouping>
> <CategoryGrouping>
> <DynamicCategories>
> <Grouping Name="ProductName">
> <GroupExpressions>
> <GroupExpression>=Fields!ProductName.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Label>=Fields!ProductName.Value</Label>
> </DynamicCategories>
> </CategoryGrouping>
> </CategoryGroupings>
> <Height>3.25in</Height>
> <SeriesGroupings>
> <SeriesGrouping>
> <StaticSeries>
> <StaticMember>
> <Label>Stock</Label>
> </StaticMember>
> </StaticSeries>
> </SeriesGrouping>
> </SeriesGroupings>
> <Subtype>Plain</Subtype>
> <PlotArea>
> <Style>
> <BackgroundColor>OldLace</BackgroundColor>
> </Style>
> </PlotArea>
> <NoRows>All rows are filtered out!</NoRows>
> <Left>0.25in</Left>
> <ValueAxis>
> <Axis>
> <Title>
> <Caption>Amount</Caption>
> <Style />
> </Title>
> <Style>
> <BorderColor>
> <Default>IndianRed</Default>
> </BorderColor>
> <FontSize>8pt</FontSize>
> </Style>
> <MajorGridLines>
> <ShowGridLines>true</ShowGridLines>
> <Style>
> <BorderColor>
> <Default>Blue</Default>
> </BorderColor>
> <BorderStyle>
> <Default>Dashed</Default>
> </BorderStyle>
> </Style>
> </MajorGridLines>
> <MinorGridLines>
> <ShowGridLines>true</ShowGridLines>
> <Style>
> <BorderColor>
> <Default>CornflowerBlue</Default>
> </BorderColor>
> <BorderStyle>
> <Default>Dotted</Default>
> </BorderStyle>
> </Style>
> </MinorGridLines>
> <MinorTickMarks>Cross</MinorTickMarks>
> <Min>0</Min>
> <MajorInterval>20</MajorInterval>
> <MinorInterval>5</MinorInterval>
> <Interlaced>true</Interlaced>
> <Margin>true</Margin>
> <Visible>true</Visible>
> <Scalar>true</Scalar>
> </Axis>
> </ValueAxis>
> </Chart>
> </ReportItems>
> <Style />
> <Height>3.75in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="Northwind">
> <rd:DataSourceID>32d95cbf-5e5b-4fb3-a37a-39b9506b8c80</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=(local);initial
> catalog=Northwind</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Width>6.75in</Width>
> <DataSets>
> <DataSet Name="Northwind">
> <Fields>
> <Field Name="ProductID">
> <DataField>ProductID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="ProductName">
> <DataField>ProductName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="SupplierID">
> <DataField>SupplierID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="CategoryID">
> <DataField>CategoryID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="QuantityPerUnit">
> <DataField>QuantityPerUnit</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="UnitPrice">
> <DataField>UnitPrice</DataField>
> <rd:TypeName>System.Decimal</rd:TypeName>
> </Field>
> <Field Name="UnitsInStock">
> <DataField>UnitsInStock</DataField>
> <rd:TypeName>System.Int16</rd:TypeName>
> </Field>
> <Field Name="UnitsOnOrder">
> <DataField>UnitsOnOrder</DataField>
> <rd:TypeName>System.Int16</rd:TypeName>
> </Field>
> <Field Name="ReorderLevel">
> <DataField>ReorderLevel</DataField>
> <rd:TypeName>System.Int16</rd:TypeName>
> </Field>
> <Field Name="Discontinued">
> <DataField>Discontinued</DataField>
> <rd:TypeName>System.Boolean</rd:TypeName>
> </Field>
> <Field Name="CategoryName">
> <DataField>CategoryName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="NullUnits">
> <DataField>NullUnits</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>Northwind</DataSourceName>
> <CommandText>SELECT TOP 10 *, NULL AS NullUnits
> FROM [Alphabetical list of products]
> WHERE (UnitsOnOrder > 0)</CommandText>
> <Timeout>30</Timeout>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>4792d607-5639-4c89-ac36-2794e9e78a74</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> </Report>
>
>
