Hi,
I am creating a view in the following way
CREATE VIEW TableView
AS
SELECT OriginalTable.*
FROM OriginalTable
WHERE ChangedFlag is null
UNION ALL
SELECT TableWithChanges.*
FROM TableWithChanges
OriginalTable and TableWithChanges have identical schemas. The data is in
OriginalTable and queries are run against it. However when I want to analyze
the query output with some changes I put the changed rows in
TableWithChanges and now run the queries against TableView. New and changed
rows are in TableWithChanges. Deletions and changes in OriginalTable are
handled by setting ChangedFlag = 1.
This way my earlier queries can keep running against OriginalTable and my
simulations can run against TableView.
The queries I run involve joins between many such tables, and sometimes self
joins too.
Problem: Performance is severely hit when I run queries against the view.
Specifically I observe that SQL Server does lot of processor intensive
activity. A query that was completing in 5 secs now is running for about 30
minutes (and has not completed yet). SQL Server is consuming close to 100%
CPU all this while.
Is there a better way in which I can combine the two data sets without
affecting performance to such an extent?
Please help me out here.
Thanks,
NitinPartitioned view is what you want. Though, you must follow its strict
guideline in order to get the benefits.
http://msdn.microsoft.com/library/e...des_06_17zr.asp
-oj
"Nitin M" <nitin@.nowhere.com> wrote in message
news:e7AWpMovFHA.4032@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am creating a view in the following way
> CREATE VIEW TableView
> AS
> SELECT OriginalTable.*
> FROM OriginalTable
> WHERE ChangedFlag is null
> UNION ALL
> SELECT TableWithChanges.*
> FROM TableWithChanges
> OriginalTable and TableWithChanges have identical schemas. The data is in
> OriginalTable and queries are run against it. However when I want to
> analyze the query output with some changes I put the changed rows in
> TableWithChanges and now run the queries against TableView. New and
> changed rows are in TableWithChanges. Deletions and changes in
> OriginalTable are handled by setting ChangedFlag = 1.
> This way my earlier queries can keep running against OriginalTable and my
> simulations can run against TableView.
> The queries I run involve joins between many such tables, and sometimes
> self joins too.
> Problem: Performance is severely hit when I run queries against the view.
> Specifically I observe that SQL Server does lot of processor intensive
> activity. A query that was completing in 5 secs now is running for about
> 30 minutes (and has not completed yet). SQL Server is consuming close to
> 100% CPU all this while.
> Is there a better way in which I can combine the two data sets without
> affecting performance to such an extent?
> Please help me out here.
> Thanks,
> Nitin
>|||Thanks OJ,
I have a explicit where clause [WHERE ChangedFlag is null] instead of the
check constraint. Will I give some better performance if I use check
constraints instead.
Also ChangedFlag column in this case is not a primary key column.
Is there any other trick to combine data?
Thanks,
Nitin
"oj" <nospam_ojngo@.home.com> wrote in message
news:eXBArSovFHA.2556@.TK2MSFTNGP15.phx.gbl...
> Partitioned view is what you want. Though, you must follow its strict
> guideline in order to get the benefits.
> http://msdn.microsoft.com/library/e...des_06_17zr.asp
>
> --
> -oj
>
> "Nitin M" <nitin@.nowhere.com> wrote in message
> news:e7AWpMovFHA.4032@.TK2MSFTNGP15.phx.gbl...
>|||If you don't follow the guideline, you don't have a partitioned view. Thus,
sqlserver *will* be forced to scan every single table in your view
definition. PV is the trick to combine data.
-oj
"Nitin M" <nitin@.nowhere.com> wrote in message
news:OpuTpcovFHA.1996@.TK2MSFTNGP10.phx.gbl...
> Thanks OJ,
> I have a explicit where clause [WHERE ChangedFlag is null] instead of the
> check constraint. Will I give some better performance if I use check
> constraints instead.
> Also ChangedFlag column in this case is not a primary key column.
> Is there any other trick to combine data?
> Thanks,
> Nitin
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:eXBArSovFHA.2556@.TK2MSFTNGP15.phx.gbl...
>|||Why did you mimic a 1950's magnetic tape file generational system in
SQL? Are yoiu really using flags in a RDBMS, as if you were writing
assembly language code?
Go back to the basics; same schema means same entity in an RDBMS. Your
data model has split a set over two tables when you should have had
only one. My guess woild be that you need to show a history, whcih
means that you will have a (start_time, end_time) pair in the table and
will get the current status by looking at (end_time IS NULL).|||Hi Celko,
I have a system in which the original tables are being used in a zillion
places. The system does some analysis using queries. Now there is a need to
do the same analysis with changes to original data, a simulation or a "what
if the data changes" sort of analysis.
In this situation if I want to keep two 'avtars' of a row in the same table
I will to think about what to do with the queries that already exist.
And all this while I do not want to disturb any reports etc. which are
accessing the original data. I want the simulation and the existing stuff to
run simulataneously.
Do tell me if there is a better way out.
Thanks,
Nitin
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127294968.322772.46020@.g49g2000cwa.googlegroups.com...
> Why did you mimic a 1950's magnetic tape file generational system in
> SQL? Are yoiu really using flags in a RDBMS, as if you were writing
> assembly language code?
>
> Go back to the basics; same schema means same entity in an RDBMS. Your
> data model has split a set over two tables when you should have had
> only one. My guess woild be that you need to show a history, whcih
> means that you will have a (start_time, end_time) pair in the table and
> will get the current status by looking at (end_time IS NULL).
>|||"Nitin M" <nitin@.nowhere.com> wrote in message
news:%23LDbZuqvFHA.2516@.TK2MSFTNGP12.phx.gbl...
> Hi Celko,
> I have a system in which the original tables are being used in a zillion
> places. The system does some analysis using queries. Now there is a need
> to do the same analysis with changes to original data, a simulation or a
> "what if the data changes" sort of analysis.
> In this situation if I want to keep two 'avtars' of a row in the same
> table I will to think about what to do with the queries that already
> exist.
> And all this while I do not want to disturb any reports etc. which are
> accessing the original data. I want the simulation and the existing stuff
> to run simulataneously.
> Do tell me if there is a better way out.
> Thanks,
> Nitin
Why not do the simulation on a copy of the database?|||<Why not do the simulation on a copy of the database?>
Wont this take lot of extra time and space too?
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:uQCI7%23qvFHA.2728@.TK2MSFTNGP14.phx.gbl...
> "Nitin M" <nitin@.nowhere.com> wrote in message
> news:%23LDbZuqvFHA.2516@.TK2MSFTNGP12.phx.gbl...
> Why not do the simulation on a copy of the database?
>|||Space is cheap.
I don't understand the extra time comment.
Some compagnies even do reporting on a database copy.
Sure, the data isn't up to date as this depends on the backup frequency.
"Nitin M" <nitin@.nowhere.com> wrote in message
news:%23yk$hIrvFHA.4020@.TK2MSFTNGP10.phx.gbl...
> <Why not do the simulation on a copy of the database?>
> Wont this take lot of extra time and space too?
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:uQCI7%23qvFHA.2728@.TK2MSFTNGP14.phx.gbl...
>
Showing posts with label creating. Show all posts
Showing posts with label creating. Show all posts
Tuesday, March 27, 2012
Thursday, March 8, 2012
columns
I have a report I am creating that needs to look similar to a newspaper. I
want it to flow down a column then at the end of the page I want it to jump
to the top and start population the next column until I have three columns
total then start all over on the second page in the first column...Is this
possible?OK, I have figured out how to have multiple columns. But it shows on the
design view that there are multple columns, so why wont it jump to the
second column when the report is ran?
"Ben Watts" <lunuticshouse@.hotmail.com> wrote in message
news:eBgRwRgiIHA.1188@.TK2MSFTNGP04.phx.gbl...
>I have a report I am creating that needs to look similar to a newspaper. I
>want it to flow down a column then at the end of the page I want it to jump
>to the top and start population the next column until I have three columns
>total then start all over on the second page in the first column...Is this
>possible?
>
want it to flow down a column then at the end of the page I want it to jump
to the top and start population the next column until I have three columns
total then start all over on the second page in the first column...Is this
possible?OK, I have figured out how to have multiple columns. But it shows on the
design view that there are multple columns, so why wont it jump to the
second column when the report is ran?
"Ben Watts" <lunuticshouse@.hotmail.com> wrote in message
news:eBgRwRgiIHA.1188@.TK2MSFTNGP04.phx.gbl...
>I have a report I am creating that needs to look similar to a newspaper. I
>want it to flow down a column then at the end of the page I want it to jump
>to the top and start population the next column until I have three columns
>total then start all over on the second page in the first column...Is this
>possible?
>
Saturday, February 25, 2012
Column Order in Matrix
Using SRS 2000.
When creating a matrix report, can you order the columns? By default, they
are in alpha order. My columns are the months of the year. Using Order By
in the query doesn't work.
I want January, February..., not April, August...If you have an actual DateTime field in the dataset, you could just add a
sort expression on the matrix column grouping like
=Month(Fields!SalesDate.Value)
If the column groupings are just based on a string field that actually
contains the month names and there are no other fields that would provide
full datetime information, you could create some "faked" datetime object
based on your month string and then use a sorting expression like
=Month(DateValue(Fields!MonthNameString.Value & " 1, 2005"))
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctmonth.asp
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctDateValue.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"del" <del@.discussions.microsoft.com> wrote in message
news:0CFB34D1-4ACA-4418-A0F6-F5278AFE48AF@.microsoft.com...
> Using SRS 2000.
> When creating a matrix report, can you order the columns? By default,
> they
> are in alpha order. My columns are the months of the year. Using Order
> By
> in the query doesn't work.
> I want January, February..., not April, August...|||Thanks for that. That converts the string to the month number, but it
doesn't sort them 1,2,3... It's still in the same order, but with numbers.
I thought I was being smart by converting in the query using datepart and
datename.
I will look through the info you gave.
"Robert Bruckner [MSFT]" wrote:
> If you have an actual DateTime field in the dataset, you could just add a
> sort expression on the matrix column grouping like
> =Month(Fields!SalesDate.Value)
> If the column groupings are just based on a string field that actually
> contains the month names and there are no other fields that would provide
> full datetime information, you could create some "faked" datetime object
> based on your month string and then use a sorting expression like
> =Month(DateValue(Fields!MonthNameString.Value & " 1, 2005"))
> See also:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctmonth.asp
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctDateValue.asp
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "del" <del@.discussions.microsoft.com> wrote in message
> news:0CFB34D1-4ACA-4418-A0F6-F5278AFE48AF@.microsoft.com...
> > Using SRS 2000.
> >
> > When creating a matrix report, can you order the columns? By default,
> > they
> > are in alpha order. My columns are the months of the year. Using Order
> > By
> > in the query doesn't work.
> >
> > I want January, February..., not April, August...
>
>|||Here's what I did:
In the query, I used date(mm,field) to convert the date in to a month. Then
In the matrix report, I sorted by order and used the monthname function. So
after it was sorted numerically, I used the expression,
=monthname(Field!...Value) and it returns the name of the month.
I would have never looked if you hadn't pointed me in the right direction.
Thanks!
"del" wrote:
> Thanks for that. That converts the string to the month number, but it
> doesn't sort them 1,2,3... It's still in the same order, but with numbers.
> I thought I was being smart by converting in the query using datepart and
> datename.
> I will look through the info you gave.
> "Robert Bruckner [MSFT]" wrote:
> > If you have an actual DateTime field in the dataset, you could just add a
> > sort expression on the matrix column grouping like
> > =Month(Fields!SalesDate.Value)
> >
> > If the column groupings are just based on a string field that actually
> > contains the month names and there are no other fields that would provide
> > full datetime information, you could create some "faked" datetime object
> > based on your month string and then use a sorting expression like
> > =Month(DateValue(Fields!MonthNameString.Value & " 1, 2005"))
> > See also:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctmonth.asp
> > http://msdn.microsoft.com/library/en-us/vblr7/html/vafctDateValue.asp
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "del" <del@.discussions.microsoft.com> wrote in message
> > news:0CFB34D1-4ACA-4418-A0F6-F5278AFE48AF@.microsoft.com...
> > > Using SRS 2000.
> > >
> > > When creating a matrix report, can you order the columns? By default,
> > > they
> > > are in alpha order. My columns are the months of the year. Using Order
> > > By
> > > in the query doesn't work.
> > >
> > > I want January, February..., not April, August...
> >
> >
> >
When creating a matrix report, can you order the columns? By default, they
are in alpha order. My columns are the months of the year. Using Order By
in the query doesn't work.
I want January, February..., not April, August...If you have an actual DateTime field in the dataset, you could just add a
sort expression on the matrix column grouping like
=Month(Fields!SalesDate.Value)
If the column groupings are just based on a string field that actually
contains the month names and there are no other fields that would provide
full datetime information, you could create some "faked" datetime object
based on your month string and then use a sorting expression like
=Month(DateValue(Fields!MonthNameString.Value & " 1, 2005"))
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctmonth.asp
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctDateValue.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"del" <del@.discussions.microsoft.com> wrote in message
news:0CFB34D1-4ACA-4418-A0F6-F5278AFE48AF@.microsoft.com...
> Using SRS 2000.
> When creating a matrix report, can you order the columns? By default,
> they
> are in alpha order. My columns are the months of the year. Using Order
> By
> in the query doesn't work.
> I want January, February..., not April, August...|||Thanks for that. That converts the string to the month number, but it
doesn't sort them 1,2,3... It's still in the same order, but with numbers.
I thought I was being smart by converting in the query using datepart and
datename.
I will look through the info you gave.
"Robert Bruckner [MSFT]" wrote:
> If you have an actual DateTime field in the dataset, you could just add a
> sort expression on the matrix column grouping like
> =Month(Fields!SalesDate.Value)
> If the column groupings are just based on a string field that actually
> contains the month names and there are no other fields that would provide
> full datetime information, you could create some "faked" datetime object
> based on your month string and then use a sorting expression like
> =Month(DateValue(Fields!MonthNameString.Value & " 1, 2005"))
> See also:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctmonth.asp
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctDateValue.asp
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "del" <del@.discussions.microsoft.com> wrote in message
> news:0CFB34D1-4ACA-4418-A0F6-F5278AFE48AF@.microsoft.com...
> > Using SRS 2000.
> >
> > When creating a matrix report, can you order the columns? By default,
> > they
> > are in alpha order. My columns are the months of the year. Using Order
> > By
> > in the query doesn't work.
> >
> > I want January, February..., not April, August...
>
>|||Here's what I did:
In the query, I used date(mm,field) to convert the date in to a month. Then
In the matrix report, I sorted by order and used the monthname function. So
after it was sorted numerically, I used the expression,
=monthname(Field!...Value) and it returns the name of the month.
I would have never looked if you hadn't pointed me in the right direction.
Thanks!
"del" wrote:
> Thanks for that. That converts the string to the month number, but it
> doesn't sort them 1,2,3... It's still in the same order, but with numbers.
> I thought I was being smart by converting in the query using datepart and
> datename.
> I will look through the info you gave.
> "Robert Bruckner [MSFT]" wrote:
> > If you have an actual DateTime field in the dataset, you could just add a
> > sort expression on the matrix column grouping like
> > =Month(Fields!SalesDate.Value)
> >
> > If the column groupings are just based on a string field that actually
> > contains the month names and there are no other fields that would provide
> > full datetime information, you could create some "faked" datetime object
> > based on your month string and then use a sorting expression like
> > =Month(DateValue(Fields!MonthNameString.Value & " 1, 2005"))
> > See also:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctmonth.asp
> > http://msdn.microsoft.com/library/en-us/vblr7/html/vafctDateValue.asp
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "del" <del@.discussions.microsoft.com> wrote in message
> > news:0CFB34D1-4ACA-4418-A0F6-F5278AFE48AF@.microsoft.com...
> > > Using SRS 2000.
> > >
> > > When creating a matrix report, can you order the columns? By default,
> > > they
> > > are in alpha order. My columns are the months of the year. Using Order
> > > By
> > > in the query doesn't work.
> > >
> > > I want January, February..., not April, August...
> >
> >
> >
Thursday, February 16, 2012
Column description
Is it possible to add a column description when creating a table? An example
would be great.
http://aspfaq.com/show.asp?id=2244
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Gerko" <Gerko@.discussions.microsoft.com> wrote in message
news:AA6FC82B-FFC7-4510-ACAE-F0B7DE70D461@.microsoft.com...
> Is it possible to add a column description when creating a table? An
> example
> would be great.
would be great.
http://aspfaq.com/show.asp?id=2244
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Gerko" <Gerko@.discussions.microsoft.com> wrote in message
news:AA6FC82B-FFC7-4510-ACAE-F0B7DE70D461@.microsoft.com...
> Is it possible to add a column description when creating a table? An
> example
> would be great.
Sunday, February 12, 2012
Color inconsistent on each slice of Pie
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>
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>
Subscribe to:
Posts (Atom)