master sysmessages: group

Showing posts with label group. Show all posts

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

Friday, March 23, 2012

Matrix w/ Subtotals Export to Excel rrRenderingError

Is it possible to export a matrix report that has subtotals on multiple
columns to Excel? With just the innermost group subtotaled, I don't get an
exception, but when I add subtotals for outer groups it will not export.
I've seen this question posted elsewhere, but no answer to it. Thanks!Magpie,
I have same problem. Do you have any idea of solution?
Regards,
Loretta
"Magpie" wrote:
> Is it possible to export a matrix report that has subtotals on multiple
> columns to Excel? With just the innermost group subtotaled, I don't get an
> exception, but when I add subtotals for outer groups it will not export.
> I've seen this question posted elsewhere, but no answer to it. Thanks!

Matrix Sub-Totals

Hi,
I have one column group and 2 columns (one Amount & other Text) under
it in a matrix. I added subtotal to that column group and now Amount
total appears fine but first TEXT value appears in total coloumn. I
would like to hide the TEXT value appearing in Subtotal column.
I sure there are lot of threads addressing this sub-total issue but i
was unable to find answer for my query.
Any help would be appreciated.
-SGYou must use the InScope function if you only want text to be displayed in
the details and not the subtotal. There was another posting addressing this
issue. I use something like the following in the expression :
=iif(inscope("ProductGroup"),first(Fields!Price.value,"ProductGroup"),nothing)
"SG" wrote:
> Hi,
> I have one column group and 2 columns (one Amount & other Text) under
> it in a matrix. I added subtotal to that column group and now Amount
> total appears fine but first TEXT value appears in total coloumn. I
> would like to hide the TEXT value appearing in Subtotal column.
> I sure there are lot of threads addressing this sub-total issue but i
> was unable to find answer for my query.
> Any help would be appreciated.
> -SG
>|||Dawie wrote:
> You must use the InScope function if you only want text to be displayed in
> the details and not the subtotal. There was another posting addressing this
> issue. I use something like the following in the expression :
> =iif(inscope("ProductGroup"),first(Fields!Price.value,"ProductGroup"),nothing)
>
> "SG" wrote:
> > Hi,
> >
> > I have one column group and 2 columns (one Amount & other Text) under
> > it in a matrix. I added subtotal to that column group and now Amount
> > total appears fine but first TEXT value appears in total coloumn. I
> > would like to hide the TEXT value appearing in Subtotal column.
> >
> > I sure there are lot of threads addressing this sub-total issue but i
> > was unable to find answer for my query.
> > Any help would be appreciated.
> >
> > -SG
> >
> >Where can i write the Expression for the subtotal?
Actually i am struggling to find a way to find where i can write the
Expression for SubTotal?

Matrix subtotal label issue (SSRS 2005)

Hello,

We have a matrix that includes two row groups with subtotals for each group, like the following:

<tr><td> </td><td>Rm 34A Total</td><td>10</td></tr>

<tr><td> </td><td>Rm 34A Total</td><td>7</td></tr>

<tr><td>Unit 1 Total</td><td> </td><td>17</td></tr>

</table>

The issue is that the second group's subtotal label doesn't display the correct name, as in the example. It's always "Rm 34A" or whichever room is returned first in the dataset, even if the subtotal is actually for Rm 35. The labels for the first group display correctly, and the issue is only a label problem - the subtotal data seems to be fine. For the subtotal label, we have =Fields!Subgroup.Value + " Total" as the expression. Anybody have any suggestions? Thanks,

RLG

RLGow wrote:

Hello,
We have a matrix that includes two row groups with subtotals for each group, like the following:
<table width="80%">
<tr><td align="center">Unit</td><td align="center">Room</td><td align="center">Data</td></tr>
<tr><td>Unit 1</td><td>Rm 34A</td><td>6</td></tr>
<tr><td> </td><td> </td><td>4</td></tr>
<tr><td> </td><td>Rm 34A Total</td><td>10</td></tr>
<tr><td> </td><td>Rm 50</td><td>7</td></tr>
<tr><td> </td><td>Rm 34A Total</td><td>7</td></tr>
<tr><td>Unit 1 Total</td><td> </td><td>17</td></tr>
</table>
The issue is that the second group's subtotal label doesn't display the correct name, as in the example. It's always "Rm 34A" or whichever room is returned first in the dataset, even if the subtotal is actually for Rm 35. The labels for the first group display correctly, and the issue is only a label problem - the subtotal data seems to be fine. For the subtotal label, we have =Fields!Subgroup.Value + " Total" as the expression. Anybody have any suggestions? Thanks,
RLG

Try

=Fields!Subgroup.Value & " Total"

|||

Hi,

I also have the same problem.

However, I have discovered that this behaviour appears then you have column-subtotals and row-subtotal.

If I remove the subtotal for rows this problem seems to disappear.

Instead I get another problems with empty labels in my subtotal text-fields. There are no NULL or empty strings in used columns in my recordset!

If you have any kind of solution or work around please let me know.

