Hello all!!
I have created a pie chart that is embedded in a matrix report, therefore
creating a new pie for every row. The problem I am having is the pie slice
for the same series is changing each time there is a new pie. For example,
in the first pie Cars may be designated as green but in the second pie it may
be red. I would like the colors to be consistent; all cars are represented
as green slices. Oh and if there are no cars don't use the color green for
another slice.
I think this can be done using a nested expression but I am unsure how to
write it...I either need an example of an expression that can handle
multiple slices or another way to handle the issue.
Thanks,
AnthonyI suggest to follow the approach discussed in this blog article:
http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx
You don't need the part about the custom legend, but you should take a close
look at the custom color part of the sample. You can modify the code so that
instead of having the hashtable approach to apply unique colors for
categories, you replace it with some IF statement to explicitly control
colors.
A trivial RDL sample with two charts using different filters but sharing the
same colors for categories is copied to the bottom of this posting.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
news:69E297FD-F5C7-44D4-9463-5E4871B1CEAB@.microsoft.com...
> Hello all!!
> I have created a pie chart that is embedded in a matrix report, therefore
> creating a new pie for every row. The problem I am having is the pie
> slice
> for the same series is changing each time there is a new pie. For
> example,
> in the first pie Cars may be designated as green but in the second pie it
> may
> be red. I would like the colors to be consistent; all cars are
> represented
> as green slices. Oh and if there are no cars don't use the color green
> for
> another slice.
> I think this can be done using a nested expression but I am unsure how to
> write it...I either need an example of an expression that can handle
> multiple slices or another way to handle the issue.
> Thanks,
> Anthony
============================================
<?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>
<Author>Robert M. Bruckner</Author>
<Body>
<ReportItems>
<Chart Name="chart2">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<ZIndex>1</ZIndex>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
<Legend>
<Visible>true</Visible>
<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>
<BackgroundColor>=Code.GetColor(Fields!CategoryName.Value)</BackgroundColor>
</Style>
<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>
<Min>0</Min>
</Axis>
</CategoryAxis>
<DataSetName>DataSet1</DataSetName>
<PointWidth>0</PointWidth>
<Type>Bar</Type>
<Top>3.125in</Top>
<Title>
<Caption>UK</Caption>
</Title>
<Width>6in</Width>
<SeriesGroupings>
<SeriesGrouping>
<DynamicSeries>
<Grouping Name="chart2_SeriesGroup1">
<GroupExpressions>
<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!CategoryName.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=Fields!CategoryName.Value</Label>
</DynamicSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<Filters>
<Filter>
<FilterExpression>=Fields!Country.Value</FilterExpression>
<Operator>Equal</Operator>
<FilterValues>
<FilterValue>UK</FilterValue>
</FilterValues>
</Filter>
</Filters>
<Left>0.125in</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>
<Chart Name="chart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
<Legend>
<Visible>true</Visible>
<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>
<BackgroundColor>=Code.GetColor(Fields!CategoryName.Value)</BackgroundColor>
</Style>
<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>
<Min>0</Min>
</Axis>
</CategoryAxis>
<DataSetName>DataSet1</DataSetName>
<PointWidth>0</PointWidth>
<Type>Bar</Type>
<Top>0.25in</Top>
<Title>
<Caption>USA</Caption>
</Title>
<Width>6in</Width>
<Height>2.5in</Height>
<SeriesGroupings>
<SeriesGrouping>
<DynamicSeries>
<Grouping Name="chart1_SeriesGroup1">
<GroupExpressions>
<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!CategoryName.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=Fields!CategoryName.Value</Label>
</DynamicSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<Filters>
<Filter>
<FilterExpression>=Fields!Country.Value</FilterExpression>
<Operator>Equal</Operator>
<FilterValues>
<FilterValue>USA</FilterValue>
</FilterValues>
</Filter>
</Filters>
<Left>0.125in</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>5.625in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>f646058b-4784-494d-a09d-27fde13a5679</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Code> Private colorPalette As String() = {"Green", "Blue", "Red",
"Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B",
"#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}
Private count As Integer = 0
Private mapping As New System.Collections.Hashtable()
Public Function GetColor(ByVal groupingValue As String) As String
If mapping.ContainsKey(groupingValue) Then
Return mapping(groupingValue)
End If
Dim c As String = colorPalette(count Mod colorPalette.Length)
count = count + 1
mapping.Add(groupingValue, c)
Return c
End Function
</Code>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="DataSet1">
<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="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Products.*, Categories.CategoryName,
Suppliers.Country
FROM Products INNER JOIN
Categories ON Products.CategoryID =Categories.CategoryID INNER JOIN
Suppliers ON Products.SupplierID =Suppliers.SupplierID</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>e5564b0f-4fab-4cf7-9cef-bda726589920</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>|||I tried the code in the article but get the following error:
There is an error on line 14 of custom code: [BC30430] 'End Function' must
be preceded by a matching 'Function'.
Build complete -- 1 errors, 0 warnings
Any ideas?
"Robert Bruckner [MSFT]" wrote:
> I suggest to follow the approach discussed in this blog article:
> http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx
> You don't need the part about the custom legend, but you should take a close
> look at the custom color part of the sample. You can modify the code so that
> instead of having the hashtable approach to apply unique colors for
> categories, you replace it with some IF statement to explicitly control
> colors.
> A trivial RDL sample with two charts using different filters but sharing the
> same colors for categories is copied to the bottom of this posting.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
> news:69E297FD-F5C7-44D4-9463-5E4871B1CEAB@.microsoft.com...
> > Hello all!!
> >
> > I have created a pie chart that is embedded in a matrix report, therefore
> > creating a new pie for every row. The problem I am having is the pie
> > slice
> > for the same series is changing each time there is a new pie. For
> > example,
> > in the first pie Cars may be designated as green but in the second pie it
> > may
> > be red. I would like the colors to be consistent; all cars are
> > represented
> > as green slices. Oh and if there are no cars don't use the color green
> > for
> > another slice.
> >
> > I think this can be done using a nested expression but I am unsure how to
> > write it...I either need an example of an expression that can handle
> > multiple slices or another way to handle the issue.
> >
> > Thanks,
> > Anthony
>
> ============================================> <?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>
> <Author>Robert M. Bruckner</Author>
> <Body>
> <ReportItems>
> <Chart Name="chart2">
> <ThreeDProperties>
> <Rotation>30</Rotation>
> <Inclination>30</Inclination>
> <Shading>Simple</Shading>
> <WallThickness>50</WallThickness>
> </ThreeDProperties>
> <ZIndex>1</ZIndex>
> <Style>
> <BackgroundColor>White</BackgroundColor>
> </Style>
> <Legend>
> <Visible>true</Visible>
> <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>
> <BackgroundColor>=Code.GetColor(Fields!CategoryName.Value)</BackgroundColor>
> </Style>
> <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>
> <Min>0</Min>
> </Axis>
> </CategoryAxis>
> <DataSetName>DataSet1</DataSetName>
> <PointWidth>0</PointWidth>
> <Type>Bar</Type>
> <Top>3.125in</Top>
> <Title>
> <Caption>UK</Caption>
> </Title>
> <Width>6in</Width>
> <SeriesGroupings>
> <SeriesGrouping>
> <DynamicSeries>
> <Grouping Name="chart2_SeriesGroup1">
> <GroupExpressions>
> <GroupExpression>=Fields!CategoryName.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields!CategoryName.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <Label>=Fields!CategoryName.Value</Label>
> </DynamicSeries>
> </SeriesGrouping>
> </SeriesGroupings>
> <Subtype>Plain</Subtype>
> <PlotArea>
> <Style>
> <BackgroundColor>LightGrey</BackgroundColor>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </PlotArea>
> <Filters>
> <Filter>
> <FilterExpression>=Fields!Country.Value</FilterExpression>
> <Operator>Equal</Operator>
> <FilterValues>
> <FilterValue>UK</FilterValue>
> </FilterValues>
> </Filter>
> </Filters>
> <Left>0.125in</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>
> <Chart Name="chart1">
> <ThreeDProperties>
> <Rotation>30</Rotation>
> <Inclination>30</Inclination>
> <Shading>Simple</Shading>
> <WallThickness>50</WallThickness>
> </ThreeDProperties>
> <Style>
> <BackgroundColor>White</BackgroundColor>
> </Style>
> <Legend>
> <Visible>true</Visible>
> <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>
> <BackgroundColor>=Code.GetColor(Fields!CategoryName.Value)</BackgroundColor>
> </Style>
> <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>
> <Min>0</Min>
> </Axis>
> </CategoryAxis>
> <DataSetName>DataSet1</DataSetName>
> <PointWidth>0</PointWidth>
> <Type>Bar</Type>
> <Top>0.25in</Top>
> <Title>
> <Caption>USA</Caption>
> </Title>
> <Width>6in</Width>
> <Height>2.5in</Height>
> <SeriesGroupings>
> <SeriesGrouping>
> <DynamicSeries>
> <Grouping Name="chart1_SeriesGroup1">
> <GroupExpressions>
> <GroupExpression>=Fields!CategoryName.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields!CategoryName.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <Label>=Fields!CategoryName.Value</Label>
> </DynamicSeries>
> </SeriesGrouping>
> </SeriesGroupings>
> <Subtype>Plain</Subtype>
> <PlotArea>
> <Style>
> <BackgroundColor>LightGrey</BackgroundColor>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </PlotArea>
> <Filters>
> <Filter>
> <FilterExpression>=Fields!Country.Value</FilterExpression>
> <Operator>Equal</Operator>
> <FilterValues>
> <FilterValue>USA</FilterValue>
> </FilterValues>
> </Filter>
> </Filters>
> <Left>0.125in</Left>
> <ValueAxis>
> <Axis>
> <Title />
> <MajorGridLines>
> <ShowGridLines>true</ShowGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MajorGridLines>|||Did you try the sample I had attached to the bottom of my previous posting,
or did you download the other sample about custom legends from the blog
article? Both should work as provided.
In your particular case, the error message would indicate that you are
missing the Function keyword for the corresponding End Function statement on
line 14 of the custom code window of the report (VS menu -> report -> report
properties -> custom code)
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
news:13337779-49F4-44A7-B0B8-4963E58D2B75@.microsoft.com...
>I tried the code in the article but get the following error:
> There is an error on line 14 of custom code: [BC30430] 'End Function' must
> be preceded by a matching 'Function'.
> Build complete -- 1 errors, 0 warnings
>
> Any ideas?
> "Robert Bruckner [MSFT]" wrote:
>> I suggest to follow the approach discussed in this blog article:
>> http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx
>> You don't need the part about the custom legend, but you should take a
>> close
>> look at the custom color part of the sample. You can modify the code so
>> that
>> instead of having the hashtable approach to apply unique colors for
>> categories, you replace it with some IF statement to explicitly control
>> colors.
>> A trivial RDL sample with two charts using different filters but sharing
>> the
>> same colors for categories is copied to the bottom of this posting.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
>> news:69E297FD-F5C7-44D4-9463-5E4871B1CEAB@.microsoft.com...
>> > Hello all!!
>> >
>> > I have created a pie chart that is embedded in a matrix report,
>> > therefore
>> > creating a new pie for every row. The problem I am having is the pie
>> > slice
>> > for the same series is changing each time there is a new pie. For
>> > example,
>> > in the first pie Cars may be designated as green but in the second pie
>> > it
>> > may
>> > be red. I would like the colors to be consistent; all cars are
>> > represented
>> > as green slices. Oh and if there are no cars don't use the color green
>> > for
>> > another slice.
>> >
>> > I think this can be done using a nested expression but I am unsure how
>> > to
>> > write it...I either need an example of an expression that can handle
>> > multiple slices or another way to handle the issue.
>> >
>> > Thanks,
>> > Anthony
>>
>> ============================================>> <?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>
>> <Author>Robert M. Bruckner</Author>
>> <Body>
>> <ReportItems>
>> <Chart Name="chart2">
>> <ThreeDProperties>
>> <Rotation>30</Rotation>
>> <Inclination>30</Inclination>
>> <Shading>Simple</Shading>
>> <WallThickness>50</WallThickness>
>> </ThreeDProperties>
>> <ZIndex>1</ZIndex>
>> <Style>
>> <BackgroundColor>White</BackgroundColor>
>> </Style>
>> <Legend>
>> <Visible>true</Visible>
>> <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>
>> <BackgroundColor>=Code.GetColor(Fields!CategoryName.Value)</BackgroundColor>
>> </Style>
>> <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>
>> <Min>0</Min>
>> </Axis>
>> </CategoryAxis>
>> <DataSetName>DataSet1</DataSetName>
>> <PointWidth>0</PointWidth>
>> <Type>Bar</Type>
>> <Top>3.125in</Top>
>> <Title>
>> <Caption>UK</Caption>
>> </Title>
>> <Width>6in</Width>
>> <SeriesGroupings>
>> <SeriesGrouping>
>> <DynamicSeries>
>> <Grouping Name="chart2_SeriesGroup1">
>> <GroupExpressions>
>> <GroupExpression>=Fields!CategoryName.Value</GroupExpression>
>> </GroupExpressions>
>> </Grouping>
>> <Sorting>
>> <SortBy>
>> <SortExpression>=Fields!CategoryName.Value</SortExpression>
>> <Direction>Ascending</Direction>
>> </SortBy>
>> </Sorting>
>> <Label>=Fields!CategoryName.Value</Label>
>> </DynamicSeries>
>> </SeriesGrouping>
>> </SeriesGroupings>
>> <Subtype>Plain</Subtype>
>> <PlotArea>
>> <Style>
>> <BackgroundColor>LightGrey</BackgroundColor>
>> <BorderStyle>
>> <Default>Solid</Default>
>> </BorderStyle>
>> </Style>
>> </PlotArea>
>> <Filters>
>> <Filter>
>> <FilterExpression>=Fields!Country.Value</FilterExpression>
>> <Operator>Equal</Operator>
>> <FilterValues>
>> <FilterValue>UK</FilterValue>
>> </FilterValues>
>> </Filter>
>> </Filters>
>> <Left>0.125in</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>
>> <Chart Name="chart1">
>> <ThreeDProperties>
>> <Rotation>30</Rotation>
>> <Inclination>30</Inclination>
>> <Shading>Simple</Shading>
>> <WallThickness>50</WallThickness>
>> </ThreeDProperties>
>> <Style>
>> <BackgroundColor>White</BackgroundColor>
>> </Style>
>> <Legend>
>> <Visible>true</Visible>
>> <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>
>> <BackgroundColor>=Code.GetColor(Fields!CategoryName.Value)</BackgroundColor>
>> </Style>
>> <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>
>> <Min>0</Min>
>> </Axis>
>> </CategoryAxis>
>> <DataSetName>DataSet1</DataSetName>
>> <PointWidth>0</PointWidth>
>> <Type>Bar</Type>
>> <Top>0.25in</Top>
>> <Title>
>> <Caption>USA</Caption>
>> </Title>
>> <Width>6in</Width>
>> <Height>2.5in</Height>
>> <SeriesGroupings>
>> <SeriesGrouping>
>> <DynamicSeries>
>> <Grouping Name="chart1_SeriesGroup1">
>> <GroupExpressions>
>> <GroupExpression>=Fields!CategoryName.Value</GroupExpression>
>> </GroupExpressions>
>> </Grouping>
>> <Sorting>
>> <SortBy>
>> <SortExpression>=Fields!CategoryName.Value</SortExpression>
>> <Direction>Ascending</Direction>
>> </SortBy>
>> </Sorting>
>> <Label>=Fields!CategoryName.Value</Label>
>> </DynamicSeries>
>> </SeriesGrouping>
>> </SeriesGroupings>
>> <Subtype>Plain</Subtype>
>> <PlotArea>
>> <Style>
>> <BackgroundColor>LightGrey</BackgroundColor>
>> <BorderStyle>
>> <Default>Solid</Default>
>> </BorderStyle>
>> </Style>
>> </PlotArea>
>> <Filters>
>> <Filter>
>> <FilterExpression>=Fields!Country.Value</FilterExpression>
>> <Operator>Equal</Operator>
>> <FilterValues>
>> <FilterValue>USA</FilterValue>
>> </FilterValues>
>> </Filter>
>> </Filters>
>> <Left>0.125in</Left>
>> <ValueAxis>
>> <Axis>
>> <Title />
>> <MajorGridLines>
>> <ShowGridLines>true</ShowGridLines>
>> <Style>
>> <BorderStyle>
>> <Default>Solid</Default>
>> </BorderStyle>
>> </Style>
>> </MajorGridLines>|||I used the code off the site I had to change the Private to Public because I
was getting errors and the only way I got them to go away was to change
Private to Public.
The code I have in the report properties code tab is as follows:
Public colorPalette As String() = {"Green", "Blue", "Red", "Orange", "Aqua",
"Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B", "#352F26", "#F1E7D6",
"#E16C56", "#CFBA9B"}
Public count As Integer = 0
Public mapping As New System.Collections.Hashtable()
Public Function GetColor(ByVal groupingValue As String) As String
If mapping.ContainsKey(groupingValue) Then
Return mapping(groupingValue)
End If
Dim c As String = colorPalette(count Mod colorPalette.Length)
count = count + 1
mapping.Add(groupingValue, c)
Return c
End Function
The Data field for my chart is: YTD
The Series field is: Project_Category
for the Data field properties on my Pie chart I went to the apperance tab
then series style... on the fill tab, color field I put the following FX:
=Code.GetColor(Fields!Project_Category.Value)
Any ideas what I am missing?
I also tried the following code that someone modified off your code example
with no luck either...
In Chart Properties, Data, Values, Edit, Appearance, Series Style I use this
function:
= Code.GetColor(Fields!Project_Category.Value)
And in the code for the report I have:
Public Function GetColor(ByVal Project_Category As String) As String
if Project_Category = "Value 1" Then
Return "#CC3333" ' "Red"
end if
if Project_Category = "Value 2" Then
Return "Pink"
end if
if Project_Category = "Value 3" Then
Return "#3300CC" ' "Blue"
end if
if (Project_Category <> "Value 1") and (Project_Category <> "Value 2") and
(Project_Category
<> "Project_Category") Then
Return "Black"
end if
End Function
again no luck with this one.....
THANKS AGAIN FOR ALL THE HELP!!!
"Robert Bruckner [MSFT]" wrote:
> Did you try the sample I had attached to the bottom of my previous posting,
> or did you download the other sample about custom legends from the blog
> article? Both should work as provided.
> In your particular case, the error message would indicate that you are
> missing the Function keyword for the corresponding End Function statement on
> line 14 of the custom code window of the report (VS menu -> report -> report
> properties -> custom code)
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
> news:13337779-49F4-44A7-B0B8-4963E58D2B75@.microsoft.com...
> >I tried the code in the article but get the following error:
> >
> > There is an error on line 14 of custom code: [BC30430] 'End Function' must
> > be preceded by a matching 'Function'.
> > Build complete -- 1 errors, 0 warnings
> >
> >
> > Any ideas?
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> I suggest to follow the approach discussed in this blog article:
> >> http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx
> >>
> >> You don't need the part about the custom legend, but you should take a
> >> close
> >> look at the custom color part of the sample. You can modify the code so
> >> that
> >> instead of having the hashtable approach to apply unique colors for
> >> categories, you replace it with some IF statement to explicitly control
> >> colors.
> >>
> >> A trivial RDL sample with two charts using different filters but sharing
> >> the
> >> same colors for categories is copied to the bottom of this posting.
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
> >> news:69E297FD-F5C7-44D4-9463-5E4871B1CEAB@.microsoft.com...
> >> > Hello all!!
> >> >
> >> > I have created a pie chart that is embedded in a matrix report,
> >> > therefore
> >> > creating a new pie for every row. The problem I am having is the pie
> >> > slice
> >> > for the same series is changing each time there is a new pie. For
> >> > example,
> >> > in the first pie Cars may be designated as green but in the second pie
> >> > it
> >> > may
> >> > be red. I would like the colors to be consistent; all cars are
> >> > represented
> >> > as green slices. Oh and if there are no cars don't use the color green
> >> > for
> >> > another slice.
> >> >
> >> > I think this can be done using a nested expression but I am unsure how
> >> > to
> >> > write it...I either need an example of an expression that can handle
> >> > multiple slices or another way to handle the issue.
> >> >
> >> > Thanks,
> >> > Anthony
> >>
> >>
> >> ============================================> >>
> >> <?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>
> >> <Author>Robert M. Bruckner</Author>
> >> <Body>
> >> <ReportItems>
> >> <Chart Name="chart2">
> >> <ThreeDProperties>
> >> <Rotation>30</Rotation>
> >> <Inclination>30</Inclination>
> >> <Shading>Simple</Shading>
> >> <WallThickness>50</WallThickness>
> >> </ThreeDProperties>
> >> <ZIndex>1</ZIndex>
> >> <Style>
> >> <BackgroundColor>White</BackgroundColor>
> >> </Style>
> >> <Legend>
> >> <Visible>true</Visible>
> >> <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>
> >>
> >> <BackgroundColor>=Code.GetColor(Fields!CategoryName.Value)</BackgroundColor>
> >> </Style>
> >> <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>
> >> <Min>0</Min>
> >> </Axis>
> >> </CategoryAxis>
> >> <DataSetName>DataSet1</DataSetName>
> >> <PointWidth>0</PointWidth>
> >> <Type>Bar</Type>
> >> <Top>3.125in</Top>
> >> <Title>
> >> <Caption>UK</Caption>
> >> </Title>
> >> <Width>6in</Width>
> >> <SeriesGroupings>
> >> <SeriesGrouping>
> >> <DynamicSeries>
> >> <Grouping Name="chart2_SeriesGroup1">
> >> <GroupExpressions>
> >>
> >> <GroupExpression>=Fields!CategoryName.Value</GroupExpression>
> >> </GroupExpressions>
> >> </Grouping>
> >> <Sorting>
> >> <SortBy>
> >>
> >> <SortExpression>=Fields!CategoryName.Value</SortExpression>
> >> <Direction>Ascending</Direction>
> >> </SortBy>
> >> </Sorting>
> >> <Label>=Fields!CategoryName.Value</Label>
> >> </DynamicSeries>
> >> </SeriesGrouping>
> >> </SeriesGroupings>
> >> <Subtype>Plain</Subtype>
> >> <PlotArea>
> >> <Style>
> >> <BackgroundColor>LightGrey</BackgroundColor>
> >> <BorderStyle>
> >> <Default>Solid</Default>
> >> </BorderStyle>
> >> </Style>
> >> </PlotArea>
> >> <Filters>
> >> <Filter>
> >> <FilterExpression>=Fields!Country.Value</FilterExpression>
> >> <Operator>Equal</Operator>
> >> <FilterValues>
> >> <FilterValue>UK</FilterValue>
> >> </FilterValues>
> >> </Filter>
> >> </Filters>
> >> <Left>0.125in</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>
> >> <Chart Name="chart1">
> >> <ThreeDProperties>
> >> <Rotation>30</Rotation>
> >> <Inclination>30</Inclination>
> >> <Shading>Simple</Shading>
> >> <WallThickness>50</WallThickness>
> >> </ThreeDProperties>
> >> <Style>
> >> <BackgroundColor>White</BackgroundColor>
> >> </Style>
> >> <Legend>
> >> <Visible>true</Visible>
> >> <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>
> >>
> >> <BackgroundColor>=Code.GetColor(Fields!CategoryName.Value)</BackgroundColor>
> >> </Style>
> >> <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>
> >> <Min>0</Min>
> >> </Axis>
> >> </CategoryAxis>
> >> <DataSetName>DataSet1</DataSetName>
> >> <PointWidth>0</PointWidth>
> >> <Type>Bar</Type>
> >> <Top>0.25in</Top>
> >> <Title>
> >> <Caption>USA</Caption>
> >> </Title>
> >> <Width>6in</Width>
> >> <Height>2.5in</Height>
> >> <SeriesGroupings>
> >> <SeriesGrouping>
> >> <DynamicSeries>
> >> <Grouping Name="chart1_SeriesGroup1">
> >> <GroupExpressions>
> >>
> >> <GroupExpression>=Fields!CategoryName.Value</GroupExpression>
> >> </GroupExpressions>
> >> </Grouping>
> >> <Sorting>|||Robert,
I found the problem with the code that I got from your blog article....in
all the cutting and pasting I ended up with a second end function that I
needed to scroll down to see.
Good thing is that the code is working...bad thing is I am getting the same
results. What I am trying to do is almost like conditional formatting. If
you return a specific value I want to return a specific color, the next value
will have another color assigned, and so on. All in all I should have no
more than 5-6 values that I need to define colors for.
I tried the following code which I think is more like what I am wanting:
In Chart Properties, Data, Values, Edit, Appearance, Series Style I use this
function:
= Code.GetColor(Fields!Project_Category.Value)
And in the code for the report I have:
Public Function GetColor(ByVal Project_Category As String) As String
if Project_Category = "Value 1" Then
Return "#CC3333" ' "Red"
end if
if Project_Category = "Value 2" Then
Return "Pink"
end if
if Project_Category = "Value 3" Then
Return "#3300CC" ' "Blue"
end if
if (Project_Category <> "Value 1") and (Project_Category <> "Value 2") and
(Project_Category
<> "Value 3") Then
Return "Black"
end if
End Function
However, as I stated below I can't seem to get it to work because of the
following error:
There is an error on line 10 of custom code: [BC30201] Expression expected.
Build complete -- 1 errors, 0 warnings
I also thought of writing an IIF but I think the nesting could get pretty
complex....
"anthonysjo" wrote:
> I used the code off the site I had to change the Private to Public because I
> was getting errors and the only way I got them to go away was to change
> Private to Public.
> The code I have in the report properties code tab is as follows:
> Public colorPalette As String() = {"Green", "Blue", "Red", "Orange", "Aqua",
> "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B", "#352F26", "#F1E7D6",
> "#E16C56", "#CFBA9B"}
> Public count As Integer = 0
> Public mapping As New System.Collections.Hashtable()
> Public Function GetColor(ByVal groupingValue As String) As String
> If mapping.ContainsKey(groupingValue) Then
> Return mapping(groupingValue)
> End If
> Dim c As String = colorPalette(count Mod colorPalette.Length)
> count = count + 1
> mapping.Add(groupingValue, c)
> Return c
> End Function
> The Data field for my chart is: YTD
> The Series field is: Project_Category
> for the Data field properties on my Pie chart I went to the apperance tab
> then series style... on the fill tab, color field I put the following FX:
> =Code.GetColor(Fields!Project_Category.Value)
> Any ideas what I am missing?
> I also tried the following code that someone modified off your code example
> with no luck either...
> In Chart Properties, Data, Values, Edit, Appearance, Series Style I use this
> function:
> = Code.GetColor(Fields!Project_Category.Value)
> And in the code for the report I have:
> Public Function GetColor(ByVal Project_Category As String) As String
> if Project_Category = "Value 1" Then
> Return "#CC3333" ' "Red"
> end if
> if Project_Category = "Value 2" Then
> Return "Pink"
> end if
> if Project_Category = "Value 3" Then
> Return "#3300CC" ' "Blue"
> end if
> if (Project_Category <> "Value 1") and (Project_Category <> "Value 2") and
> (Project_Category
> <> "Project_Category") Then
> Return "Black"
> end if
> End Function
> again no luck with this one.....
> THANKS AGAIN FOR ALL THE HELP!!!
> "Robert Bruckner [MSFT]" wrote:
> > Did you try the sample I had attached to the bottom of my previous posting,
> > or did you download the other sample about custom legends from the blog
> > article? Both should work as provided.
> >
> > In your particular case, the error message would indicate that you are
> > missing the Function keyword for the corresponding End Function statement on
> > line 14 of the custom code window of the report (VS menu -> report -> report
> > properties -> custom code)
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> >
> > "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
> > news:13337779-49F4-44A7-B0B8-4963E58D2B75@.microsoft.com...
> > >I tried the code in the article but get the following error:
> > >
> > > There is an error on line 14 of custom code: [BC30430] 'End Function' must
> > > be preceded by a matching 'Function'.
> > > Build complete -- 1 errors, 0 warnings
> > >
> > >
> > > Any ideas?
> > >
> > > "Robert Bruckner [MSFT]" wrote:
> > >
> > >> I suggest to follow the approach discussed in this blog article:
> > >> http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx
> > >>
> > >> You don't need the part about the custom legend, but you should take a
> > >> close
> > >> look at the custom color part of the sample. You can modify the code so
> > >> that
> > >> instead of having the hashtable approach to apply unique colors for
> > >> categories, you replace it with some IF statement to explicitly control
> > >> colors.
> > >>
> > >> A trivial RDL sample with two charts using different filters but sharing
> > >> the
> > >> same colors for categories is copied to the bottom of this posting.
> > >>
> > >> -- Robert
> > >> This posting is provided "AS IS" with no warranties, and confers no
> > >> rights.
> > >>
> > >>
> > >> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
> > >> news:69E297FD-F5C7-44D4-9463-5E4871B1CEAB@.microsoft.com...
> > >> > Hello all!!
> > >> >
> > >> > I have created a pie chart that is embedded in a matrix report,
> > >> > therefore
> > >> > creating a new pie for every row. The problem I am having is the pie
> > >> > slice
> > >> > for the same series is changing each time there is a new pie. For
> > >> > example,
> > >> > in the first pie Cars may be designated as green but in the second pie
> > >> > it
> > >> > may
> > >> > be red. I would like the colors to be consistent; all cars are
> > >> > represented
> > >> > as green slices. Oh and if there are no cars don't use the color green
> > >> > for
> > >> > another slice.
> > >> >
> > >> > I think this can be done using a nested expression but I am unsure how
> > >> > to
> > >> > write it...I either need an example of an expression that can handle
> > >> > multiple slices or another way to handle the issue.
> > >> >
> > >> > Thanks,
> > >> > Anthony
> > >>
> > >>
> > >> ============================================> > >>
> > >> <?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>
> > >> <Author>Robert M. Bruckner</Author>
> > >> <Body>
> > >> <ReportItems>
> > >> <Chart Name="chart2">
> > >> <ThreeDProperties>
> > >> <Rotation>30</Rotation>
> > >> <Inclination>30</Inclination>
> > >> <Shading>Simple</Shading>
> > >> <WallThickness>50</WallThickness>
> > >> </ThreeDProperties>
> > >> <ZIndex>1</ZIndex>
> > >> <Style>
> > >> <BackgroundColor>White</BackgroundColor>
> > >> </Style>
> > >> <Legend>
> > >> <Visible>true</Visible>
> > >> <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>
> > >>
> > >> <BackgroundColor>=Code.GetColor(Fields!CategoryName.Value)</BackgroundColor>
> > >> </Style>
> > >> <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>
> > >> <Min>0</Min>
> > >> </Axis>
> > >> </CategoryAxis>
> > >> <DataSetName>DataSet1</DataSetName>
> > >> <PointWidth>0</PointWidth>
> > >> <Type>Bar</Type>
> > >> <Top>3.125in</Top>
> > >> <Title>
> > >> <Caption>UK</Caption>
> > >> </Title>
> > >> <Width>6in</Width>
> > >> <SeriesGroupings>
> > >> <SeriesGrouping>
> > >> <DynamicSeries>
> > >> <Grouping Name="chart2_SeriesGroup1">
> > >> <GroupExpressions>
> > >>
> > >> <GroupExpression>=Fields!CategoryName.Value</GroupExpression>
> > >> </GroupExpressions>
> > >> </Grouping>
> > >> <Sorting>
> > >> <SortBy>
> > >>
> > >> <SortExpression>=Fields!CategoryName.Value</SortExpression>
> > >> <Direction>Ascending</Direction>
> > >> </SortBy>
> > >> </Sorting>
> > >> <Label>=Fields!CategoryName.Value</Label>
> > >> </DynamicSeries>
> > >> </SeriesGrouping>
> > >> </SeriesGroupings>
> > >> <Subtype>Plain</Subtype>
> > >> <PlotArea>
> > >> <Style>
> > >> <BackgroundColor>LightGrey</BackgroundColor>
> > >> <BorderStyle>
> > >> <Default>Solid</Default>
> > >> </BorderStyle>
> > >> </Style>
> > >> </PlotArea>
> > >> <Filters>
> > >> <Filter>
> > >> <FilterExpression>=Fields!Country.Value</FilterExpression>
> > >> <Operator>Equal</Operator>
> > >> <FilterValues>
> > >> <FilterValue>UK</FilterValue>
> > >> </FilterValues>
> > >> </Filter>
> > >> </Filters>
> > >> <Left>0.125in</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>
> > >> <Chart Name="chart1">
> > >> <ThreeDProperties>
> > >> <Rotation>30</Rotation>
> > >> <Inclination>30</Inclination>
> > >> <Shading>Simple</Shading>
> > >> <WallThickness>50</WallThickness>
> > >> </ThreeDProperties>
> > >> <Style>
> > >> <BackgroundColor>White</BackgroundColor>
> > >> </Style>
> > >> <Legend>
> > >> <Visible>true</Visible>
> > >> <Style>
> > >> <BorderStyle>
> > >> <Default>Solid</Default>
> > >> </BorderStyle>
> > >> </Style>|||All is well!!! I took the following code:
= Code.GetColor(Fields!Project_Category.Value)
> And in the code for the report I have:
> Public Function GetColor(ByVal Project_Category As String) As String
> if Project_Category = "Value 1" Then
> Return "#CC3333" ' "Red"
> end if
> if Project_Category = "Value 2" Then
> Return "Pink"
> end if
> if Project_Category = "Value 3" Then
> Return "#3300CC" ' "Blue"
> end if
> if (Project_Category <> "Value 1") and (Project_Category <> "Value 2") and
> (Project_Category
> <> "Value 3") Then
> Return "Black"
> end if
> End Function
Then removed this....
if (Project_Category <> "Value 1") and (Project_Category <> "Value 2") and
> (Project_Category
> <> "Value 3") Then
> Return "Black"
> end if
Everything works great now!!!!
Thanks for all the help!!
"anthonysjo" wrote:
> Robert,
> I found the problem with the code that I got from your blog article....in
> all the cutting and pasting I ended up with a second end function that I
> needed to scroll down to see.
> Good thing is that the code is working...bad thing is I am getting the same
> results. What I am trying to do is almost like conditional formatting. If
> you return a specific value I want to return a specific color, the next value
> will have another color assigned, and so on. All in all I should have no
> more than 5-6 values that I need to define colors for.
> I tried the following code which I think is more like what I am wanting:
> In Chart Properties, Data, Values, Edit, Appearance, Series Style I use this
> function:
> = Code.GetColor(Fields!Project_Category.Value)
> And in the code for the report I have:
> Public Function GetColor(ByVal Project_Category As String) As String
> if Project_Category = "Value 1" Then
> Return "#CC3333" ' "Red"
> end if
> if Project_Category = "Value 2" Then
> Return "Pink"
> end if
> if Project_Category = "Value 3" Then
> Return "#3300CC" ' "Blue"
> end if
> if (Project_Category <> "Value 1") and (Project_Category <> "Value 2") and
> (Project_Category
> <> "Value 3") Then
> Return "Black"
> end if
> End Function
> However, as I stated below I can't seem to get it to work because of the
> following error:
> There is an error on line 10 of custom code: [BC30201] Expression expected.
> Build complete -- 1 errors, 0 warnings
> I also thought of writing an IIF but I think the nesting could get pretty
> complex....
> "anthonysjo" wrote:
> > I used the code off the site I had to change the Private to Public because I
> > was getting errors and the only way I got them to go away was to change
> > Private to Public.
> >
> > The code I have in the report properties code tab is as follows:
> > Public colorPalette As String() = {"Green", "Blue", "Red", "Orange", "Aqua",
> > "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B", "#352F26", "#F1E7D6",
> > "#E16C56", "#CFBA9B"}
> > Public count As Integer = 0
> > Public mapping As New System.Collections.Hashtable()
> >
> > Public Function GetColor(ByVal groupingValue As String) As String
> > If mapping.ContainsKey(groupingValue) Then
> > Return mapping(groupingValue)
> > End If
> > Dim c As String = colorPalette(count Mod colorPalette.Length)
> > count = count + 1
> > mapping.Add(groupingValue, c)
> > Return c
> > End Function
> >
> > The Data field for my chart is: YTD
> > The Series field is: Project_Category
> >
> > for the Data field properties on my Pie chart I went to the apperance tab
> > then series style... on the fill tab, color field I put the following FX:
> > =Code.GetColor(Fields!Project_Category.Value)
> >
> > Any ideas what I am missing?
> >
> > I also tried the following code that someone modified off your code example
> > with no luck either...
> >
> > In Chart Properties, Data, Values, Edit, Appearance, Series Style I use this
> > function:
> >
> > = Code.GetColor(Fields!Project_Category.Value)
> >
> > And in the code for the report I have:
> >
> > Public Function GetColor(ByVal Project_Category As String) As String
> > if Project_Category = "Value 1" Then
> > Return "#CC3333" ' "Red"
> > end if
> > if Project_Category = "Value 2" Then
> > Return "Pink"
> > end if
> > if Project_Category = "Value 3" Then
> > Return "#3300CC" ' "Blue"
> > end if
> > if (Project_Category <> "Value 1") and (Project_Category <> "Value 2") and
> > (Project_Category
> > <> "Project_Category") Then
> > Return "Black"
> > end if
> > End Function
> >
> > again no luck with this one.....
> >
> > THANKS AGAIN FOR ALL THE HELP!!!
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > Did you try the sample I had attached to the bottom of my previous posting,
> > > or did you download the other sample about custom legends from the blog
> > > article? Both should work as provided.
> > >
> > > In your particular case, the error message would indicate that you are
> > > missing the Function keyword for the corresponding End Function statement on
> > > line 14 of the custom code window of the report (VS menu -> report -> report
> > > properties -> custom code)
> > >
> > > -- Robert
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > >
> > >
> > > "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
> > > news:13337779-49F4-44A7-B0B8-4963E58D2B75@.microsoft.com...
> > > >I tried the code in the article but get the following error:
> > > >
> > > > There is an error on line 14 of custom code: [BC30430] 'End Function' must
> > > > be preceded by a matching 'Function'.
> > > > Build complete -- 1 errors, 0 warnings
> > > >
> > > >
> > > > Any ideas?
> > > >
> > > > "Robert Bruckner [MSFT]" wrote:
> > > >
> > > >> I suggest to follow the approach discussed in this blog article:
> > > >> http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx
> > > >>
> > > >> You don't need the part about the custom legend, but you should take a
> > > >> close
> > > >> look at the custom color part of the sample. You can modify the code so
> > > >> that
> > > >> instead of having the hashtable approach to apply unique colors for
> > > >> categories, you replace it with some IF statement to explicitly control
> > > >> colors.
> > > >>
> > > >> A trivial RDL sample with two charts using different filters but sharing
> > > >> the
> > > >> same colors for categories is copied to the bottom of this posting.
> > > >>
> > > >> -- Robert
> > > >> This posting is provided "AS IS" with no warranties, and confers no
> > > >> rights.
> > > >>
> > > >>
> > > >> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
> > > >> news:69E297FD-F5C7-44D4-9463-5E4871B1CEAB@.microsoft.com...
> > > >> > Hello all!!
> > > >> >
> > > >> > I have created a pie chart that is embedded in a matrix report,
> > > >> > therefore
> > > >> > creating a new pie for every row. The problem I am having is the pie
> > > >> > slice
> > > >> > for the same series is changing each time there is a new pie. For
> > > >> > example,
> > > >> > in the first pie Cars may be designated as green but in the second pie
> > > >> > it
> > > >> > may
> > > >> > be red. I would like the colors to be consistent; all cars are
> > > >> > represented
> > > >> > as green slices. Oh and if there are no cars don't use the color green
> > > >> > for
> > > >> > another slice.
> > > >> >
> > > >> > I think this can be done using a nested expression but I am unsure how
> > > >> > to
> > > >> > write it...I either need an example of an expression that can handle
> > > >> > multiple slices or another way to handle the issue.
> > > >> >
> > > >> > Thanks,
> > > >> > Anthony
> > > >>
> > > >>
> > > >> ============================================> > > >>
> > > >> <?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>
> > > >> <Author>Robert M. Bruckner</Author>
> > > >> <Body>
> > > >> <ReportItems>
> > > >> <Chart Name="chart2">
> > > >> <ThreeDProperties>
> > > >> <Rotation>30</Rotation>
> > > >> <Inclination>30</Inclination>
> > > >> <Shading>Simple</Shading>
> > > >> <WallThickness>50</WallThickness>
> > > >> </ThreeDProperties>
> > > >> <ZIndex>1</ZIndex>
> > > >> <Style>
> > > >> <BackgroundColor>White</BackgroundColor>
> > > >> </Style>
> > > >> <Legend>
> > > >> <Visible>true</Visible>
> > > >> <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>
> > > >>
> > > >> <BackgroundColor>=Code.GetColor(Fields!CategoryName.Value)</BackgroundColor>
> > > >> </Style>
> > > >> <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>
> > > >> <Min>0</Min>
> > > >> </Axis>
> > > >> </CategoryAxis>
> > > >> <DataSetName>DataSet1</DataSetName>
> > > >> <PointWidth>0</PointWidth>
> > > >> <Type>Bar</Type>
> > > >> <Top>3.125in</Top>
> > > >> <Title>
> > > >> <Caption>UK</Caption>
> > > >> </Title>
> > > >> <Width>6in</Width>
> > > >> <SeriesGroupings>
> > > >> <SeriesGrouping>
> > > >> <DynamicSeries>
> > > >> <Grouping Name="chart2_SeriesGroup1">
> > > >> <GroupExpressions>
> > > >>
> > > >> <GroupExpression>=Fields!CategoryName.Value</GroupExpression>
> > > >> </GroupExpressions>
> > > >> </Grouping>
> > > >> <Sorting>
> > > >> <SortBy>
> > > >>
> > > >> <SortExpression>=Fields!CategoryName.Value</SortExpression>
> > > >> <Direction>Ascending</Direction>
> > > >> </SortBy>
> > > >> </Sorting>
> > > >> <Label>=Fields!CategoryName.Value</Label>
> > > >> </DynamicSeries>
> > > >> </SeriesGrouping>
> > > >> </SeriesGroupings>
> > > >> <Subtype>Plain</Subtype>
> > > >> <PlotArea>
> > > >> <Style>
> > > >> <BackgroundColor>LightGrey</BackgroundColor>
> > > >> <BorderStyle>
> > > >> <Default>Solid</Default>
> > > >> </BorderStyle>
> > > >> </Style>
> > > >> </PlotArea>
> > > >> <Filters>
> > > >> <Filter>
> > > >> <FilterExpression>=Fields!Country.Value</FilterExpression>
> > > >> <Operator>Equal</Operator>
