Change Data Type

Hi,
I am not sure if I am posting to the correct group, but I am wondering if
there are any dangers in changing datatypes on a column.
More specifically, I want to change some varchars to nvarchars and texts to
ntexts. I already have the script written for the columns I need to change.
I have seen no issues in doing this on my test database, but I was wondering
if there are any inherent dangers in doing this.
Are there any articles that can be helpful for this subject?
Thank you,
Paul
Paul,
The issues with varchar and nvarchar are fairly straight-forward, however
ALTER TABLE cannot change texts and ntexts. I understand that you must drop
any indexes on a varchar that you are changing to nvarchar before doing the
alter. One of the other main restrictions is that the data must all fit
into the new target. The BOL explains most of this in the ALTER TABLE
topic.
You might also find the following article from SQL Server magazine helpful.
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnsqlmag04/html/InsideALTERTABLE.asp
Russell Fields
"Paul" <paul@.gotheta.com> wrote in message
news:uJjE5d6MEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am not sure if I am posting to the correct group, but I am wondering if
> there are any dangers in changing datatypes on a column.
> More specifically, I want to change some varchars to nvarchars and texts
to
> ntexts. I already have the script written for the columns I need to
change.
> I have seen no issues in doing this on my test database, but I was
wondering
> if there are any inherent dangers in doing this.
> Are there any articles that can be helpful for this subject?
> Thank you,
> Paul
>
|||With the varchar to nvarchar make sure you're aware of the storage requirements. If you have long string lengths you need to remember that nvarchar will double your storage:
e.g.
DECLARE @.string VARCHAR(10)
SELECT@.String = 'Hello'
SELECTDATALENGTH(@.String)
SELECTDATALENGTH(CAST(@.String AS NVARCHAR(10)))
Regards
Julie
http://www.sqlporn.co.uk :o)

Change Data Type

