Create a View in MSSQL 2000

I have created a view in MS SQL2000 as followed:

Select order_NO, shiptoname, Shiptoaddress, Shiptocity,shiptostate, shiptozip,
EMAILaddress
FROM orders;

my question is: If the email exist in the EMAILaddress column then I need to have a Y show in another column called EMAILflag, if the EMAILaddress does not exist then I would need the EMAILflag to be a N.

Any HELP would be GREAT.
Thank You!!I have created a view in MS SQL2000 as followed:

Select order_NO, shiptoname, Shiptoaddress, Shiptocity,shiptostate, shiptozip,
EMAILaddress
FROM orders;

my question is: If the email exist in the EMAILaddress column then I need to have a Y show in another column called EMAILflag, if the EMAILaddress does not exist then I would need the EMAILflag to be a N.

Any HELP would be GREAT.
Thank You!!

Check out the CASE Statement in Books on Line.

Regards,

hmscott|||Select order_NO, shiptoname, Shiptoaddress, Shiptocity,shiptostate, shiptozip, EMAILaddress
FROM orders;

Semi-Colon(";")

Is this for DB2 or Oracle 8i?

SQL Server would be

SELECT order_NO
, shiptoname
, Shiptoaddress
, Shiptocity
, shiptostate
, shiptozip
, EMAILaddress
, CASE WHEN EMAILaddress IS NULL THEN 'N' ELSE 'Y' END AS EMAILflag
FROM orders
GO|||Thank You!

Create a Text File

Dear all,
I wanted to create a text file to be passed as input to a Mainframe
system. I have created an SP with the query to create the data exactly
in the requisite format. I need to be creating the text file from
within SQL Server only. I have used osql utility to achieve this and it
works.. However, it adds a couple of extra new lines in the end of the
text file, which is not acceptable to Mainframe system.. Is there any
way I can get rid of the 2 additional lines in the end of the file? Or
is there another way to do this?
I would really appreciate a quick response..
Best regards,
Varkey
PS: I am using master..xp_cmdshell to execute the osql statement. I am
using SQL Server 2000
osql format is 'osql -Q"EXEC mysp" -o c:\test.txt -h-1 -s"" -Smyserver
-dmydb -E'Consider making a small DTS package with the stored proc as the SQL for the
Data Pump. Have SQL Server execute the package.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<varkey.mathew@.wipro.com> wrote in message
news:1147823437.443893.165480@.i39g2000cwa.googlegroups.com...
Dear all,
I wanted to create a text file to be passed as input to a Mainframe
system. I have created an SP with the query to create the data exactly
in the requisite format. I need to be creating the text file from
within SQL Server only. I have used osql utility to achieve this and it
works.. However, it adds a couple of extra new lines in the end of the
text file, which is not acceptable to Mainframe system.. Is there any
way I can get rid of the 2 additional lines in the end of the file? Or
is there another way to do this?
I would really appreciate a quick response..
Best regards,
Varkey
PS: I am using master..xp_cmdshell to execute the osql statement. I am
using SQL Server 2000
osql format is 'osql -Q"EXEC mysp" -o c:\test.txt -h-1 -s"" -Smyserver
-dmydb -E'

Create a Text File

Dear all,
I wanted to create a text file to be passed as input to a Mainframe
system. I have created an SP with the query to create the data exactly
in the requisite format. I need to be creating the text file from
within SQL Server only. I have used osql utility to achieve this and it
works.. However, it adds a couple of extra new lines in the end of the
text file, which is not acceptable to Mainframe system.. Is there any
way I can get rid of the 2 additional lines in the end of the file? Or
is there another way to do this?
I would really appreciate a quick response..
Best regards,
Varkey
PS: I am using master..xp_cmdshell to execute the osql statement. I am
using SQL Server 2000
osql format is 'osql -Q"EXEC mysp" -o c:\test.txt -h-1 -s"" -Smyserver
-dmydb -E'Consider making a small DTS package with the stored proc as the SQL for the
Data Pump. Have SQL Server execute the package.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<varkey.mathew@.wipro.com> wrote in message
news:1147823437.443893.165480@.i39g2000cwa.googlegroups.com...
Dear all,
I wanted to create a text file to be passed as input to a Mainframe
system. I have created an SP with the query to create the data exactly
in the requisite format. I need to be creating the text file from
within SQL Server only. I have used osql utility to achieve this and it
works.. However, it adds a couple of extra new lines in the end of the
text file, which is not acceptable to Mainframe system.. Is there any
way I can get rid of the 2 additional lines in the end of the file? Or
is there another way to do this?
I would really appreciate a quick response..
Best regards,
Varkey
PS: I am using master..xp_cmdshell to execute the osql statement. I am
using SQL Server 2000
osql format is 'osql -Q"EXEC mysp" -o c:\test.txt -h-1 -s"" -Smyserver
-dmydb -E'

Create a Text File

Dear all,
I wanted to create a text file to be passed as input to a Mainframe
system. I have created an SP with the query to create the data exactly
in the requisite format. I need to be creating the text file from
within SQL Server only. I have used osql utility to achieve this and it
works.. However, it adds a couple of extra new lines in the end of the
text file, which is not acceptable to Mainframe system.. Is there any
way I can get rid of the 2 additional lines in the end of the file? Or
is there another way to do this?
I would really appreciate a quick response..
Best regards,
Varkey
PS: I am using master..xp_cmdshell to execute the osql statement. I am
using SQL Server 2000
osql format is 'osql -Q"EXEC mysp" -o c:\test.txt -h-1 -s"" -Smyserver
-dmydb -E'Consider making a small DTS package with the stored proc as the SQL for the
Data Pump. Have SQL Server execute the package.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<varkey.mathew@.wipro.com> wrote in message
news:1147823437.443893.165480@.i39g2000cwa.googlegroups.com...
Dear all,
I wanted to create a text file to be passed as input to a Mainframe
system. I have created an SP with the query to create the data exactly
in the requisite format. I need to be creating the text file from
within SQL Server only. I have used osql utility to achieve this and it
works.. However, it adds a couple of extra new lines in the end of the
text file, which is not acceptable to Mainframe system.. Is there any
way I can get rid of the 2 additional lines in the end of the file? Or
is there another way to do this?
I would really appreciate a quick response..
Best regards,
Varkey
PS: I am using master..xp_cmdshell to execute the osql statement. I am
using SQL Server 2000
osql format is 'osql -Q"EXEC mysp" -o c:\test.txt -h-1 -s"" -Smyserver
-dmydb -E'

Create a table from a select statement

Good afternoon.
Who knows how I can easily create a table where the column names come from another table and are created dynamically?

That is to say. I need to create a new table where the columns, an unknown quantity, are created and given their names from an existing table.

Theory would say:

Create table Bin
as select xyz from ABC
group by xyz

Unfortunately, MS Query Analyzer complains about the AS !!
SQL 2000 server.

Look forward to hearing about the correct way of doing this :-)Yep, SQL Server doesn't like the Oracle format for doing this.

Try

Select XYZ
Into Bin
From ABC
Group by xyz

This will create the correct column data types & lengths but will not create any of their corresponding dependencies. If this is to be part of a process you will also need some sort of logic to determine if that table already exists.

Hope this helps.|||Thanks, good but this only works into a #temp, otherwise get "run sp_dboption" error.
This enters the data as rows. I need columns so that I can add the "real" data afterwards.
Any more ideas ?
Thanks very much|||Select into used to be discouraged because it was a nonlogged transaction that would invalidate your backup sequence. It may still be, though I couldn't find anything specific on this in Books Online.

Run this statement:

select DATABASEPROPERTY('YOURDBNAME', 'IsBulkCopy')

If the result is 0, your database is not set to allow non-logged transactions. You may need to change the setting to use SELECT INSERT.

blindman|||http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_1r94.asp

Is there a specific reason the SELECT INTO option is not permitted in the DB you are working on? You may want to look into it. As for creating just the columns, add the clause WHERE 1 = -1.|||Interesting, very interesting.
I shall have to look into this and find out why that DB has been set up in such a manner on Monday.
I will use a work round, export to .csv gives me a nice long, 1500 comma sperated names, and then just do a normal create and let it all work for a while :-)
What a nightmare, I hate work arounds, but it is a once off DB create.
Thanks for your help, I shall stay tuned and hope that I can be of help to you in the near future.
Take care and enjoy
HandyMac|||Option No 2
Open Fox 7, work with data, create the required table, import into SQL server.
Life is great :-)
Take care and have a great weekend,
HandyMacsql

Create a table and then using it as OLE DB Destination component

I want to do the following in a package:

Create a table at the beginning of a package (using a ExecuteSQLTask component) and then use the created table as a OLE DB destination component, later on the package.

Is this possible in SSIS?

The problem I run into is that I have to point the OLE DB destination component to a table and set up mappings, however as the table does not exist until the package is running, it does not seem to be possible.

I've looked at:

http://blogs.conchango.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx

Which is slightly similar to what I want, but the table I create would not be a temp tables, and I need to set up mappings and I don't see how this is possible.

Thanks

To design and build the package you will need to create the table. Once that has been completed, the table can be dropped, and you can rely on the table created in the Exec SQL Task for the actual running of the package. You will however need to change the DelayValidation property to be True, on the Data Flow Task. This will prevent the task (and components it contains) from being validated, and failing due to the missing table. The Data Flow Task will still be validated, immediately prior to execution, but by that stage the Exec SQL Task will have completed, so the table will then exist.|||

Ok, thanks for the answer but thats whats was mentioned on the cochango link. The difference here is that I do not wish to drop the table after it has been created, I want to create it, use it and keep it...

I'm currently creating packages in C#, however the problem occurs when I make the call:

// Establish a connection

componentInstance.AcquireConnections(null);

// Initialize the metadata

componentInstance.ReinitializeMetaData();

The component is question here is a OLE DB Destination component, it throws an exception because the table does not exist in the database whilst the package is being created.

Now, I know I can set DelayValidation on the Task which contains the destination component

It would be nice if I could:

1. set DelayValidation = true on the Task which contains this component

2. Skip the componentInstance.ReinitializeMetaData() method

