How do I refer to the actual value for the label inside my expression?
Say you have datetime data across the x-axis for the data. I get one row per
month in the dataset (every row is same date and time for each row/month). I
understand how I can format this, for instance "MMM" to get month name in
short format.
But if I want to do further or a bit more complex manipulation? In this
case, I want to show only first letter of the month. I fail to connect how
to put the actual data value inside my VB.NET expression (substring, left or
similar function in this case).
(I can retrieve the first letter of the month along with the data, as an
extra column, doing this in my stored procedure. If above is difficult, I'd
appreciate tips on how to refer to this column for the chart label. I.e., I
want to show some other column as the label, not the one used to derive the
actual value.)
TIA
Tibor Karaszi
SQL Server MVPExpressions for formatting labels are not directly supported in the current
release. You could do a bar chart and use an expression
(=Left(Format(Fields!OrderDate.Value, "MMM"), 1)) for the corresponding
category group. The sample report attached at the end of this post (which
runs against local Northwind database) demonstrates this.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
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:OEwX0qaiEHA.2848@.TK2MSFTNGP10.phx.gbl...
> How do I refer to the actual value for the label inside my expression?
> Say you have datetime data across the x-axis for the data. I get one row
per
> month in the dataset (every row is same date and time for each row/month).
I
> understand how I can format this, for instance "MMM" to get month name in
> short format.
> But if I want to do further or a bit more complex manipulation? In this
> case, I want to show only first letter of the month. I fail to connect how
> to put the actual data value inside my VB.NET expression (substring, left
or
> similar function in this case).
> (I can retrieve the first letter of the month along with the data, as an
> extra column, doing this in my stored procedure. If above is difficult,
I'd
> appreciate tips on how to refer to this column for the chart label. I.e.,
I
> want to show some other column as the label, not the one used to derive
the
> actual value.)
> TIA
> Tibor Karaszi
> SQL Server MVP
>
+++++++++++++ Sample report +++++++++++++
<?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>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!Freight.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker />
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title />
<Style>
<FontSize>8pt</FontSize>
</Style>
<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>
<Title />
<Width>5.25in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!OrderDate.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Left(Format(Fields!OrderDate.Value, "MMM"), 1) & "
[" & Format(Fields!OrderDate.Value, "MMM yyyy") & "]"</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title />
<Style>
<Format>c</Format>
<FontSize>8pt</FontSize>
</Style>
<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>3in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>14b06457-afff-49a5-9624-2ecc74ef5643</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>initial catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.25in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="Freight">
<DataField>Freight</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT TOP 10 OrderDate, Freight
FROM Orders
ORDER BY ShipCity</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>b8405333-c29c-4c57-8ba3-7915ae7bf5eb</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment