Showing posts with label number. Show all posts
Showing posts with label number. Show all posts

Friday, March 30, 2012

Max Num of Worker Threads and User Connections/Logins

Hi everyone,
I need to know what is the relationship between the "Maxium Number of Worker Threads" setting (default = 255) and user connections/sessions/logins.
From time to time, we are experiencing random login failure for several busy databases. On average, we see about 264 total sessions on the box and we were wondering if the max worker threads number, bumping it up would fix the issue.
Can anyone offer any suggestions?
Thanks in advance,
Errol Neal
As long as there are fewer than "Max worker threads" number of connections,
each connection gets its own individual thread... Once you go over that
number, several connections will share the same thread... You can increase
the number of worker threads, but that uses more memory ( even if there are
fewer connections.)
Thread sharing could result in front end timeouts ( especially if the other
connection you are sharing the thread with is a busy one.)
"Errol U. Neal" <eneal@.enhtech.com> wrote in message
news:09B0F39B-1468-4069-A8A1-BF2AF2E645C8@.microsoft.com...
> Hi everyone,
> I need to know what is the relationship between the "Maxium Number of
Worker Threads" setting (default = 255) and user
connections/sessions/logins.
> From time to time, we are experiencing random login failure for several
busy databases. On average, we see about 264 total sessions on the box and
we were wondering if the max worker threads number, bumping it up would fix
the issue.
> Can anyone offer any suggestions?
>
> Thanks in advance,
>
> Errol Neal
|||Okay. I understand that. Thanks!
But why are we having random login failures? We are per-processor licensing so I am confused. Its not that the login is timing out. They are outright failing and I know because I am doing a SQL Server Trace and watching them as they fail.
-- Wayne Snyder wrote: --
As long as there are fewer than "Max worker threads" number of connections,
each connection gets its own individual thread... Once you go over that
number, several connections will share the same thread... You can increase
the number of worker threads, but that uses more memory ( even if there are
fewer connections.)
Thread sharing could result in front end timeouts ( especially if the other
connection you are sharing the thread with is a busy one.)
|||Do you see any errors on the SQL error log e.g. "could not spawn process
login thread" or similar ? We have seen these under conditions of high
memory contention especially in the MemToLeave area where new threads cannot
allocate enough memory to start.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Errol Uriel Neal" <eneal@.enhtech.com> wrote in message
news:94D7AB61-A7F2-4FA1-B19C-D79B64B3B23D@.microsoft.com...
> Okay. I understand that. Thanks!
> But why are we having random login failures? We are per-processor
licensing so I am confused. Its not that the login is timing out. They are
outright failing and I know because I am doing a SQL Server Trace and
watching them as they fail.
>
> -- Wayne Snyder wrote: --
> As long as there are fewer than "Max worker threads" number of
connections,
> each connection gets its own individual thread... Once you go over
that
> number, several connections will share the same thread... You can
increase
> the number of worker threads, but that uses more memory ( even if
there are
> fewer connections.)
> Thread sharing could result in front end timeouts ( especially if the
other
> connection you are sharing the thread with is a busy one.)
>
|||No. There is nothing like that or remotely similiar to that in the logs.
I've been having this issue now for over a month. I even completely rebuilt my SQL Server because
of it.
-- Jasper Smith wrote: --
Do you see any errors on the SQL error log e.g. "could not spawn process
login thread" or similar ? We have seen these under conditions of high
memory contention especially in the MemToLeave area where new threads cannot
allocate enough memory to start.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org

Max Num of Worker Threads and User Connections/Logins

Hi everyone,
I need to know what is the relationship between the "Maxium Number of Worker
Threads" setting (default = 255) and user connections/sessions/logins.
From time to time, we are experiencing random login failure for several busy
databases. On average, we see about 264 total sessions on the box and we we
re wondering if the max worker threads number, bumping it up would fix the i
ssue.
Can anyone offer any suggestions?
Thanks in advance,
Errol NealAs long as there are fewer than "Max worker threads" number of connections,
each connection gets its own individual thread... Once you go over that
number, several connections will share the same thread... You can increase
the number of worker threads, but that uses more memory ( even if there are
fewer connections.)
Thread sharing could result in front end timeouts ( especially if the other
connection you are sharing the thread with is a busy one.)
"Errol U. Neal" <eneal@.enhtech.com> wrote in message
news:09B0F39B-1468-4069-A8A1-BF2AF2E645C8@.microsoft.com...
> Hi everyone,
> I need to know what is the relationship between the "Maxium Number of
Worker Threads" setting (default = 255) and user
connections/sessions/logins.
> From time to time, we are experiencing random login failure for several
busy databases. On average, we see about 264 total sessions on the box and
we were wondering if the max worker threads number, bumping it up would fix
the issue.
> Can anyone offer any suggestions?
>
> Thanks in advance,
>
> Errol Neal|||Okay. I understand that. Thanks!
But why are we having random login failures? We are per-processor licensing
so I am confused. Its not that the login is timing out. They are outright fa
iling and I know because I am doing a SQL Server Trace and watching them as
they fail.
-- Wayne Snyder wrote: --
As long as there are fewer than "Max worker threads" number of connections,
each connection gets its own individual thread... Once you go over that
number, several connections will share the same thread... You can increase
the number of worker threads, but that uses more memory ( even if there are
fewer connections.)
Thread sharing could result in front end timeouts ( especially if the other
connection you are sharing the thread with is a busy one.)|||Do you see any errors on the SQL error log e.g. "could not spawn process
login thread" or similar ? We have seen these under conditions of high
memory contention especially in the MemToLeave area where new threads cannot
allocate enough memory to start.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Errol Uriel Neal" <eneal@.enhtech.com> wrote in message
news:94D7AB61-A7F2-4FA1-B19C-D79B64B3B23D@.microsoft.com...
> Okay. I understand that. Thanks!
> But why are we having random login failures? We are per-processor
licensing so I am confused. Its not that the login is timing out. They are
outright failing and I know because I am doing a SQL Server Trace and
watching them as they fail.
>
> -- Wayne Snyder wrote: --
> As long as there are fewer than "Max worker threads" number of
connections,
> each connection gets its own individual thread... Once you go over
that
> number, several connections will share the same thread... You can
increase
> the number of worker threads, but that uses more memory ( even if
there are
> fewer connections.)
> Thread sharing could result in front end timeouts ( especially if the
other
> connection you are sharing the thread with is a busy one.)
>|||No. There is nothing like that or remotely similiar to that in the logs.
I've been having this issue now for over a month. I even completely rebuilt
my SQL Server because
of it.
-- Jasper Smith wrote: --
Do you see any errors on the SQL error log e.g. "could not spawn process
login thread" or similar ? We have seen these under conditions of high
memory contention especially in the MemToLeave area where new threads cannot
allocate enough memory to start.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.orgsql

Max Num of Worker Threads and User Connections/Logins