3. Set up mappings between the input collection and external meta columns (each of which I would have to create)

However, if I skip the method componentInstance.ReinitializeMetaData I receive there is no inputCollection available to map to, so when you call:

// Get the destination's default input and virtual input.

IDTSInput90 input = component.InputCollection[0];

IDTSVirtualInput90 vInput = input.GetVirtualInput();

there is are no columns available....

Therefore, my question is, can this be done in a C# environment, or does the same rule as creating this in the Designer apply, whereby I would have to have the table available in the database on the creation of the package in order to create the package?

create a log file of a script

I'm am new to using SQL Server. In Oracle, when I create scripts to
manipulate data, I have a log file automatically created from within the
script by issuing the SPOOL command. Is there a similar functionality in SQL
server? besides the log file which records each transaction. Is there any
other way to run a script (automatically) besides cut & paste into SQL query
analyzer?
ThanksLee Ann,
The transaction log for the database is the only automatic record of the
transactions that I'm aware of. You could implement a custom solution using
a trigger/audit table or use a third-party log viewing tool. If you're
trying to test out the command prior to having it automatically commit
(admin type stuff) you can embed the code in a user-defined transaction
(BEGIN TRAN...COMMIT TRAN/ROLLBACK TRAN).

> Is there any other way to run a script (automatically)
You can use schedule a job: T-SQL, schedule a job - DTS, use OSQL and
Windows scheduler, auto-startup procs.
HTH
Jerry
"Lee Ann" <LeeAnn@.discussions.microsoft.com> wrote in message
news:1CA21C9E-3E8B-4265-B7A3-EBF26811AE94@.microsoft.com...
> I'm am new to using SQL Server. In Oracle, when I create scripts to
> manipulate data, I have a log file automatically created from within the
> script by issuing the SPOOL command. Is there a similar functionality in
> SQL
> server? besides the log file which records each transaction. Is there any
> other way to run a script (automatically) besides cut & paste into SQL
> query
> analyzer?
> Thanks|||Lee Ann,
One other approach would be to add auditing code (INSERTs and PRINTs) into
your original script.
HTH
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:Ovq8PAqyFHA.1856@.TK2MSFTNGP12.phx.gbl...
> Lee Ann,
> The transaction log for the database is the only automatic record of the
> transactions that I'm aware of. You could implement a custom solution
> using a trigger/audit table or use a third-party log viewing tool. If
> you're trying to test out the command prior to having it automatically
> commit (admin type stuff) you can embed the code in a user-defined
> transaction (BEGIN TRAN...COMMIT TRAN/ROLLBACK TRAN).
>
> You can use schedule a job: T-SQL, schedule a job - DTS, use OSQL and
> Windows scheduler, auto-startup procs.
> HTH
> Jerry
> "Lee Ann" <LeeAnn@.discussions.microsoft.com> wrote in message
> news:1CA21C9E-3E8B-4265-B7A3-EBF26811AE94@.microsoft.com...
>|||Thanks Jerry...I will go and read up on your suggestions and then try it
out.
Lee Ann
"Jerry Spivey" wrote:

> Lee Ann,
> One other approach would be to add auditing code (INSERTs and PRINTs) into
> your original script.
> HTH
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:Ovq8PAqyFHA.1856@.TK2MSFTNGP12.phx.gbl...
>
>sql

Create 2 publications for same table

Hi

I created a DB named 'TestDB' and created a table called Users. This user table having 3 columns (Uname and pwd,version).

I need to create two publication for this user table.

1) Create a publication using all columns.

2) create a publication using Uname and pwd (not version column).

I am using Merge publication.

When I create first publication (any one - all 3 columns or any 2 coulmns) it create successfully.

When I create second publication it throws error. The details are below.

TITLE: New Publication Wizard

SQL Server Management Studio could not create article 'Users'.

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

An article with a different subscriber_upload_options value already exists for object '[dbo].[Users]'.
Changed database context to 'TestDB'. (Microsoft SQL Server, Error: 20053)

How can i create the second publication? Is it possible? If yes, please give me the steps.

Thanks

Hi

Got the solution. Solved the problem.

The problem was when i create the second publication I changed the article synchronization direction. thats why it throws the error. After givig the same article synchronization direction it is working.

cpu going high (100%) after Index is created

