|
|
You're in the
sql server reporting services
group:Chart, how do I use format code for a label?
sql server reporting services:
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
Expressions 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... [quoted text, click to view] > How do I refer to the actual value for the label inside my expression? +++++++++++++ Sample report +++++++++++++> > 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 > <?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>
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |