sql delete multiple tables: own

Showing posts with label own. Show all posts

Sunday, March 11, 2012

Defrag Tool?

I need to know if there are any good tools available to defrag/reindex
SQL2000 databases? I have to manage my own server and I am not a SQL guru. I
run Diskkeeper to take care of the physical files, but I need an easy and
effective way to defrag/reindex the SQL databases.
Thanks,
DerekHi Derek,
You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment the
indexes. DBCC DBREINDEX will defragment as well, so you don't have to run
them both. DBRINDEX will take out locks on your tables though, so you can
only run it when your database is not in use or very quiet.
You can run DBCC DBREINDEX on all tables with:
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
And you can create a script to defrag all indexes with:
SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME (id)
+ ''''+ CHAR(13) +
'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
indid) + ')' + CHAR(13) +
'DBCC SHRINKFILE(1, TRUNCATEONLY)'
FROM sysindexes
WHERE indid BETWEEN 1 AND 254
AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
ORDER BY OBJECT_NAME(id), indid
--
Jacco Schalkwijk
SQL Server MVP
"Derek" <derekb@.nospamderekb.com> wrote in message
news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> I need to know if there are any good tools available to defrag/reindex
> SQL2000 databases? I have to manage my own server and I am not a SQL guru.
I
> run Diskkeeper to take care of the physical files, but I need an easy and
> effective way to defrag/reindex the SQL databases.
> Thanks,
> Derek
>|||A disk defrag doesn't really work with SQL Server, apart
from getting the files in one continuous chunk of disk
space.
However you can defrag the files themselves with the DBCC
INDEXDEFRAG command.
J
>--Original Message--
>I need to know if there are any good tools available to
defrag/reindex
>SQL2000 databases? I have to manage my own server and I
am not a SQL guru. I
>run Diskkeeper to take care of the physical files, but I
need an easy and
>effective way to defrag/reindex the SQL databases.
>Thanks,
>Derek
>
>.
>|||Thanks. Just to make sure, I would ut the following into Query Analyzer and
run:
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
Also, How do I make my databases offline to run?
Thanks much!
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...
> Hi Derek,
> You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment
the
> indexes. DBCC DBREINDEX will defragment as well, so you don't have to run
> them both. DBRINDEX will take out locks on your tables though, so you can
> only run it when your database is not in use or very quiet.
> You can run DBCC DBREINDEX on all tables with:
> EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> And you can create a script to defrag all indexes with:
> SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME
(id)
> + ''''+ CHAR(13) +
> 'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
> indid) + ')' + CHAR(13) +
> 'DBCC SHRINKFILE(1, TRUNCATEONLY)'
> FROM sysindexes
> WHERE indid BETWEEN 1 AND 254
> AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
> ORDER BY OBJECT_NAME(id), indid
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Derek" <derekb@.nospamderekb.com> wrote in message
> news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> > I need to know if there are any good tools available to defrag/reindex
> > SQL2000 databases? I have to manage my own server and I am not a SQL
guru.
> I
> > run Diskkeeper to take care of the physical files, but I need an easy
and
> > effective way to defrag/reindex the SQL databases.
> >
> > Thanks,
> > Derek
> >
> >
>|||Hi Derek,
SQL Server databases do not have to be taken offline to be reindexed. The
reindexing process will block other processes from accessing the tables and
indexes that are being reindexed though, which might be inconvenient if the
reindexing takes a long time.
If that is an issue, you can prevent users from connecting to the database
with (SQL Server 2000 only):
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and after the reindexing you can give them access again with:
ALTER DATABASE <database name> SET MULTI_USER
--
Jacco Schalkwijk
SQL Server MVP
"Derek" <derekb@.nospamderekb.com> wrote in message
news:%23PZdXPZ6DHA.2524@.TK2MSFTNGP11.phx.gbl...
> Thanks. Just to make sure, I would ut the following into Query Analyzer
and
> run:
> EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> Also, How do I make my databases offline to run?
> Thanks much!
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...
> > Hi Derek,
> >
> > You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment
> the
> > indexes. DBCC DBREINDEX will defragment as well, so you don't have to
run
> > them both. DBRINDEX will take out locks on your tables though, so you
can
> > only run it when your database is not in use or very quiet.
> >
> > You can run DBCC DBREINDEX on all tables with:
> > EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> >
> > And you can create a script to defrag all indexes with:
> > SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME
> (id)
> > + ''''+ CHAR(13) +
> > 'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
> > indid) + ')' + CHAR(13) +
> > 'DBCC SHRINKFILE(1, TRUNCATEONLY)'
> > FROM sysindexes
> > WHERE indid BETWEEN 1 AND 254
> > AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
> > AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
> > ORDER BY OBJECT_NAME(id), indid
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "Derek" <derekb@.nospamderekb.com> wrote in message
> > news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> > > I need to know if there are any good tools available to defrag/reindex
> > > SQL2000 databases? I have to manage my own server and I am not a SQL
> guru.
> > I
> > > run Diskkeeper to take care of the physical files, but I need an easy
> and
> > > effective way to defrag/reindex the SQL databases.
> > >
> > > Thanks,
> > > Derek
> > >
> > >
> >
> >
>|||Thanks, I should have been more specific. Diskkeeper takes care of the
external files, I was referring to defrag internally. But I think Jacco has
me taken care of. Thanks anyway!
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:889901c3e97b$4907e810$a401280a@.phx.gbl...
> A disk defrag doesn't really work with SQL Server, apart
> from getting the files in one continuous chunk of disk
> space.
> However you can defrag the files themselves with the DBCC
> INDEXDEFRAG command.
> J
>
> >--Original Message--
> >I need to know if there are any good tools available to
> defrag/reindex
> >SQL2000 databases? I have to manage my own server and I
> am not a SQL guru. I
> >run Diskkeeper to take care of the physical files, but I
> need an easy and
> >effective way to defrag/reindex the SQL databases.
> >
> >Thanks,
> >Derek
> >
> >
> >.
> >|||Thanks! I will try this later tonight
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23Cwm3YZ6DHA.2952@.tk2msftngp13.phx.gbl...
> Hi Derek,
> SQL Server databases do not have to be taken offline to be reindexed. The
> reindexing process will block other processes from accessing the tables
and
> indexes that are being reindexed though, which might be inconvenient if
the
> reindexing takes a long time.
> If that is an issue, you can prevent users from connecting to the database
> with (SQL Server 2000 only):
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> and after the reindexing you can give them access again with:
> ALTER DATABASE <database name> SET MULTI_USER
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Derek" <derekb@.nospamderekb.com> wrote in message
> news:%23PZdXPZ6DHA.2524@.TK2MSFTNGP11.phx.gbl...
> > Thanks. Just to make sure, I would ut the following into Query Analyzer
> and
> > run:
> >
> > EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> >
> > Also, How do I make my databases offline to run?
> >
> > Thanks much!
> >
> >
> > "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> > news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...
> > > Hi Derek,
> > >
> > > You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to
defragment
> > the
> > > indexes. DBCC DBREINDEX will defragment as well, so you don't have to
> run
> > > them both. DBRINDEX will take out locks on your tables though, so you
> can
> > > only run it when your database is not in use or very quiet.
> > >
> > > You can run DBCC DBREINDEX on all tables with:
> > > EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> > >
> > > And you can create a script to defrag all indexes with:
> > > SELECT 'SELECT ''Defragging index ' + name + ' on table ' +
OBJECT_NAME
> > (id)
> > > + ''''+ CHAR(13) +
> > > 'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' +
CONVERT(VARCHAR,
> > > indid) + ')' + CHAR(13) +
> > > 'DBCC SHRINKFILE(1, TRUNCATEONLY)'
> > > FROM sysindexes
> > > WHERE indid BETWEEN 1 AND 254
> > > AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
> > > AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
> > > ORDER BY OBJECT_NAME(id), indid
> > >
> > > --
> > > Jacco Schalkwijk
> > > SQL Server MVP
> > >
> > >
> > > "Derek" <derekb@.nospamderekb.com> wrote in message
> > > news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> > > > I need to know if there are any good tools available to
defrag/reindex
> > > > SQL2000 databases? I have to manage my own server and I am not a SQL
> > guru.
> > > I
> > > > run Diskkeeper to take care of the physical files, but I need an
easy
> > and
> > > > effective way to defrag/reindex the SQL databases.
> > > >
> > > > Thanks,
> > > > Derek
> > > >
> > > >
> > >
> > >
> >
> >
>

Defrag Tool?

quote:

> I need to know if there are any good tools available to defrag/reindex
> SQL2000 databases? I have to manage my own server and I am not a SQL guru.

quote:

> run Diskkeeper to take care of the physical files, but I need an easy and
> effective way to defrag/reindex the SQL databases.
> Thanks,
> Derek
>

|||Thanks. Just to make sure, I would ut the following into Query Analyzer and
run:
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
Also, How do I make my databases offline to run?
Thanks much!
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...

quote:

> Hi Derek,
> You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment

the

quote:

