Dimensions within Excel Pivot Table

Hi

I have a cube that has dimensions such as year, company, customer, statustext, employee etc.

In the Browse in Analysis Manager - all the dimensions look fine.

When I access the same cube from Excel after dragging and dropping the dimensions during analysis the dimensions in the Page section are not what is show when dragged to the row section.

For example - i have a display Customer as rows, years as columns. I drag the statustext next to customer and shows customer. The filter in the page section for statustext is correct. I have tried moving the dimension back to the Field List and re-adding, refreshing from the cube makes no difference. The only solution I have been able to come up with is rebuild the Pivot table - not what I want users to have to do.

Unfortunately, at teh moment we are limited to Excel for presentation.

Anyone ever seen this or have any suggestions?

Just remembered, I have experienced "Catastrophic Failure" - in Excel not sure if this is related and is server generated or local to Excel.

Steve

What version of SQL Server and what version of Excel are you using?

Also I did not quite understand the problem you are experiencing. Could you be more specific?

|||

Hi

SQL Server 2000 - Analysis Server 2000 SP3

I have a Pivot table from a cube, this pivot table has multiple dimensions.

For example - Account Manager, Status, Customer, Employee

If I display Sales by Customer (row) by Year (col) - that works.

If I then drag Status to the row - either to replace or in addition to customer - the display is actually Account Manager. The dimension Status is no longer in the Page Header but Account Manager is.

However, if I filter in the Page Header for a specific Status this shows correctly.

The only way I can fix it is to go into the Wizard - choose options - remove all dimensions, re-add them.

Thanks

|||

One possibility is that if your Excel file got corrupted (some mixup with dimensions), this would explain current behavior.

Can you re-produce the problem from scratch (blank spreadsheet)? If you can, how long does it take?

|||

It does happen from scratch but I cannot reproduce it intentionally. I did wonder if it may be because the structure of teh cube within Analysis Server changed. I hope this is not the case.

|||

I have seen a thread where somebody said he regularly experiences problems with pivot becoming corrupt when a datasource changes.

He said it's really common in Excel XP, but does not happen as often with Excel 2003. Since I don't know the Excel version you use, one possibility is to go with Excel 2003.

Also another solution is to always build pivots from scratch. You can even automate this by writing some VBA logic. Hope this helps!

Dimension Question

I have a table that consists of:
companyid
name
industrycd
subindustrycd
regioncd
when creating the company dimension, should I be linking to other dimensions
(i.e. region, subindustry) or include the names of regions/subindustries
within the company dimension?
One of the primary benefits of OLAP is allowing users to drill down and
drill up through fact data using related dimension structures; see...
http://www.intelligententerprise.com/030320/605warehouse1_2.jhtml
So if there is a natural hierarchy of
Industry -> Sub Industry -> Company
then you would generally make use of that in your dimension to allow
this drill down and drill up to occur.
As the relationship between Industry and Region is likely to be
many-to-many; a second Region -> Company dimension would be the
simplest option to include this hierarchy.
A good resource for dimensional modelling concepts is...
http://www.rkimball.com/html/articlesFundamental.html
JP wrote:
> I have a table that consists of:
> companyid
> name
> industrycd
> subindustrycd
> regioncd
> when creating the company dimension, should I be linking to other dimensions
> (i.e. region, subindustry) or include the names of regions/subindustries
> within the company dimension?

Dimension Question

