Thursday, March 22, 2012
Bar chart, conditionally formatting the label?
have different format for the label. This is for the x-axis (what actually
is the y-axis visually for the bar chart).
I want to check against the column "ord" in the data set and if 1, then
color should be red, else black. If 3, the font weight should be bold, else
normal. Etc.
I've tried the following (Chart, Properties):
X-axis, Show Labels, Style Properties, Font:
Color: =Iif( Fields!ord.Value=1, "red", "black" )
Weight: =Iif( Fields!ord.Value=3, "Bold", "Normal" )
However, above expressions doesn't seem to be connected with the rows in the
dataset. Seems like the value used is the *first* returned for the dataset.
The format for all labels (across all rows) is the same...
Am I correct in assuming that you cannot format the labels conditionally
(based on the data in the dataset, for each row)?
Would very much appreciate an answer, even if it is "No, you can't do that".
(I'm still doing this evaluation for a customer...:-). )
Thanks
Tibor KarasziSee attached example (at the bottom). You will need RS 2000 SP1 installed.
The important part is the expression on chart properties dialog -> data
tab -> edit values -> point label tab -> lable style button -> color
expression:
=Choose(Fields!CategoryID.Value, "Red", "Blue", "Yellow", "Aqua", "Green")
(!) Note: if you use series groupings in the chart, the expression has to
use a First aggregate function scoped on the series grouping (!) - otherwise
you will compare against the first row of the entire chart. E.g.
=Choose(First(Fields!CategoryID.Value, "SeriesGroupName"), "Red", "Blue",
"Yellow", "Aqua", "Green")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<?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="newChart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>20</Inclination>
<Shading>Real</Shading>
<WallThickness>25</WallThickness>
<DrawingStyle>Cylinder</DrawingStyle>
<Clustered>true</Clustered>
</ThreeDProperties>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<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>
<Color>=Choose(Fields!CategoryID.Value, "Red", "Blue",
"Yellow", "Aqua", "Green")</Color>
</Style>
<Value>=Fields!UnitsInStock.Value</Value>
<Visible>true</Visible>
</DataLabel>
<Style>
<BackgroundGradientEndColor>Black</BackgroundGradientEndColor>
<BackgroundGradientType>LeftRight</BackgroundGradientType>
<BackgroundColor>=Choose(Fields!CategoryID.Value, "Red",
"Blue", "Yellow", "Aqua", "Green")</BackgroundColor>
</Style>
<Marker>
<Type>Triangle</Type>
<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>
<Style>
<BorderStyle>
<Default>Dotted</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<MinorTickMarks>Outside</MinorTickMarks>
<MajorInterval>5</MajorInterval>
<MinorInterval>1</MinorInterval>
<CrossAt>0</CrossAt>
<Reverse>true</Reverse>
<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>
</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>
<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>|||Hi Robert,
Unfortunately, your example did not provide anything but a solid (faded) big blob over the whole
plot area... I created a new project and based on your suggestion changed the color expression for
point label, label style for label for data tab. However, this will change the point label (to the
right of each bar) and not the label itself (to the left in of the bar, outside the plot area. Below
is the rdl file:
<?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">
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<RightMargin>2.5cm</RightMargin>
<Body>
<ReportItems>
<Chart Name="chart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
<Legend>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitsInStock.Value)</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style>
<Color>=Choose(Fields!CategoryID.Value, "Red", "Blue","Yellow", "Aqua",
"Green")</Color>
</Style>
<Visible>true</Visible>
</DataLabel>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title />
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>0</PointWidth>
<Type>Bar</Type>
<Top>0.75cm</Top>
<Title />
<Width>13cm</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!ProductName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Fields!ProductName.Value</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>6.75cm</Height>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<Left>1cm</Left>
<ValueAxis>
<Axis>
<Title />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>8cm</Height>
<ColumnSpacing>1cm</ColumnSpacing>
</Body>
<TopMargin>2.5cm</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>6f1bc838-989b-468d-80a2-7711b0c23bc7</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=.;initial catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>14.5cm</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="ProductName">
<DataField>ProductName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CategoryName">
<DataField>CategoryName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="UnitsInStock">
<DataField>UnitsInStock</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="CategoryID">
<DataField>CategoryID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT ProductName, CategoryName, UnitsInStock, CategoryID
FROM [Alphabetical list of products]
WHERE (UnitsOnOrder > 10)</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>2.5cm</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<PageHeight>29.7cm</PageHeight>
<rd:DrawGrid>true</rd:DrawGrid>
<PageWidth>21cm</PageWidth>
<rd:ReportID>815ad11a-63aa-4e27-8f44-a30a8fd6959d</rd:ReportID>
<BottomMargin>2.5cm</BottomMargin>
<Language>en-US</Language>
</Report>
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/|||I think I misread your original posting then. You don't want control over
the datapoint label styles, but the axis label styles. You can only globally
set X-axis and Y-axis styles (click on the style icon near the "show labels"
checkbox).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:euL%23gmNmEHA.2968@.TK2MSFTNGP14.phx.gbl...
> Hi Robert,
> Unfortunately, your example did not provide anything but a solid (faded)
big blob over the whole
> plot area... I created a new project and based on your suggestion changed
the color expression for
> point label, label style for label for data tab. However, this will change
the point label (to the
> right of each bar) and not the label itself (to the left in of the bar,
outside the plot area. Below
> is the rdl file:
> <?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">
> <rd:GridSpacing>0.25cm</rd:GridSpacing>
> <RightMargin>2.5cm</RightMargin>
> <Body>
> <ReportItems>
> <Chart Name="chart1">
> <ThreeDProperties>
> <Rotation>30</Rotation>
> <Inclination>30</Inclination>
> <Shading>Simple</Shading>
> <WallThickness>50</WallThickness>
> </ThreeDProperties>
> <Style>
> <BackgroundColor>White</BackgroundColor>
> </Style>
> <Legend>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> <Position>RightCenter</Position>
> </Legend>
> <Palette>Default</Palette>
> <ChartData>
> <ChartSeries>
> <DataPoints>
> <DataPoint>
> <DataValues>
> <DataValue>
> <Value>=Sum(Fields!UnitsInStock.Value)</Value>
> </DataValue>
> </DataValues>
> <DataLabel>
> <Style>
> <Color>=Choose(Fields!CategoryID.Value, "Red",
"Blue","Yellow", "Aqua",
> "Green")</Color>
> </Style>
> <Visible>true</Visible>
> </DataLabel>
> <Marker>
> <Size>6pt</Size>
> </Marker>
> </DataPoint>
> </DataPoints>
> </ChartSeries>
> </ChartData>
> <CategoryAxis>
> <Axis>
> <Title />
> <MajorGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MajorGridLines>
> <MinorGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MinorGridLines>
> <MajorTickMarks>Outside</MajorTickMarks>
> <Min>0</Min>
> <Visible>true</Visible>
> </Axis>
> </CategoryAxis>
> <DataSetName>Northwind</DataSetName>
> <PointWidth>0</PointWidth>
> <Type>Bar</Type>
> <Top>0.75cm</Top>
> <Title />
> <Width>13cm</Width>
> <CategoryGroupings>
> <CategoryGrouping>
> <DynamicCategories>
> <Grouping Name="chart1_CategoryGroup1">
> <GroupExpressions>
>
<GroupExpression>=Fields!ProductName.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Label>=Fields!ProductName.Value</Label>
> </DynamicCategories>
> </CategoryGrouping>
> </CategoryGroupings>
> <Height>6.75cm</Height>
> <Subtype>Plain</Subtype>
> <PlotArea>
> <Style>
> <BackgroundColor>LightGrey</BackgroundColor>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </PlotArea>
> <Left>1cm</Left>
> <ValueAxis>
> <Axis>
> <Title />
> <MajorGridLines>
> <ShowGridLines>true</ShowGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MajorGridLines>
> <MinorGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MinorGridLines>
> <MajorTickMarks>Outside</MajorTickMarks>
> <Min>0</Min>
> <Margin>true</Margin>
> <Visible>true</Visible>
> <Scalar>true</Scalar>
> </Axis>
> </ValueAxis>
> </Chart>
> </ReportItems>
> <Style />
> <Height>8cm</Height>
> <ColumnSpacing>1cm</ColumnSpacing>
> </Body>
> <TopMargin>2.5cm</TopMargin>
> <DataSources>
> <DataSource Name="Northwind">
>
<rd:DataSourceID>6f1bc838-989b-468d-80a2-7711b0c23bc7</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=.;initial
catalog=Northwind</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Width>14.5cm</Width>
> <DataSets>
> <DataSet Name="Northwind">
> <Fields>
> <Field Name="ProductName">
> <DataField>ProductName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="CategoryName">
> <DataField>CategoryName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="UnitsInStock">
> <DataField>UnitsInStock</DataField>
> <rd:TypeName>System.Int16</rd:TypeName>
> </Field>
> <Field Name="CategoryID">
> <DataField>CategoryID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>Northwind</DataSourceName>
> <CommandText>SELECT ProductName, CategoryName, UnitsInStock,
CategoryID
> FROM [Alphabetical list of products]
> WHERE (UnitsOnOrder > 10)</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>2.5cm</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <PageHeight>29.7cm</PageHeight>
> <rd:DrawGrid>true</rd:DrawGrid>
> <PageWidth>21cm</PageWidth>
> <rd:ReportID>815ad11a-63aa-4e27-8f44-a30a8fd6959d</rd:ReportID>
> <BottomMargin>2.5cm</BottomMargin>
> <Language>en-US</Language>
> </Report>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>|||Thanks for the confirmation, Robert.
The problem comes from me showing value for the whole company, each region
and each department. All in the same diagram. I would have done separate
diagrams, but I have no control over the plot area, so I cannot align the
diagrams.
Yes, I want to highlight the value for the current department in the axis
label (make the label bold), or for the whole region (make it red), or
generate a separator (between regional values and departmental value) and
make that separator label big font and bold.
Seems I now need to work with two separate chart controls, where I
unfortunately don't have control over the plot area (meaning that the
diagram won't align vertically). My current workaround it to generate an
extra row in the dataset, and make for the column I use for label, I output
'_______________' so it is longer than any possible value I will have there.
I can then fiddle with aligning the other elements against this. (I tried
spaces, but RS seem to ignore these.)
Tibor
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:e6JndlQmEHA.3392@.TK2MSFTNGP15.phx.gbl...
> I think I misread your original posting then. You don't want control over
> the datapoint label styles, but the axis label styles. You can only
globally
> set X-axis and Y-axis styles (click on the style icon near the "show
labels"
> checkbox).
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:euL%23gmNmEHA.2968@.TK2MSFTNGP14.phx.gbl...
> > Hi Robert,
> >
> > Unfortunately, your example did not provide anything but a solid (faded)
> big blob over the whole
> > plot area... I created a new project and based on your suggestion
changed
> the color expression for
> > point label, label style for label for data tab. However, this will
change
> the point label (to the
> > right of each bar) and not the label itself (to the left in of the bar,
> outside the plot area. Below
> > is the rdl file:
> >
> > <?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">
> > <rd:GridSpacing>0.25cm</rd:GridSpacing>
> > <RightMargin>2.5cm</RightMargin>
> > <Body>
> > <ReportItems>
> > <Chart Name="chart1">
> > <ThreeDProperties>
> > <Rotation>30</Rotation>
> > <Inclination>30</Inclination>
> > <Shading>Simple</Shading>
> > <WallThickness>50</WallThickness>
> > </ThreeDProperties>
> > <Style>
> > <BackgroundColor>White</BackgroundColor>
> > </Style>
> > <Legend>
> > <Style>
> > <BorderStyle>
> > <Default>Solid</Default>
> > </BorderStyle>
> > </Style>
> > <Position>RightCenter</Position>
> > </Legend>
> > <Palette>Default</Palette>
> > <ChartData>
> > <ChartSeries>
> > <DataPoints>
> > <DataPoint>
> > <DataValues>
> > <DataValue>
> > <Value>=Sum(Fields!UnitsInStock.Value)</Value>
> > </DataValue>
> > </DataValues>
> > <DataLabel>
> > <Style>
> > <Color>=Choose(Fields!CategoryID.Value, "Red",
> "Blue","Yellow", "Aqua",
> > "Green")</Color>
> > </Style>
> > <Visible>true</Visible>
> > </DataLabel>
> > <Marker>
> > <Size>6pt</Size>
> > </Marker>
> > </DataPoint>
> > </DataPoints>
> > </ChartSeries>
> > </ChartData>
> > <CategoryAxis>
> > <Axis>
> > <Title />
> > <MajorGridLines>
> > <Style>
> > <BorderStyle>
> > <Default>Solid</Default>
> > </BorderStyle>
> > </Style>
> > </MajorGridLines>
> > <MinorGridLines>
> > <Style>
> > <BorderStyle>
> > <Default>Solid</Default>
> > </BorderStyle>
> > </Style>
> > </MinorGridLines>
> > <MajorTickMarks>Outside</MajorTickMarks>
> > <Min>0</Min>
> > <Visible>true</Visible>
> > </Axis>
> > </CategoryAxis>
> > <DataSetName>Northwind</DataSetName>
> > <PointWidth>0</PointWidth>
> > <Type>Bar</Type>
> > <Top>0.75cm</Top>
> > <Title />
> > <Width>13cm</Width>
> > <CategoryGroupings>
> > <CategoryGrouping>
> > <DynamicCategories>
> > <Grouping Name="chart1_CategoryGroup1">
> > <GroupExpressions>
> >
> <GroupExpression>=Fields!ProductName.Value</GroupExpression>
> > </GroupExpressions>
> > </Grouping>
> > <Label>=Fields!ProductName.Value</Label>
> > </DynamicCategories>
> > </CategoryGrouping>
> > </CategoryGroupings>
> > <Height>6.75cm</Height>
> > <Subtype>Plain</Subtype>
> > <PlotArea>
> > <Style>
> > <BackgroundColor>LightGrey</BackgroundColor>
> > <BorderStyle>
> > <Default>Solid</Default>
> > </BorderStyle>
> > </Style>
> > </PlotArea>
> > <Left>1cm</Left>
> > <ValueAxis>
> > <Axis>
> > <Title />
> > <MajorGridLines>
> > <ShowGridLines>true</ShowGridLines>
> > <Style>
> > <BorderStyle>
> > <Default>Solid</Default>
> > </BorderStyle>
> > </Style>
> > </MajorGridLines>
> > <MinorGridLines>
> > <Style>
> > <BorderStyle>
> > <Default>Solid</Default>
> > </BorderStyle>
> > </Style>
> > </MinorGridLines>
> > <MajorTickMarks>Outside</MajorTickMarks>
> > <Min>0</Min>
> > <Margin>true</Margin>
> > <Visible>true</Visible>
> > <Scalar>true</Scalar>
> > </Axis>
> > </ValueAxis>
> > </Chart>
> > </ReportItems>
> > <Style />
> > <Height>8cm</Height>
> > <ColumnSpacing>1cm</ColumnSpacing>
> > </Body>
> > <TopMargin>2.5cm</TopMargin>
> > <DataSources>
> > <DataSource Name="Northwind">
> >
> <rd:DataSourceID>6f1bc838-989b-468d-80a2-7711b0c23bc7</rd:DataSourceID>
> > <ConnectionProperties>
> > <DataProvider>SQL</DataProvider>
> > <ConnectString>data source=.;initial
> catalog=Northwind</ConnectString>
> > <IntegratedSecurity>true</IntegratedSecurity>
> > </ConnectionProperties>
> > </DataSource>
> > </DataSources>
> > <Width>14.5cm</Width>
> > <DataSets>
> > <DataSet Name="Northwind">
> > <Fields>
> > <Field Name="ProductName">
> > <DataField>ProductName</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="CategoryName">
> > <DataField>CategoryName</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="UnitsInStock">
> > <DataField>UnitsInStock</DataField>
> > <rd:TypeName>System.Int16</rd:TypeName>
> > </Field>
> > <Field Name="CategoryID">
> > <DataField>CategoryID</DataField>
> > <rd:TypeName>System.Int32</rd:TypeName>
> > </Field>
> > </Fields>
> > <Query>
> > <DataSourceName>Northwind</DataSourceName>
> > <CommandText>SELECT ProductName, CategoryName, UnitsInStock,
> CategoryID
> > FROM [Alphabetical list of products]
> > WHERE (UnitsOnOrder > 10)</CommandText>
> > <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > </Query>
> > </DataSet>
> > </DataSets>
> > <LeftMargin>2.5cm</LeftMargin>
> > <rd:SnapToGrid>true</rd:SnapToGrid>
> > <PageHeight>29.7cm</PageHeight>
> > <rd:DrawGrid>true</rd:DrawGrid>
> > <PageWidth>21cm</PageWidth>
> > <rd:ReportID>815ad11a-63aa-4e27-8f44-a30a8fd6959d</rd:ReportID>
> > <BottomMargin>2.5cm</BottomMargin>
> > <Language>en-US</Language>
> > </Report>
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
>
Bar chart report(grouping datetime info yearly, monthly, etc...)
hello,
I am creating a bar chart report which contains a datetime information on the x -axis. The unit of the x-axis value depends on the parameters value(StartDate and EndDate). For example, if the difference between StartDate and EndDate is > 2 years, say 4 years for instance, then the value of the x-axis should be presented on a yearly basis, like this:
2001-03-04 2002-03-04 2003-03-04 2004-03-04
Assume the line is the x-axis of the bar chart and 2001-03-04 and 2004-03-04 are the StartDate and EndDate respectively.
Similarly,if the difference between the StartDate and EndDate is between 4 months and 2 years, say 5 months for instance, then the value fo the x-axis should presented on a monthly basis, like this:
2006-03-04 2006-04-04 2006-05-04 2006-06-04 2006-07-04
Assume the line is the x-axis of the bar chart and 2006-03-04 and 2006-07-04 are the StartDate and EndDate respectively.
Please let me know if anybody has came across similar situation.
Any idea is appreciable.
Sincerely,
Amde
One possible solution is to create two separate graphs. For the first graph, you can use years as the increments. For the second graph, you can use months as the increments. Then, create a visibility expression using IIF to show one graph when the range between the dates meets the requirements, This will enable you to determine which graph is visible to the user based on the range between the dates.
I understand this isn't the most efficient solution, but it will at least solve your problem.
|||Hi Joel,
That was not my question. As I mentioned earlier, I want to change the x-axis value based on the parameters value. The main thing is grouping the datetime info based on the parameter condition(yearly, monthly, etc.) as I mentioned earlier. Please refer the highlighted values in the previous post. On the top of that, the two examples that I mentioned earlier are just two conditions, I might need to add another conditions such as weekly, daily and hourly basis reports.
Sincerely,
Amde
|||I am creating a bar chart report which contains a datetime information on the x -axis. The unit of the x-axis value depends on the parameters value(StartDate and EndDate). For example, if the difference between StartDate and EndDate is > 2 years, say 4 years for instance, then the value of the x-axis should be presented on a yearly basis, like this:
2001-03-04 2002-03-04 2003-03-04 2004-03-04
Assume the line is the x-axis of the bar chart and 2001-03-04 and 2004-03-04 are the StartDate and EndDate respectively.
Similarly,if the difference between the StartDate and EndDate is between 4 months and 2 years, say 5 months for instance, then the value fo the x-axis should presented on a monthly basis, like this:
2006-03-04 2006-04-04 2006-05-04 2006-06-04 2006-07-04
Assume the line is the x-axis of the bar chart and 2006-03-04 and 2006-07-04 are the StartDate and EndDate respectively.
Please let me know if anybody has came across similar situation.
Any idea is appreciable.
Sincerely,
Amde
|||Hi guys,
Is this question not clear or difficult to answer? Does any body came across this kind of scenario?
Please let me know?
Sincerely,
Amde
sqlBar chart report
hello,
I am creating a bar chart report which contains a datetime information on the x -axis. The unit of the x-axis value depends on the parameters value(StartDate and EndDate). For example, if the difference between StartDate and EndDate is > 2 years, say 4 years for instance, then the value of the x-axis should be presented on a yearly basis, like this:
2001-03-04 2002-03-04 2003-03-04 2004-03-04
Assume the line is the x-axis of the bar chart and 2001-03-04 and 2004-03-04 are the StartDate and EndDate respectively.
Similarly,if the difference between the StartDate and EndDate is between 4 months and 2 years, say 5 months for instance, then the value fo the x-axis should presented on a monthly basis, like this:
2006-03-04 2006-04-04 2006-05-04 2006-06-04 2006-07-04
Assume the line is the x-axis of the bar chart and 2006-03-04 and 2006-07-04 are the StartDate and EndDate respectively.
Please let me know if anybody has came across similar situation.
Any idea is appreciable.
Sincerely,
Amde
One possible solution is to create two separate graphs. For the first graph, you can use years as the increments. For the second graph, you can use months as the increments. Then, create a visibility expression using IIF to show one graph when the range between the dates meets the requirements, This will enable you to determine which graph is visible to the user based on the range between the dates.
I understand this isn't the most efficient solution, but it will at least solve your problem.
|||Hi Joel,
That was not my question. As I mentioned earlier, I want to change the x-axis value based on the parameters value. The main thing is grouping the datetime info based on the parameter condition(yearly, monthly, etc.) as I mentioned earlier. Please refer the highlighted values in the previous post. On the top of that, the two examples that I mentioned earlier are just two conditions, I might need to add another conditions such as weekly, daily and hourly basis reports.
Sincerely,
Amde
|||I am creating a bar chart report which contains a datetime information on the x -axis. The unit of the x-axis value depends on the parameters value(StartDate and EndDate). For example, if the difference between StartDate and EndDate is > 2 years, say 4 years for instance, then the value of the x-axis should be presented on a yearly basis, like this:
2001-03-04 2002-03-04 2003-03-04 2004-03-04
Assume the line is the x-axis of the bar chart and 2001-03-04 and 2004-03-04 are the StartDate and EndDate respectively.
Similarly,if the difference between the StartDate and EndDate is between 4 months and 2 years, say 5 months for instance, then the value fo the x-axis should presented on a monthly basis, like this:
2006-03-04 2006-04-04 2006-05-04 2006-06-04 2006-07-04
Assume the line is the x-axis of the bar chart and 2006-03-04 and 2006-07-04 are the StartDate and EndDate respectively.
Please let me know if anybody has came across similar situation.
Any idea is appreciable.
Sincerely,
Amde
|||Hi guys,
Is this question not clear or difficult to answer? Does any body came across this kind of scenario?
Please let me know?
Sincerely,
Amde
Bar chart problems
1. How can I move the left-most and right-most bar into the center so that it won't show only half?
2. How can I show the x-axis with all the day in a selected month? The dataset may contains only some dates but I would like the x-axis should all days.
#1:
On the x-axis tab of the chart properties dialog, select the "margins" option.
#2:
Make sure your category grouping expression returns a DateTime object. In addition, you must have the x-axis option selected for "numeric or timescale values".
Still, if the underlying dataset only has data for the 2nd till 10th of the month, you won't get the full month shown, unless you explicitly set the x-axis maximum value to the last day of the month. In RS 2005, you can use expressions for the x-axis maximum and dynamically calculate the last day of a month.
-- Robert
Bar chart point label missing
bar chart. All the other bars have the correct point label, displayed to the
right of the bar, but this one does not display anything.
I have tried changing:
a) the sort order of the underlying dataset so that a different value is at
this location on the chart
b) the font size of the point label
c) the point label format
d) the point label position - auto on/off, different positions when manually
selected
all to no avail.
Any suggestions?Solved the problem by ticking the side margins option for the x-axis.
"magendo_man" wrote:
> I'm having problems with the point label on the bar nearest the x-axis of a
> bar chart. All the other bars have the correct point label, displayed to the
> right of the bar, but this one does not display anything.
> I have tried changing:
> a) the sort order of the underlying dataset so that a different value is at
> this location on the chart
> b) the font size of the point label
> c) the point label format
> d) the point label position - auto on/off, different positions when manually
> selected
> all to no avail.
> Any suggestions?
Bar Chart hiding final Data Label
I have a Bar Chart that displays the Point Labels for the data.
Unfortunately the final value (the one nearest the x-axis) is not being
displayed. Can anyone tell me why and/or how to turn this off?
Thanks in advance
CathieDid you turn off margins to see it helps?
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cathie Hagen" <cathie@.bob.com> wrote in message
news:Oj9$coPrEHA.376@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a Bar Chart that displays the Point Labels for the data.
> Unfortunately the final value (the one nearest the x-axis) is not being
> displayed. Can anyone tell me why and/or how to turn this off?
> Thanks in advance
> Cathie
>sql