> indexes. DBCC DBREINDEX will defragment as well, so you don't have to run
> them both. DBRINDEX will take out locks on your tables though, so you can
> only run it when your database is not in use or very quiet.
> You can run DBCC DBREINDEX on all tables with:
> EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> And you can create a script to defrag all indexes with:
> SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME

(id)

quote:

> + ''''+ CHAR(13) +
> 'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
> indid) + ')' + CHAR(13) +
> 'DBCC SHRINKFILE(1, TRUNCATEONLY)'
> FROM sysindexes
> WHERE indid BETWEEN 1 AND 254
> AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
> ORDER BY OBJECT_NAME(id), indid
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Derek" <derekb@.nospamderekb.com> wrote in message
> news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
guru.[QUOTE]
> I
and[QUOTE]
>

|||Hi Derek,
SQL Server databases do not have to be taken offline to be reindexed. The
reindexing process will block other processes from accessing the tables and
indexes that are being reindexed though, which might be inconvenient if the
reindexing takes a long time.
If that is an issue, you can prevent users from connecting to the database
with (SQL Server 2000 only):
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and after the reindexing you can give them access again with:
ALTER DATABASE <database name> SET MULTI_USER
Jacco Schalkwijk
SQL Server MVP
"Derek" <derekb@.nospamderekb.com> wrote in message
news:%23PZdXPZ6DHA.2524@.TK2MSFTNGP11.phx.gbl...

quote:

> Thanks. Just to make sure, I would ut the following into Query Analyzer

and

quote:

> run:
> EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> Also, How do I make my databases offline to run?
> Thanks much!
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...
> the
run[QUOTE]
can[QUOTE]
> (id)
> guru.
> and
>

|||Thanks! I will try this later tonight
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23Cwm3YZ6DHA.2952@.tk2msftngp13.phx.gbl...

quote:

> Hi Derek,
> SQL Server databases do not have to be taken offline to be reindexed. The
> reindexing process will block other processes from accessing the tables

and

quote:

> indexes that are being reindexed though, which might be inconvenient if

the

quote:

> reindexing takes a long time.
> If that is an issue, you can prevent users from connecting to the database
> with (SQL Server 2000 only):
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> and after the reindexing you can give them access again with:
> ALTER DATABASE <database name> SET MULTI_USER
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Derek" <derekb@.nospamderekb.com> wrote in message
> news:%23PZdXPZ6DHA.2524@.TK2MSFTNGP11.phx.gbl...
> and
defragment[QUOTE]
> run
> can
OBJECT_NAME[QUOTE]
CONVERT(VARCHAR,[QUOTE]
defrag/reindex[QUOTE]
easy[QUOTE]
>

Defrag Script

Hi all,
I'm writing my own defrag script. I've set it up so under certain conditions
it will run an IndexDefrag and under others a Reindex. I wanted to post the
conditions I've set and see if anyone has any feedback that can improve them.
I have two temporary tables 1 contains the tables and indexes, and one the
ShowContig information.
The IndexDefrag test is:
UPDATE #TableIndexList
SET ReqAction = 'I'
FROM #TableIndexList AS a
INNER JOIN #FragStats AS b
ON a.ObjectID = b.ObjectID
WHERE b.Pages BETWEEN 16 AND 4096
AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
OR b.AveragePageDensity < 75)
The reindex criteria is:
UPDATE #TableIndexList
SET ReqAction = 'R'
FROM #TableIndexList AS a
INNER JOIN #FragStats AS b
ON a.ObjectID = b.ObjectID
WHERE (b.Pages > 16
AND (b.LogicalFragmentation > 40
OR b.AveragePageDensity > 95))
OR (b.Pages > 4096
AND (b.LogicalFragmentation > 20
OR b.AveragePageDensity < 75))
As advised any feedback would be greatly appreciated...Have you compared the before and after results. Sometimes due to the way you
have constructed your table you will find residual fragmentation after
running such scripts will be almost the same.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> Hi all,
> I'm writing my own defrag script. I've set it up so under certain
> conditions
> it will run an IndexDefrag and under others a Reindex. I wanted to post
> the
> conditions I've set and see if anyone has any feedback that can improve
> them.
> I have two temporary tables 1 contains the tables and indexes, and one the
> ShowContig information.
> The IndexDefrag test is:
> UPDATE #TableIndexList
> SET ReqAction = 'I'
> FROM #TableIndexList AS a
> INNER JOIN #FragStats AS b
> ON a.ObjectID = b.ObjectID
> WHERE b.Pages BETWEEN 16 AND 4096
> AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> OR b.AveragePageDensity < 75)
> The reindex criteria is:
> UPDATE #TableIndexList
> SET ReqAction = 'R'
> FROM #TableIndexList AS a
> INNER JOIN #FragStats AS b
> ON a.ObjectID = b.ObjectID
> WHERE (b.Pages > 16
> AND (b.LogicalFragmentation > 40
> OR b.AveragePageDensity > 95))
> OR (b.Pages > 4096
> AND (b.LogicalFragmentation > 20
> OR b.AveragePageDensity < 75))
> As advised any feedback would be greatly appreciated...|||Thats interesting.. ..I'm identifying heaps for manual defrags, I'm excluding
blobs, is it a scenario where the table is busy and the IndexDefrag is
skipping the pages?
"Hilary Cotter" wrote:
> Have you compared the before and after results. Sometimes due to the way you
> have constructed your table you will find residual fragmentation after
> running such scripts will be almost the same.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> > Hi all,
> >
> > I'm writing my own defrag script. I've set it up so under certain
> > conditions
> > it will run an IndexDefrag and under others a Reindex. I wanted to post
> > the
> > conditions I've set and see if anyone has any feedback that can improve
> > them.
> > I have two temporary tables 1 contains the tables and indexes, and one the
> > ShowContig information.
> >
> > The IndexDefrag test is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'I'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE b.Pages BETWEEN 16 AND 4096
> > AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> > OR b.AveragePageDensity < 75)
> >
> > The reindex criteria is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'R'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE (b.Pages > 16
> > AND (b.LogicalFragmentation > 40
> > OR b.AveragePageDensity > 95))
> > OR (b.Pages > 4096
> > AND (b.LogicalFragmentation > 20
> > OR b.AveragePageDensity < 75))
> >
> > As advised any feedback would be greatly appreciated...
>
>|||or maybe when there's not enough disk space to run DBCC Reindex on a large
table
"Hilary Cotter" wrote:
> Have you compared the before and after results. Sometimes due to the way you
> have constructed your table you will find residual fragmentation after
> running such scripts will be almost the same.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> > Hi all,
> >
> > I'm writing my own defrag script. I've set it up so under certain
> > conditions
> > it will run an IndexDefrag and under others a Reindex. I wanted to post
> > the
> > conditions I've set and see if anyone has any feedback that can improve
> > them.
> > I have two temporary tables 1 contains the tables and indexes, and one the
> > ShowContig information.
> >
> > The IndexDefrag test is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'I'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE b.Pages BETWEEN 16 AND 4096
> > AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> > OR b.AveragePageDensity < 75)
> >
> > The reindex criteria is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'R'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE (b.Pages > 16
> > AND (b.LogicalFragmentation > 40
> > OR b.AveragePageDensity > 95))
> > OR (b.Pages > 4096
> > AND (b.LogicalFragmentation > 20
> > OR b.AveragePageDensity < 75))
> >
> > As advised any feedback would be greatly appreciated...
>
>

Defrag Script

Friday, March 9, 2012

Defining your own Palette in the Chart

The chart contol within Reporting Services only has 7 palette options,
is it possible to define your own palette?You need SP1 to be able to do this.
Bryan Avery wrote:
> The chart contol within Reporting Services only has 7 palette
options,
> is it possible to define your own palette?|||Did you find out how to do it?
I've been wondering about it, didn't find a way to do it.
Kaisa
"Bryan Avery" <b_avery@.yahoo.com> wrote in message
news:1112708844.404082.158390@.o13g2000cwo.googlegroups.com...
> You need SP1 to be able to do this.
> Bryan Avery wrote:
> > The chart contol within Reporting Services only has 7 palette
> options,
> > is it possible to define your own palette?
>|||Once I'd upgraded to SP1 and RTFM it worked:
Here is the extract from the SP1 Release notes
Chart Styles
Chart styles have been enhanced for SP1. Previously, the style of a
chart value element (for example, a bar or pie slice in the chart)
could not be changed in Report Designer. Colors were limited to the
automatic palette for the entire chart; you could not choose the colors
that were assigned to each element. With SP1, the line style and fill
color for an element can be edited using Report Designer. You can also
use expressions to control these styles.
To edit the style of a chart element
1. Using Report Designer, create a chart. For more information about
creating a chart, see "Adding a Chart" in Reporting Services Books
Online.
2. In Layout view, right-click the chart, and then click Properties.
3. On the Data tab, in Values, select the value to edit, and then click
Edit.
NOTE: If there is only one value series in the chart, select [Value].
4. On the Appearance tab, click Series Style.
5. On the Border and Line tab, select the line style, width, and color.
To use an expression, click the Expression (fx) button next to the
desired option.
6. On the Fill tab, select the color, gradient, and gradient end color.
To use an expression, click the Expression (fx) button next to the
desired option.
Note Providing a static value will cause all chart elements for that
value series to display using the same style. For example, selecting
Red for the fill color will cause all bars to become red, overriding
the automatic color assignment from the chart palette. To provide
different styles based on field or other values, use an expression. If
you use an expression for fill color, the chart elements will be white
in Layout view, but will display properly when the report is run.
Note A fill color of Transparent will cause the chart elements to
display using the automatic color assignment from the chart palette.
Kaisa M. Lindahl wrote:
> Did you find out how to do it?
> I've been wondering about it, didn't find a way to do it.
> Kaisa
> "Bryan Avery" <b_avery@.yahoo.com> wrote in message
> news:1112708844.404082.158390@.o13g2000cwo.googlegroups.com...
> > You need SP1 to be able to do this.
> >
> > Bryan Avery wrote:
> > > The chart contol within Reporting Services only has 7 palette
> > options,
> > > is it possible to define your own palette?
> >|||But what about the expression? Something like "= Choose(RowNumber(Nothing or
Dateset name), "#0060A1","#3380B4", "#669FC6", ...)" - which works in detail
table rows - does not work in style expressions why? And how to solve the
problem?
"Bryan Avery" wrote:
> Once I'd upgraded to SP1 and RTFM it worked:
> Here is the extract from the SP1 Release notes
> Chart Styles
> Chart styles have been enhanced for SP1. Previously, the style of a
> chart value element (for example, a bar or pie slice in the chart)
> could not be changed in Report Designer. Colors were limited to the
> automatic palette for the entire chart; you could not choose the colors
> that were assigned to each element. With SP1, the line style and fill
> color for an element can be edited using Report Designer. You can also
> use expressions to control these styles.
> To edit the style of a chart element
> 1. Using Report Designer, create a chart. For more information about
> creating a chart, see "Adding a Chart" in Reporting Services Books
> Online.
> 2. In Layout view, right-click the chart, and then click Properties.
> 3. On the Data tab, in Values, select the value to edit, and then click
> Edit.
> NOTE: If there is only one value series in the chart, select [Value].
> 4. On the Appearance tab, click Series Style.
> 5. On the Border and Line tab, select the line style, width, and color.
> To use an expression, click the Expression (fx) button next to the
> desired option.
> 6. On the Fill tab, select the color, gradient, and gradient end color.
> To use an expression, click the Expression (fx) button next to the
> desired option.
> Note Providing a static value will cause all chart elements for that
> value series to display using the same style. For example, selecting
> Red for the fill color will cause all bars to become red, overriding
> the automatic color assignment from the chart palette. To provide
> different styles based on field or other values, use an expression. If
> you use an expression for fill color, the chart elements will be white
> in Layout view, but will display properly when the report is run.
> Note A fill color of Transparent will cause the chart elements to
> display using the automatic color assignment from the chart palette.
> Kaisa M. Lindahl wrote:
> > Did you find out how to do it?
> > I've been wondering about it, didn't find a way to do it.
> >
> > Kaisa
> > "Bryan Avery" <b_avery@.yahoo.com> wrote in message
> > news:1112708844.404082.158390@.o13g2000cwo.googlegroups.com...
> > > You need SP1 to be able to do this.
> > >
> > > Bryan Avery wrote:
> > > > The chart contol within Reporting Services only has 7 palette
> > > options,
> > > > is it possible to define your own palette?
> > >
>|||RowNumber does not work in charts, because a chart is very similar to a
matrix (i.e. you have groupings in two dimensions: columns (categories), and
rows (series)). You cannot use RowNumber in a matrix.
Ok, here is a (trivial) way to use your own custom chart palette.
You would add the following code to report - report properties - code:
Private Shared count As Integer = 0
Private Shared colorPalette As String() = {"Green", "Blue", "Red",
"Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B",
"#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}
Public Function GetColor() As String
Dim c as string = colors(count Mod colors.Length)
count = count + 1
Return c
End Function
On the datapoint, you would then set the BackgroundColor to
=Code.GetColor()
A slightly more advanced way of using your own custom chart palette is to
use the following code:
Private Shared colorPalette As String() = {"Green", "Blue", "Red",
"Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B",
"#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}
Private Shared count As Integer = 0
Private Shared mapping As New System.Collections.Hashtable()
Public Function GetColor(ByVal groupingValue As String) As String
If mapping.ContainsKey(groupingValue) Then
Return osMapping(groupingValue)
End If
Dim c As String = colorPalette(count Mod colorPalette.Length)
count = count + 1
mapping.Add(groupingValue, c)
Return c
End Function
You would then typically use the same expression as argument for
GetColor(...) as you would use on the category grouping expression (assuming
your expression evaluates to a string). As you can see in the code above,
the hash table will store color mappings and look up existing color
mappings. If you have a new group, it will add a new color mapping.
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hawkeye" <Hawkeye@.discussions.microsoft.com> wrote in message
news:CF9A87DB-DBB3-4381-A2D2-FC5227ACC79A@.microsoft.com...
> But what about the expression? Something like "= Choose(RowNumber(Nothing
> or
> Dateset name), "#0060A1","#3380B4", "#669FC6", ...)" - which works in
> detail
> table rows - does not work in style expressions why? And how to solve the
> problem?
> "Bryan Avery" wrote:
>> Once I'd upgraded to SP1 and RTFM it worked:
>> Here is the extract from the SP1 Release notes
>> Chart Styles
>> Chart styles have been enhanced for SP1. Previously, the style of a
>> chart value element (for example, a bar or pie slice in the chart)
>> could not be changed in Report Designer. Colors were limited to the
>> automatic palette for the entire chart; you could not choose the colors
>> that were assigned to each element. With SP1, the line style and fill
>> color for an element can be edited using Report Designer. You can also
>> use expressions to control these styles.
>> To edit the style of a chart element
>> 1. Using Report Designer, create a chart. For more information about
>> creating a chart, see "Adding a Chart" in Reporting Services Books
>> Online.
>> 2. In Layout view, right-click the chart, and then click Properties.
>> 3. On the Data tab, in Values, select the value to edit, and then click
>> Edit.
>> NOTE: If there is only one value series in the chart, select [Value].
>> 4. On the Appearance tab, click Series Style.
>> 5. On the Border and Line tab, select the line style, width, and color.
>> To use an expression, click the Expression (fx) button next to the
>> desired option.
>> 6. On the Fill tab, select the color, gradient, and gradient end color.
>> To use an expression, click the Expression (fx) button next to the
>> desired option.
>> Note Providing a static value will cause all chart elements for that
>> value series to display using the same style. For example, selecting
>> Red for the fill color will cause all bars to become red, overriding
>> the automatic color assignment from the chart palette. To provide
>> different styles based on field or other values, use an expression. If
>> you use an expression for fill color, the chart elements will be white
>> in Layout view, but will display properly when the report is run.
>> Note A fill color of Transparent will cause the chart elements to
>> display using the automatic color assignment from the chart palette.
>> Kaisa M. Lindahl wrote:
>> > Did you find out how to do it?
>> > I've been wondering about it, didn't find a way to do it.
>> >
>> > Kaisa
>> > "Bryan Avery" <b_avery@.yahoo.com> wrote in message
>> > news:1112708844.404082.158390@.o13g2000cwo.googlegroups.com...
>> > > You need SP1 to be able to do this.
>> > >
>> > > Bryan Avery wrote:
>> > > > The chart contol within Reporting Services only has 7 palette
>> > > options,
>> > > > is it possible to define your own palette?
>> > >
>>|||Thx! Setting the FillColor to Code.GetColor() works!
"Robert Bruckner [MSFT]" wrote:
> RowNumber does not work in charts, because a chart is very similar to a
> matrix (i.e. you have groupings in two dimensions: columns (categories), and
> rows (series)). You cannot use RowNumber in a matrix.
>
> Ok, here is a (trivial) way to use your own custom chart palette.
> You would add the following code to report - report properties - code:
> Private Shared count As Integer = 0
> Private Shared colorPalette As String() = {"Green", "Blue", "Red",
> "Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B",
> "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}
> Public Function GetColor() As String
> Dim c as string = colors(count Mod colors.Length)
> count = count + 1
> Return c
> End Function
> On the datapoint, you would then set the BackgroundColor to
> =Code.GetColor()
>
> A slightly more advanced way of using your own custom chart palette is to
> use the following code:
> Private Shared colorPalette As String() = {"Green", "Blue", "Red",
> "Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B",
> "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}
> Private Shared count As Integer = 0
> Private Shared mapping As New System.Collections.Hashtable()
> Public Function GetColor(ByVal groupingValue As String) As String
> If mapping.ContainsKey(groupingValue) Then
> Return osMapping(groupingValue)
> End If
> Dim c As String = colorPalette(count Mod colorPalette.Length)
> count = count + 1
> mapping.Add(groupingValue, c)
> Return c
> End Function
> You would then typically use the same expression as argument for
> GetColor(...) as you would use on the category grouping expression (assuming
> your expression evaluates to a string). As you can see in the code above,
> the hash table will store color mappings and look up existing color
> mappings. If you have a new group, it will add a new color mapping.
>
> --
> Robert M. Bruckner
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> "Hawkeye" <Hawkeye@.discussions.microsoft.com> wrote in message
> news:CF9A87DB-DBB3-4381-A2D2-FC5227ACC79A@.microsoft.com...
> > But what about the expression? Something like "= Choose(RowNumber(Nothing
> > or
> > Dateset name), "#0060A1","#3380B4", "#669FC6", ...)" - which works in
> > detail
> > table rows - does not work in style expressions why? And how to solve the
> > problem?
> >
> > "Bryan Avery" wrote:
> >
> >> Once I'd upgraded to SP1 and RTFM it worked:
> >>
> >> Here is the extract from the SP1 Release notes
> >>
> >> Chart Styles
> >> Chart styles have been enhanced for SP1. Previously, the style of a
> >> chart value element (for example, a bar or pie slice in the chart)
> >> could not be changed in Report Designer. Colors were limited to the
> >> automatic palette for the entire chart; you could not choose the colors
> >> that were assigned to each element. With SP1, the line style and fill
> >> color for an element can be edited using Report Designer. You can also
> >> use expressions to control these styles.
> >>
> >> To edit the style of a chart element
> >>
> >> 1. Using Report Designer, create a chart. For more information about
> >> creating a chart, see "Adding a Chart" in Reporting Services Books
> >> Online.
> >>
> >> 2. In Layout view, right-click the chart, and then click Properties.
> >>
> >> 3. On the Data tab, in Values, select the value to edit, and then click
> >> Edit.
> >>
> >> NOTE: If there is only one value series in the chart, select [Value].
> >> 4. On the Appearance tab, click Series Style.
> >>
> >> 5. On the Border and Line tab, select the line style, width, and color.
> >> To use an expression, click the Expression (fx) button next to the
> >> desired option.
> >>
> >> 6. On the Fill tab, select the color, gradient, and gradient end color.
> >> To use an expression, click the Expression (fx) button next to the
> >> desired option.
> >>
> >> Note Providing a static value will cause all chart elements for that
> >> value series to display using the same style. For example, selecting
> >> Red for the fill color will cause all bars to become red, overriding
> >> the automatic color assignment from the chart palette. To provide
> >> different styles based on field or other values, use an expression. If
> >> you use an expression for fill color, the chart elements will be white
> >> in Layout view, but will display properly when the report is run.
> >> Note A fill color of Transparent will cause the chart elements to
> >> display using the automatic color assignment from the chart palette.
> >> Kaisa M. Lindahl wrote:
> >> > Did you find out how to do it?
> >> > I've been wondering about it, didn't find a way to do it.
> >> >
> >> > Kaisa
> >> > "Bryan Avery" <b_avery@.yahoo.com> wrote in message
> >> > news:1112708844.404082.158390@.o13g2000cwo.googlegroups.com...
> >> > > You need SP1 to be able to do this.
> >> > >
> >> > > Bryan Avery wrote:
> >> > > > The chart contol within Reporting Services only has 7 palette
> >> > > options,
> >> > > > is it possible to define your own palette?
> >> > >
> >>
> >>
>
>|||... It works! But now I want to reuse the colorPalette-Array. So I have to
call a simple sub
Public Sub Reset() count = 0 End Function
How to call this Function in the layout section?
"Robert Bruckner [MSFT]" wrote:
> RowNumber does not work in charts, because a chart is very similar to a
> matrix (i.e. you have groupings in two dimensions: columns (categories), and
> rows (series)). You cannot use RowNumber in a matrix.
>
> Ok, here is a (trivial) way to use your own custom chart palette.
> You would add the following code to report - report properties - code:
> Private Shared count As Integer = 0
> Private Shared colorPalette As String() = {"Green", "Blue", "Red",
> "Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B",
> "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}
> Public Function GetColor() As String
> Dim c as string = colors(count Mod colors.Length)
> count = count + 1
> Return c
> End Function
> On the datapoint, you would then set the BackgroundColor to
> =Code.GetColor()
>
> A slightly more advanced way of using your own custom chart palette is to
> use the following code:
> Private Shared colorPalette As String() = {"Green", "Blue", "Red",
> "Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B",
> "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}
> Private Shared count As Integer = 0
> Private Shared mapping As New System.Collections.Hashtable()
> Public Function GetColor(ByVal groupingValue As String) As String
> If mapping.ContainsKey(groupingValue) Then
> Return osMapping(groupingValue)
> End If
> Dim c As String = colorPalette(count Mod colorPalette.Length)
> count = count + 1
> mapping.Add(groupingValue, c)
> Return c
> End Function
> You would then typically use the same expression as argument for
> GetColor(...) as you would use on the category grouping expression (assuming
> your expression evaluates to a string). As you can see in the code above,
> the hash table will store color mappings and look up existing color
> mappings. If you have a new group, it will add a new color mapping.
>
> --
> Robert M. Bruckner
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> "Hawkeye" <Hawkeye@.discussions.microsoft.com> wrote in message
> news:CF9A87DB-DBB3-4381-A2D2-FC5227ACC79A@.microsoft.com...
> > But what about the expression? Something like "= Choose(RowNumber(Nothing
> > or
> > Dateset name), "#0060A1","#3380B4", "#669FC6", ...)" - which works in
> > detail
> > table rows - does not work in style expressions why? And how to solve the
> > problem?
> >
> > "Bryan Avery" wrote:
> >
> >> Once I'd upgraded to SP1 and RTFM it worked:
> >>
> >> Here is the extract from the SP1 Release notes
> >>
> >> Chart Styles
> >> Chart styles have been enhanced for SP1. Previously, the style of a
> >> chart value element (for example, a bar or pie slice in the chart)
> >> could not be changed in Report Designer. Colors were limited to the
> >> automatic palette for the entire chart; you could not choose the colors
> >> that were assigned to each element. With SP1, the line style and fill
> >> color for an element can be edited using Report Designer. You can also
> >> use expressions to control these styles.
> >>
> >> To edit the style of a chart element
> >>
> >> 1. Using Report Designer, create a chart. For more information about
> >> creating a chart, see "Adding a Chart" in Reporting Services Books
> >> Online.
> >>
> >> 2. In Layout view, right-click the chart, and then click Properties.
> >>
> >> 3. On the Data tab, in Values, select the value to edit, and then click
> >> Edit.
> >>
> >> NOTE: If there is only one value series in the chart, select [Value].
> >> 4. On the Appearance tab, click Series Style.
> >>
> >> 5. On the Border and Line tab, select the line style, width, and color.
> >> To use an expression, click the Expression (fx) button next to the
> >> desired option.
> >>
> >> 6. On the Fill tab, select the color, gradient, and gradient end color.
> >> To use an expression, click the Expression (fx) button next to the
> >> desired option.
> >>
> >> Note Providing a static value will cause all chart elements for that
> >> value series to display using the same style. For example, selecting
> >> Red for the fill color will cause all bars to become red, overriding
> >> the automatic color assignment from the chart palette. To provide
> >> different styles based on field or other values, use an expression. If
> >> you use an expression for fill color, the chart elements will be white
> >> in Layout view, but will display properly when the report is run.
> >> Note A fill color of Transparent will cause the chart elements to
> >> display using the automatic color assignment from the chart palette.
> >> Kaisa M. Lindahl wrote:
> >> > Did you find out how to do it?
> >> > I've been wondering about it, didn't find a way to do it.
> >> >
> >> > Kaisa
> >> > "Bryan Avery" <b_avery@.yahoo.com> wrote in message
> >> > news:1112708844.404082.158390@.o13g2000cwo.googlegroups.com...
> >> > > You need SP1 to be able to do this.
> >> > >
> >> > > Bryan Avery wrote:
> >> > > > The chart contol within Reporting Services only has 7 palette
> >> > > options,
> >> > > > is it possible to define your own palette?
> >> > >
> >>
> >>
>
>|||If you don't use the chart title, x-axis title, or y-axis title you could
invoke a function there to reset the palette.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hawkeye" <Hawkeye@.discussions.microsoft.com> wrote in message
news:FBF93B34-3367-4E16-9F45-982E1B7112C4@.microsoft.com...
> ... It works! But now I want to reuse the colorPalette-Array. So I have to
> call a simple sub
> Public Sub Reset() count = 0 End Function
> How to call this Function in the layout section?
>
> "Robert Bruckner [MSFT]" wrote:
>> RowNumber does not work in charts, because a chart is very similar to a
>> matrix (i.e. you have groupings in two dimensions: columns (categories),
>> and
>> rows (series)). You cannot use RowNumber in a matrix.
>>
>> Ok, here is a (trivial) way to use your own custom chart palette.
>> You would add the following code to report - report properties - code:
>> Private Shared count As Integer = 0
>> Private Shared colorPalette As String() = {"Green", "Blue", "Red",
>> "Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B",
>> "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}
>> Public Function GetColor() As String
>> Dim c as string = colors(count Mod colors.Length)
>> count = count + 1
>> Return c
>> End Function
>> On the datapoint, you would then set the BackgroundColor to
>> =Code.GetColor()
>>
>> A slightly more advanced way of using your own custom chart palette is to
>> use the following code:
>> Private Shared colorPalette As String() = {"Green", "Blue", "Red",
>> "Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B",
>> "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}
>> Private Shared count As Integer = 0
>> Private Shared mapping As New System.Collections.Hashtable()
>> Public Function GetColor(ByVal groupingValue As String) As String
>> If mapping.ContainsKey(groupingValue) Then
>> Return osMapping(groupingValue)
>> End If
>> Dim c As String = colorPalette(count Mod colorPalette.Length)
>> count = count + 1
>> mapping.Add(groupingValue, c)
>> Return c
>> End Function
>> You would then typically use the same expression as argument for
>> GetColor(...) as you would use on the category grouping expression
>> (assuming
>> your expression evaluates to a string). As you can see in the code above,
>> the hash table will store color mappings and look up existing color
>> mappings. If you have a new group, it will add a new color mapping.
>>
>> --
>> Robert M. Bruckner
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>>
>> "Hawkeye" <Hawkeye@.discussions.microsoft.com> wrote in message
>> news:CF9A87DB-DBB3-4381-A2D2-FC5227ACC79A@.microsoft.com...
>> > But what about the expression? Something like "=>> > Choose(RowNumber(Nothing
>> > or
>> > Dateset name), "#0060A1","#3380B4", "#669FC6", ...)" - which works in
>> > detail
>> > table rows - does not work in style expressions why? And how to solve
>> > the
>> > problem?
>> >
>> > "Bryan Avery" wrote:
>> >
>> >> Once I'd upgraded to SP1 and RTFM it worked:
>> >>
>> >> Here is the extract from the SP1 Release notes
>> >>
>> >> Chart Styles
>> >> Chart styles have been enhanced for SP1. Previously, the style of a
>> >> chart value element (for example, a bar or pie slice in the chart)
>> >> could not be changed in Report Designer. Colors were limited to the
>> >> automatic palette for the entire chart; you could not choose the
>> >> colors
>> >> that were assigned to each element. With SP1, the line style and fill
>> >> color for an element can be edited using Report Designer. You can also
>> >> use expressions to control these styles.
>> >>
>> >> To edit the style of a chart element
>> >>
>> >> 1. Using Report Designer, create a chart. For more information about
>> >> creating a chart, see "Adding a Chart" in Reporting Services Books
>> >> Online.
>> >>
>> >> 2. In Layout view, right-click the chart, and then click Properties.
>> >>
>> >> 3. On the Data tab, in Values, select the value to edit, and then
>> >> click
>> >> Edit.
>> >>
>> >> NOTE: If there is only one value series in the chart, select [Value].
>> >> 4. On the Appearance tab, click Series Style.
>> >>
>> >> 5. On the Border and Line tab, select the line style, width, and
>> >> color.
>> >> To use an expression, click the Expression (fx) button next to the
>> >> desired option.
>> >>
>> >> 6. On the Fill tab, select the color, gradient, and gradient end
>> >> color.
>> >> To use an expression, click the Expression (fx) button next to the
>> >> desired option.
>> >>
>> >> Note Providing a static value will cause all chart elements for that
>> >> value series to display using the same style. For example, selecting
>> >> Red for the fill color will cause all bars to become red, overriding
>> >> the automatic color assignment from the chart palette. To provide
>> >> different styles based on field or other values, use an expression. If
>> >> you use an expression for fill color, the chart elements will be white
>> >> in Layout view, but will display properly when the report is run.
>> >> Note A fill color of Transparent will cause the chart elements to
>> >> display using the automatic color assignment from the chart palette.
>> >> Kaisa M. Lindahl wrote:
>> >> > Did you find out how to do it?
>> >> > I've been wondering about it, didn't find a way to do it.
>> >> >
>> >> > Kaisa
>> >> > "Bryan Avery" <b_avery@.yahoo.com> wrote in message
>> >> > news:1112708844.404082.158390@.o13g2000cwo.googlegroups.com...
>> >> > > You need SP1 to be able to do this.
>> >> > >
>> >> > > Bryan Avery wrote:
>> >> > > > The chart contol within Reporting Services only has 7 palette
>> >> > > options,
>> >> > > > is it possible to define your own palette?
>> >> > >
>> >>
>> >>
>>|||I use the chart type "pie chart" so I cannot use x-title or y-title. Is there
an alternative? (Besides using the chart title)
And: How can I call Subs (instead of functions)?
"Robert Bruckner [MSFT]" wrote:
> If you don't use the chart title, x-axis title, or y-axis title you could
> invoke a function there to reset the palette.
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Hawkeye" <Hawkeye@.discussions.microsoft.com> wrote in message
> news:FBF93B34-3367-4E16-9F45-982E1B7112C4@.microsoft.com...
> > ... It works! But now I want to reuse the colorPalette-Array. So I have to
> > call a simple sub
> > Public Sub Reset() count = 0 End Function
> > How to call this Function in the layout section?
> >
> >
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> RowNumber does not work in charts, because a chart is very similar to a
> >> matrix (i.e. you have groupings in two dimensions: columns (categories),
> >> and
> >> rows (series)). You cannot use RowNumber in a matrix.
> >>
> >>
> >> Ok, here is a (trivial) way to use your own custom chart palette.
> >> You would add the following code to report - report properties - code:
> >>
> >> Private Shared count As Integer = 0
> >> Private Shared colorPalette As String() = {"Green", "Blue", "Red",
> >> "Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B",
> >> "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}
> >> Public Function GetColor() As String
> >> Dim c as string = colors(count Mod colors.Length)
> >> count = count + 1
> >> Return c
> >> End Function
> >>
> >> On the datapoint, you would then set the BackgroundColor to
> >> =Code.GetColor()
> >>
> >>
> >> A slightly more advanced way of using your own custom chart palette is to
> >> use the following code:
> >>
> >> Private Shared colorPalette As String() = {"Green", "Blue", "Red",
> >> "Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B",
> >> "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}
> >> Private Shared count As Integer = 0
> >> Private Shared mapping As New System.Collections.Hashtable()
> >> Public Function GetColor(ByVal groupingValue As String) As String
> >> If mapping.ContainsKey(groupingValue) Then
> >> Return osMapping(groupingValue)
> >> End If
> >> Dim c As String = colorPalette(count Mod colorPalette.Length)
> >> count = count + 1
> >> mapping.Add(groupingValue, c)
> >> Return c
> >> End Function
> >>
> >> You would then typically use the same expression as argument for
> >> GetColor(...) as you would use on the category grouping expression
> >> (assuming
> >> your expression evaluates to a string). As you can see in the code above,
> >> the hash table will store color mappings and look up existing color
> >> mappings. If you have a new group, it will add a new color mapping.
> >>
> >>
> >> --
> >> Robert M. Bruckner
> >> Microsoft SQL Server Reporting Services
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >>
> >>
> >> "Hawkeye" <Hawkeye@.discussions.microsoft.com> wrote in message
> >> news:CF9A87DB-DBB3-4381-A2D2-FC5227ACC79A@.microsoft.com...
> >> > But what about the expression? Something like "=> >> > Choose(RowNumber(Nothing
> >> > or
> >> > Dateset name), "#0060A1","#3380B4", "#669FC6", ...)" - which works in
> >> > detail
> >> > table rows - does not work in style expressions why? And how to solve
> >> > the
> >> > problem?
> >> >
> >> > "Bryan Avery" wrote:
> >> >
> >> >> Once I'd upgraded to SP1 and RTFM it worked:
> >> >>
> >> >> Here is the extract from the SP1 Release notes
> >> >>
> >> >> Chart Styles
> >> >> Chart styles have been enhanced for SP1. Previously, the style of a
> >> >> chart value element (for example, a bar or pie slice in the chart)
> >> >> could not be changed in Report Designer. Colors were limited to the
> >> >> automatic palette for the entire chart; you could not choose the
> >> >> colors
> >> >> that were assigned to each element. With SP1, the line style and fill
> >> >> color for an element can be edited using Report Designer. You can also
> >> >> use expressions to control these styles.
> >> >>
> >> >> To edit the style of a chart element
> >> >>
> >> >> 1. Using Report Designer, create a chart. For more information about
> >> >> creating a chart, see "Adding a Chart" in Reporting Services Books
> >> >> Online.
> >> >>
> >> >> 2. In Layout view, right-click the chart, and then click Properties.
> >> >>
> >> >> 3. On the Data tab, in Values, select the value to edit, and then
> >> >> click
> >> >> Edit.
> >> >>
> >> >> NOTE: If there is only one value series in the chart, select [Value].
> >> >> 4. On the Appearance tab, click Series Style.
> >> >>
> >> >> 5. On the Border and Line tab, select the line style, width, and
> >> >> color.
> >> >> To use an expression, click the Expression (fx) button next to the
> >> >> desired option.
> >> >>
> >> >> 6. On the Fill tab, select the color, gradient, and gradient end
> >> >> color.
> >> >> To use an expression, click the Expression (fx) button next to the
> >> >> desired option.
> >> >>
> >> >> Note Providing a static value will cause all chart elements for that
> >> >> value series to display using the same style. For example, selecting
> >> >> Red for the fill color will cause all bars to become red, overriding
> >> >> the automatic color assignment from the chart palette. To provide
> >> >> different styles based on field or other values, use an expression. If
> >> >> you use an expression for fill color, the chart elements will be white
> >> >> in Layout view, but will display properly when the report is run.
> >> >> Note A fill color of Transparent will cause the chart elements to
> >> >> display using the automatic color assignment from the chart palette.
> >> >> Kaisa M. Lindahl wrote:
> >> >> > Did you find out how to do it?
> >> >> > I've been wondering about it, didn't find a way to do it.
> >> >> >
> >> >> > Kaisa
> >> >> > "Bryan Avery" <b_avery@.yahoo.com> wrote in message
> >> >> > news:1112708844.404082.158390@.o13g2000cwo.googlegroups.com...
> >> >> > > You need SP1 to be able to do this.
> >> >> > >
> >> >> > > Bryan Avery wrote:
> >> >> > > > The chart contol within Reporting Services only has 7 palette
> >> >> > > options,
> >> >> > > > is it possible to define your own palette?
> >> >> > >
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Hi,
I tried the attached code and I received this error: There is an error on
line 1 of custom code: [BC30201] Expression expected.
Can someone tell me what I'm doing wrong?
Thanks!
"Hawkeye" wrote:
> I use the chart type "pie chart" so I cannot use x-title or y-title. Is there
> an alternative? (Besides using the chart title)
> And: How can I call Subs (instead of functions)?
>
>
> "Robert Bruckner [MSFT]" wrote:
> > If you don't use the chart title, x-axis title, or y-axis title you could
> > invoke a function there to reset the palette.
> >
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Hawkeye" <Hawkeye@.discussions.microsoft.com> wrote in message
> > news:FBF93B34-3367-4E16-9F45-982E1B7112C4@.microsoft.com...
> > > ... It works! But now I want to reuse the colorPalette-Array. So I have to
> > > call a simple sub
> > > Public Sub Reset() count = 0 End Function
> > > How to call this Function in the layout section?
> > >
> > >
> > >
> > > "Robert Bruckner [MSFT]" wrote:
> > >
> > >> RowNumber does not work in charts, because a chart is very similar to a
> > >> matrix (i.e. you have groupings in two dimensions: columns (categories),
> > >> and
> > >> rows (series)). You cannot use RowNumber in a matrix.
> > >>
> > >>
> > >> Ok, here is a (trivial) way to use your own custom chart palette.
> > >> You would add the following code to report - report properties - code:
> > >>
> > >> Private Shared count As Integer = 0
> > >> Private Shared colorPalette As String() = {"Green", "Blue", "Red",
> > >> "Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B",
> > >> "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}
> > >> Public Function GetColor() As String
> > >> Dim c as string = colors(count Mod colors.Length)
> > >> count = count + 1
> > >> Return c
> > >> End Function
> > >>
> > >> On the datapoint, you would then set the BackgroundColor to
> > >> =Code.GetColor()
> > >>
> > >>
> > >> A slightly more advanced way of using your own custom chart palette is to
> > >> use the following code:
> > >>
> > >> Private Shared colorPalette As String() = {"Green", "Blue", "Red",
> > >> "Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B",
> > >> "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}
> > >> Private Shared count As Integer = 0
> > >> Private Shared mapping As New System.Collections.Hashtable()
> > >> Public Function GetColor(ByVal groupingValue As String) As String
> > >> If mapping.ContainsKey(groupingValue) Then
> > >> Return osMapping(groupingValue)
> > >> End If
> > >> Dim c As String = colorPalette(count Mod colorPalette.Length)
> > >> count = count + 1
> > >> mapping.Add(groupingValue, c)
> > >> Return c
> > >> End Function
> > >>
> > >> You would then typically use the same expression as argument for
> > >> GetColor(...) as you would use on the category grouping expression
> > >> (assuming
> > >> your expression evaluates to a string). As you can see in the code above,
> > >> the hash table will store color mappings and look up existing color
> > >> mappings. If you have a new group, it will add a new color mapping.
> > >>
> > >>
> > >> --
> > >> Robert M. Bruckner
> > >> Microsoft SQL Server Reporting Services
> > >> This posting is provided "AS IS" with no warranties, and confers no
> > >> rights.
> > >>
> > >>
> > >>
> > >>
> > >> "Hawkeye" <Hawkeye@.discussions.microsoft.com> wrote in message
> > >> news:CF9A87DB-DBB3-4381-A2D2-FC5227ACC79A@.microsoft.com...
> > >> > But what about the expression? Something like "=> > >> > Choose(RowNumber(Nothing
> > >> > or
> > >> > Dateset name), "#0060A1","#3380B4", "#669FC6", ...)" - which works in
> > >> > detail
> > >> > table rows - does not work in style expressions why? And how to solve
> > >> > the
> > >> > problem?
> > >> >
> > >> > "Bryan Avery" wrote:
> > >> >
> > >> >> Once I'd upgraded to SP1 and RTFM it worked:
> > >> >>
> > >> >> Here is the extract from the SP1 Release notes
> > >> >>
> > >> >> Chart Styles
> > >> >> Chart styles have been enhanced for SP1. Previously, the style of a
> > >> >> chart value element (for example, a bar or pie slice in the chart)
> > >> >> could not be changed in Report Designer. Colors were limited to the
> > >> >> automatic palette for the entire chart; you could not choose the
> > >> >> colors
> > >> >> that were assigned to each element. With SP1, the line style and fill
> > >> >> color for an element can be edited using Report Designer. You can also
> > >> >> use expressions to control these styles.
> > >> >>
> > >> >> To edit the style of a chart element
> > >> >>
> > >> >> 1. Using Report Designer, create a chart. For more information about
> > >> >> creating a chart, see "Adding a Chart" in Reporting Services Books
> > >> >> Online.
> > >> >>
> > >> >> 2. In Layout view, right-click the chart, and then click Properties.
> > >> >>
> > >> >> 3. On the Data tab, in Values, select the value to edit, and then
> > >> >> click
> > >> >> Edit.
> > >> >>
> > >> >> NOTE: If there is only one value series in the chart, select [Value].
> > >> >> 4. On the Appearance tab, click Series Style.
> > >> >>
> > >> >> 5. On the Border and Line tab, select the line style, width, and
> > >> >> color.
> > >> >> To use an expression, click the Expression (fx) button next to the
> > >> >> desired option.
> > >> >>
> > >> >> 6. On the Fill tab, select the color, gradient, and gradient end
> > >> >> color.
> > >> >> To use an expression, click the Expression (fx) button next to the
> > >> >> desired option.
> > >> >>
> > >> >> Note Providing a static value will cause all chart elements for that
> > >> >> value series to display using the same style. For example, selecting
> > >> >> Red for the fill color will cause all bars to become red, overriding
> > >> >> the automatic color assignment from the chart palette. To provide
> > >> >> different styles based on field or other values, use an expression. If
> > >> >> you use an expression for fill color, the chart elements will be white
> > >> >> in Layout view, but will display properly when the report is run.
> > >> >> Note A fill color of Transparent will cause the chart elements to
> > >> >> display using the automatic color assignment from the chart palette.
> > >> >> Kaisa M. Lindahl wrote:
> > >> >> > Did you find out how to do it?
> > >> >> > I've been wondering about it, didn't find a way to do it.
> > >> >> >
> > >> >> > Kaisa
> > >> >> > "Bryan Avery" <b_avery@.yahoo.com> wrote in message
> > >> >> > news:1112708844.404082.158390@.o13g2000cwo.googlegroups.com...
> > >> >> > > You need SP1 to be able to do this.
> > >> >> > >
> > >> >> > > Bryan Avery wrote:
> > >> >> > > > The chart contol within Reporting Services only has 7 palette
> > >> >> > > options,
> > >> >> > > > is it possible to define your own palette?
> > >> >> > >
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >|||>I use the chart type "pie chart" so I cannot use x-title or y-title. Is
>there an alternative? (Besides using the chart title)
You could, for instance, call the expression in the following place:
* open chart properties dialog
* click on the "chart area style" button in the general tab
* e.g. for the Style property use an expression like =Code.ResetColor
* Add the following function on the custom code window:
Public Function ResetColor() As String
count = 0
Return "None"
End Function
> And: How can I call Subs (instead of functions)?
You would convert the sub into a function which returns a string
representing the value you would want to set anyway on that property. But in
addition you would reset the count. See example above.
Alternatively, you could define multiple color palettes and multiple
GetColor functions. In that case there would be no need to reset because you
don't share the palette in multiple charts (or data regions in general).
BTW: my advanced sample in my previous posting had a minor typo which is
fixed below.
Private Shared colorPalette As String() = {"Green", "Blue", "Red", "Orange",
"Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B", "#352F26",
"#F1E7D6", "#E16C56", "#CFBA9B"}
Private Shared count As Integer = 0
Private Shared 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
At the bottom I added a fully functional RDL file which shows the usage of
custom palettes.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
=====================================================
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Author>Robert M. Bruckner</Author>
<Body>
<ReportItems>
<Table Name="table1">
<Height>0.5in</Height>
<ZIndex>1</ZIndex>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Legend</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<DataSetName>DataSet1</DataSetName>
<Top>0.125in</Top>
<Width>2in</Width>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Rectangle Name="rectangle1">
<ZIndex>1</ZIndex>
<Style>
<BackgroundColor>=Code.GetColor(Fields!CategoryName.Value)</BackgroundColor>
</Style>
</Rectangle>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="CategoryName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>CategoryName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!CategoryName.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</TableGroup>
</TableGroups>
<Left>3.25in</Left>
<TableColumns>
<TableColumn>
<Width>0.25in</Width>
</TableColumn>
<TableColumn>
<Width>1.75in</Width>
</TableColumn>
</TableColumns>
</Table>
<Chart Name="chart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
<Legend>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>BottomCenter</Position>
<Layout>Table</Layout>
</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 />
<Width>3in</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>
<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>3.25in</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 Shared colorPalette As String() = {"Green", "Blue",
"Red", "Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B",
"#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}
Private Shared count As Integer = 0
Private Shared 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>|||That works! I couldn't use subs, could I?
What I found interesting is that without reseting the counter, the last
value of the counter resist in memory so that for the next run (!) of the
report the counter is not only not reinit to zero, but also further increased.
Is that the normal behaviour of shared report variables?
"Robert Bruckner [MSFT]" wrote:
> >I use the chart type "pie chart" so I cannot use x-title or y-title. Is
> >there an alternative? (Besides using the chart title)
> You could, for instance, call the expression in the following place:
> * open chart properties dialog
> * click on the "chart area style" button in the general tab
> * e.g. for the Style property use an expression like =Code.ResetColor
> * Add the following function on the custom code window:
> Public Function ResetColor() As String
> count = 0
> Return "None"
> End Function
> > And: How can I call Subs (instead of functions)?
> You would convert the sub into a function which returns a string
> representing the value you would want to set anyway on that property. But in
> addition you would reset the count. See example above.
>
> Alternatively, you could define multiple color palettes and multiple
> GetColor functions. In that case there would be no need to reset because you
> don't share the palette in multiple charts (or data regions in general).
> BTW: my advanced sample in my previous posting had a minor typo which is
> fixed below.
> Private Shared colorPalette As String() = {"Green", "Blue", "Red", "Orange",
> "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B", "#352F26",
> "#F1E7D6", "#E16C56", "#CFBA9B"}
> Private Shared count As Integer = 0
> Private Shared 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
>
> At the bottom I added a fully functional RDL file which shows the usage of
> custom palettes.
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =====================================================> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>1in</RightMargin>
> <Author>Robert M. Bruckner</Author>
> <Body>
> <ReportItems>
> <Table Name="table1">
> <Height>0.5in</Height>
> <ZIndex>1</ZIndex>
> <Style />
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>3</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox2">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>textbox2</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Legend</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <DataSetName>DataSet1</DataSetName>
> <Top>0.125in</Top>
> <Width>2in</Width>
> <TableGroups>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Rectangle Name="rectangle1">
> <ZIndex>1</ZIndex>
> <Style>
> <BackgroundColor>=Code.GetColor(Fields!CategoryName.Value)</BackgroundColor>
> </Style>
> </Rectangle>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="CategoryName">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>CategoryName</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!CategoryName.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Grouping Name="table1_Group1">
> <GroupExpressions>
> <GroupExpression>=Fields!CategoryName.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields!CategoryName.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> </TableGroup>
> </TableGroups>
> <Left>3.25in</Left>
> <TableColumns>
> <TableColumn>
> <Width>0.25in</Width>
> </TableColumn>
> <TableColumn>
> <Width>1.75in</Width>
> </TableColumn>
> </TableColumns>
> </Table>
> <Chart Name="chart1">
> <ThreeDProperties>
> <Rotation>30</Rotation>
> <Inclination>30</Inclination>
> <Shading>Simple</Shading>
> <WallThickness>50</WallThickness>
> </ThreeDProperties>
> <Style>
> <BackgroundColor>White</BackgroundColor>
> </Style>
> <Legend>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> <Position>BottomCenter</Position>
> <Layout>Table</Layout>
> </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 />
> <Width>3in</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>
> <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>3.25in</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>|||Yes, the variables are marked as shared - so they are really shared across
all requests on a server. One way to solve this is to use e.g. a hashtable
storing the counter based on the username (from the Globals collection) to
make sure that different users running the same report at the same time
can't affect each other.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hawkeye" <Hawkeye@.discussions.microsoft.com> wrote in message
news:91642910-E126-408B-A2A9-1F5B02BEB7FE@.microsoft.com...
> That works! I couldn't use subs, could I?
> What I found interesting is that without reseting the counter, the last
> value of the counter resist in memory so that for the next run (!) of the
> report the counter is not only not reinit to zero, but also further
> increased.
> Is that the normal behaviour of shared report variables?
>
> "Robert Bruckner [MSFT]" wrote:
>> >I use the chart type "pie chart" so I cannot use x-title or y-title. Is
>> >there an alternative? (Besides using the chart title)
>> You could, for instance, call the expression in the following place:
>> * open chart properties dialog
>> * click on the "chart area style" button in the general tab
>> * e.g. for the Style property use an expression like =Code.ResetColor
>> * Add the following function on the custom code window:
>> Public Function ResetColor() As String
>> count = 0
>> Return "None"
>> End Function
>> > And: How can I call Subs (instead of functions)?
>> You would convert the sub into a function which returns a string
>> representing the value you would want to set anyway on that property. But
>> in
>> addition you would reset the count. See example above.
>>
>> Alternatively, you could define multiple color palettes and multiple
>> GetColor functions. In that case there would be no need to reset because
>> you
>> don't share the palette in multiple charts (or data regions in general).
>> BTW: my advanced sample in my previous posting had a minor typo which is
>> fixed below.
>> Private Shared colorPalette As String() = {"Green", "Blue", "Red",
>> "Orange",
>> "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B", "#352F26",
>> "#F1E7D6", "#E16C56", "#CFBA9B"}
>> Private Shared count As Integer = 0
>> Private Shared 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
>>
>> At the bottom I added a fully functional RDL file which shows the usage
>> of
>> custom palettes.
>>
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> =====================================================>> <?xml version="1.0" encoding="utf-8"?>
>> <Report
>> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
>> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
>> <RightMargin>1in</RightMargin>
>> <Author>Robert M. Bruckner</Author>
>> <Body>
>> <ReportItems>
>> <Table Name="table1">
>> <Height>0.5in</Height>
>> <ZIndex>1</ZIndex>
>> <Style />
>> <Header>
>> <TableRows>
>> <TableRow>
>> <Height>0.25in</Height>
>> <TableCells>
>> <TableCell>
>> <ReportItems>
>> <Textbox Name="textbox1">
>> <Style>
>> <PaddingLeft>2pt</PaddingLeft>
>> <PaddingBottom>2pt</PaddingBottom>
>> <PaddingTop>2pt</PaddingTop>
>> <PaddingRight>2pt</PaddingRight>
>> </Style>
>> <ZIndex>3</ZIndex>
>> <rd:DefaultName>textbox1</rd:DefaultName>
>> <CanGrow>true</CanGrow>
>> <Value />
>> </Textbox>
>> </ReportItems>
>> </TableCell>
>> <TableCell>
>> <ReportItems>
>> <Textbox Name="textbox2">
>> <Style>
>> <PaddingLeft>2pt</PaddingLeft>
>> <PaddingBottom>2pt</PaddingBottom>
>> <PaddingTop>2pt</PaddingTop>
>> <PaddingRight>2pt</PaddingRight>
>> <FontWeight>700</FontWeight>
>> </Style>
>> <ZIndex>2</ZIndex>
>> <rd:DefaultName>textbox2</rd:DefaultName>
>> <CanGrow>true</CanGrow>
>> <Value>Legend</Value>
>> </Textbox>
>> </ReportItems>
>> </TableCell>
>> </TableCells>
>> </TableRow>
>> </TableRows>
>> </Header>
>> <DataSetName>DataSet1</DataSetName>
>> <Top>0.125in</Top>
>> <Width>2in</Width>
>> <TableGroups>
>> <TableGroup>
>> <Header>
>> <TableRows>
>> <TableRow>
>> <Height>0.25in</Height>
>> <TableCells>
>> <TableCell>
>> <ReportItems>
>> <Rectangle Name="rectangle1">
>> <ZIndex>1</ZIndex>
>> <Style>
>> <BackgroundColor>=Code.GetColor(Fields!CategoryName.Value)</BackgroundColor>
>> </Style>
>> </Rectangle>
>> </ReportItems>
>> </TableCell>
>> <TableCell>
>> <ReportItems>
>> <Textbox Name="CategoryName">
>> <Style>
>> <PaddingLeft>2pt</PaddingLeft>
>> <PaddingBottom>2pt</PaddingBottom>
>> <PaddingTop>2pt</PaddingTop>
>> <PaddingRight>2pt</PaddingRight>
>> </Style>
>> <rd:DefaultName>CategoryName</rd:DefaultName>
>> <CanGrow>true</CanGrow>
>> <Value>=Fields!CategoryName.Value</Value>
>> </Textbox>
>> </ReportItems>
>> </TableCell>
>> </TableCells>
>> </TableRow>
>> </TableRows>
>> </Header>
>> <Grouping Name="table1_Group1">
>> <GroupExpressions>
>> <GroupExpression>=Fields!CategoryName.Value</GroupExpression>
>> </GroupExpressions>
>> </Grouping>
>> <Sorting>
>> <SortBy>
>> <SortExpression>=Fields!CategoryName.Value</SortExpression>
>> <Direction>Ascending</Direction>
>> </SortBy>
>> </Sorting>
>> </TableGroup>
>> </TableGroups>
>> <Left>3.25in</Left>
>> <TableColumns>
>> <TableColumn>
>> <Width>0.25in</Width>
>> </TableColumn>
>> <TableColumn>
>> <Width>1.75in</Width>
>> </TableColumn>
>> </TableColumns>
>> </Table>
>> <Chart Name="chart1">
>> <ThreeDProperties>
>> <Rotation>30</Rotation>
>> <Inclination>30</Inclination>
>> <Shading>Simple</Shading>
>> <WallThickness>50</WallThickness>
>> </ThreeDProperties>
>> <Style>
>> <BackgroundColor>White</BackgroundColor>
>> </Style>
>> <Legend>
>> <Style>
>> <BorderStyle>
>> <Default>Solid</Default>
>> </BorderStyle>
>> </Style>
>> <Position>BottomCenter</Position>
>> <Layout>Table</Layout>
>> </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 />
>> <Width>3in</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>
>> <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>3.25in</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>|||Actually, in this case it would also work without defining the variables as
shared.
i.e.:
Private count As Integer = 0
Private mapping As New System.Collections.Hashtable()
...
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23IohCWXXFHA.712@.TK2MSFTNGP14.phx.gbl...
> Yes, the variables are marked as shared - so they are really shared across
> all requests on a server. One way to solve this is to use e.g. a hashtable
> storing the counter based on the username (from the Globals collection) to
> make sure that different users running the same report at the same time
> can't affect each other.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Hawkeye" <Hawkeye@.discussions.microsoft.com> wrote in message
> news:91642910-E126-408B-A2A9-1F5B02BEB7FE@.microsoft.com...
>> That works! I couldn't use subs, could I?
>> What I found interesting is that without reseting the counter, the last
>> value of the counter resist in memory so that for the next run (!) of the
>> report the counter is not only not reinit to zero, but also further
>> increased.
>> Is that the normal behaviour of shared report variables?
>>
>> "Robert Bruckner [MSFT]" wrote:
>> >I use the chart type "pie chart" so I cannot use x-title or y-title. Is
>> >there an alternative? (Besides using the chart title)
>> You could, for instance, call the expression in the following place:
>> * open chart properties dialog
>> * click on the "chart area style" button in the general tab
>> * e.g. for the Style property use an expression like =Code.ResetColor
>> * Add the following function on the custom code window:
>> Public Function ResetColor() As String
>> count = 0
>> Return "None"
>> End Function
>> > And: How can I call Subs (instead of functions)?
>> You would convert the sub into a function which returns a string
>> representing the value you would want to set anyway on that property.
>> But in
>> addition you would reset the count. See example above.
>>
>> Alternatively, you could define multiple color palettes and multiple
>> GetColor functions. In that case there would be no need to reset because
>> you
>> don't share the palette in multiple charts (or data regions in general).
>> BTW: my advanced sample in my previous posting had a minor typo which is
>> fixed below.
>> Private Shared colorPalette As String() = {"Green", "Blue", "Red",
>> "Orange",
>> "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B", "#352F26",
>> "#F1E7D6", "#E16C56", "#CFBA9B"}
>> Private Shared count As Integer = 0
>> Private Shared 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
>>
>> At the bottom I added a fully functional RDL file which shows the usage
>> of
>> custom palettes.
>>
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> =====================================================>> <?xml version="1.0" encoding="utf-8"?>
>> <Report
>> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
>> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
>> <RightMargin>1in</RightMargin>
>> <Author>Robert M. Bruckner</Author>
>> <Body>
>> <ReportItems>
>> <Table Name="table1">
>> <Height>0.5in</Height>
>> <ZIndex>1</ZIndex>
>> <Style />
>> <Header>
>> <TableRows>
>> <TableRow>
>> <Height>0.25in</Height>
>> <TableCells>
>> <TableCell>
>> <ReportItems>
>> <Textbox Name="textbox1">
>> <Style>
>> <PaddingLeft>2pt</PaddingLeft>
>> <PaddingBottom>2pt</PaddingBottom>
>> <PaddingTop>2pt</PaddingTop>
>> <PaddingRight>2pt</PaddingRight>
>> </Style>
>> <ZIndex>3</ZIndex>
>> <rd:DefaultName>textbox1</rd:DefaultName>
>> <CanGrow>true</CanGrow>
>> <Value />
>> </Textbox>
>> </ReportItems>
>> </TableCell>
>> <TableCell>
>> <ReportItems>
>> <Textbox Name="textbox2">
>> <Style>
>> <PaddingLeft>2pt</PaddingLeft>
>> <PaddingBottom>2pt</PaddingBottom>
>> <PaddingTop>2pt</PaddingTop>
>> <PaddingRight>2pt</PaddingRight>
>> <FontWeight>700</FontWeight>
>> </Style>
>> <ZIndex>2</ZIndex>
>> <rd:DefaultName>textbox2</rd:DefaultName>
>> <CanGrow>true</CanGrow>
>> <Value>Legend</Value>
>> </Textbox>
>> </ReportItems>
>> </TableCell>
>> </TableCells>
>> </TableRow>
>> </TableRows>
>> </Header>
>> <DataSetName>DataSet1</DataSetName>
>> <Top>0.125in</Top>
>> <Width>2in</Width>
>> <TableGroups>
>> <TableGroup>
>> <Header>
>> <TableRows>
>> <TableRow>
>> <Height>0.25in</Height>
>> <TableCells>
>> <TableCell>
>> <ReportItems>
>> <Rectangle Name="rectangle1">
>> <ZIndex>1</ZIndex>
>> <Style>
>> <BackgroundColor>=Code.GetColor(Fields!CategoryName.Value)</BackgroundColor>
>> </Style>
>> </Rectangle>
>> </ReportItems>
>> </TableCell>
>> <TableCell>
>> <ReportItems>
>> <Textbox Name="CategoryName">
>> <Style>
>> <PaddingLeft>2pt</PaddingLeft>
>> <PaddingBottom>2pt</PaddingBottom>
>> <PaddingTop>2pt</PaddingTop>
>> <PaddingRight>2pt</PaddingRight>
>> </Style>
>> <rd:DefaultName>CategoryName</rd:DefaultName>
>> <CanGrow>true</CanGrow>
>> <Value>=Fields!CategoryName.Value</Value>
>> </Textbox>
>> </ReportItems>
>> </TableCell>
>> </TableCells>
>> </TableRow>
>> </TableRows>
>> </Header>
>> <Grouping Name="table1_Group1">
>> <GroupExpressions>
>> <GroupExpression>=Fields!CategoryName.Value</GroupExpression>
>> </GroupExpressions>
>> </Grouping>
>> <Sorting>
>> <SortBy>
>> <SortExpression>=Fields!CategoryName.Value</SortExpression>
>> <Direction>Ascending</Direction>
>> </SortBy>
>> </Sorting>
>> </TableGroup>
>> </TableGroups>
>> <Left>3.25in</Left>
>> <TableColumns>
>> <TableColumn>
>> <Width>0.25in</Width>
>> </TableColumn>
>> <TableColumn>
>> <Width>1.75in</Width>
>> </TableColumn>
>> </TableColumns>
>> </Table>
>> <Chart Name="chart1">
>> <ThreeDProperties>
>> <Rotation>30</Rotation>
>> <Inclination>30</Inclination>
>> <Shading>Simple</Shading>
>> <WallThickness>50</WallThickness>
>> </ThreeDProperties>
>> <Style>
>> <BackgroundColor>White</BackgroundColor>
>> </Style>
>> <Legend>
>> <Style>
>> <BorderStyle>
>> <Default>Solid</Default>
>> </BorderStyle>
>> </Style>
>> <Position>BottomCenter</Position>
>> <Layout>Table</Layout>
>> </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 />
>> <Width>3in</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>
>> <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>3.25in</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>
>

Sunday, March 11, 2012

Defrag Tool?

Defrag Tool?

Defrag Script

Defrag Script

Friday, March 9, 2012

Defining your own Palette in the Chart

sql delete multiple tables

Blog Archive

About Me