I have a table that consists of:
companyid
name
industrycd
subindustrycd
regioncd
when creating the company dimension, should I be linking to other dimensions
(i.e. region, subindustry) or include the names of regions/subindustries
within the company dimension?One of the primary benefits of OLAP is allowing users to drill down and
drill up through fact data using related dimension structures; see...
http://www.intelligententerprise.co...ehouse1_2.jhtml
So if there is a natural hierarchy of
Industry -> Sub Industry -> Company
then you would generally make use of that in your dimension to allow
this drill down and drill up to occur.
As the relationship between Industry and Region is likely to be
many-to-many; a second Region -> Company dimension would be the
simplest option to include this hierarchy.
A good resource for dimensional modelling concepts is...
http://www.rkimball.com/html/articlesFundamental.html
--
JP wrote:
> I have a table that consists of:
> companyid
> name
> industrycd
> subindustrycd
> regioncd
> when creating the company dimension, should I be linking to other dimensio
ns
> (i.e. region, subindustry) or include the names of regions/subindustries
> within the company dimension?

Dimension Creation Problem

I am currently developing data warehouse design documentation for our compan
y as our first step in creating a data warehouse. During the design I ran i
nto an issue:
Our users want to answer this question: "What are our Export sales?"
Now that is a tricky question and I'll explain why...Export sales is the com
bination of sales for all products with the Product Type of Export and any s
ales (for any product) that are of type Export. I am stumped on how to acco
midate this very necessary
question and many just like it. How do I design my dimensions so that I can
sum based on 2 different dimensions into one answer?
I am sorry if this is a simple question, but for some reason it has me scrat
ching my head.You can either use a view or design your dim tables to accomodate
Ray Higdon MCSE, MCDBA, CCNA
--
"Jason Fischer" <jfischer@.bi-vetmedica.com> wrote in message
news:AC8E6672-7EAB-4DCB-A9AB-38F02E2A4ACC@.microsoft.com...
> I am currently developing data warehouse design documentation for our
company as our first step in creating a data warehouse. During the design I
ran into an issue:
> Our users want to answer this question: "What are our Export sales?"
> Now that is a tricky question and I'll explain why...Export sales is the
combination of sales for all products with the Product Type of Export and
any sales (for any product) that are of type Export. I am stumped on how to
accomidate this very necessary question and many just like it. How do I
design my dimensions so that I can sum based on 2 different dimensions into
one answer?
> I am sorry if this is a simple question, but for some reason it has me
scratching my head.|||I think you need two dimension tables - salestype and product(which contains
product type field).
You also need one fact table - sales with foreign key to product and one to
salestype.
so when you want to ask question "What are the Export Sales", you can query
fact table sales where salestype =
export and product type = export.
-- Jason Fischer wrote: --
I am currently developing data warehouse design documentation for our compan
y as our first step in creating a data warehouse. During the design I ran i
nto an issue:
Our users want to answer this question: "What are our Export sales?"
Now that is a tricky question and I'll explain why...Export sales is the com
bination of sales for all products with the Product Type of Export and any s
ales (for any product) that are of type Export. I am stumped on how to acco
midate this very neces
sary question and many just like it. How do I design my dimensions so that
I can sum based on 2 different dimensions into one answer?
I am sorry if this is a simple question, but for some reason it has me scrat
ching my head.

Different Collation designator Settings