Hi,
Any idea why CPU is going to 100% utilization as soon as i created index
(non clustered) on a table which has indexes already on the table.
before creating index the CPU is about 10 to 20% busy, after creating index
it is going to 100% consistently for about 10 minutes then i dropped index
cpu usage came down to normal
Thanks,
SUBBU.creating index is a resource intensive process, that is one of the
reason people normally find a off peak window for reindexing.
Resources varies depends on how many rows you have of the table and how
many columns you include in the non-clustered index and what are they
(data type).
If you can provide clustered index columns (if any), non-clustered
index columns (the one you intended to put), we can find out more.
Mel|||Hi
I understand creating CPU is resource intensive. CPU is going high after
index is created not while being created.
As of now the table has 98000 rows, aprox 800 pages, 1 clustered composite,
6 non clustered composite.
Data is very static in nature
Thanks,
Subbu.
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
> creating index is a resource intensive process, that is one of the
> reason people normally find a off peak window for reindexing.
> Resources varies depends on how many rows you have of the table and how
> many columns you include in the non-clustered index and what are they
> (data type).
> If you can provide clustered index columns (if any), non-clustered
> index columns (the one you intended to put), we can find out more.
> Mel
>|||You might just have gotten a bad plan. I would try it again.
--
Andrew J. Kelly SQL MVP
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
> Hi
> I understand creating CPU is resource intensive. CPU is going high after
> index is created not while being created.
> As of now the table has 98000 rows, aprox 800 pages, 1 clustered
> composite,
> 6 non clustered composite.
> Data is very static in nature
> Thanks,
> Subbu.
>
> "MSLam" <MelodySLam@.googlemail.com> wrote in message
> news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
>> creating index is a resource intensive process, that is one of the
>> reason people normally find a off peak window for reindexing.
>> Resources varies depends on how many rows you have of the table and how
>> many columns you include in the non-clustered index and what are they
>> (data type).
>> If you can provide clustered index columns (if any), non-clustered
>> index columns (the one you intended to put), we can find out more.
>> Mel
>|||Sorry misunderstood your question in the first place.
One thing to bear in mind that putting index is NOT costless. In fact,
having more than enough indexes can cause problem, particularly if you
enviornment is OLTP. It will help for SELECT but not
UPDATE/INSERT/DELETE (as the index will need to be updated at the same
time).
My rule is always have a one good clustered index and 1-3 non-clustered
indexes should do the job.
Mel|||(BI = Before Index Created
AI = After Index created)
The query for which i have created index is executing faster AI. the query
which was going for index scan (BI) now going for index seek (AI), and
total execution time came down from 9 sec to 1 sec. But CPU is going to
100%, I am guessing does the new index made all other queries to run slow. I
dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
(the data is very static). Is SQL Server not intelligent to ignore indexes
that are not useful in plan preparation. If i create a new index it looks
like other queries which joins this table are going to wrong execution
plans. Please help.
Thanks,
Subbu
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
> You might just have gotten a bad plan. I would try it again.
> --
> Andrew J. Kelly SQL MVP
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
> > Hi
> >
> > I understand creating CPU is resource intensive. CPU is going high after
> > index is created not while being created.
> >
> > As of now the table has 98000 rows, aprox 800 pages, 1 clustered
> > composite,
> > 6 non clustered composite.
> >
> > Data is very static in nature
> >
> > Thanks,
> > Subbu.
> >
> >
> > "MSLam" <MelodySLam@.googlemail.com> wrote in message
> > news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
> >> creating index is a resource intensive process, that is one of the
> >> reason people normally find a off peak window for reindexing.
> >>
> >> Resources varies depends on how many rows you have of the table and how
> >> many columns you include in the non-clustered index and what are they
> >> (data type).
> >>
> >> If you can provide clustered index columns (if any), non-clustered
> >> index columns (the one you intended to put), we can find out more.
> >>
> >> Mel
> >>
> >
> >
>|||How many CPU's do you have? It's possible that parallelism is being used
suboptimally. I'd use the Profiler to find which queries are being
affected. For those, you may want to use OPTION (MAXDOP 1).
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
(BI = Before Index Created
AI = After Index created)
The query for which i have created index is executing faster AI. the query
which was going for index scan (BI) now going for index seek (AI), and
total execution time came down from 9 sec to 1 sec. But CPU is going to
100%, I am guessing does the new index made all other queries to run slow. I
dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
(the data is very static). Is SQL Server not intelligent to ignore indexes
that are not useful in plan preparation. If i create a new index it looks
like other queries which joins this table are going to wrong execution
plans. Please help.
Thanks,
Subbu
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
> You might just have gotten a bad plan. I would try it again.
> --
> Andrew J. Kelly SQL MVP
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
> > Hi
> >
> > I understand creating CPU is resource intensive. CPU is going high after
> > index is created not while being created.
> >
> > As of now the table has 98000 rows, aprox 800 pages, 1 clustered
> > composite,
> > 6 non clustered composite.
> >
> > Data is very static in nature
> >
> > Thanks,
> > Subbu.
> >
> >
> > "MSLam" <MelodySLam@.googlemail.com> wrote in message
> > news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
> >> creating index is a resource intensive process, that is one of the
> >> reason people normally find a off peak window for reindexing.
> >>
> >> Resources varies depends on how many rows you have of the table and how
> >> many columns you include in the non-clustered index and what are they
> >> (data type).
> >>
> >> If you can provide clustered index columns (if any), non-clustered
> >> index columns (the one you intended to put), we can find out more.
> >>
> >> Mel
> >>
> >
> >
>|||Thanks for the idea, i have one cpu.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23H$8I$8XGHA.128@.TK2MSFTNGP05.phx.gbl...
> How many CPU's do you have? It's possible that parallelism is being used
> suboptimally. I'd use the Profiler to find which queries are being
> affected. For those, you may want to use OPTION (MAXDOP 1).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
> (BI = Before Index Created
> AI = After Index created)
>
> The query for which i have created index is executing faster AI. the query
> which was going for index scan (BI) now going for index seek (AI), and
> total execution time came down from 9 sec to 1 sec. But CPU is going to
> 100%, I am guessing does the new index made all other queries to run slow.
I
> dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
> (the data is very static). Is SQL Server not intelligent to ignore indexes
> that are not useful in plan preparation. If i create a new index it looks
> like other queries which joins this table are going to wrong execution
> plans. Please help.
> Thanks,
> Subbu
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
> > You might just have gotten a bad plan. I would try it again.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "subbu" <subbaiahd@.hotmail.com> wrote in message
> > news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
> > > Hi
> > >
> > > I understand creating CPU is resource intensive. CPU is going high
after
> > > index is created not while being created.
> > >
> > > As of now the table has 98000 rows, aprox 800 pages, 1 clustered
> > > composite,
> > > 6 non clustered composite.
> > >
> > > Data is very static in nature
> > >
> > > Thanks,
> > > Subbu.
> > >
> > >
> > > "MSLam" <MelodySLam@.googlemail.com> wrote in message
> > > news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
> > >> creating index is a resource intensive process, that is one of the
> > >> reason people normally find a off peak window for reindexing.
> > >>
> > >> Resources varies depends on how many rows you have of the table and
how
> > >> many columns you include in the non-clustered index and what are they
> > >> (data type).
> > >>
> > >> If you can provide clustered index columns (if any), non-clustered
> > >> index columns (the one you intended to put), we can find out more.
> > >>
> > >> Mel
> > >>
> > >
> > >
> >
> >
>|||Well, it's not parallelism, then. I'd still use the profiler to figure out
where the bones are buried.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:eoh%23Ks9XGHA.4248@.TK2MSFTNGP05.phx.gbl...
Thanks for the idea, i have one cpu.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23H$8I$8XGHA.128@.TK2MSFTNGP05.phx.gbl...
> How many CPU's do you have? It's possible that parallelism is being used
> suboptimally. I'd use the Profiler to find which queries are being
> affected. For those, you may want to use OPTION (MAXDOP 1).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
> (BI = Before Index Created
> AI = After Index created)
>
> The query for which i have created index is executing faster AI. the query
> which was going for index scan (BI) now going for index seek (AI), and
> total execution time came down from 9 sec to 1 sec. But CPU is going to
> 100%, I am guessing does the new index made all other queries to run slow.
I
> dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
> (the data is very static). Is SQL Server not intelligent to ignore indexes
> that are not useful in plan preparation. If i create a new index it looks
> like other queries which joins this table are going to wrong execution
> plans. Please help.
> Thanks,
> Subbu
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
> > You might just have gotten a bad plan. I would try it again.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "subbu" <subbaiahd@.hotmail.com> wrote in message
> > news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
> > > Hi
> > >
> > > I understand creating CPU is resource intensive. CPU is going high
after
> > > index is created not while being created.
> > >
> > > As of now the table has 98000 rows, aprox 800 pages, 1 clustered
> > > composite,
> > > 6 non clustered composite.
> > >
> > > Data is very static in nature
> > >
> > > Thanks,
> > > Subbu.
> > >
> > >
> > > "MSLam" <MelodySLam@.googlemail.com> wrote in message
> > > news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
> > >> creating index is a resource intensive process, that is one of the
> > >> reason people normally find a off peak window for reindexing.
> > >>
> > >> Resources varies depends on how many rows you have of the table and
how
> > >> many columns you include in the non-clustered index and what are they
> > >> (data type).
> > >>
> > >> If you can provide clustered index columns (if any), non-clustered
> > >> index columns (the one you intended to put), we can find out more.
> > >>
> > >> Mel
> > >>
> > >
> > >
> >
> >
>|||If you query originally took 9 seconds and now takes 1 then you must be
returning a lot of rows. The CPU going to 100% is probably just a sign that
it is now able to process more efficiently and thus faster. If no one lese
is on the server and you issue the query and the CPU spikes to 100% for 1
second and then drops to 0 everything is OK.
--
Andrew J. Kelly SQL MVP
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
> (BI = Before Index Created
> AI = After Index created)
>
> The query for which i have created index is executing faster AI. the query
> which was going for index scan (BI) now going for index seek (AI), and
> total execution time came down from 9 sec to 1 sec. But CPU is going to
> 100%, I am guessing does the new index made all other queries to run slow.
> I
> dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
> (the data is very static). Is SQL Server not intelligent to ignore indexes
> that are not useful in plan preparation. If i create a new index it looks
> like other queries which joins this table are going to wrong execution
> plans. Please help.
> Thanks,
> Subbu
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
>> You might just have gotten a bad plan. I would try it again.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "subbu" <subbaiahd@.hotmail.com> wrote in message
>> news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
>> > Hi
>> >
>> > I understand creating CPU is resource intensive. CPU is going high
>> > after
>> > index is created not while being created.
>> >
>> > As of now the table has 98000 rows, aprox 800 pages, 1 clustered
>> > composite,
>> > 6 non clustered composite.
>> >
>> > Data is very static in nature
>> >
>> > Thanks,
>> > Subbu.
>> >
>> >
>> > "MSLam" <MelodySLam@.googlemail.com> wrote in message
>> > news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
>> >> creating index is a resource intensive process, that is one of the
>> >> reason people normally find a off peak window for reindexing.
>> >>
>> >> Resources varies depends on how many rows you have of the table and
>> >> how
>> >> many columns you include in the non-clustered index and what are they
>> >> (data type).
>> >>
>> >> If you can provide clustered index columns (if any), non-clustered
>> >> index columns (the one you intended to put), we can find out more.
>> >>
>> >> Mel
>> >>
>> >
>> >
>>
>|||Sorry for responding late.
Query is returning only few rows around 25. After query executed even then
cpu is not coming down, query executed in one second cpu is 100% for almost
10 minutes then i dropped new indexes created , and immeadiately cpu came
down to normal (20% ).
Thanks,
Subbu.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJF9hdAYGHA.4060@.TK2MSFTNGP02.phx.gbl...
> If you query originally took 9 seconds and now takes 1 then you must be
> returning a lot of rows. The CPU going to 100% is probably just a sign
that
> it is now able to process more efficiently and thus faster. If no one lese
> is on the server and you issue the query and the CPU spikes to 100% for 1
> second and then drops to 0 everything is OK.
> --
> Andrew J. Kelly SQL MVP
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
> >
> > (BI = Before Index Created
> > AI = After Index created)
> >
> >
> > The query for which i have created index is executing faster AI. the
query
> > which was going for index scan (BI) now going for index seek (AI), and
> > total execution time came down from 9 sec to 1 sec. But CPU is going to
> > 100%, I am guessing does the new index made all other queries to run
slow.
> > I
> > dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
> > (the data is very static). Is SQL Server not intelligent to ignore
indexes
> > that are not useful in plan preparation. If i create a new index it
looks
> > like other queries which joins this table are going to wrong execution
> > plans. Please help.
> >
> > Thanks,
> > Subbu
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
> >> You might just have gotten a bad plan. I would try it again.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "subbu" <subbaiahd@.hotmail.com> wrote in message
> >> news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
> >> > Hi
> >> >
> >> > I understand creating CPU is resource intensive. CPU is going high
> >> > after
> >> > index is created not while being created.
> >> >
> >> > As of now the table has 98000 rows, aprox 800 pages, 1 clustered
> >> > composite,
> >> > 6 non clustered composite.
> >> >
> >> > Data is very static in nature
> >> >
> >> > Thanks,
> >> > Subbu.
> >> >
> >> >
> >> > "MSLam" <MelodySLam@.googlemail.com> wrote in message
> >> > news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
> >> >> creating index is a resource intensive process, that is one of the
> >> >> reason people normally find a off peak window for reindexing.
> >> >>
> >> >> Resources varies depends on how many rows you have of the table and
> >> >> how
> >> >> many columns you include in the non-clustered index and what are
they
> >> >> (data type).
> >> >>
> >> >> If you can provide clustered index columns (if any), non-clustered
> >> >> index columns (the one you intended to put), we can find out more.
> >> >>
> >> >> Mel
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||Again, use the profiler to determine the ling-running queries, as well as
those that take a lot of CPU.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:OBoqVgiYGHA.3328@.TK2MSFTNGP02.phx.gbl...
Sorry for responding late.
Query is returning only few rows around 25. After query executed even then
cpu is not coming down, query executed in one second cpu is 100% for almost
10 minutes then i dropped new indexes created , and immeadiately cpu came
down to normal (20% ).
Thanks,
Subbu.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJF9hdAYGHA.4060@.TK2MSFTNGP02.phx.gbl...
> If you query originally took 9 seconds and now takes 1 then you must be
> returning a lot of rows. The CPU going to 100% is probably just a sign
that
> it is now able to process more efficiently and thus faster. If no one lese
> is on the server and you issue the query and the CPU spikes to 100% for 1
> second and then drops to 0 everything is OK.
> --
> Andrew J. Kelly SQL MVP
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
> >
> > (BI = Before Index Created
> > AI = After Index created)
> >
> >
> > The query for which i have created index is executing faster AI. the
query
> > which was going for index scan (BI) now going for index seek (AI), and
> > total execution time came down from 9 sec to 1 sec. But CPU is going to
> > 100%, I am guessing does the new index made all other queries to run
slow.
> > I
> > dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
> > (the data is very static). Is SQL Server not intelligent to ignore
indexes
> > that are not useful in plan preparation. If i create a new index it
looks
> > like other queries which joins this table are going to wrong execution
> > plans. Please help.
> >
> > Thanks,
> > Subbu
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
> >> You might just have gotten a bad plan. I would try it again.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "subbu" <subbaiahd@.hotmail.com> wrote in message
> >> news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
> >> > Hi
> >> >
> >> > I understand creating CPU is resource intensive. CPU is going high
> >> > after
> >> > index is created not while being created.
> >> >
> >> > As of now the table has 98000 rows, aprox 800 pages, 1 clustered
> >> > composite,
> >> > 6 non clustered composite.
> >> >
> >> > Data is very static in nature
> >> >
> >> > Thanks,
> >> > Subbu.
> >> >
> >> >
> >> > "MSLam" <MelodySLam@.googlemail.com> wrote in message
> >> > news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
> >> >> creating index is a resource intensive process, that is one of the
> >> >> reason people normally find a off peak window for reindexing.
> >> >>
> >> >> Resources varies depends on how many rows you have of the table and
> >> >> how
> >> >> many columns you include in the non-clustered index and what are
they
> >> >> (data type).
> >> >>
> >> >> If you can provide clustered index columns (if any), non-clustered
> >> >> index columns (the one you intended to put), we can find out more.
> >> >>
> >> >> Mel
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>