Hi,
I am not sure if I am posting to the correct group, but I am wondering if
there are any dangers in changing datatypes on a column.
More specifically, I want to change some varchars to nvarchars and texts to
ntexts. I already have the script written for the columns I need to change.
I have seen no issues in doing this on my test database, but I was wondering
if there are any inherent dangers in doing this.
Are there any articles that can be helpful for this subject?
Thank you,
PaulPaul,
The issues with varchar and nvarchar are fairly straight-forward, however
ALTER TABLE cannot change texts and ntexts. I understand that you must drop
any indexes on a varchar that you are changing to nvarchar before doing the
alter. One of the other main restrictions is that the data must all fit
into the new target. The BOL explains most of this in the ALTER TABLE
topic.
You might also find the following article from SQL Server magazine helpful.
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnsqlmag04/html/InsideALTERTABLE.asp
Russell Fields
"Paul" <paul@.gotheta.com> wrote in message
news:uJjE5d6MEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am not sure if I am posting to the correct group, but I am wondering if
> there are any dangers in changing datatypes on a column.
> More specifically, I want to change some varchars to nvarchars and texts
to
> ntexts. I already have the script written for the columns I need to
change.
> I have seen no issues in doing this on my test database, but I was
wondering
> if there are any inherent dangers in doing this.
> Are there any articles that can be helpful for this subject?
> Thank you,
> Paul
>|||With the varchar to nvarchar make sure you're aware of the storage requirements. If you have long string lengths you need to remember that nvarchar will double your storage
e.g
DECLARE @.string VARCHAR(10
SELECT @.String = 'Hello
SELECT DATALENGTH(@.String
SELECT DATALENGTH(CAST(@.String AS NVARCHAR(10))
Regard
Juli
http://www.sqlporn.co.uk :o)

Change Data Type

Hi,
I am not sure if I am posting to the correct group, but I am wondering if
there are any dangers in changing datatypes on a column.
More specifically, I want to change some varchars to nvarchars and texts to
ntexts. I already have the script written for the columns I need to change.
I have seen no issues in doing this on my test database, but I was wondering
if there are any inherent dangers in doing this.
Are there any articles that can be helpful for this subject?
Thank you,
PaulPaul,
The issues with varchar and nvarchar are fairly straight-forward, however
ALTER TABLE cannot change texts and ntexts. I understand that you must drop
any indexes on a varchar that you are changing to nvarchar before doing the
alter. One of the other main restrictions is that the data must all fit
into the new target. The BOL explains most of this in the ALTER TABLE
topic.
You might also find the following article from SQL Server magazine helpful.
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnsqlmag04/html/InsideALTERTABLE.asp
Russell Fields
"Paul" <paul@.gotheta.com> wrote in message
news:uJjE5d6MEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am not sure if I am posting to the correct group, but I am wondering if
> there are any dangers in changing datatypes on a column.
> More specifically, I want to change some varchars to nvarchars and texts
to
> ntexts. I already have the script written for the columns I need to
change.
> I have seen no issues in doing this on my test database, but I was
wondering
> if there are any inherent dangers in doing this.
> Are there any articles that can be helpful for this subject?
> Thank you,
> Paul
>|||With the varchar to nvarchar make sure you're aware of the storage requireme
nts. If you have long string lengths you need to remember that nvarchar will
double your storage:
e.g.
DECLARE @.string VARCHAR(10)
SELECT @.String = 'Hello'
SELECT DATALENGTH(@.String)
SELECT DATALENGTH(CAST(@.String AS NVARCHAR(10)))
Regards
Julie
http://www.sqlporn.co.uk :o)

Change Collation in database/server

Hello group,
Hi everyone I localize aplication to polish language and I want change
collation in database.
How change Collation in data base?
My database name MyDB
I want collation : 'Polish_CI_AS'
And my second question:
How set default collation for server (for new databases) if the collation is
different as operation system (ex. polish collation in databese, on windows
server english version)?
Thanks
PawelRHave you read these:
You can start here:
http://msdn.microsoft.com/library/d...br />
1pwz.asp
Default collation:
http://msdn.microsoft.com/library/d...br />
0f3n.asp
Changing collations:
http://msdn.microsoft.com/library/d...br />
819v.asp
ML|||ALTER DATABASE MyDB COLLATE <collationName>
"PawelR" wrote:

> Hello group,
> Hi everyone I localize aplication to polish language and I want change
> collation in database.
> How change Collation in data base?
> My database name MyDB
> I want collation : 'Polish_CI_AS'
> And my second question:
> How set default collation for server (for new databases) if the collation
is
> different as operation system (ex. polish collation in databese, on window
s
> server english version)?
>
> Thanks
> PawelR
>
>

Change Cluster Disk Owner

I have an Active/Active cluster with Windows 2003 with SQL Server 2000. I
am trying to move the cluster disk T: owner to my other group.
I keep receiving the following error:
An error occurred attempting to change the group of the
Disk T: to 'Other Resource' Group. The cluster node is not the owner of the
group. Error ID:5016
I checked all cluster groups and they have both owners. I also checked and
there is no dependency on Disk T:
Please help me with moving Disk T: to the Other Resource Group.
Thanks,
You can only change ownership of a resource from the node that currently
both resource groups.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:BAFDEF6E-33BF-4C6B-9066-8CD9F5347325@.microsoft.com...
> I have an Active/Active cluster with Windows 2003 with SQL Server 2000. I
> am trying to move the cluster disk T: owner to my other group.
> I keep receiving the following error:
> An error occurred attempting to change the group of the
> Disk T: to 'Other Resource' Group. The cluster node is not the owner of
the
> group. Error ID:5016
> I checked all cluster groups and they have both owners. I also checked
and
> there is no dependency on Disk T:
> Please help me with moving Disk T: to the Other Resource Group.
>
> Thanks,
|||And the SOLUTION would be to create a new resource group ("FOO") with no resources in it. Move it to the same node as the group that owns the resource you want to move. Now you can move the resource into the FOO resource group.
Move the FOO resource group to the node that owns the group to which you'd like to move the resource. Now you can move the resource to its destination.
Remove the FOO group and you are done.
This, of course, is only necessary if you can't move both resources to the same node for some reason.
HTH
jg

Quote:

Originally posted by Geoff N. Hiten
You can only change ownership of a resource from the node that currently
both resource groups.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
> I have an Active/Active cluster with Windows 2003 with SQL Server 2000. I
> am trying to move the cluster disk T: owner to my other group.
> I keep receiving the following error:
> An error occurred attempting to change the group of the
> Disk T: to 'Other Resource' Group. The cluster node is not the owner of[/vbcol]
the
> group. Error ID:5016
> I checked all cluster groups and they have both owners. I also checked
and
> there is no dependency on Disk T:
> Please help me with moving Disk T: to the Other Resource Group.
>
> Thanks,

|||Joe,
Geoff is correct.
Make sure you both groups are on the same node.
Then from that node, move the T: drive into the other resource group.
You will of course need to make sure T: is not a dependency of any other
resources prior to moving it.
Hope that helps!
Donna Lambert
Microsoft PSS
SQL Server Support

Change BackgroundColor for subtotals in Matrix

Hi,
I want to create a Matrix like this:
Group 1 Group 2 Value
A 1 2
2 3
Subtotal 5
B 1 8
2 10
Subtotal 18
...
I want the subtotals to have another BackgroundColor (grey) to differentiate
between the detail values and the subtotal value.
Please help !
ThomasIf you click on the little green triangle of the matrix heading cells, you
will notice that the VS properties windows shows properties that apply
specifically for subtotals. You can then set the background color on the
subtotal to grey.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Thomas" <Thomas@.discussions.microsoft.com> wrote in message
news:0F933BBE-A7AA-447E-B887-EB0F21635A4B@.microsoft.com...
> Hi,
> I want to create a Matrix like this:
> Group 1 Group 2 Value
> A 1 2
> 2 3
> Subtotal 5
> B 1 8
> 2 10
> Subtotal 18
> ...
> I want the subtotals to have another BackgroundColor (grey) to
> differentiate
> between the detail values and the subtotal value.
> Please help !
> Thomas|||It´s that easy ! Thank you very much.
I used the Inscope-Function in the detail-cell to change colors and
Borderstyle for the subtotal, but this is much better.
Thomas
"Robert Bruckner [MSFT]" wrote:
> If you click on the little green triangle of the matrix heading cells, you
> will notice that the VS properties windows shows properties that apply
> specifically for subtotals. You can then set the background color on the
> subtotal to grey.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Thomas" <Thomas@.discussions.microsoft.com> wrote in message
> news:0F933BBE-A7AA-447E-B887-EB0F21635A4B@.microsoft.com...
> > Hi,
> > I want to create a Matrix like this:
> >
> > Group 1 Group 2 Value
> > A 1 2
> > 2 3
> > Subtotal 5
> > B 1 8
> > 2 10
> > Subtotal 18
> > ...
> >
> > I want the subtotals to have another BackgroundColor (grey) to
> > differentiate
> > between the detail values and the subtotal value.
> >
> > Please help !
> >
> > Thomas
>
>|||Using InScope() approach is useful if you the style should depend on the
cell's value. If you want formatting for subtotals in general, it is easier
to use the properties on the subtotal heading.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Thomas" <Thomas@.discussions.microsoft.com> wrote in message
news:7FB87177-A383-4356-A67A-6D5332B1AEBA@.microsoft.com...
> It´s that easy ! Thank you very much.
> I used the Inscope-Function in the detail-cell to change colors and
> Borderstyle for the subtotal, but this is much better.
> Thomas
> "Robert Bruckner [MSFT]" wrote:
>> If you click on the little green triangle of the matrix heading cells,
>> you
>> will notice that the VS properties windows shows properties that apply
>> specifically for subtotals. You can then set the background color on the
>> subtotal to grey.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Thomas" <Thomas@.discussions.microsoft.com> wrote in message
>> news:0F933BBE-A7AA-447E-B887-EB0F21635A4B@.microsoft.com...
>> > Hi,
>> > I want to create a Matrix like this:
>> >
>> > Group 1 Group 2 Value
>> > A 1 2
>> > 2 3
>> > Subtotal 5
>> > B 1 8
>> > 2 10
>> > Subtotal 18
>> > ...
>> >
>> > I want the subtotals to have another BackgroundColor (grey) to
>> > differentiate
>> > between the detail values and the subtotal value.
>> >
>> > Please help !
>> >
>> > Thomas
>>

Certification

Hi All,
This post isn't particularly directly related to the Group but I
thought I would get some opinions.
I use SQL Server 2000 on a daily basis - both administering &
programming, coupled with VB6 client applications. I am currently
learning VB .NET
I am thinking about getting some kind of certification as I learn VB
.NET. I have been looking at the MCAD track on the Microsoft site. At
my current programming level this seems to be a good starting point. I
passed Exam 70-229 last year, which counts as an elective exam towards
the MCAD certification.
Does anyone have any advice/opinions for my current situation.
I am aware that there is the MCSD certification, however, I was
planning to tackle that one a bit later - see how I get on with the
MCAD first?
Thanks in advance for any comments.
BarryMicrosoft is in the process of rolling out new exams and certifications:
http://www.microsoft.com/learning/mcp/newgen/
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1139511623.548378.271810@.g44g2000cwa.googlegroups.com...
> Hi All,
> This post isn't particularly directly related to the Group but I
> thought I would get some opinions.
> I use SQL Server 2000 on a daily basis - both administering &
> programming, coupled with VB6 client applications. I am currently
> learning VB .NET
> I am thinking about getting some kind of certification as I learn VB
> .NET. I have been looking at the MCAD track on the Microsoft site. At
> my current programming level this seems to be a good starting point. I
> passed Exam 70-229 last year, which counts as an elective exam towards
> the MCAD certification.
> Does anyone have any advice/opinions for my current situation.
> I am aware that there is the MCSD certification, however, I was
> planning to tackle that one a bit later - see how I get on with the
> MCAD first?
> Thanks in advance for any comments.
> Barry
>

Cell Spanning Multiple Rows

I have a report where the first column needs to be spanned over multiple rows. The first column is part of a group, just the group name is shown on the detail row with the details. The data looked like this:
Group1 Detail 1
Group1 Detail 2
Group1 Detail 3
Group2 Detail 1
Group2 Detail 2
But I dont want the first column to repeat so it looks like this:
Group1 Detail 1
Detail 2
Detail 3
Group2 Detail 1
Detail 2
I tried to use the "Hide Duplicates" option on the group column textbox, but the borders for the textbox still show up. I could not find a simple way to get the first column to span all the rows in the group, so what I did was take the detail columns and put them in their own table and nest it in the group header row. That worked great but here is the problem:
The first column's data pushes the textbox to display 5 lines of text, but the detail may only have 1 line of data. It worked fine in the designer, but when rendered to HTML the lone detail line was aligned to the bottom of the group row. Even though the nested table's vertical alignment property is set to top, it was ignored.
I have service pack 1 installed, so I'm on the latest version. Any Ideas guys?Tables support cells that span multiple cells. When you select an entire row
an Merge Cells icon becomes active on in the Toolbar area Report Designer.
See the Merge Cells section at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_structure_objects_v1_2esl.asp
for more information.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JRodman" <JRodman@.discussions.microsoft.com> wrote in message
news:BCCC1E74-354B-40F4-8938-B71C50DFF9A1@.microsoft.com...
> I have a report where the first column needs to be spanned over multiple
rows. The first column is part of a group, just the group name is shown on
the detail row with the details. The data looked like this:
> Group1 Detail 1
> Group1 Detail 2
> Group1 Detail 3
> Group2 Detail 1
> Group2 Detail 2
> But I dont want the first column to repeat so it looks like this:
> Group1 Detail 1
> Detail 2
> Detail 3
> Group2 Detail 1
> Detail 2
> I tried to use the "Hide Duplicates" option on the group column textbox,
but the borders for the textbox still show up. I could not find a simple way
to get the first column to span all the rows in the group, so what I did was
take the detail columns and put them in their own table and nest it in the
group header row. That worked great but here is the problem:
> The first column's data pushes the textbox to display 5 lines of text, but
the detail may only have 1 line of data. It worked fine in the designer, but
when rendered to HTML the lone detail line was aligned to the bottom of the
group row. Even though the nested table's vertical alignment property is set
to top, it was ignored.
> I have service pack 1 installed, so I'm on the latest version. Any Ideas
guys?
TableSpanCells.rdl
---
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>1in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Country</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="City">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>City</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!City.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>DataSet1</DataSetName>
<Top>0.125in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ColSpan>2</ColSpan>
<ReportItems>
<Textbox Name="Country">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Grouping Name="CountryGroup">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
<PageBreakAtEnd>true</PageBreakAtEnd>
</Grouping>
</TableGroup>
</TableGroups>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<Left>0.125in</Left>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.375in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>d5774a08-d8e5-4e74-a663-f500669d7aba</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.62501in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactName">
<DataField>ContactName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactTitle">
<DataField>ContactTitle</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Address">
<DataField>Address</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Region">
<DataField>Region</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PostalCode">
<DataField>PostalCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Phone">
<DataField>Phone</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Fax">
<DataField>Fax</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Customers.*
FROM Customers
ORDER BY Country</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>4f653deb-b9af-49ca-98e1-fe972d395f88</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>|||You have a couple of options for row spanning:
Hide the cell that contains the group field using Previous():
<Hidden>=iif(Fields!Country.Value = Previous(Fields!Country.Value),
true, false)</Hidden>
Use a matrix that does not contain row groups or detail cells.
A sample report that demonstrates both approaches is at the end of this
posting.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JRodman" <JRodman@.discussions.microsoft.com> wrote in message
news:818FDD2E-9ED1-41D1-AD0B-3BB95EE85D78@.microsoft.com...
> What I am attempting to do is span ROWS not columns. Any ideas how to do
this without using the "Hide Duplicates" option?
> "Bruce Johnson [MSFT]" wrote:
> > Tables support cells that span multiple cells. When you select an entire
row
> > an Merge Cells icon becomes active on in the Toolbar area Report
Designer.
> > See the Merge Cells section at
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_structure_objects_v1_2esl.asp
> > for more information.
> > --
> > Bruce Johnson [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "JRodman" <JRodman@.discussions.microsoft.com> wrote in message
> > news:BCCC1E74-354B-40F4-8938-B71C50DFF9A1@.microsoft.com...
> > > I have a report where the first column needs to be spanned over
multiple
> > rows. The first column is part of a group, just the group name is shown
on
> > the detail row with the details. The data looked like this:
> > >
> > > Group1 Detail 1
> > > Group1 Detail 2
> > > Group1 Detail 3
> > > Group2 Detail 1
> > > Group2 Detail 2
> > >
> > > But I dont want the first column to repeat so it looks like this:
> > >
> > > Group1 Detail 1
> > > Detail 2
> > > Detail 3
> > > Group2 Detail 1
> > > Detail 2
> > >
> > > I tried to use the "Hide Duplicates" option on the group column
textbox,
> > but the borders for the textbox still show up. I could not find a simple
way
> > to get the first column to span all the rows in the group, so what I did
was
> > take the detail columns and put them in their own table and nest it in
the
> > group header row. That worked great but here is the problem:
> > >
> > > The first column's data pushes the textbox to display 5 lines of text,
but
> > the detail may only have 1 line of data. It worked fine in the designer,
but
> > when rendered to HTML the lone detail line was aligned to the bottom of
the
> > group row. Even though the nested table's vertical alignment property is
set
> > to top, it was ignored.
> > >
> > > I have service pack 1 installed, so I'm on the latest version. Any
Ideas
> > guys?
> >
SpanRows.rdl
---
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<ZIndex>2</ZIndex>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.25in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>0.125in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>DataSet1</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_ColumnGroup1">
<GroupExpressions>
<GroupExpression />
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>2.125in</Width>
<Top>1.25in</Top>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_Country">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Country_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>Country_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_City">
<GroupExpressions>
<GroupExpression>=Fields!City.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="City_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>City_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!City.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1in</Width>
</RowGrouping>
</RowGroupings>
</Matrix>
<Table Name="table1">
<Height>0.75in</Height>
<ZIndex>1</ZIndex>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Country</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>City</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Country">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<Visibility>
<Hidden>=iif(Fields!Country.Value =Previous(Fields!Country.Value), true, false)</Hidden>
</Visibility>
<rd:DefaultName>Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="City">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>City</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!City.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>DataSet1</DataSetName>
<TableGroups>
<TableGroup>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>2.20834in</Width>
</TableColumn>
<TableColumn>
<Width>2.20834in</Width>
</TableColumn>
<TableColumn>
<Width>2.20834in</Width>
</TableColumn>
</TableColumns>
</Table>
<Line Name="line1">
<Top>1in</Top>
<Height>0in</Height>
<Width>6.375in</Width>
<Style>
<BorderWidth>
<Default>4pt</Default>
</BorderWidth>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Left>0.125in</Left>
</Line>
</ReportItems>
<Style />
<Height>2.75in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>e1d9b0a0-afac-4bfc-b4d3-342bf39c17d4</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.62502in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactName">
<DataField>ContactName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactTitle">
<DataField>ContactTitle</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Address">
<DataField>Address</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Region">
<DataField>Region</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PostalCode">
<DataField>PostalCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Phone">
<DataField>Phone</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Fax">
<DataField>Fax</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>select * from customers</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>4f653deb-b9af-49ca-98e1-fe972d395f88</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

Cell Data Security question

I have a cube that needs to filter out data for a group of users based on the company they work for.

I thought about using the following MDX expression in the Cell Data section in the Edit Roles window for this particular cube:

Ancestor([Dim OPCO].CurrentMember,[OPCO]) IS [COMPANY NAME]

The issue is that the users have to select there particular OPCO COMPANY Name in which ever tool they are using otherwise they will only see NA as the value for all measures.

I would like to filter the data somehow so they do not need to select the particular company they are working for. I would then setup different role groups based on the users company.

Is there some other way of accomplishing this type of task using a filter or scope command?

Thanks ahead of time

Steve Fibich

Hi Steve,

Once you have your roles in place, how about using member security to only permit the company users to see the member for their company? (And make this the default member for that particular role - I assume this is possible, but have yet to do it myself!).

An alternative is to have separate cubes for each company, which I can appreciate is a considerable amount of overhead.

Regards,

Will.

|||

Will,

This sounds like a good solution, how do you set the default propertie in the role. I tried the the following MDX but I get an error when I try to open the Cube.

Under default Member for that particular dimension

[Dim OPCO].[Opco Desc].MemberValue =[Company Name]

I am pretty new to MDX so if this statement is incorrect please let me know. Also is there a good place to get actual examples of MDX vs. the description provided through most of the MS MSDN help sites.

Thanks,

Steve

|||

Never mind I just found the MDX GUI Tool ....I am a little slow today.

Thanks for your help.

catch sql command if value doesnt exist

I have a sql command that is loaded on page load that collects information based on the query string. The query string is a random group of numbers and letters. How do I catch it and direct to an error page if the query can not be found in the database?

Thanks!

if you are trying to get some parameters from the querystring then you can use

dim queryvariable as string =request.querystring("variable")

try

dim sqlquery as string

sqlquery="SELECT column_Name from Table_name where variable= "& queryvariable

//use this sqlquery to check whether it returns some rows or not

catch

response.redirect("pageNotFound.aspx")

end try

|||

I'm pretty sure i did all that.

in page load i'm doing

getUserInfo(Request.QueryString["uid"]);

then the method

protected void getUserInfo(string userid) {string selectCmd ="SELECT * from users WHERE ID = @.id";string strConnection = ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString; SqlConnection myConnection =new SqlConnection(strConnection); SqlCommand myCommand =new SqlCommand(selectCmd, myConnection); myCommand.Parameters.Add(new SqlParameter("@.id", SqlDbType.VarChar, 10)); myCommand.Parameters["@.id"].Value = userid;try { myConnection.Open(); SqlDataReader datareader = myCommand.ExecuteReader();while (datareader.Read()) { lblFirstName.Text = datareader["firstname"].ToString(); lblLastName.Text = datareader["lastname"].ToString(); lblTeam.Text = datareader["team"].ToString(); lblOffice.Text = datareader["office"].ToString(); } datareader.Close(); myConnection.Close(); }catch { Response.Redirect("~/error.aspx"); }

|||

any ideas?

|||

In your code you aren't checking if the datareader actually contains any data or not, so if no records are being returned nothing happens. 1 simple way to do it is:

1. Declare a boolean variable at the top initialized to False: boolean bolUserFound = False

2. Inside the while loop set the value to true: bolUserFound = True

3. After you close the connection evaluate the variable and if it's still false you know no records were found and you need to redirect to your error page:

if (bolUserFound = False) {

Response.Redirect("~/error.aspx");

}

|||

perfect!

Exactly what i needed...