Greetings,
In our company we have different kinds of SQL databases and SQL servers. We
are trying to consolidate those servers under a couple of powerful servers.
But when we began to tell this project to software houses and ask their
idea; we met a big problem .
Some of those companies are using SQL databases with Collation Designator =
Latin1_General setting. But the majority is using with Collation Designator
= Turkish setting. We want to consolidate them under a big MS SQL Server
2000. Anybody that we asked said that is impossible to consolidate those
databases under a server.
So my questions are ;
1- Is is really impossible to consolidate SQL dbases with different
character sets under a single server ? (Latin1_General + Turkish )
2- If no, how should I setup this MS SQL Server to support those char.sets ?
I will be very happy if anybody can help me .
Best regards.
Shadowfax
Hi Shadow,
You can have databases (and even columns) with different collations on SQL
Server 2000. That wasn't possible on SQL Server 7.
Whether it is going to work with 3rd party application is uncertain though.
The problem is that temporary tables are created with the collation of
tempdb on your server. And unless the 3rd party applications are good
quality code (which unfortunately is quite unlikely), there is a good chance
that they will compare character values in permanent tables with character
values in temporary tables while assuming that tempdb has the same collation
as the database that application uses. And if that is not the case, you will
get collation conflicts.
This can be avoided by specifying COLLATE DATABASE_DEFAULT with each
character column when creating temporary tables so that the default
collation of the database the user is currently connected to is used. But as
I said earlier, applications very rarely get up to this level of code
quality.
You would probably best off to have 2 servers, one with Latin1_General
collation and one with Turkish collation.
Jacco Schalkwijk
SQL Server MVP
"Shadow" <shadowfaxx001@.hotmail.com> wrote in message
news:ePhKpPlAFHA.2112@.TK2MSFTNGP14.phx.gbl...
> Greetings,
> In our company we have different kinds of SQL databases and SQL servers.
> We are trying to consolidate those servers under a couple of powerful
> servers. But when we began to tell this project to software houses and ask
> their idea; we met a big problem .
> Some of those companies are using SQL databases with Collation Designator
> = Latin1_General setting. But the majority is using with Collation
> Designator = Turkish setting. We want to consolidate them under a big MS
> SQL Server 2000. Anybody that we asked said that is impossible to
> consolidate those databases under a server.
> So my questions are ;
> 1- Is is really impossible to consolidate SQL dbases with different
> character sets under a single server ? (Latin1_General + Turkish )
> 2- If no, how should I setup this MS SQL Server to support those char.sets
> ?
> I will be very happy if anybody can help me .
> Best regards.
> Shadowfax
>
>
|||Thanks for your help Jacco.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23EJi6DmAFHA.2428@.TK2MSFTNGP14.phx.gbl...
> Hi Shadow,
> You can have databases (and even columns) with different collations on SQL
> Server 2000. That wasn't possible on SQL Server 7.
>
|||You could also consider having two instances of SQL (one w/Latin1_General,
the other with Turkish collation) on the same server.
Bart
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Shadow" <shadowfaxx001@.hotmail.com>
| References: <ePhKpPlAFHA.2112@.TK2MSFTNGP14.phx.gbl>
<#EJi6DmAFHA.2428@.TK2MSFTNGP14.phx.gbl>
| Subject: Re: Different Collation designator Settings
| Date: Tue, 25 Jan 2005 07:09:04 +0200
| Lines: 11
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Response
| Message-ID: <#Slv$vpAFHA.1392@.tk2msftngp13.phx.gbl>
| Newsgroups:
microsoft.public.sqlserver.connect,microsoft.publi c.sqlserver.odbc,microsoft
.public.sqlserver.server,microsoft.public.sqlserve r.setup
| NNTP-Posting-Host: dsl85-97-7802.ttnet.net.tr 85.97.30.122
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFT NGP08.phx.gbl!tk2msftngp13
.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.odbc:43188
microsoft.public.sqlserver.server:375538
microsoft.public.sqlserver.setup:68919
microsoft.public.sqlserver.connect:44240
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| Thanks for your help Jacco.
|
| "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
wrote
| in message news:%23EJi6DmAFHA.2428@.TK2MSFTNGP14.phx.gbl...
| > Hi Shadow,
| >
| > You can have databases (and even columns) with different collations on
SQL
| > Server 2000. That wasn't possible on SQL Server 7.
| >
|
|
|

Different Collation designator Settings

Greetings,
In our company we have different kinds of SQL databases and SQL servers. We
are trying to consolidate those servers under a couple of powerful servers.
But when we began to tell this project to software houses and ask their
idea; we met a big problem .
Some of those companies are using SQL databases with Collation Designator =
Latin1_General setting. But the majority is using with Collation Designator
= Turkish setting. We want to consolidate them under a big MS SQL Server
2000. Anybody that we asked said that is impossible to consolidate those
databases under a server.
So my questions are ;
1- Is is really impossible to consolidate SQL dbases with different
character sets under a single server ? (Latin1_General + Turkish )
2- If no, how should I setup this MS SQL Server to support those char.sets ?
I will be very happy if anybody can help me .
Best regards.
Shadowfax
Hi Shadow,
You can have databases (and even columns) with different collations on SQL
Server 2000. That wasn't possible on SQL Server 7.
Whether it is going to work with 3rd party application is uncertain though.
The problem is that temporary tables are created with the collation of
tempdb on your server. And unless the 3rd party applications are good
quality code (which unfortunately is quite unlikely), there is a good chance
that they will compare character values in permanent tables with character
values in temporary tables while assuming that tempdb has the same collation
as the database that application uses. And if that is not the case, you will
get collation conflicts.
This can be avoided by specifying COLLATE DATABASE_DEFAULT with each
character column when creating temporary tables so that the default
collation of the database the user is currently connected to is used. But as
I said earlier, applications very rarely get up to this level of code
quality.
You would probably best off to have 2 servers, one with Latin1_General
collation and one with Turkish collation.
Jacco Schalkwijk
SQL Server MVP
"Shadow" <shadowfaxx001@.hotmail.com> wrote in message
news:ePhKpPlAFHA.2112@.TK2MSFTNGP14.phx.gbl...
> Greetings,
> In our company we have different kinds of SQL databases and SQL servers.
> We are trying to consolidate those servers under a couple of powerful
> servers. But when we began to tell this project to software houses and ask
> their idea; we met a big problem .
> Some of those companies are using SQL databases with Collation Designator
> = Latin1_General setting. But the majority is using with Collation
> Designator = Turkish setting. We want to consolidate them under a big MS
> SQL Server 2000. Anybody that we asked said that is impossible to
> consolidate those databases under a server.
> So my questions are ;
> 1- Is is really impossible to consolidate SQL dbases with different
> character sets under a single server ? (Latin1_General + Turkish )
> 2- If no, how should I setup this MS SQL Server to support those char.sets
> ?
> I will be very happy if anybody can help me .
> Best regards.
> Shadowfax
>
>
|||Thanks for your help Jacco.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23EJi6DmAFHA.2428@.TK2MSFTNGP14.phx.gbl...
> Hi Shadow,
> You can have databases (and even columns) with different collations on SQL
> Server 2000. That wasn't possible on SQL Server 7.
>
|||You could also consider having two instances of SQL (one w/Latin1_General,
the other with Turkish collation) on the same server.
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Shadow" <shadowfaxx001@.hotmail.com>
| References: <ePhKpPlAFHA.2112@.TK2MSFTNGP14.phx.gbl>
<#EJi6DmAFHA.2428@.TK2MSFTNGP14.phx.gbl>
| Subject: Re: Different Collation designator Settings
| Date: Tue, 25 Jan 2005 07:09:04 +0200
| Lines: 11
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Response
| Message-ID: <#Slv$vpAFHA.1392@.tk2msftngp13.phx.gbl>
| Newsgroups:
microsoft.public.sqlserver.connect,microsoft.publi c.sqlserver.odbc,microsoft
public.sqlserver.server,microsoft.public.sqlserver .setup
| NNTP-Posting-Host: dsl85-97-7802.ttnet.net.tr 85.97.30.122
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFT NGP08.phx.gbl!tk2msftngp13
phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.odbc:43188
microsoft.public.sqlserver.server:375538
microsoft.public.sqlserver.setup:68919
microsoft.public.sqlserver.connect:44240
| X-Tomcat-NG: microsoft.public.sqlserver.connect
|
| Thanks for your help Jacco.
|
| "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
wrote
| in message news:%23EJi6DmAFHA.2428@.TK2MSFTNGP14.phx.gbl...
| > Hi Shadow,
| >
| > You can have databases (and even columns) with different collations on
SQL
| > Server 2000. That wasn't possible on SQL Server 7.
| >
|
|
|

Different Collation designator Settings

Greetings,
In our company we have different kinds of SQL databases and SQL servers. We
are trying to consolidate those servers under a couple of powerful servers.
But when we began to tell this project to software houses and ask their
idea; we met a big problem .
Some of those companies are using SQL databases with Collation Designator = Latin1_General setting. But the majority is using with Collation Designator
= Turkish setting. We want to consolidate them under a big MS SQL Server
2000. Anybody that we asked said that is impossible to consolidate those
databases under a server.
So my questions are ;
1- Is is really impossible to consolidate SQL dbases with different
character sets under a single server ? (Latin1_General + Turkish )
2- If no, how should I setup this MS SQL Server to support those char.sets ?
I will be very happy if anybody can help me .
Best regards.
ShadowfaxHi Shadow,
You can have databases (and even columns) with different collations on SQL
Server 2000. That wasn't possible on SQL Server 7.
Whether it is going to work with 3rd party application is uncertain though.
The problem is that temporary tables are created with the collation of
tempdb on your server. And unless the 3rd party applications are good
quality code (which unfortunately is quite unlikely), there is a good chance
that they will compare character values in permanent tables with character
values in temporary tables while assuming that tempdb has the same collation
as the database that application uses. And if that is not the case, you will
get collation conflicts.
This can be avoided by specifying COLLATE DATABASE_DEFAULT with each
character column when creating temporary tables so that the default
collation of the database the user is currently connected to is used. But as
I said earlier, applications very rarely get up to this level of code
quality.
You would probably best off to have 2 servers, one with Latin1_General
collation and one with Turkish collation.
--
Jacco Schalkwijk
SQL Server MVP
"Shadow" <shadowfaxx001@.hotmail.com> wrote in message
news:ePhKpPlAFHA.2112@.TK2MSFTNGP14.phx.gbl...
> Greetings,
> In our company we have different kinds of SQL databases and SQL servers.
> We are trying to consolidate those servers under a couple of powerful
> servers. But when we began to tell this project to software houses and ask
> their idea; we met a big problem .
> Some of those companies are using SQL databases with Collation Designator
> = Latin1_General setting. But the majority is using with Collation
> Designator = Turkish setting. We want to consolidate them under a big MS
> SQL Server 2000. Anybody that we asked said that is impossible to
> consolidate those databases under a server.
> So my questions are ;
> 1- Is is really impossible to consolidate SQL dbases with different
> character sets under a single server ? (Latin1_General + Turkish )
> 2- If no, how should I setup this MS SQL Server to support those char.sets
> ?
> I will be very happy if anybody can help me .
> Best regards.
> Shadowfax
>
>|||Thanks for your help Jacco.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23EJi6DmAFHA.2428@.TK2MSFTNGP14.phx.gbl...
> Hi Shadow,
> You can have databases (and even columns) with different collations on SQL
> Server 2000. That wasn't possible on SQL Server 7.
>|||You could also consider having two instances of SQL (one w/Latin1_General,
the other with Turkish collation) on the same server.
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Shadow" <shadowfaxx001@.hotmail.com>
| References: <ePhKpPlAFHA.2112@.TK2MSFTNGP14.phx.gbl>
<#EJi6DmAFHA.2428@.TK2MSFTNGP14.phx.gbl>
| Subject: Re: Different Collation designator Settings
| Date: Tue, 25 Jan 2005 07:09:04 +0200
| Lines: 11
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Response
| Message-ID: <#Slv$vpAFHA.1392@.tk2msftngp13.phx.gbl>
| Newsgroups:
microsoft.public.sqlserver.connect,microsoft.public.sqlserver.odbc,microsoft
public.sqlserver.server,microsoft.public.sqlserver.setup
| NNTP-Posting-Host: dsl85-97-7802.ttnet.net.tr 85.97.30.122
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13
phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.odbc:43188
microsoft.public.sqlserver.server:375538
microsoft.public.sqlserver.setup:68919
microsoft.public.sqlserver.connect:44240
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Thanks for your help Jacco.
|
| "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
wrote
| in message news:%23EJi6DmAFHA.2428@.TK2MSFTNGP14.phx.gbl...
| > Hi Shadow,
| >
| > You can have databases (and even columns) with different collations on
SQL
| > Server 2000. That wasn't possible on SQL Server 7.
| >
|
|
|