cpu going high (100%) after Index is created

Hi,
Any idea why CPU is going to 100% utilization as soon as i created index
(non clustered) on a table which has indexes already on the table.
before creating index the CPU is about 10 to 20% busy, after creating index
it is going to 100% consistently for about 10 minutes then i dropped index
cpu usage came down to normal
Thanks,
SUBBU.creating index is a resource intensive process, that is one of the
reason people normally find a off peak window for reindexing.
Resources varies depends on how many rows you have of the table and how
many columns you include in the non-clustered index and what are they
(data type).
If you can provide clustered index columns (if any), non-clustered
index columns (the one you intended to put), we can find out more.
Mel|||Hi
I understand creating CPU is resource intensive. CPU is going high after
index is created not while being created.
As of now the table has 98000 rows, aprox 800 pages, 1 clustered composite,
6 non clustered composite.
Data is very static in nature
Thanks,
Subbu.
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
> creating index is a resource intensive process, that is one of the
> reason people normally find a off peak window for reindexing.
> Resources varies depends on how many rows you have of the table and how
> many columns you include in the non-clustered index and what are they
> (data type).
> If you can provide clustered index columns (if any), non-clustered
> index columns (the one you intended to put), we can find out more.
> Mel
>|||You might just have gotten a bad plan. I would try it again.
Andrew J. Kelly SQL MVP
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
> Hi
> I understand creating CPU is resource intensive. CPU is going high after
> index is created not while being created.
> As of now the table has 98000 rows, aprox 800 pages, 1 clustered
> composite,
> 6 non clustered composite.
> Data is very static in nature
> Thanks,
> Subbu.
>
> "MSLam" <MelodySLam@.googlemail.com> wrote in message
> news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
>|||Sorry misunderstood your question in the first place.
One thing to bear in mind that putting index is NOT costless. In fact,
having more than enough indexes can cause problem, particularly if you
enviornment is OLTP. It will help for SELECT but not
UPDATE/INSERT/DELETE (as the index will need to be updated at the same
time).
My rule is always have a one good clustered index and 1-3 non-clustered
indexes should do the job.
Mel|||(BI = Before Index Created
AI = After Index created)
The query for which i have created index is executing faster AI. the query
which was going for index scan (BI) now going for index seek (AI), and
total execution time came down from 9 sec to 1 sec. But CPU is going to
100%, I am guessing does the new index made all other queries to run slow. I
dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
(the data is very static). Is SQL Server not intelligent to ignore indexes
that are not useful in plan preparation. If i create a new index it looks
like other queries which joins this table are going to wrong execution
plans. Please help.
Thanks,
Subbu
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
> You might just have gotten a bad plan. I would try it again.
> --
> Andrew J. Kelly SQL MVP
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
>|||How many CPU's do you have? It's possible that parallelism is being used
suboptimally. I'd use the Profiler to find which queries are being
affected. For those, you may want to use OPTION (MAXDOP 1).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
(BI = Before Index Created
AI = After Index created)
The query for which i have created index is executing faster AI. the query
which was going for index scan (BI) now going for index seek (AI), and
total execution time came down from 9 sec to 1 sec. But CPU is going to
100%, I am guessing does the new index made all other queries to run slow. I
dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
(the data is very static). Is SQL Server not intelligent to ignore indexes
that are not useful in plan preparation. If i create a new index it looks
like other queries which joins this table are going to wrong execution
plans. Please help.
Thanks,
Subbu
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
> You might just have gotten a bad plan. I would try it again.
> --
> Andrew J. Kelly SQL MVP
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
>|||Thanks for the idea, i have one cpu.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23H$8I$8XGHA.128@.TK2MSFTNGP05.phx.gbl...
> How many CPU's do you have? It's possible that parallelism is being used
> suboptimally. I'd use the Profiler to find which queries are being
> affected. For those, you may want to use OPTION (MAXDOP 1).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
> (BI = Before Index Created
> AI = After Index created)
>
> The query for which i have created index is executing faster AI. the query
> which was going for index scan (BI) now going for index seek (AI), and
> total execution time came down from 9 sec to 1 sec. But CPU is going to
> 100%, I am guessing does the new index made all other queries to run slow.
I
> dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
> (the data is very static). Is SQL Server not intelligent to ignore indexes
> that are not useful in plan preparation. If i create a new index it looks
> like other queries which joins this table are going to wrong execution
> plans. Please help.
> Thanks,
> Subbu
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
after[vbcol=seagreen]
how[vbcol=seagreen]
>|||Well, it's not parallelism, then. I'd still use the profiler to figure out
where the bones are buried.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:eoh%23Ks9XGHA.4248@.TK2MSFTNGP05.phx.gbl...
Thanks for the idea, i have one cpu.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23H$8I$8XGHA.128@.TK2MSFTNGP05.phx.gbl...
> How many CPU's do you have? It's possible that parallelism is being used
> suboptimally. I'd use the Profiler to find which queries are being
> affected. For those, you may want to use OPTION (MAXDOP 1).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
> (BI = Before Index Created
> AI = After Index created)
>
> The query for which i have created index is executing faster AI. the query
> which was going for index scan (BI) now going for index seek (AI), and
> total execution time came down from 9 sec to 1 sec. But CPU is going to
> 100%, I am guessing does the new index made all other queries to run slow.
I
> dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
> (the data is very static). Is SQL Server not intelligent to ignore indexes
> that are not useful in plan preparation. If i create a new index it looks
> like other queries which joins this table are going to wrong execution
> plans. Please help.
> Thanks,
> Subbu
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
after[vbcol=seagreen]
how[vbcol=seagreen]
>|||If you query originally took 9 seconds and now takes 1 then you must be
returning a lot of rows. The CPU going to 100% is probably just a sign that
it is now able to process more efficiently and thus faster. If no one lese
is on the server and you issue the query and the CPU spikes to 100% for 1
second and then drops to 0 everything is OK.
Andrew J. Kelly SQL MVP
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
> (BI = Before Index Created
> AI = After Index created)
>
> The query for which i have created index is executing faster AI. the query
> which was going for index scan (BI) now going for index seek (AI), and
> total execution time came down from 9 sec to 1 sec. But CPU is going to
> 100%, I am guessing does the new index made all other queries to run slow.
> I
> dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
> (the data is very static). Is SQL Server not intelligent to ignore indexes
> that are not useful in plan preparation. If i create a new index it looks
> like other queries which joins this table are going to wrong execution
> plans. Please help.
> Thanks,
> Subbu
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
>