Regards, Jonas

Matrix subtotal label issue (SSRS 2005)

Hello,

We have a matrix that includes two row groups with subtotals for each group, like the following:

<tr><td> </td><td>Rm 34A Total</td><td>10</td></tr>

<tr><td> </td><td>Rm 34A Total</td><td>7</td></tr>

<tr><td>Unit 1 Total</td><td> </td><td>17</td></tr>

</table>

RLG

RLGow wrote:

Hello,

We have a matrix that includes two row groups with subtotals for each group, like the following:

<table width="80%">

<tr><td align="center">Unit</td><td align="center">Room</td><td align="center">Data</td></tr>

<tr><td>Unit 1</td><td>Rm 34A</td><td>6</td></tr>

<tr><td> </td><td> </td><td>4</td></tr>

<tr><td> </td><td>Rm 34A Total</td><td>10</td></tr>

<tr><td> </td><td>Rm 50</td><td>7</td></tr>

<tr><td> </td><td>Rm 34A Total</td><td>7</td></tr>

<tr><td>Unit 1 Total</td><td> </td><td>17</td></tr>

</table>

The issue is that the second group's subtotal label doesn't display the correct name, as in the example. It's always "Rm 34A" or whichever room is returned first in the dataset, even if the subtotal is actually for Rm 35. The labels for the first group display correctly, and the issue is only a label problem - the subtotal data seems to be fine. For the subtotal label, we have =Fields!Subgroup.Value + " Total" as the expression. Anybody have any suggestions? Thanks,

RLG

Try

=Fields!Subgroup.Value & " Total"

|||

Hi,

I also have the same problem.

However, I have discovered that this behaviour appears then you have column-subtotals and row-subtotal.

If I remove the subtotal for rows this problem seems to disappear.

Instead I get another problems with empty labels in my subtotal text-fields. There are no NULL or empty strings in used columns in my recordset!

If you have any kind of solution or work around please let me know.

Regards, Jonas

Wednesday, March 21, 2012

matrix runningavalue

hi all,
i have a matrix within my report. the column's group has inside one cell
with a table with 4 groups. one of groups have an expresion with the
runningvalue function. the problem came when i've applicated a subtotal at
this column group.
to be more clear, my columns describe the year's month. when i use the
runningvalue and the subtotal, at the preview the first column (january)
gives me the right value, after that everything is wrong in that row.
it could be another functions alternative that may help me?
thank youthat runningvalue it's not much compatible with the matrix. because with or
without subtotal it doesn't work right. it is something i could put in its
place, with the same role?
thank you
"Mirela" wrote:
> hi all,
> i have a matrix within my report. the column's group has inside one cell
> with a table with 4 groups. one of groups have an expresion with the
> runningvalue function. the problem came when i've applicated a subtotal at
> this column group.
> to be more clear, my columns describe the year's month. when i use the
> runningvalue and the subtotal, at the preview the first column (january)
> gives me the right value, after that everything is wrong in that row.
> it could be another functions alternative that may help me?
> thank yousql

Monday, March 19, 2012

Matrix report 4th row group subtotal row color

I'm dealing w/ SSRS 2005.

I have my main matrix report which has five row groups.

What I'd like to do is have the subtotal at the 4th level have a coloring for the whole row at run-time...so the user can follow from left to right what the 4th level subtotal actually is (the report can get fairly wide).

At design time, you don't even see the rows to the right of the subtotal, you just see the subtotal box.

Thanks!

got it...click on the little green triangle in the upper right corner, and set the background property.

Matrix Report - Subtotals

Good moring,
I am working on my first matrix report and trying to add in a total for a
group and rows
Table is as follows:
Account Manager Job Number Jan Sales Feb Sales
Peter J54455 598 60
Peter J65559 500 70
David J76666 400 80
Jane J74445 700 90
I would like to have:
1) a subtotal row when the Account Manager changes.
2) a total for each row.
Example:
Account Manager Job Number Jan Sales Feb Sales Total
Peter J54455 500 60
560
Peter J65559 500 70
570
Total Peter 1000 130
1130
David J76666 400 80
480
Total David 400 80
480
Jane J74445 700 90
790
Total Jane 700 90
790
I know this is inherent in the matrix report, however I have spent hours
trying to figure out and still cannot determine how to achieve both of these
2 things (simple as they may be).
Can anyone help?
Thanks JamesAs an amendment,
I have discovered how to add row total so ignore that part.
As to the subtotal on the Account Manager I still have an issue with that.
I tried right clicking on the Account Manager name and choose subtotal.
However this puts a grand total for all account Managers, not a subtotal for
each account manager. Any ideas'?

Matrix Questions

I have been running into a couple of problems with my Matrix style reports:

First my Matrix is a drillthrough that looks like this

Year Group QuarterGroup Detail name Detail name Group1 Group2 Group3 Detail data Detail Data

1. How do I interactively sort by a detail column name, right now I'm using a switch statement in the Group1-3 sorts to allow the user to sort, the problem is that the user has to choose how to sort before running the report and I can't set the sort direction. Sort direction doesn't take an expression.