Different Collation designator Settings

Greetings,
In our company we have different kinds of SQL databases and SQL servers. We
are trying to consolidate those servers under a couple of powerful servers.
But when we began to tell this project to software houses and ask their
idea; we met a big problem .
Some of those companies are using SQL databases with Collation Designator =
Latin1_General setting. But the majority is using with Collation Designator
= Turkish setting. We want to consolidate them under a big MS SQL Server
2000. Anybody that we asked said that is impossible to consolidate those
databases under a server.
So my questions are ;
1- Is is really impossible to consolidate SQL dbases with different
character sets under a single server ? (Latin1_General + Turkish )
2- If no, how should I setup this MS SQL Server to support those char.sets ?
I will be very happy if anybody can help me .
Best regards.
ShadowfaxHi Shadow,
You can have databases (and even columns) with different collations on SQL
Server 2000. That wasn't possible on SQL Server 7.
Whether it is going to work with 3rd party application is uncertain though.
The problem is that temporary tables are created with the collation of
tempdb on your server. And unless the 3rd party applications are good
quality code (which unfortunately is quite unlikely), there is a good chance
that they will compare character values in permanent tables with character
values in temporary tables while assuming that tempdb has the same collation
as the database that application uses. And if that is not the case, you will
get collation conflicts.
This can be avoided by specifying COLLATE DATABASE_DEFAULT with each
character column when creating temporary tables so that the default
collation of the database the user is currently connected to is used. But as
I said earlier, applications very rarely get up to this level of code
quality.
You would probably best off to have 2 servers, one with Latin1_General
collation and one with Turkish collation.
Jacco Schalkwijk
SQL Server MVP
"Shadow" <shadowfaxx001@.hotmail.com> wrote in message
news:ePhKpPlAFHA.2112@.TK2MSFTNGP14.phx.gbl...
> Greetings,
> In our company we have different kinds of SQL databases and SQL servers.
> We are trying to consolidate those servers under a couple of powerful
> servers. But when we began to tell this project to software houses and ask
> their idea; we met a big problem .
> Some of those companies are using SQL databases with Collation Designator
> = Latin1_General setting. But the majority is using with Collation
> Designator = Turkish setting. We want to consolidate them under a big MS
> SQL Server 2000. Anybody that we asked said that is impossible to
> consolidate those databases under a server.
> So my questions are ;
> 1- Is is really impossible to consolidate SQL dbases with different
> character sets under a single server ? (Latin1_General + Turkish )
> 2- If no, how should I setup this MS SQL Server to support those char.sets
> ?
> I will be very happy if anybody can help me .
> Best regards.
> Shadowfax
>
>|||Thanks for your help Jacco.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23EJi6DmAFHA.2428@.TK2MSFTNGP14.phx.gbl...
> Hi Shadow,
> You can have databases (and even columns) with different collations on SQL
> Server 2000. That wasn't possible on SQL Server 7.
>|||You could also consider having two instances of SQL (one w/Latin1_General,
the other with Turkish collation) on the same server.
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Shadow" <shadowfaxx001@.hotmail.com>
| References: <ePhKpPlAFHA.2112@.TK2MSFTNGP14.phx.gbl>
<#EJi6DmAFHA.2428@.TK2MSFTNGP14.phx.gbl>
| Subject: Re: Different Collation designator Settings
| Date: Tue, 25 Jan 2005 07:09:04 +0200
| Lines: 11
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Response
| Message-ID: <#Slv$vpAFHA.1392@.tk2msftngp13.phx.gbl>
| Newsgroups:
microsoft.public.sqlserver.connect,microsoft.public.sqlserver.odbc,microsoft
public.sqlserver.server,microsoft.public.sqlserver.setup
| NNTP-Posting-Host: dsl85-97-7802.ttnet.net.tr 85.97.30.122
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13
phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.odbc:43188
microsoft.public.sqlserver.server:375538
microsoft.public.sqlserver.setup:68919
microsoft.public.sqlserver.connect:44240
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Thanks for your help Jacco.
|
| "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
wrote
| in message news:%23EJi6DmAFHA.2428@.TK2MSFTNGP14.phx.gbl...
| > Hi Shadow,
| >
| > You can have databases (and even columns) with different collations on
SQL
| > Server 2000. That wasn't possible on SQL Server 7.
| >
|
|
|