Couple questions about partitioning and performance gain

Hello,

I try to maximize cube performance with partitioning. As a test, I have created a very thight partition for one customer only and compare the cube performance between this customer and other customers in larger partitions.
I am a little confused because I do not see a difference.
I also note that when I include calculated measures in the result set, it really knock-down the performance. example, this calculated measure:

iif([Margin Type].CurrentMember.name = "Historical",
iif(isempty([Measures].[AMT]) or [Measures].[AMT] =0, null,
([Measures].[AMT] - [Measures].[Margin1 Cost])/[Measures].[AMT])
, iif(isempty([Measures].[AMT]) or [Measures].[AMT] =0, null,
([Measures].[AMT] - [Measures].[Margin1 Cost Inception])/[Measures].[AMT]))

Really slow-down the cube even for the customer that has a dedicated partition.
The expected result is a matrix of 10,000 rows for 4-5 attributes and 4 columns of measures plus 4 calcuted measures times 8 quarters. unfortunately, I need that much data.

I tried to set the non-empty behavior of this calculated measure to be both types of cost and removed the isempty or 0 test but did not see any change.

I also did both a 30% aggregation plus the user queries optimization.

The fact table is 6M rows, 2 of the dim tables are 300K rows. I run SQL2005 enterprise RTM (no sp1 yet, I did not test it). I use Excel 2003 pivot as front-end.