Hi everyone
I need to know what is the relationship between the "Maxium Number of Worker Threads" setting (default = 255) and user connections/sessions/logins.
From time to time, we are experiencing random login failure for several busy databases. On average, we see about 264 total sessions on the box and we were wondering if the max worker threads number, bumping it up would fix the issue.
Can anyone offer any suggestions
Thanks in advance
Errol NealAs long as there are fewer than "Max worker threads" number of connections,
each connection gets its own individual thread... Once you go over that
number, several connections will share the same thread... You can increase
the number of worker threads, but that uses more memory ( even if there are
fewer connections.)
Thread sharing could result in front end timeouts ( especially if the other
connection you are sharing the thread with is a busy one.)
"Errol U. Neal" <eneal@.enhtech.com> wrote in message
news:09B0F39B-1468-4069-A8A1-BF2AF2E645C8@.microsoft.com...
> Hi everyone,
> I need to know what is the relationship between the "Maxium Number of
Worker Threads" setting (default = 255) and user
connections/sessions/logins.
> From time to time, we are experiencing random login failure for several
busy databases. On average, we see about 264 total sessions on the box and
we were wondering if the max worker threads number, bumping it up would fix
the issue.
> Can anyone offer any suggestions?
>
> Thanks in advance,
>
> Errol Neal|||Do you see any errors on the SQL error log e.g. "could not spawn process
login thread" or similar ? We have seen these under conditions of high
memory contention especially in the MemToLeave area where new threads cannot
allocate enough memory to start.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Errol Uriel Neal" <eneal@.enhtech.com> wrote in message
news:94D7AB61-A7F2-4FA1-B19C-D79B64B3B23D@.microsoft.com...
> Okay. I understand that. Thanks!
> But why are we having random login failures? We are per-processor
licensing so I am confused. Its not that the login is timing out. They are
outright failing and I know because I am doing a SQL Server Trace and
watching them as they fail.
>
> -- Wayne Snyder wrote: --
> As long as there are fewer than "Max worker threads" number of
connections,
> each connection gets its own individual thread... Once you go over
that
> number, several connections will share the same thread... You can
increase
> the number of worker threads, but that uses more memory ( even if
there are
> fewer connections.)
> Thread sharing could result in front end timeouts ( especially if the
other
> connection you are sharing the thread with is a busy one.)
>|||No. There is nothing like that or remotely similiar to that in the logs.
I've been having this issue now for over a month. I even completely rebuilt my SQL Server because
of it.
-- Jasper Smith wrote: --
Do you see any errors on the SQL error log e.g. "could not spawn proces
login thread" or similar ? We have seen these under conditions of hig
memory contention especially in the MemToLeave area where new threads canno
allocate enough memory to start
--
HT
Jasper Smith (SQL Server MVP
I support PASS - the definitive, globa
community for SQL Server professionals
http://www.sqlpass.or

max no. of values in WHERE ... IN clause

Hi all,

can anyone tell mey what the maximum number of values (if there is any) which can be used in the WHERE ... IN clause?

ex:
... WHERE ID IN (1, 2, 3 ... , n) ...
what is the maximum number of values which I can put between paranthesis?

10x in advance
mihai.That's decided by the specific DB system.|||10x in advance

"10X" = "thanks"? Whatever next...!|||How many can you put there is one question. How many can you put there and still get an answer while you are young enough to care is a different question. The answer to both questions varies significantly from one query and database engine to another.

-PatP

Max limit of Auto

May i know what is the MAX number Auto ID can go in a table for SQL server
'Depends on your datatype you use. Check out the capacities of the datatypes
INT, BIGINT, Decimal etc. in BooksOnLine.
--
Andrew J. Kelly SQL MVP
"Cason" <Cason@.cason.com> wrote in message
news:uie9yplAEHA.3352@.TK2MSFTNGP09.phx.gbl...
> May i know what is the MAX number Auto ID can go in a table for SQL server
> '
>|||A column with the IDENTITY property is limited only by the precision of the
underlying data type:
tinyint=127
smallint-32767
int=2147483647
bigint=9223372036854775807
decimal(38)=99999999999999999999999999999999999999
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Cason" <Cason@.cason.com> wrote in message
news:uie9yplAEHA.3352@.TK2MSFTNGP09.phx.gbl...
> May i know what is the MAX number Auto ID can go in a table for SQL server
> '
>|||Hi,
That depends up on the data type for which the column is associated.
Maximum limit for an autoid (Identity) is 2 power 63 that is
9,223,372,036,854,775,807. For this the data type should be BIGINT.
The below datypes are supported for Autoid (Identity)
1. Tinyint
2. Smallint
3. int
4. Bigint
1. Numeric
2. decimal
For Numeric and decimal scale shoule be '0' and it allows a maximum of
Numeric(38,0)
Thanks
Hari
MCDBA
"Cason" <Cason@.cason.com> wrote in message
news:uie9yplAEHA.3352@.TK2MSFTNGP09.phx.gbl...
> May i know what is the MAX number Auto ID can go in a table for SQL server
> '
>|||If you are speaking of an Identity column, it depends on the underlying data
type. if you use a bigint datatype with an identity with a seed and
increment of 1 then the maximum positive number is 9223372036854775808. If
you are looking at womething like a uniqueidentifier (GUID) I believe this
is unlimited...
Neil MacMurchy
Senior DBA
Regional Municipality of Niagara
"Cason" <Cason@.cason.com> wrote in message
news:uie9yplAEHA.3352@.TK2MSFTNGP09.phx.gbl...
> May i know what is the MAX number Auto ID can go in a table for SQL server
> '
>|||If under "Auto ID" you mean IDENTITY then it depends on the column's data
type.
For bigint:
Integer (whole number) data from -2^63 (-9,223,372,036,854,775,808) through
2^63-1 (9,223,372,036,854,775,807). Storage size is 8 bytes.
For decimal:
Fixed precision and scale numbers. When maximum precision is used, valid
values are from - 10^38 +1 through 10^38 - 1.
HTH
Igor Raytsin
"Cason" <Cason@.cason.com> wrote in message
news:uie9yplAEHA.3352@.TK2MSFTNGP09.phx.gbl...
> May i know what is the MAX number Auto ID can go in a table for SQL server
> '
>

Max limit of Auto

May i know what is the MAX number Auto ID can go in a table for SQL server
'Depends on your datatype you use. Check out the capacities of the datatypes
INT, BIGINT, Decimal etc. in BooksOnLine.
Andrew J. Kelly SQL MVP
"Cason" <Cason@.cason.com> wrote in message
news:uie9yplAEHA.3352@.TK2MSFTNGP09.phx.gbl...
> May i know what is the MAX number Auto ID can go in a table for SQL server
> '
>|||A column with the IDENTITY property is limited only by the precision of the
underlying data type:
tinyint=127
smallint-32767
int=2147483647
bigint=9223372036854775807
decimal(38)=9999999999999999999999999999
9999999999
Hope this helps.
Dan Guzman
SQL Server MVP
"Cason" <Cason@.cason.com> wrote in message
news:uie9yplAEHA.3352@.TK2MSFTNGP09.phx.gbl...
> May i know what is the MAX number Auto ID can go in a table for SQL server
> '
>|||Hi,
That depends up on the data type for which the column is associated.
Maximum limit for an autoid (Identity) is 2 power 63 that is
9,223,372,036,854,775,807. For this the data type should be BIGINT.
The below datypes are supported for Autoid (Identity)
1. Tinyint
2. Smallint
3. int
4. Bigint
1. Numeric
2. decimal
For Numeric and decimal scale shoule be '0' and it allows a maximum of
Numeric(38,0)
Thanks
Hari
MCDBA
"Cason" <Cason@.cason.com> wrote in message
news:uie9yplAEHA.3352@.TK2MSFTNGP09.phx.gbl...
> May i know what is the MAX number Auto ID can go in a table for SQL server
> '
>|||If you are speaking of an Identity column, it depends on the underlying data
type. if you use a bigint datatype with an identity with a seed and
increment of 1 then the maximum positive number is 9223372036854775808. If
you are looking at womething like a uniqueidentifier (GUID) I believe this
is unlimited...
Neil MacMurchy
Senior DBA
Regional Municipality of Niagara
"Cason" <Cason@.cason.com> wrote in message
news:uie9yplAEHA.3352@.TK2MSFTNGP09.phx.gbl...
> May i know what is the MAX number Auto ID can go in a table for SQL server
> '
>|||If under "Auto ID" you mean IDENTITY then it depends on the column's data
type.
For bigint:
Integer (whole number) data from -2^63 (-9,223,372,036,854,775,808) through
2^63-1 (9,223,372,036,854,775,807). Storage size is 8 bytes.
For decimal:
Fixed precision and scale numbers. When maximum precision is used, valid
values are from - 10^38 +1 through 10^38 - 1.
HTH
Igor Raytsin
"Cason" <Cason@.cason.com> wrote in message
news:uie9yplAEHA.3352@.TK2MSFTNGP09.phx.gbl...
> May i know what is the MAX number Auto ID can go in a table for SQL server
> '
>

Wednesday, March 28, 2012

Max file group per database in SQL Server 2005.

Hi All,
What’s the max number of file group in SQL Server 2005 database? I knew that
in SQL Server 2000, the max file group per database is 16.
Regards,
Chen
Actually there were 256 in SQL2000. In 2005 there are 32,767.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/13e95046-0e76-4604-b561-d1a74dd824d7.htm
Andrew J. Kelly SQL MVP
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:81540E0C-F76D-462E-9C4B-42E0C683D63E@.microsoft.com...
> Hi All,
> What?Ts the max number of file group in SQL Server 2005 database? I knew
> that
> in SQL Server 2000, the max file group per database is 16.
> Regards,
> Chen
>
|||From Books Online (2005): 32,767
The 2000 Books Online claims that max number of filegroups per database is 256. Did you try to
create more than 16? Either we have an error in Books Online, a bug in the product or perhaps you
was misinformed?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:81540E0C-F76D-462E-9C4B-42E0C683D63E@.microsoft.com...
> Hi All,
> What’s the max number of file group in SQL Server 2005 database? I knew that
> in SQL Server 2000, the max file group per database is 16.
> Regards,
> Chen
>
|||On Mon, 23 Apr 2007 20:24:41 +0100, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>http://msdn2.microsoft.com/en-us/library/aa933149(SQL.80).aspx
>According to my 2000 BOL the maximum was 256 in 2000 as well. I can't say
>I've ever put those limits to the test.
>It is often recommended that you should aim to have the same number of files
>as you have processors. On that basis you would be unlikely to need as many
>as 256 filegroups.
In regards to the processors, I would point out that this should be
read as relating to the number of processors for *active* filegroups,
you may want some more to hold archive stuff, and to facilitate
backups, to support partitioned tables, and to map to different
classes of storage (RAID 1,5,10).
All those good reasons, and I've never really played much with it
myself!
Josh
sql

Max file group per database in SQL Server 2005.

Hi All,
What’s the max number of file group in SQL Server 2005 database? I knew th
at
in SQL Server 2000, the max file group per database is 16.
Regards,
Chen"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:81540E0C-F76D-462E-9C4B-42E0C683D63E@.microsoft.com...
> Hi All,
> What's the max number of file group in SQL Server 2005 database? I knew
> that
> in SQL Server 2000, the max file group per database is 16.
> Regards,
> Chen
>
BOL is your friend:
http://msdn2.microsoft.com/en-us/library/aa933149(SQL.80).aspx
According to my 2000 BOL the maximum was 256 in 2000 as well. I can't say
I've ever put those limits to the test.
It is often recommended that you should aim to have the same number of files
as you have processors. On that basis you would be unlikely to need as many
as 256 filegroups.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Actually there were 256 in SQL2000. In 2005 there are 32,767.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/13e95046-0e76-4604-b561-
d1a74dd824d7.htm
Andrew J. Kelly SQL MVP
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:81540E0C-F76D-462E-9C4B-42E0C683D63E@.microsoft.com...
> Hi All,
> What?Ts the max number of file group in SQL Server 2005 database? I knew
> that
> in SQL Server 2000, the max file group per database is 16.
> Regards,
> Chen
>|||From Books Online (2005): 32,767
The 2000 Books Online claims that max number of filegroups per database is 2
56. Did you try to
create more than 16? Either we have an error in Books Online, a bug in the p
roduct or perhaps you
was misinformed?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:81540E0C-F76D-462E-9C4B-42E0C683D63E@.microsoft.com...
> Hi All,
> What’s the max number of file group in SQL Server 2005 database? I knew
that
> in SQL Server 2000, the max file group per database is 16.
> Regards,
> Chen
>|||On Mon, 23 Apr 2007 20:24:41 +0100, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>http://msdn2.microsoft.com/en-us/library/aa933149(SQL.80).aspx
>According to my 2000 BOL the maximum was 256 in 2000 as well. I can't say
>I've ever put those limits to the test.
>It is often recommended that you should aim to have the same number of file
s
>as you have processors. On that basis you would be unlikely to need as many
>as 256 filegroups.
In regards to the processors, I would point out that this should be
read as relating to the number of processors for *active* filegroups,
you may want some more to hold archive stuff, and to facilitate
backups, to support partitioned tables, and to map to different
classes of storage (RAID 1,5,10).
All those good reasons, and I've never really played much with it
myself!
Josh

Max file group per database in SQL Server 2005.

Hi All,
Whatâ's the max number of file group in SQL Server 2005 database? I knew that
in SQL Server 2000, the max file group per database is 16.
Regards,
Chen"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:81540E0C-F76D-462E-9C4B-42E0C683D63E@.microsoft.com...
> Hi All,
> What's the max number of file group in SQL Server 2005 database? I knew
> that
> in SQL Server 2000, the max file group per database is 16.
> Regards,
> Chen
>
BOL is your friend:
http://msdn2.microsoft.com/en-us/library/aa933149(SQL.80).aspx
According to my 2000 BOL the maximum was 256 in 2000 as well. I can't say
I've ever put those limits to the test.
It is often recommended that you should aim to have the same number of files
as you have processors. On that basis you would be unlikely to need as many
as 256 filegroups.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Actually there were 256 in SQL2000. In 2005 there are 32,767.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/13e95046-0e76-4604-b561-d1a74dd824d7.htm
--
Andrew J. Kelly SQL MVP
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:81540E0C-F76D-462E-9C4B-42E0C683D63E@.microsoft.com...
> Hi All,
> Whatâ?Ts the max number of file group in SQL Server 2005 database? I knew
> that
> in SQL Server 2000, the max file group per database is 16.
> Regards,
> Chen
>|||From Books Online (2005): 32,767
The 2000 Books Online claims that max number of filegroups per database is 256. Did you try to
create more than 16? Either we have an error in Books Online, a bug in the product or perhaps you
was misinformed?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:81540E0C-F76D-462E-9C4B-42E0C683D63E@.microsoft.com...
> Hi All,
> Whatâ's the max number of file group in SQL Server 2005 database? I knew that
> in SQL Server 2000, the max file group per database is 16.
> Regards,
> Chen
>|||On Mon, 23 Apr 2007 20:24:41 +0100, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:
>http://msdn2.microsoft.com/en-us/library/aa933149(SQL.80).aspx
>According to my 2000 BOL the maximum was 256 in 2000 as well. I can't say
>I've ever put those limits to the test.
>It is often recommended that you should aim to have the same number of files
>as you have processors. On that basis you would be unlikely to need as many
>as 256 filegroups.
In regards to the processors, I would point out that this should be
read as relating to the number of processors for *active* filegroups,
you may want some more to hold archive stuff, and to facilitate
backups, to support partitioned tables, and to map to different
classes of storage (RAID 1,5,10).
All those good reasons, and I've never really played much with it
myself!
Josh

Monday, March 26, 2012

Max count of logins

Hi!
SQL 2000 has a limitation of approx. ~ 16000 SQL Server logins.
(Don't know the exactly number yet, but the limitation is there...)
Did anyone know if SQL Server 2005 still has a limitation regarding the
number of possible SQL Server logins?
Thanks in advance.
Thomas
www.sqlscripter.com
Hi
Any that is a problem?
What is your scenario that you need so many logins?
How do you manage such a number?
Are you not using NT/AD groups and giving the groups access to he DB?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Thomas" wrote:

> Hi!
> SQL 2000 has a limitation of approx. ~ 16000 SQL Server logins.
> (Don't know the exactly number yet, but the limitation is there...)
> Did anyone know if SQL Server 2005 still has a limitation regarding the
> number of possible SQL Server logins?
> Thanks in advance.
> Thomas
> www.sqlscripter.com
>
|||Thanks for your feedback.
We need that for a Self Service system, i know that using NT (groups) we
will not have these problems ...
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Any that is a problem?
> What is your scenario that you need so many logins?
> How do you manage such a number?
> Are you not using NT/AD groups and giving the groups access to he DB?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Thomas" wrote:

Max count of logins

Hi!
SQL 2000 has a limitation of approx. ~ 16000 SQL Server logins.
(Don't know the exactly number yet, but the limitation is there...)
Did anyone know if SQL Server 2005 still has a limitation regarding the
number of possible SQL Server logins?
Thanks in advance.
Thomas
www.sqlscripter.comHi
Any that is a problem?
What is your scenario that you need so many logins?
How do you manage such a number?
Are you not using NT/AD groups and giving the groups access to he DB?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Thomas" wrote:

> Hi!
> SQL 2000 has a limitation of approx. ~ 16000 SQL Server logins.
> (Don't know the exactly number yet, but the limitation is there...)
> Did anyone know if SQL Server 2005 still has a limitation regarding the
> number of possible SQL Server logins?
> Thanks in advance.
> Thomas
> www.sqlscripter.com
>|||Thanks for your feedback.
We need that for a Self Service system, i know that using NT (groups) we
will not have these problems ...
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Any that is a problem?
> What is your scenario that you need so many logins?
> How do you manage such a number?
> Are you not using NT/AD groups and giving the groups access to he DB?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Thomas" wrote:
>

Max count of logins

Hi!
SQL 2000 has a limitation of approx. ~ 16000 SQL Server logins.
(Don't know the exactly number yet, but the limitation is there...)
Did anyone know if SQL Server 2005 still has a limitation regarding the
number of possible SQL Server logins?
Thanks in advance.
Thomas
www.sqlscripter.comHi
Any that is a problem?
What is your scenario that you need so many logins?
How do you manage such a number?
Are you not using NT/AD groups and giving the groups access to he DB?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Thomas" wrote:
> Hi!
> SQL 2000 has a limitation of approx. ~ 16000 SQL Server logins.
> (Don't know the exactly number yet, but the limitation is there...)
> Did anyone know if SQL Server 2005 still has a limitation regarding the
> number of possible SQL Server logins?
> Thanks in advance.
> Thomas
> www.sqlscripter.com
>|||Thanks for your feedback.
We need that for a Self Service system, i know that using NT (groups) we
will not have these problems ...
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Any that is a problem?
> What is your scenario that you need so many logins?
> How do you manage such a number?
> Are you not using NT/AD groups and giving the groups access to he DB?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Thomas" wrote:
> > Hi!
> >
> > SQL 2000 has a limitation of approx. ~ 16000 SQL Server logins.
> > (Don't know the exactly number yet, but the limitation is there...)
> >
> > Did anyone know if SQL Server 2005 still has a limitation regarding the
> > number of possible SQL Server logins?
> >
> > Thanks in advance.
> >
> > Thomas
> > www.sqlscripter.com
> >sql

Max connections in sql server 2000 MSDE

Hi !

What is the max number of connections in sql server 2000 MSDE ?

I have found three opinions on the web: 5,8 and unlimited(32,768...)

Which is true ?

/hakan

I don't think there is any limit to the number of connections. . .

there is a throttle on performance based on number of concurrent connections. the actual number that triggers the throttle i beleive depends on build/sp.

that being said. . . if you build your app correctly and design you database properly, you could probably service 100 to 200 concurrent users of your windows app without seeing a performance hit and thousands of concurrent users of a web app. thats just an opinion, don't ask me to prove it. but I will keep repeating that until someone from MS tells me differently.

but, build you app/db improperly and it doesnt matter which version of sql server you use. performance is going to suck any way.

MAx # if Report Params?

Does anyone happen to know if there is a maximum Number of report parameters
supported?
thanks
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.There is no limit on the number of report parameters.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uvAlddBRFHA.3140@.tk2msftngp13.phx.gbl...
> Does anyone happen to know if there is a maximum Number of report
> parameters
> supported?
> thanks
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
>

Friday, March 23, 2012

Matrix with Percentages

Is there any way to create a matrix table like this? I can't figure out how
to do percentages.
Rank Number %
Professor 48 49
Associate Professor 25 26
Assistant Professor 24 25
Total 97 100
Thanks!
Danhi Dan
I have a table in my report and have the following as an expression.
=FormatPercent(Fields!SLAResponseCompliant.Value/Fields!ClosedTickets.Value)
not sure how it will go with a matrix table but if you use something
similar? or it may at least give you something to work with.
Jewel
"Dan" wrote:
> Is there any way to create a matrix table like this? I can't figure out how
> to do percentages.
> Rank Number %
> Professor 48 49
> Associate Professor 25 26
> Assistant Professor 24 25
> Total 97 100
> Thanks!
> Dan
>sql

Matrix subtotals

I have a matrix where the columns show the number of attained grades for a
location along with a subtotal.
Location A B C Passes
London 2 4 5 11
Each grade has a number of points, eg A is worth 4 points, B 3 points etc. I
need to add an extra subtotal column to give me the total points.
Location A B C Passes TotPoints
London 2 4 5 11 30
Is this possible? The reference material suggests that the Total columns are
always based on the other columns. That means I'd have to display the total
points for each grade which the user's don't want.
It gets worse as they also want the average points per candidate.
Have I missed something? Is there a way to do this?
Thanks,
AndrewHello Andrew,
Unfortunately, you could not add another SubTotal Column in the Matrix.
My suggestion is you could add a Table just beside the matrix to show the
SubTotal.
Also, if the Grade column is fixed, you could use a Table instead of Matrix.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the reply. A matrix would've been useful as the number of grades
is variable.
Sounds like an opportunity for extra functionality in the matrix :)
Andrew|||Hello Andrew,
You may submit your feedback to the produce team directly.
http://connect.microsoft.com/sql
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||On Jul 17, 6:22 am, Duke (AN247) <D...@.newsgroup.nospam> wrote:
> I have a matrix where the columns show the number of attained grades for a
> location along with a subtotal.
> Location A B C Passes
> London 2 4 5 11
> Each grade has a number of points, eg A is worth 4 points, B 3 points etc. I
> need to add an extra subtotal column to give me the total points.
> Location A B C Passes TotPoints
> London 2 4 5 11 30
> Is this possible? The reference material suggests that the Total columns are
> always based on the other columns. That means I'd have to display the total
> points for each grade which the user's don't want.
> It gets worse as they also want the average points per candidate.
> Have I missed something? Is there a way to do this?
> Thanks,
> Andrew
For some reason my answer was not submitted.
Press on the column title and right click. From the drop down menu
press "Subtotal", it will add a column that does totals to your
matrix. Hope that helps.|||Hello Ayman,
Thanks for your provide. Your suggestion will create the Subtotal column.
But Duke's issue is that he wants to add an additional column which not
only sum the detail values.
Currently reporting services did not provide such a function for Matrix.
Anyway, thank you for your efforts.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||On Jul 17, 6:22 am, Duke (AN247) <D...@.newsgroup.nospam> wrote:
> I have a matrix where the columns show the number of attained grades for a
> location along with a subtotal.
> Location A B C Passes
> London 2 4 5 11
> Each grade has a number of points, eg A is worth 4 points, B 3 points etc. I
> need to add an extra subtotal column to give me the total points.
> Location A B C Passes TotPoints
> London 2 4 5 11 30
> Is this possible? The reference material suggests that the Total columns are
> always based on the other columns. That means I'd have to display the total
> points for each grade which the user's don't want.
> It gets worse as they also want the average points per candidate.
> Have I missed something? Is there a way to do this?
> Thanks,
> Andrew
Right click on the matrix column header and press "Subtotal" from the
drop down menu. That should work.

Matrix Subtotal Format

Have several rows in a Matrix, every row has a different format (percentage,
Currently, Number). The Matrix has 3 groups (all groups not displayed below)
Average Shop1 Shop2 Shop3
Technicians 8.0 7 5 2
Efficiency 70.5% 60.0 75.2 70.1
Hours 1,000 500 2000 1000
I do an average for all columns display the average for that column on the
left side
In my development environment (Visual Studio) the average column formatting
works fine and takes the formatting of other columns in the row. After being
deployed to the reporting server and viewing through the report viewer the
formatting does not seem to work and seems to take the formatting of the
first group.
ThanksAfter hours of digging, the issue seems to be because of SP2.
http://forums.microsoft.com/MSDN/showpost.aspx?postid=2215374&siteid=1
"jeo78" wrote:
> Have several rows in a Matrix, every row has a different format (percentage,
> Currently, Number). The Matrix has 3 groups (all groups not displayed below)
> Average Shop1 Shop2 Shop3
> Technicians 8.0 7 5 2
> Efficiency 70.5% 60.0 75.2 70.1
> Hours 1,000 500 2000 1000
> I do an average for all columns display the average for that column on the
> left side
> In my development environment (Visual Studio) the average column formatting
> works fine and takes the formatting of other columns in the row. After being
> deployed to the reporting server and viewing through the report viewer the
> formatting does not seem to work and seems to take the formatting of the
> first group.
> Thankssql

Wednesday, March 21, 2012

Matrix row total

I have a count(field!orderstamp.Value) in a field and I would like to
show the percentage that number is of the whole row?
I can add a column next to it, but how do I show a percentage of a row
in a matrix?
Thanks.Assuming you have a matrix row grouping called "MatrixRowGroup", you can try
this:
=Count(Fields!orderstamp.Value) / Count(Fields!orderstamp.Value,
"MatrixRowGroup")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Geddes" <john_g@.alamode.com> wrote in message
news:OVtGb$p8EHA.2804@.TK2MSFTNGP15.phx.gbl...
> I have a count(field!orderstamp.Value) in a field and I would like to
> show the percentage that number is of the whole row?
> I can add a column next to it, but how do I show a percentage of a row
> in a matrix?
> Thanks.
>

Matrix row number?

I have a matrix. For each person, they may have multiple rows in the dataset
but it is translated to a single row with multiple columns using the matrix.
I want to put a number in the first column indicating the occurence starting
at one and incrementing by 1 for each person: 1, 2, 3, 4, 5, etc. I've tried
RowNumber, Count, RunningValue with no success. Perhaps I am using the wrong
scope.
Does anyone know how to create the row number for the matrix?
StephanieI found it:
RunningValue(Fields!Country.Value,CountDistinct,Nothing)
Stephanie
"Stephanie" wrote:
> I have a matrix. For each person, they may have multiple rows in the dataset
> but it is translated to a single row with multiple columns using the matrix.
> I want to put a number in the first column indicating the occurence starting
> at one and incrementing by 1 for each person: 1, 2, 3, 4, 5, etc. I've tried
> RowNumber, Count, RunningValue with no success. Perhaps I am using the wrong
> scope.
> Does anyone know how to create the row number for the matrix?
> Stephanie

Matrix Row Headers After Data

I have a report that uses Analysis Services data in a matrix. The column
groups in the matrix are based on date, and the number of column groups is
variable based on the start time and end time entered as report parameters.
I'd like to display the row headers after the column groups, regardless of
the number of column groups. The "GroupsBeforeRowHeaders" property looks
like it will work, but it only takes an integer - I would like to set it to
"All" or something similar.
Is there any way to do this without writing code that will calculate the
number of column groups and then using that in an expression for the
"GroupsBeforeRowHeaders" property? I'd like to avoid doing this because the
start and end times may be weeks, months, or years, so calculating the
number of columns would be a pain.
Thanks for any help,
Sean Carpenter
ProMetrics Consulting, Inc.Did you try setting the integer to a very large value, e.g. 100000000
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sean Carpenter" <stcarpenter2005@.community.nospam> wrote in message
news:u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl...
> I have a report that uses Analysis Services data in a matrix. The column
> groups in the matrix are based on date, and the number of column groups is
> variable based on the start time and end time entered as report
parameters.
> I'd like to display the row headers after the column groups, regardless of
> the number of column groups. The "GroupsBeforeRowHeaders" property looks
> like it will work, but it only takes an integer - I would like to set it
to
> "All" or something similar.
> Is there any way to do this without writing code that will calculate the
> number of column groups and then using that in an expression for the
> "GroupsBeforeRowHeaders" property? I'd like to avoid doing this because
the
> start and end times may be weeks, months, or years, so calculating the
> number of columns would be a pain.
> Thanks for any help,
> Sean Carpenter
> ProMetrics Consulting, Inc.
>|||Yes. If I set it to anything larger than the actual number of column
groups, it doesn't appear to do anything - the headers stay to the left of
all of the column groups.
Sean Carpenter
ProMetrics Consulting, Inc.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:u7k3Tj%235EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Did you try setting the integer to a very large value, e.g. 100000000
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Sean Carpenter" <stcarpenter2005@.community.nospam> wrote in message
> news:u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl...
>> I have a report that uses Analysis Services data in a matrix. The column
>> groups in the matrix are based on date, and the number of column groups
>> is
>> variable based on the start time and end time entered as report
> parameters.
>> I'd like to display the row headers after the column groups, regardless
>> of
>> the number of column groups. The "GroupsBeforeRowHeaders" property looks
>> like it will work, but it only takes an integer - I would like to set it
> to
>> "All" or something similar.
>> Is there any way to do this without writing code that will calculate the
>> number of column groups and then using that in an expression for the
>> "GroupsBeforeRowHeaders" property? I'd like to avoid doing this because
> the
>> start and end times may be weeks, months, or years, so calculating the
>> number of columns would be a pain.
>> Thanks for any help,
>> Sean Carpenter
>> ProMetrics Consulting, Inc.
>>
>|||I have the same thing and I did not set the group header number. It is set
to 0 and the Matrix Columns expand Right to left is selected. Thus all of my
columns appear to the left and then my rows appear to right.
Hope this is not to late to help, if it works for you.
"Sean Carpenter" wrote:
> Yes. If I set it to anything larger than the actual number of column
> groups, it doesn't appear to do anything - the headers stay to the left of
> all of the column groups.
> Sean Carpenter
> ProMetrics Consulting, Inc.
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:u7k3Tj%235EHA.1408@.TK2MSFTNGP10.phx.gbl...
> > Did you try setting the integer to a very large value, e.g. 100000000
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> > "Sean Carpenter" <stcarpenter2005@.community.nospam> wrote in message
> > news:u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl...
> >> I have a report that uses Analysis Services data in a matrix. The column
> >> groups in the matrix are based on date, and the number of column groups
> >> is
> >> variable based on the start time and end time entered as report
> > parameters.
> >> I'd like to display the row headers after the column groups, regardless
> >> of
> >> the number of column groups. The "GroupsBeforeRowHeaders" property looks
> >> like it will work, but it only takes an integer - I would like to set it
> > to
> >> "All" or something similar.
> >>
> >> Is there any way to do this without writing code that will calculate the
> >> number of column groups and then using that in an expression for the
> >> "GroupsBeforeRowHeaders" property? I'd like to avoid doing this because
> > the
> >> start and end times may be weeks, months, or years, so calculating the
> >> number of columns would be a pain.
> >>
> >> Thanks for any help,
> >> Sean Carpenter
> >> ProMetrics Consulting, Inc.
> >>
> >>
> >
> >
>
>|||I didn't know about that property of the matrix. I tried it and it works,
except that it reverses the order of all of the columns (which makes sense
considering what the property does). I can reverse the order of the columns
in the query result so that when the matrix reverses them again they display
correctly, but it would be nice to have a "clean" way of doing this. This
will definitely get me through the current report, though.
Sean Carpenter
ProMetrics Consulting, Inc.
"HBWAL" wrote:
> I have the same thing and I did not set the group header number. It is set
> to 0 and the Matrix Columns expand Right to left is selected. Thus all of my
> columns appear to the left and then my rows appear to right.
> Hope this is not to late to help, if it works for you.
> "Sean Carpenter" wrote:
> > Yes. If I set it to anything larger than the actual number of column
> > groups, it doesn't appear to do anything - the headers stay to the left of
> > all of the column groups.
> >
> > Sean Carpenter
> > ProMetrics Consulting, Inc.
> >
> > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> > news:u7k3Tj%235EHA.1408@.TK2MSFTNGP10.phx.gbl...
> > > Did you try setting the integer to a very large value, e.g. 100000000
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > >
> > > "Sean Carpenter" <stcarpenter2005@.community.nospam> wrote in message
> > > news:u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl...
> > >> I have a report that uses Analysis Services data in a matrix. The column
> > >> groups in the matrix are based on date, and the number of column groups
> > >> is
> > >> variable based on the start time and end time entered as report
> > > parameters.
> > >> I'd like to display the row headers after the column groups, regardless
> > >> of
> > >> the number of column groups. The "GroupsBeforeRowHeaders" property looks
> > >> like it will work, but it only takes an integer - I would like to set it
> > > to
> > >> "All" or something similar.
> > >>
> > >> Is there any way to do this without writing code that will calculate the
> > >> number of column groups and then using that in an expression for the
> > >> "GroupsBeforeRowHeaders" property? I'd like to avoid doing this because
> > > the
> > >> start and end times may be weeks, months, or years, so calculating the
> > >> number of columns would be a pain.
> > >>
> > >> Thanks for any help,
> > >> Sean Carpenter
> > >> ProMetrics Consulting, Inc.
> > >>
> > >>
> > >
> > >
> >
> >
> >|||Regarding the order - you should add a sort expression on the column
grouping of the matrix. The RS processing engine does not change any order
if there is no sorting applied in the report. You get the data in the order
returned by the data provider.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sean Carpenter" <SeanCarpenter@.discussions.microsoft.com> wrote in message
news:34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com...
> I didn't know about that property of the matrix. I tried it and it works,
> except that it reverses the order of all of the columns (which makes sense
> considering what the property does). I can reverse the order of the
columns
> in the query result so that when the matrix reverses them again they
display
> correctly, but it would be nice to have a "clean" way of doing this. This
> will definitely get me through the current report, though.
> Sean Carpenter
> ProMetrics Consulting, Inc.
> "HBWAL" wrote:
> > I have the same thing and I did not set the group header number. It is
set
> > to 0 and the Matrix Columns expand Right to left is selected. Thus all
of my
> > columns appear to the left and then my rows appear to right.
> >
> > Hope this is not to late to help, if it works for you.
> >
> > "Sean Carpenter" wrote:
> >
> > > Yes. If I set it to anything larger than the actual number of column
> > > groups, it doesn't appear to do anything - the headers stay to the
left of
> > > all of the column groups.
> > >
> > > Sean Carpenter
> > > ProMetrics Consulting, Inc.
> > >
> > > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in
message
> > > news:u7k3Tj%235EHA.1408@.TK2MSFTNGP10.phx.gbl...
> > > > Did you try setting the integer to a very large value, e.g.
100000000
> > > >
> > > > --
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > > > rights.
> > > >
> > > > "Sean Carpenter" <stcarpenter2005@.community.nospam> wrote in message
> > > > news:u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl...
> > > >> I have a report that uses Analysis Services data in a matrix. The
column
> > > >> groups in the matrix are based on date, and the number of column
groups
> > > >> is
> > > >> variable based on the start time and end time entered as report
> > > > parameters.
> > > >> I'd like to display the row headers after the column groups,
regardless
> > > >> of
> > > >> the number of column groups. The "GroupsBeforeRowHeaders" property
looks
> > > >> like it will work, but it only takes an integer - I would like to
set it
> > > > to
> > > >> "All" or something similar.
> > > >>
> > > >> Is there any way to do this without writing code that will
calculate the
> > > >> number of column groups and then using that in an expression for
the
> > > >> "GroupsBeforeRowHeaders" property? I'd like to avoid doing this
because
> > > > the
> > > >> start and end times may be weeks, months, or years, so calculating
the
> > > >> number of columns would be a pain.
> > > >>
> > > >> Thanks for any help,
> > > >> Sean Carpenter
> > > >> ProMetrics Consulting, Inc.
> > > >>
> > > >>
> > > >
> > > >
> > >
> > >
> > >|||I understand that RS doesn't change the order - the problem is that when I
set the matrix to expand from right to left, this effectively changes the
order of the data. I can't sort in the matrix because the groups need to be
sorted correctly by date, by the column grouping is based on the date name
returned from Analysis Services (which doesn't always sort the same as the
actual date).
Either way, it turns out this doesn't help since when I export to Excel, the
row headers are still on the left of the data - it seems the excel export
ignores the "LayoutDirection" property of the matrix.
I guess I'll be stuck with calculating the number of column groups and
setting the "GroupsBeforeRowHeaders" property using an expression.
Sean Carpenter
ProMetrics Consulting, Inc.
"Robert Bruckner [MSFT]" wrote:
> Regarding the order - you should add a sort expression on the column
> grouping of the matrix. The RS processing engine does not change any order
> if there is no sorting applied in the report. You get the data in the order
> returned by the data provider.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Sean Carpenter" <SeanCarpenter@.discussions.microsoft.com> wrote in message
> news:34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com...
> > I didn't know about that property of the matrix. I tried it and it works,
> > except that it reverses the order of all of the columns (which makes sense
> > considering what the property does). I can reverse the order of the
> columns
> > in the query result so that when the matrix reverses them again they
> display
> > correctly, but it would be nice to have a "clean" way of doing this. This
> > will definitely get me through the current report, though.
> >
> > Sean Carpenter
> > ProMetrics Consulting, Inc.
> >
> > "HBWAL" wrote:
> >
> > > I have the same thing and I did not set the group header number. It is
> set
> > > to 0 and the Matrix Columns expand Right to left is selected. Thus all
> of my
> > > columns appear to the left and then my rows appear to right.
> > >
> > > Hope this is not to late to help, if it works for you.
> > >
> > > "Sean Carpenter" wrote:
> > >
> > > > Yes. If I set it to anything larger than the actual number of column
> > > > groups, it doesn't appear to do anything - the headers stay to the
> left of
> > > > all of the column groups.
> > > >
> > > > Sean Carpenter
> > > > ProMetrics Consulting, Inc.
> > > >
> > > > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in
> message
> > > > news:u7k3Tj%235EHA.1408@.TK2MSFTNGP10.phx.gbl...
> > > > > Did you try setting the integer to a very large value, e.g.
> 100000000
> > > > >
> > > > > --
> > > > > This posting is provided "AS IS" with no warranties, and confers no
> > > > > rights.
> > > > >
> > > > > "Sean Carpenter" <stcarpenter2005@.community.nospam> wrote in message
> > > > > news:u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl...
> > > > >> I have a report that uses Analysis Services data in a matrix. The
> column
> > > > >> groups in the matrix are based on date, and the number of column
> groups
> > > > >> is
> > > > >> variable based on the start time and end time entered as report
> > > > > parameters.
> > > > >> I'd like to display the row headers after the column groups,
> regardless
> > > > >> of
> > > > >> the number of column groups. The "GroupsBeforeRowHeaders" property
> looks
> > > > >> like it will work, but it only takes an integer - I would like to
> set it
> > > > > to
> > > > >> "All" or something similar.
> > > > >>
> > > > >> Is there any way to do this without writing code that will
> calculate the
> > > > >> number of column groups and then using that in an expression for
> the
> > > > >> "GroupsBeforeRowHeaders" property? I'd like to avoid doing this
> because
> > > > > the
> > > > >> start and end times may be weeks, months, or years, so calculating
> the
> > > > >> number of columns would be a pain.
> > > > >>
> > > > >> Thanks for any help,
> > > > >> Sean Carpenter
> > > > >> ProMetrics Consulting, Inc.
> > > > >>
> > > > >>
> > > > >
> > > > >
> > > >
> > > >
> > > >
>
>|||It looks like my solution from the previous post won't work. I wrote a
function in the Custom Code section of the report to return the correct
number of column groups based on the report parameters. When I try to set
the "GroupsBeforeRowHeaders" property to "=Code.ColumnCount()", it won't let
me since it requires an Int32 value for the property.
Does anyone have any ideas on this one? I'd like to have the row headers to
the right of the column groups and have it appear that way on Excel export as
well.
Thanks,
Sean Carpenter
ProMetrics Consulting, Inc.
"Robert Bruckner [MSFT]" wrote:
> Regarding the order - you should add a sort expression on the column
> grouping of the matrix. The RS processing engine does not change any order
> if there is no sorting applied in the report. You get the data in the order
> returned by the data provider.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Sean Carpenter" <SeanCarpenter@.discussions.microsoft.com> wrote in message
> news:34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com...
> > I didn't know about that property of the matrix. I tried it and it works,
> > except that it reverses the order of all of the columns (which makes sense
> > considering what the property does). I can reverse the order of the
> columns
> > in the query result so that when the matrix reverses them again they
> display
> > correctly, but it would be nice to have a "clean" way of doing this. This
> > will definitely get me through the current report, though.
> >
> > Sean Carpenter
> > ProMetrics Consulting, Inc.
> >
> > "HBWAL" wrote:
> >
> > > I have the same thing and I did not set the group header number. It is
> set
> > > to 0 and the Matrix Columns expand Right to left is selected. Thus all
> of my
> > > columns appear to the left and then my rows appear to right.
> > >
> > > Hope this is not to late to help, if it works for you.
> > >
> > > "Sean Carpenter" wrote:
> > >
> > > > Yes. If I set it to anything larger than the actual number of column
> > > > groups, it doesn't appear to do anything - the headers stay to the
> left of
> > > > all of the column groups.
> > > >
> > > > Sean Carpenter
> > > > ProMetrics Consulting, Inc.
> > > >
> > > > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in
> message
> > > > news:u7k3Tj%235EHA.1408@.TK2MSFTNGP10.phx.gbl...
> > > > > Did you try setting the integer to a very large value, e.g.
> 100000000
> > > > >
> > > > > --
> > > > > This posting is provided "AS IS" with no warranties, and confers no
> > > > > rights.
> > > > >
> > > > > "Sean Carpenter" <stcarpenter2005@.community.nospam> wrote in message
> > > > > news:u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl...
> > > > >> I have a report that uses Analysis Services data in a matrix. The
> column
> > > > >> groups in the matrix are based on date, and the number of column
> groups
> > > > >> is
> > > > >> variable based on the start time and end time entered as report
> > > > > parameters.
> > > > >> I'd like to display the row headers after the column groups,
> regardless
> > > > >> of
> > > > >> the number of column groups. The "GroupsBeforeRowHeaders" property
> looks
> > > > >> like it will work, but it only takes an integer - I would like to
> set it
> > > > > to
> > > > >> "All" or something similar.
> > > > >>
> > > > >> Is there any way to do this without writing code that will
> calculate the
> > > > >> number of column groups and then using that in an expression for
> the
> > > > >> "GroupsBeforeRowHeaders" property? I'd like to avoid doing this
> because
> > > > > the
> > > > >> start and end times may be weeks, months, or years, so calculating
> the
> > > > >> number of columns would be a pain.
> > > > >>
> > > > >> Thanks for any help,
> > > > >> Sean Carpenter
> > > > >> ProMetrics Consulting, Inc.
> > > > >>
> > > > >>
> > > > >
> > > > >
> > > >
> > > >
> > > >
>
>|||Hello Sean,
GroupsBeforeRowHeaders property is indeed an integer and you will need to
know how many instances you want to move before row headings.
The matrix LayoutDirection = RTL should help you to achieve what you want
and adding a sort expression in the report (the sorting will be done by our
processing component) should help you to sort them in the order you want.
Could you provide the report (created by CU) and a screenshot with the
desired result? You could send them to me at petery@.microsoft.com
Thanks & Regards,
Peter Yang
MCSE2000, MCSA, MCDBA
Microsoft Partner Online Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Matrix Row Headers After Data
| thread-index: AcTuES8Dac2k+nRYQjO7EsINx2zh4g==| X-WBNR-Posting-Host: 141.151.17.169
| From: "=?Utf-8?B?U2VhbiBDYXJwZW50ZXI=?="
<SeanCarpenter@.discussions.microsoft.com>
| References: <u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl>
<u7k3Tj#5EHA.1408@.TK2MSFTNGP10.phx.gbl>
<emm2uuE6EHA.3336@.TK2MSFTNGP11.phx.gbl>
<3F5F013D-5CBD-4D63-AB93-1FAE12CD78FC@.microsoft.com>
<34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com>
<etWT1$g6EHA.992@.TK2MSFTNGP12.phx.gbl>
| Subject: Re: Matrix Row Headers After Data
| Date: Wed, 29 Dec 2004 17:45:04 -0800
| Lines: 113
| Message-ID: <1032A92D-A366-4E70-8A5C-9734EB226004@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:38404
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| It looks like my solution from the previous post won't work. I wrote a
| function in the Custom Code section of the report to return the correct
| number of column groups based on the report parameters. When I try to
set
| the "GroupsBeforeRowHeaders" property to "=Code.ColumnCount()", it won't
let
| me since it requires an Int32 value for the property.
|
| Does anyone have any ideas on this one? I'd like to have the row headers
to
| the right of the column groups and have it appear that way on Excel
export as
| well.
|
| Thanks,
| Sean Carpenter
| ProMetrics Consulting, Inc.
|
| "Robert Bruckner [MSFT]" wrote:
|
| > Regarding the order - you should add a sort expression on the column
| > grouping of the matrix. The RS processing engine does not change any
order
| > if there is no sorting applied in the report. You get the data in the
order
| > returned by the data provider.
| >
| > --
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| >
| > "Sean Carpenter" <SeanCarpenter@.discussions.microsoft.com> wrote in
message
| > news:34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com...
| > > I didn't know about that property of the matrix. I tried it and it
works,
| > > except that it reverses the order of all of the columns (which makes
sense
| > > considering what the property does). I can reverse the order of the
| > columns
| > > in the query result so that when the matrix reverses them again they
| > display
| > > correctly, but it would be nice to have a "clean" way of doing this.
This
| > > will definitely get me through the current report, though.
| > >
| > > Sean Carpenter
| > > ProMetrics Consulting, Inc.
| > >
| > > "HBWAL" wrote:
| > >
| > > > I have the same thing and I did not set the group header number.
It is
| > set
| > > > to 0 and the Matrix Columns expand Right to left is selected. Thus
all
| > of my
| > > > columns appear to the left and then my rows appear to right.
| > > >
| > > > Hope this is not to late to help, if it works for you.
| > > >
| > > > "Sean Carpenter" wrote:
| > > >
| > > > > Yes. If I set it to anything larger than the actual number of
column
| > > > > groups, it doesn't appear to do anything - the headers stay to the
| > left of
| > > > > all of the column groups.
| > > > >
| > > > > Sean Carpenter
| > > > > ProMetrics Consulting, Inc.
| > > > >
| > > > > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in
| > message
| > > > > news:u7k3Tj%235EHA.1408@.TK2MSFTNGP10.phx.gbl...
| > > > > > Did you try setting the integer to a very large value, e.g.
| > 100000000
| > > > > >
| > > > > > --
| > > > > > This posting is provided "AS IS" with no warranties, and
confers no
| > > > > > rights.
| > > > > >
| > > > > > "Sean Carpenter" <stcarpenter2005@.community.nospam> wrote in
message
| > > > > > news:u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl...
| > > > > >> I have a report that uses Analysis Services data in a matrix.
The
| > column
| > > > > >> groups in the matrix are based on date, and the number of
column
| > groups
| > > > > >> is
| > > > > >> variable based on the start time and end time entered as report
| > > > > > parameters.
| > > > > >> I'd like to display the row headers after the column groups,
| > regardless
| > > > > >> of
| > > > > >> the number of column groups. The "GroupsBeforeRowHeaders"
property
| > looks
| > > > > >> like it will work, but it only takes an integer - I would like
to
| > set it
| > > > > > to
| > > > > >> "All" or something similar.
| > > > > >>
| > > > > >> Is there any way to do this without writing code that will
| > calculate the
| > > > > >> number of column groups and then using that in an expression
for
| > the
| > > > > >> "GroupsBeforeRowHeaders" property? I'd like to avoid doing
this
| > because
| > > > > > the
| > > > > >> start and end times may be weeks, months, or years, so
calculating
| > the
| > > > > >> number of columns would be a pain.
| > > > > >>
| > > > > >> Thanks for any help,
| > > > > >> Sean Carpenter
| > > > > >> ProMetrics Consulting, Inc.
| > > > > >>
| > > > > >>
| > > > > >
| > > > > >
| > > > >
| > > > >
| > > > >
| >
| >
| >
||||Peter,
I have emailed you an example report that exhibits the problem I'm having.
Thanks for you help.
Sean Carpenter
ProMetrics Consulting, Inc.
"Peter Yang [MSFT]" wrote:
> Hello Sean,
> GroupsBeforeRowHeaders property is indeed an integer and you will need to
> know how many instances you want to move before row headings.
> The matrix LayoutDirection = RTL should help you to achieve what you want
> and adding a sort expression in the report (the sorting will be done by our
> processing component) should help you to sort them in the order you want.
> Could you provide the report (created by CU) and a screenshot with the
> desired result? You could send them to me at petery@.microsoft.com
> Thanks & Regards,
> Peter Yang
> MCSE2000, MCSA, MCDBA
> Microsoft Partner Online Support
> Get Secure! - www.microsoft.com/security
> =====================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> | Thread-Topic: Matrix Row Headers After Data
> | thread-index: AcTuES8Dac2k+nRYQjO7EsINx2zh4g==> | X-WBNR-Posting-Host: 141.151.17.169
> | From: "=?Utf-8?B?U2VhbiBDYXJwZW50ZXI=?="
> <SeanCarpenter@.discussions.microsoft.com>
> | References: <u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl>
> <u7k3Tj#5EHA.1408@.TK2MSFTNGP10.phx.gbl>
> <emm2uuE6EHA.3336@.TK2MSFTNGP11.phx.gbl>
> <3F5F013D-5CBD-4D63-AB93-1FAE12CD78FC@.microsoft.com>
> <34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com>
> <etWT1$g6EHA.992@.TK2MSFTNGP12.phx.gbl>
> | Subject: Re: Matrix Row Headers After Data
> | Date: Wed, 29 Dec 2004 17:45:04 -0800
> | Lines: 113
> | Message-ID: <1032A92D-A366-4E70-8A5C-9734EB226004@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:38404
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | It looks like my solution from the previous post won't work. I wrote a
> | function in the Custom Code section of the report to return the correct
> | number of column groups based on the report parameters. When I try to
> set
> | the "GroupsBeforeRowHeaders" property to "=Code.ColumnCount()", it won't
> let
> | me since it requires an Int32 value for the property.
> |
> | Does anyone have any ideas on this one? I'd like to have the row headers
> to
> | the right of the column groups and have it appear that way on Excel
> export as
> | well.
> |
> | Thanks,
> | Sean Carpenter
> | ProMetrics Consulting, Inc.
> |
> | "Robert Bruckner [MSFT]" wrote:
> |
> | > Regarding the order - you should add a sort expression on the column
> | > grouping of the matrix. The RS processing engine does not change any
> order
> | > if there is no sorting applied in the report. You get the data in the
> order
> | > returned by the data provider.
> | >
> | > --
> | > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> | >
> | >
> | >
> | > "Sean Carpenter" <SeanCarpenter@.discussions.microsoft.com> wrote in
> message
> | > news:34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com...
> | > > I didn't know about that property of the matrix. I tried it and it
> works,
> | > > except that it reverses the order of all of the columns (which makes
> sense
> | > > considering what the property does). I can reverse the order of the
> | > columns
> | > > in the query result so that when the matrix reverses them again they
> | > display
> | > > correctly, but it would be nice to have a "clean" way of doing this.
> This
> | > > will definitely get me through the current report, though.
> | > >
> | > > Sean Carpenter
> | > > ProMetrics Consulting, Inc.
> | > >
> | > > "HBWAL" wrote:
> | > >
> | > > > I have the same thing and I did not set the group header number.
> It is
> | > set
> | > > > to 0 and the Matrix Columns expand Right to left is selected. Thus
> all
> | > of my
> | > > > columns appear to the left and then my rows appear to right.
> | > > >
> | > > > Hope this is not to late to help, if it works for you.
> | > > >
> | > > > "Sean Carpenter" wrote:
> | > > >
> | > > > > Yes. If I set it to anything larger than the actual number of
> column
> | > > > > groups, it doesn't appear to do anything - the headers stay to the
> | > left of
> | > > > > all of the column groups.
> | > > > >
> | > > > > Sean Carpenter
> | > > > > ProMetrics Consulting, Inc.
> | > > > >
> | > > > > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in
> | > message
> | > > > > news:u7k3Tj%235EHA.1408@.TK2MSFTNGP10.phx.gbl...
> | > > > > > Did you try setting the integer to a very large value, e.g.
> | > 100000000
> | > > > > >
> | > > > > > --
> | > > > > > This posting is provided "AS IS" with no warranties, and
> confers no
> | > > > > > rights.
> | > > > > >
> | > > > > > "Sean Carpenter" <stcarpenter2005@.community.nospam> wrote in
> message
> | > > > > > news:u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl...
> | > > > > >> I have a report that uses Analysis Services data in a matrix.
> The
> | > column
> | > > > > >> groups in the matrix are based on date, and the number of
> column
> | > groups
> | > > > > >> is
> | > > > > >> variable based on the start time and end time entered as report
> | > > > > > parameters.
> | > > > > >> I'd like to display the row headers after the column groups,
> | > regardless
> | > > > > >> of
> | > > > > >> the number of column groups. The "GroupsBeforeRowHeaders"
> property
> | > looks
> | > > > > >> like it will work, but it only takes an integer - I would like
> to
> | > set it
> | > > > > > to
> | > > > > >> "All" or something similar.
> | > > > > >>
> | > > > > >> Is there any way to do this without writing code that will
> | > calculate the
> | > > > > >> number of column groups and then using that in an expression
> for
> | > the
> | > > > > >> "GroupsBeforeRowHeaders" property? I'd like to avoid doing
> this
> | > because
> | > > > > > the
> | > > > > >> start and end times may be weeks, months, or years, so
> calculating
> | > the
> | > > > > >> number of columns would be a pain.
> | > > > > >>
> | > > > > >> Thanks for any help,
> | > > > > >> Sean Carpenter
> | > > > > >> ProMetrics Consulting, Inc.
> | > > > > >>
> | > > > > >>
> | > > > > >
> | > > > > >
> | > > > >
> | > > > >
> | > > > >
> | >
> | >
> | >
> |
>|||Hello Sean,
After consulting the proudct team, there are no plans to change
GroupsBeforeRowHeaders to be an expression.
One possible workaround is to add an outer column grouping with a
GroupExpression like "=1" (so you will have only on column), set its height
to zero and set GroupsBeforeRowHeaders to 1.
Hope this is helpful.
Thanks & Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Matrix Row Headers After Data
| thread-index: AcTuiwA5dD+6t9c8QheMRd1ZJCE6Wg==| X-WBNR-Posting-Host: 65.126.12.173
| From: "=?Utf-8?B?U2VhbiBDYXJwZW50ZXI=?="
<SeanCarpenter@.discussions.microsoft.com>
| References: <u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl>
<u7k3Tj#5EHA.1408@.TK2MSFTNGP10.phx.gbl>
<emm2uuE6EHA.3336@.TK2MSFTNGP11.phx.gbl>
<3F5F013D-5CBD-4D63-AB93-1FAE12CD78FC@.microsoft.com>
<34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com>
<etWT1$g6EHA.992@.TK2MSFTNGP12.phx.gbl>
<1032A92D-A366-4E70-8A5C-9734EB226004@.microsoft.com>
<v9rQBQi7EHA.2768@.cpmsftngxa10.phx.gbl>
| Subject: Re: Matrix Row Headers After Data
| Date: Thu, 30 Dec 2004 08:17:04 -0800
| Lines: 208
| Message-ID: <B598246D-BBB6-4269-A943-F082FC51E5DD@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:38437
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Peter,
| I have emailed you an example report that exhibits the problem I'm having.
|
| Thanks for you help.
|
| Sean Carpenter
| ProMetrics Consulting, Inc.
|
| "Peter Yang [MSFT]" wrote:
|
| > Hello Sean,
| >
| > GroupsBeforeRowHeaders property is indeed an integer and you will need
to
| > know how many instances you want to move before row headings.
| >
| > The matrix LayoutDirection = RTL should help you to achieve what you
want
| > and adding a sort expression in the report (the sorting will be done by
our
| > processing component) should help you to sort them in the order you
want.
| >
| > Could you provide the report (created by CU) and a screenshot with the
| > desired result? You could send them to me at petery@.microsoft.com
| >
| > Thanks & Regards,
| >
| > Peter Yang
| > MCSE2000, MCSA, MCDBA
| > Microsoft Partner Online Support
| >
| > Get Secure! - www.microsoft.com/security
| >
| > =====================================================| > When responding to posts, please "Reply to Group" via
| > your newsreader so that others may learn and benefit
| > from your issue.
| > =====================================================| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| > --
| > | Thread-Topic: Matrix Row Headers After Data
| > | thread-index: AcTuES8Dac2k+nRYQjO7EsINx2zh4g==| > | X-WBNR-Posting-Host: 141.151.17.169
| > | From: "=?Utf-8?B?U2VhbiBDYXJwZW50ZXI=?="
| > <SeanCarpenter@.discussions.microsoft.com>
| > | References: <u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl>
| > <u7k3Tj#5EHA.1408@.TK2MSFTNGP10.phx.gbl>
| > <emm2uuE6EHA.3336@.TK2MSFTNGP11.phx.gbl>
| > <3F5F013D-5CBD-4D63-AB93-1FAE12CD78FC@.microsoft.com>
| > <34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com>
| > <etWT1$g6EHA.992@.TK2MSFTNGP12.phx.gbl>
| > | Subject: Re: Matrix Row Headers After Data
| > | Date: Wed, 29 Dec 2004 17:45:04 -0800
| > | Lines: 113
| > | Message-ID: <1032A92D-A366-4E70-8A5C-9734EB226004@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| > | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.reportingsvcs:38404
| > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > |
| > | It looks like my solution from the previous post won't work. I wrote
a
| > | function in the Custom Code section of the report to return the
correct
| > | number of column groups based on the report parameters. When I try
to
| > set
| > | the "GroupsBeforeRowHeaders" property to "=Code.ColumnCount()", it
won't
| > let
| > | me since it requires an Int32 value for the property.
| > |
| > | Does anyone have any ideas on this one? I'd like to have the row
headers
| > to
| > | the right of the column groups and have it appear that way on Excel
| > export as
| > | well.
| > |
| > | Thanks,
| > | Sean Carpenter
| > | ProMetrics Consulting, Inc.
| > |
| > | "Robert Bruckner [MSFT]" wrote:
| > |
| > | > Regarding the order - you should add a sort expression on the column
| > | > grouping of the matrix. The RS processing engine does not change
any
| > order
| > | > if there is no sorting applied in the report. You get the data in
the
| > order
| > | > returned by the data provider.
| > | >
| > | > --
| > | > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| > | >
| > | >
| > | >
| > | > "Sean Carpenter" <SeanCarpenter@.discussions.microsoft.com> wrote in
| > message
| > | > news:34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com...
| > | > > I didn't know about that property of the matrix. I tried it and
it
| > works,
| > | > > except that it reverses the order of all of the columns (which
makes
| > sense
| > | > > considering what the property does). I can reverse the order of
the
| > | > columns
| > | > > in the query result so that when the matrix reverses them again
they
| > | > display
| > | > > correctly, but it would be nice to have a "clean" way of doing
this.
| > This
| > | > > will definitely get me through the current report, though.
| > | > >
| > | > > Sean Carpenter
| > | > > ProMetrics Consulting, Inc.
| > | > >
| > | > > "HBWAL" wrote:
| > | > >
| > | > > > I have the same thing and I did not set the group header
number.
| > It is
| > | > set
| > | > > > to 0 and the Matrix Columns expand Right to left is selected.
Thus
| > all
| > | > of my
| > | > > > columns appear to the left and then my rows appear to right.
| > | > > >
| > | > > > Hope this is not to late to help, if it works for you.
| > | > > >
| > | > > > "Sean Carpenter" wrote:
| > | > > >
| > | > > > > Yes. If I set it to anything larger than the actual number
of
| > column
| > | > > > > groups, it doesn't appear to do anything - the headers stay
to the
| > | > left of
| > | > > > > all of the column groups.
| > | > > > >
| > | > > > > Sean Carpenter
| > | > > > > ProMetrics Consulting, Inc.
| > | > > > >
| > | > > > > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote
in
| > | > message
| > | > > > > news:u7k3Tj%235EHA.1408@.TK2MSFTNGP10.phx.gbl...
| > | > > > > > Did you try setting the integer to a very large value, e.g.
| > | > 100000000
| > | > > > > >
| > | > > > > > --
| > | > > > > > This posting is provided "AS IS" with no warranties, and
| > confers no
| > | > > > > > rights.
| > | > > > > >
| > | > > > > > "Sean Carpenter" <stcarpenter2005@.community.nospam> wrote
in
| > message
| > | > > > > > news:u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl...
| > | > > > > >> I have a report that uses Analysis Services data in a
matrix.
| > The
| > | > column
| > | > > > > >> groups in the matrix are based on date, and the number of
| > column
| > | > groups
| > | > > > > >> is
| > | > > > > >> variable based on the start time and end time entered as
report
| > | > > > > > parameters.
| > | > > > > >> I'd like to display the row headers after the column
groups,
| > | > regardless
| > | > > > > >> of
| > | > > > > >> the number of column groups. The "GroupsBeforeRowHeaders"
| > property
| > | > looks
| > | > > > > >> like it will work, but it only takes an integer - I would
like
| > to
| > | > set it
| > | > > > > > to
| > | > > > > >> "All" or something similar.
| > | > > > > >>
| > | > > > > >> Is there any way to do this without writing code that will
| > | > calculate the
| > | > > > > >> number of column groups and then using that in an
expression
| > for
| > | > the
| > | > > > > >> "GroupsBeforeRowHeaders" property? I'd like to avoid
doing
| > this
| > | > because
| > | > > > > > the
| > | > > > > >> start and end times may be weeks, months, or years, so
| > calculating
| > | > the
| > | > > > > >> number of columns would be a pain.
| > | > > > > >>
| > | > > > > >> Thanks for any help,
| > | > > > > >> Sean Carpenter
| > | > > > > >> ProMetrics Consulting, Inc.
| > | > > > > >>
| > | > > > > >>
| > | > > > > >
| > | > > > > >
| > | > > > >
| > | > > > >
| > | > > > >
| > | >
| > | >
| > | >
| > |
| >
| >
||||I guess the bigger problem for me right now is that the Excel export ignores
the "LayoutDirection" property of the matrix. Is it possible to get this
fixed as a bug?
My overall problem (which is displaying row headers to the right of the
columns) still exists. Does anyone have any ideas? The whole idea of the
matrix is to have a variable number of columns - if I knew how many columns I
had I could use a table instead.
Sean Carpenter
ProMetrics Consulting, Inc.
"Peter Yang [MSFT]" wrote:
> Hello Sean,
> After consulting the proudct team, there are no plans to change
> GroupsBeforeRowHeaders to be an expression.
> One possible workaround is to add an outer column grouping with a
> GroupExpression like "=1" (so you will have only on column), set its height
> to zero and set GroupsBeforeRowHeaders to 1.
> Hope this is helpful.
> Thanks & Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> =====================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> | Thread-Topic: Matrix Row Headers After Data
> | thread-index: AcTuiwA5dD+6t9c8QheMRd1ZJCE6Wg==> | X-WBNR-Posting-Host: 65.126.12.173
> | From: "=?Utf-8?B?U2VhbiBDYXJwZW50ZXI=?="
> <SeanCarpenter@.discussions.microsoft.com>
> | References: <u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl>
> <u7k3Tj#5EHA.1408@.TK2MSFTNGP10.phx.gbl>
> <emm2uuE6EHA.3336@.TK2MSFTNGP11.phx.gbl>
> <3F5F013D-5CBD-4D63-AB93-1FAE12CD78FC@.microsoft.com>
> <34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com>
> <etWT1$g6EHA.992@.TK2MSFTNGP12.phx.gbl>
> <1032A92D-A366-4E70-8A5C-9734EB226004@.microsoft.com>
> <v9rQBQi7EHA.2768@.cpmsftngxa10.phx.gbl>
> | Subject: Re: Matrix Row Headers After Data
> | Date: Thu, 30 Dec 2004 08:17:04 -0800
> | Lines: 208
> | Message-ID: <B598246D-BBB6-4269-A943-F082FC51E5DD@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:38437
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Peter,
> | I have emailed you an example report that exhibits the problem I'm having.
> |
> | Thanks for you help.
> |
> | Sean Carpenter
> | ProMetrics Consulting, Inc.
> |
> | "Peter Yang [MSFT]" wrote:
> |
> | > Hello Sean,
> | >
> | > GroupsBeforeRowHeaders property is indeed an integer and you will need
> to
> | > know how many instances you want to move before row headings.
> | >
> | > The matrix LayoutDirection = RTL should help you to achieve what you
> want
> | > and adding a sort expression in the report (the sorting will be done by
> our
> | > processing component) should help you to sort them in the order you
> want.
> | >
> | > Could you provide the report (created by CU) and a screenshot with the
> | > desired result? You could send them to me at petery@.microsoft.com
> | >
> | > Thanks & Regards,
> | >
> | > Peter Yang
> | > MCSE2000, MCSA, MCDBA
> | > Microsoft Partner Online Support
> | >
> | > Get Secure! - www.microsoft.com/security
> | >
> | > =====================================================> | > When responding to posts, please "Reply to Group" via
> | > your newsreader so that others may learn and benefit
> | > from your issue.
> | > =====================================================> | > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> | >
> | >
> | > --
> | > | Thread-Topic: Matrix Row Headers After Data
> | > | thread-index: AcTuES8Dac2k+nRYQjO7EsINx2zh4g==> | > | X-WBNR-Posting-Host: 141.151.17.169
> | > | From: "=?Utf-8?B?U2VhbiBDYXJwZW50ZXI=?="
> | > <SeanCarpenter@.discussions.microsoft.com>
> | > | References: <u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl>
> | > <u7k3Tj#5EHA.1408@.TK2MSFTNGP10.phx.gbl>
> | > <emm2uuE6EHA.3336@.TK2MSFTNGP11.phx.gbl>
> | > <3F5F013D-5CBD-4D63-AB93-1FAE12CD78FC@.microsoft.com>
> | > <34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com>
> | > <etWT1$g6EHA.992@.TK2MSFTNGP12.phx.gbl>
> | > | Subject: Re: Matrix Row Headers After Data
> | > | Date: Wed, 29 Dec 2004 17:45:04 -0800
> | > | Lines: 113
> | > | Message-ID: <1032A92D-A366-4E70-8A5C-9734EB226004@.microsoft.com>
> | > | MIME-Version: 1.0
> | > | Content-Type: text/plain;
> | > | charset="Utf-8"
> | > | Content-Transfer-Encoding: 7bit
> | > | X-Newsreader: Microsoft CDO for Windows 2000
> | > | Content-Class: urn:content-classes:message
> | > | Importance: normal
> | > | Priority: normal
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | > | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | > | Xref: cpmsftngxa10.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:38404
> | > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> | > |
> | > | It looks like my solution from the previous post won't work. I wrote
> a
> | > | function in the Custom Code section of the report to return the
> correct
> | > | number of column groups based on the report parameters. When I try
> to
> | > set
> | > | the "GroupsBeforeRowHeaders" property to "=Code.ColumnCount()", it
> won't
> | > let
> | > | me since it requires an Int32 value for the property.
> | > |
> | > | Does anyone have any ideas on this one? I'd like to have the row
> headers
> | > to
> | > | the right of the column groups and have it appear that way on Excel
> | > export as
> | > | well.
> | > |
> | > | Thanks,
> | > | Sean Carpenter
> | > | ProMetrics Consulting, Inc.
> | > |
> | > | "Robert Bruckner [MSFT]" wrote:
> | > |
> | > | > Regarding the order - you should add a sort expression on the column
> | > | > grouping of the matrix. The RS processing engine does not change
> any
> | > order
> | > | > if there is no sorting applied in the report. You get the data in
> the
> | > order
> | > | > returned by the data provider.
> | > | >
> | > | > --
> | > | > This posting is provided "AS IS" with no warranties, and confers no
> | > rights.
> | > | >
> | > | >
> | > | >
> | > | > "Sean Carpenter" <SeanCarpenter@.discussions.microsoft.com> wrote in
> | > message
> | > | > news:34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com...
> | > | > > I didn't know about that property of the matrix. I tried it and
> it
> | > works,
> | > | > > except that it reverses the order of all of the columns (which
> makes
> | > sense
> | > | > > considering what the property does). I can reverse the order of
> the
> | > | > columns
> | > | > > in the query result so that when the matrix reverses them again
> they
> | > | > display
> | > | > > correctly, but it would be nice to have a "clean" way of doing
> this.
> | > This
> | > | > > will definitely get me through the current report, though.
> | > | > >
> | > | > > Sean Carpenter
> | > | > > ProMetrics Consulting, Inc.
> | > | > >
> | > | > > "HBWAL" wrote:
> | > | > >
> | > | > > > I have the same thing and I did not set the group header
> number.
> | > It is
> | > | > set
> | > | > > > to 0 and the Matrix Columns expand Right to left is selected.
> Thus
> | > all
> | > | > of my
> | > | > > > columns appear to the left and then my rows appear to right.
> | > | > > >
> | > | > > > Hope this is not to late to help, if it works for you.
> | > | > > >
> | > | > > > "Sean Carpenter" wrote:
> | > | > > >
> | > | > > > > Yes. If I set it to anything larger than the actual number
> of
> | > column
> | > | > > > > groups, it doesn't appear to do anything - the headers stay
> to the
> | > | > left of
> | > | > > > > all of the column groups.
> | > | > > > >
> | > | > > > > Sean Carpenter
> | > | > > > > ProMetrics Consulting, Inc.
> | > | > > > >
> | > | > > > > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote
> in
> | > | > message
> | > | > > > > news:u7k3Tj%235EHA.1408@.TK2MSFTNGP10.phx.gbl...
> | > | > > > > > Did you try setting the integer to a very large value, e.g.
> | > | > 100000000
> | > | > > > > >
> | > | > > > > > --
> | > | > > > > > This posting is provided "AS IS" with no warranties, and
> | > confers no
> | > | > > > > > rights.
> | > | > > > > >
> | > | > > > > > "Sean Carpenter" <stcarpenter2005@.community.nospam> wrote
> in
> | > message
> | > | > > > > > news:u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl...
> | > | > > > > >> I have a report that uses Analysis Services data in a
> matrix.
> | > The
> | > | > column
> | > | > > > > >> groups in the matrix are based on date, and the number of
> | > column
> | > | > groups
> | > | > > > > >> is
> | > | > > > > >> variable based on the start time and end time entered as
> report
> | > | > > > > > parameters.
> | > | > > > > >> I'd like to display the row headers after the column
> groups,
> | > | > regardless
> | > | > > > > >> of
> | > | > > > > >> the number of column groups. The "GroupsBeforeRowHeaders"
> | > property
> | > | > looks
> | > | > > > > >> like it will work, but it only takes an integer - I would
> like
> | > to
> | > | > set it
> | > | > > > > > to
> | > | > > > > >> "All" or something similar.
> | > | > > > > >>
> | > | > > > > >> Is there any way to do this without writing code that will
> | > | > calculate the
> | > | > > > > >> number of column groups and then using that in an
> expression
> | > for
> | > | > the
> | > | > > > > >> "GroupsBeforeRowHeaders" property? I'd like to avoid
> doing
> | > this
> | > | > because
> | > | > > > > > the
> | > | > > > > >> start and end times may be weeks, months, or years, so
> | > calculating
> | > | > the
> | > | > > > > >> number of columns would be a pain.
> | > | > > > > >>
> | > | > > > > >> Thanks for any help,
> | > | > > > > >> Sean Carpenter
> | > | > > > > >> ProMetrics Consulting, Inc.
> | > | > > > > >>
> | > | > > > > >>
> | > | > > > > >
> | > | > > > > >
> | > | > > > >
> | > | > > > >
> | > | > > > >
> | > | >
> | > | >
> | > | >
> | > |
> | >
> | >
> |
>|||Hello Sean,
I'd like to know if you have try the workaround as I suggested on the
sample report you send to me.
Set outer column grouping with a GroupExpression like "=1"
1). Change the dataset query to: (remove the sorting function)
SELECT { Measures.[Unit Sales] } on columns,
NON EMPTY CROSSJOIN({ Store.[Store State].Members },
Time.[1997].[Q1].[1]:Time.[1997].[Q4].[12] ) on rows
from Sales
2). Right click the Matrix up-right corner->Properties
3). On Group tab, select a Column group, click Add to add a new column
group. Name it Columngroup2
4). On the General tab, type "=1" (without quotos) in Expression textbox
under "Group on"
5). Move it to the first one in the column group.
6). On General tab of Matrix property dialog, select "Left to right"
7). Select "1" under Groups before row headers.
8). Right click cell with "=1" in matrix->Properties->Advanced->Visibility,
select Hidden.
9). Preview the report, you shall see the proper sequece of rows and row
headers
10). Deploy and export the report to excel, the sequence is also correct.
Thus, we need not set LayoutDirection from "right to left". You can sort it
as you want in dataset query and it will properly shown in the report.
Because there is a hidden column group with only 1 column, you can always
set "Groups before row headers" to 1 no matter how many real column in the
secondary column group.
Hope this is helpful.
Thanks & Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security|||Peter -
I'm sorry - when I first read your reply with the workaround, I misread
adding the hidden column group as adding a hidden row group and didn't see
how it would work. This workaround did work for me and my report now works
the way I need it to.
Thanks for your help,
Sean Carpenter
ProMetrics Consulting, Inc.
"Peter Yang [MSFT]" wrote:
> Hello Sean,
> I'd like to know if you have try the workaround as I suggested on the
> sample report you send to me.
> Set outer column grouping with a GroupExpression like "=1"
> 1). Change the dataset query to: (remove the sorting function)
> SELECT { Measures.[Unit Sales] } on columns,
> NON EMPTY CROSSJOIN({ Store.[Store State].Members },
> Time.[1997].[Q1].[1]:Time.[1997].[Q4].[12] ) on rows
> from Sales
> 2). Right click the Matrix up-right corner->Properties
> 3). On Group tab, select a Column group, click Add to add a new column
> group. Name it Columngroup2
> 4). On the General tab, type "=1" (without quotos) in Expression textbox
> under "Group on"
> 5). Move it to the first one in the column group.
> 6). On General tab of Matrix property dialog, select "Left to right"
> 7). Select "1" under Groups before row headers.
> 8). Right click cell with "=1" in matrix->Properties->Advanced->Visibility,
> select Hidden.
> 9). Preview the report, you shall see the proper sequece of rows and row
> headers
> 10). Deploy and export the report to excel, the sequence is also correct.
> Thus, we need not set LayoutDirection from "right to left". You can sort it
> as you want in dataset query and it will properly shown in the report.
> Because there is a hidden column group with only 1 column, you can always
> set "Groups before row headers" to 1 no matter how many real column in the
> secondary column group.
> Hope this is helpful.
> Thanks & Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
>|||Hello Sean,
Glad to hear this work for you. Have a great day!
Thanks & Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Matrix Row Headers After Data
| thread-index: AcTz8/os9oJcCNQXSAetcHm6AQwaQQ==| X-WBNR-Posting-Host: 65.126.12.173
| From: "=?Utf-8?B?U2VhbiBDYXJwZW50ZXI=?="
<SeanCarpenter@.discussions.microsoft.com>
| References: <u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl>
<u7k3Tj#5EHA.1408@.TK2MSFTNGP10.phx.gbl>
<emm2uuE6EHA.3336@.TK2MSFTNGP11.phx.gbl>
<3F5F013D-5CBD-4D63-AB93-1FAE12CD78FC@.microsoft.com>
<34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com>
<etWT1$g6EHA.992@.TK2MSFTNGP12.phx.gbl>
<1032A92D-A366-4E70-8A5C-9734EB226004@.microsoft.com>
<v9rQBQi7EHA.2768@.cpmsftngxa10.phx.gbl>
<B598246D-BBB6-4269-A943-F082FC51E5DD@.microsoft.com>
<kBQgVJy8EHA.3520@.cpmsftngxa10.phx.gbl>
<1ED5A573-D4EE-43A6-B0F9-E9389D41DBC1@.microsoft.com>
<vRavHH78EHA.3520@.cpmsftngxa10.phx.gbl>
| Subject: Re: Matrix Row Headers After Data
| Date: Thu, 6 Jan 2005 05:31:07 -0800
| Lines: 65
| Message-ID: <36BE3E3D-63BE-45EE-9A5E-2737921B3644@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:38820
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Peter -
| I'm sorry - when I first read your reply with the workaround, I misread
| adding the hidden column group as adding a hidden row group and didn't
see
| how it would work. This workaround did work for me and my report now
works
| the way I need it to.
|
| Thanks for your help,
| Sean Carpenter
| ProMetrics Consulting, Inc.
|
| "Peter Yang [MSFT]" wrote:
|
| > Hello Sean,
| >
| > I'd like to know if you have try the workaround as I suggested on the
| > sample report you send to me.
| >
| > Set outer column grouping with a GroupExpression like "=1"
| >
| > 1). Change the dataset query to: (remove the sorting function)
| >
| > SELECT { Measures.[Unit Sales] } on columns,
| > NON EMPTY CROSSJOIN({ Store.[Store State].Members },
| > Time.[1997].[Q1].[1]:Time.[1997].[Q4].[12] ) on rows
| > from Sales
| >
| > 2). Right click the Matrix up-right corner->Properties
| >
| > 3). On Group tab, select a Column group, click Add to add a new column
| > group. Name it Columngroup2
| >
| > 4). On the General tab, type "=1" (without quotos) in Expression
textbox
| > under "Group on"
| >
| > 5). Move it to the first one in the column group.
| >
| > 6). On General tab of Matrix property dialog, select "Left to right"
| >
| > 7). Select "1" under Groups before row headers.
| >
| > 8). Right click cell with "=1" in
matrix->Properties->Advanced->Visibility,
| > select Hidden.
| >
| > 9). Preview the report, you shall see the proper sequece of rows and
row
| > headers
| >
| > 10). Deploy and export the report to excel, the sequence is also
correct.
| >
| > Thus, we need not set LayoutDirection from "right to left". You can
sort it
| > as you want in dataset query and it will properly shown in the report.
| > Because there is a hidden column group with only 1 column, you can
always
| > set "Groups before row headers" to 1 no matter how many real column in
the
| > secondary column group.
| >
| > Hope this is helpful.
| >
| > Thanks & Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > Get Secure! - www.microsoft.com/security
| >
| >
|