Different Collation designator Settings

Greetings,
In our company we have different kinds of SQL databases and SQL servers. We
are trying to consolidate those servers under a couple of powerful servers.
But when we began to tell this project to software houses and ask their
idea; we met a big problem .
Some of those companies are using SQL databases with Collation Designator =
Latin1_General setting. But the majority is using with Collation Designator
= Turkish setting. We want to consolidate them under a big MS SQL Server
2000. Anybody that we asked said that is impossible to consolidate those
databases under a server.
So my questions are ;
1- Is is really impossible to consolidate SQL dbases with different
character sets under a single server ? (Latin1_General + Turkish )
2- If no, how should I setup this MS SQL Server to support those char.sets ?
I will be very happy if anybody can help me .
Best regards.
Shadowfax
Hi Shadow,
You can have databases (and even columns) with different collations on SQL
Server 2000. That wasn't possible on SQL Server 7.
Whether it is going to work with 3rd party application is uncertain though.
The problem is that temporary tables are created with the collation of
tempdb on your server. And unless the 3rd party applications are good
quality code (which unfortunately is quite unlikely), there is a good chance
that they will compare character values in permanent tables with character
values in temporary tables while assuming that tempdb has the same collation
as the database that application uses. And if that is not the case, you will
get collation conflicts.
This can be avoided by specifying COLLATE DATABASE_DEFAULT with each
character column when creating temporary tables so that the default
collation of the database the user is currently connected to is used. But as
I said earlier, applications very rarely get up to this level of code
quality.
You would probably best off to have 2 servers, one with Latin1_General
collation and one with Turkish collation.
Jacco Schalkwijk
SQL Server MVP
"Shadow" <shadowfaxx001@.hotmail.com> wrote in message
news:ePhKpPlAFHA.2112@.TK2MSFTNGP14.phx.gbl...
> Greetings,
> In our company we have different kinds of SQL databases and SQL servers.
> We are trying to consolidate those servers under a couple of powerful
> servers. But when we began to tell this project to software houses and ask
> their idea; we met a big problem .
> Some of those companies are using SQL databases with Collation Designator
> = Latin1_General setting. But the majority is using with Collation
> Designator = Turkish setting. We want to consolidate them under a big MS
> SQL Server 2000. Anybody that we asked said that is impossible to
> consolidate those databases under a server.
> So my questions are ;
> 1- Is is really impossible to consolidate SQL dbases with different
> character sets under a single server ? (Latin1_General + Turkish )
> 2- If no, how should I setup this MS SQL Server to support those char.sets
> ?
> I will be very happy if anybody can help me .
> Best regards.
> Shadowfax
>
>
|||Thanks for your help Jacco.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23EJi6DmAFHA.2428@.TK2MSFTNGP14.phx.gbl...
> Hi Shadow,
> You can have databases (and even columns) with different collations on SQL
> Server 2000. That wasn't possible on SQL Server 7.
>
|||You could also consider having two instances of SQL (one w/Latin1_General,
the other with Turkish collation) on the same server.
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Shadow" <shadowfaxx001@.hotmail.com>
| References: <ePhKpPlAFHA.2112@.TK2MSFTNGP14.phx.gbl>
<#EJi6DmAFHA.2428@.TK2MSFTNGP14.phx.gbl>
| Subject: Re: Different Collation designator Settings
| Date: Tue, 25 Jan 2005 07:09:04 +0200
| Lines: 11
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Response
| Message-ID: <#Slv$vpAFHA.1392@.tk2msftngp13.phx.gbl>
| Newsgroups:
microsoft.public.sqlserver.connect,microsoft.publi c.sqlserver.odbc,microsoft
public.sqlserver.server,microsoft.public.sqlserver .setup
| NNTP-Posting-Host: dsl85-97-7802.ttnet.net.tr 85.97.30.122
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFT NGP08.phx.gbl!tk2msftngp13
phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.odbc:43188
microsoft.public.sqlserver.server:375538
microsoft.public.sqlserver.setup:68919
microsoft.public.sqlserver.connect:44240
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Thanks for your help Jacco.
|
| "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
wrote
| in message news:%23EJi6DmAFHA.2428@.TK2MSFTNGP14.phx.gbl...
| > Hi Shadow,
| >
| > You can have databases (and even columns) with different collations on
SQL
| > Server 2000. That wasn't possible on SQL Server 7.
| >
|
|
|