2. How do I collapse a Detail name column, when I set the visible property to false on the text boxes in the column all they disappear but the Year and Quarter Group textboxes don't resize.

I would also like to have an drillthrough matrix report that looks like this

Year Group QuarterGroup Detail % Change over previous time period Group1 Group2 Group3 Detail data % of change from last quarter or year

What expression should I place in the % of change from last quarter or year box?

Anybody?

Monday, March 12, 2012

Matrix grouping

I have a report with the Month attribute as the column group and specific measures as the row groupings. Now, here's my delima. The months are not being displayed in order. They look like this:

Jan Feb May Jun Jul Aug Sep Oct Nov Dec Mar Total

Why is it doing this?

Here's a view of my matrix in layout view...

Month_Name

Fatal Crashes =sum(fatal_crashes.value)

Injury Crashes =sum(injury_crashes.value)

Property Damage =sum(Prop_Damage.value)

Total Crashes =sum(Total_crashes.value)

Chicago Crashes =sum(Chicago_crashes.value)

Crashes Located =sum(located_crashes.value)

% Located =sum(percent_located.value)

any help would be greatly appreciated!! THANKS!

Try changing the sorting of the matrix to use the month number if available.

http://msdn2.microsoft.com/en-us/library/aa179319(SQL.80).aspx

If it's not available, you may be able to build an ugly IIF statement to provide the row numbers, or use a function to return them.

cheers,

Andrew

|||

I tried to hardcode in a switch statement such as this one :

=Switch((Fields!Month_Name.Value) = "Jan", 1, Fields!Month_Name.Value = "Feb", 2, Fields!Month_Name.Value = "Mar", 3, Fields!Month_Name.Value = "April", 4, Fields!Month_Name.Value= "May",5, Fields!Month_Name.Value= "Jun",6, Fields!Month_Name.Value= "Jul",7, Fields!Month_Name.Value= "Aug",8, Fields!Month_Name.Value="Sep",9, Fields!Month_Name.Value="Oct",10, Fields!Month_Name.Value="Nov",11, Fields!Month_Name.Value="Dec",12)

But it didnt change anything. I also changed the sort to Fields!Month_Name.key and Fields!Month_Name.Level and nothing changed as well. I wonder if its because i added extra rows to the matrix ...but still it should work, i have added extra columns before and never had this problem.

Matrix grouping

My fiscal year starts from April. How can I group with fiscal year like this?

Select items,sum(sales), date from tableA

2007

2006

Books

Panel

Frame

Try to add your fiscal year at your Time dimension.

Helped?

Regards

|||

Is it possible user date field group to like this in matrix?

2005-4-1 to 2006-3-31

2006-4-1 to 2007-3-31

Books

Panel

Frame

|||

Dear Friend,

the both columns is based in the date parameter of your report, correct?

You only need 2 periods? 1 year ago and 2 years ago from parameter date, correct?!

Regards!

|||

Hi PedroCGD

The columns is based in the date parameter. I wants to do 5 year periods. Can you help me?

|||

Yes I'll help you, but only in a few hours when I arrive home!!

You'll get it! Dont panic! :-)

regards!

|||

palm99,

Can I try resolve your problem or you already resolved?

Regards

|||

Hi PedroCGD

I am waiting your help.

Matrix grouping

My fiscal year starts from April. How can I group with fiscal year like this?

Select items,sum(sales), date from tableA

2007

2006

Books

Panel

Frame

Try to add your fiscal year at your Time dimension.

Helped?

Regards

|||

Is it possible user date field group to like this in matrix?

2005-4-1 to 2006-3-31

2006-4-1 to 2007-3-31

Books

Panel

Frame

|||

Dear Friend,

the both columns is based in the date parameter of your report, correct?

You only need 2 periods? 1 year ago and 2 years ago from parameter date, correct?!

Regards!

|||

Hi PedroCGD

The columns is based in the date parameter. I wants to do 5 year periods. Can you help me?

|||

Yes I'll help you, but only in a few hours when I arrive home!!

You'll get it! Dont panic! :-)

regards!

|||

palm99,

Can I try resolve your problem or you already resolved?

Regards

|||

Hi PedroCGD

I am waiting your help.

Wednesday, March 28, 2012

Max file group per database in SQL Server 2005.

Max file group per database in SQL Server 2005.

Max file group per database in SQL Server 2005.

Friday, March 23, 2012

Matrix w/ Subtotals Export to Excel rrRenderingError

Matrix Sub-Totals

Matrix subtotal label issue (SSRS 2005)

Matrix subtotal label issue (SSRS 2005)

Wednesday, March 21, 2012

matrix runningavalue

Monday, March 19, 2012

Matrix report 4th row group subtotal row color

Matrix Report - Subtotals

Matrix Questions

Monday, March 12, 2012

Matrix grouping

Matrix grouping

Matrix grouping

master sysmessages

Blog Archive

About Me