So is there any benefit in creating lot of small partitions to boost most likely users queries?

I am learning, reading and trying but I lack the practical experience.

Any insights?

Thanks

Philippe

Does this perform any faster?

Create Member Measures.yourcalcmeasure = 1 - [Measures].[Margin1 Cost Inception] / [Measures].[AMT];

(Measures.yourcalcmeasure, [Margin Type].Historical) = 1 - [Measures].[Margin1 Cost]/[Measures].[AMT];

(and try to deal with "divide by zero" errors in the client application)

Thank you

Couple of questions over SSRS over SSAS

I've created a sales analysis cube using SQL Server 2005 and want to do some reporting using SSRS. My problems are as follow:

1. How can I change the report parameter from a Date type into a member of the Date dimension? I need to do this because it's easier for business user to select the date and I can set a sensible default to it.

2. How can I create a hourly sales analysis report for all branches? If I add a chart into SSRS, I will get all shop data summing up right now into a single chart. Instead, I would like to have a chart for each branch. My business user will not accept to manually select different branch and generate the required chart.

3. How can I add a percentage to the corresponding value? My business user may want to view the sales performance during 1:00pm ~ 2:00pm comparing to the whole day. Moreover, they may also want to compare the sales on Monday to the sales for the whole week in percentage sense.

Thanks for any advise.

Regards,
AlexI've solved the third question. In order to do this, I need to add a new column to the matrix. After that, I put the following into expression of the new cell to calculate the percentage.

=Count(Fields!Sales_Count.Value)/Count(Fields!Sales_Count.Value, "matrix1_Day")

matrix1_Day is the column group name that I found out by right click and select the "Edit Group".

This solution is available in the book Microsoft SQL Server 2005 Reporting Services by McGrawHill pp.327-331.

Hope this can help someone later on.

Regards,
Alex