Differences between two Columns

Has anyone computed a difference between two columns in a matrix agianst
the group by column?
Example
YEAR Diff
2003 2004
Company A 100 200 100
Company B 50 200 150
I posted this question earlier but I don't think I was clear about what
I was asking about. If anyone has any ideas please let me know. This
is a pretty common problem for our reporting efforts so a solution would
be greatly appreciated.
Thanks Ahead of Time
Steve
sfibich@.pfgc.comOne way to compute the difference between two columns is to write a function
(either embedded in the .rdl or as an assembly) and call the function from
the cell that is going to hold the return value.
Using your example and using an embedded function:
In the Tab=Layout of your design, click the menu item 'Report' then click
'Report
Properties'. On the resulting dialog box, click Tab=Code. Type in the
function:
Public Function ColumnDiff (YearAmt1 as integer, YearAmt2 as integer)
RETURN YearAmt2 - YearAmt1
End Function
Then in the Layout of your form: put the following expression in your 'Diff'
cell.
=Code.ColumnDiff(Fields!YearAmt1.Value, Fields!YearAmt2.Value)
Good Luck
Dawn|||Dawn wrote:
> One way to compute the difference between two columns is to write a function
> (either embedded in the .rdl or as an assembly) and call the function from
> the cell that is going to hold the return value.
> Using your example and using an embedded function:
> In the Tab=Layout of your design, click the menu item 'Report' then click
> 'Report
> Properties'. On the resulting dialog box, click Tab=Code. Type in the
> function:
> Public Function ColumnDiff (YearAmt1 as integer, YearAmt2 as integer)
> RETURN YearAmt2 - YearAmt1
> End Function
> Then in the Layout of your form: put the following expression in your 'Diff'
> cell.
> =Code.ColumnDiff(Fields!YearAmt1.Value, Fields!YearAmt2.Value)
> Good Luck
> Dawn
>
>
>
>
>
Thats not exactly what I'm looking for. My data is not structure in a
way that I have two columns of data already split on years, it is one
column depicting dollar values, another column depicting year.
Example Data Result Set:
Year Sales Dollars
2004 1000
2003 1020
2002 900
2001 50
2000 1000
What I would want is to put the data into a Matrix
Example:
Year 2004 2003 diff2004/2003 2002 diff
Dollar Value 1000 1020 -20 900 +30
I am wondering if anyone else is running into any year over year
comparison reports and how they are handling it. If anyone has any
suggestions please let me know.
Thanks