Question with Email Subscription

Hi,

I have installed Visual Studio 2005 Reporting Services and try to make some subscription. The strange thing is that after I clicked "New Subscription" in the Reporting Manager and Report Delivery Option only shows the option "Report Server File Share" .I cannot select email subscription. Did I miss something out here?

THanks for the help

Josh

Hi Josh,

sure that you have configured the mail-settings using the RS-Frontend?

cheers
markus

Question on QUOTED_IDENTIFIER

I have a very strange problem, it only happen to one SQL Server, other
SQL Server seems to be fine

I got the following error when trying to run a sp against one of the
SQL Server:

SELECT failed because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for
use with indexed views and/or indexes on computed columns and/or query
notifications and/or xml data type methods.

If I put the SET QUOTED_IDENTIFIER OFF In the beginning of the sp,
everything works fine, but the questions is why should I do that? and
why it only happen to only one SQLServer ? The database option on
QUOTED_IDENTIFIER are off on all SQLServer. (I am using SQL2005)

Thanks in advance.
John

Enclose is the statement that create the database

if db_id('testdb') is not null
drop database [testdb]
go
begin
USE [master]

CREATE DATABASE [testdb] ON PRIMARY
( NAME = N'testdb',
FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\testdb.mdf' ,
SIZE = 8192KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testdb_log',
FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\testdb_log.ldf' ,
SIZE = 29504KB ,
MAXSIZE = 2048GB ,
FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
EXEC dbo.sp_dbcmptlevel @.dbname=N'testdb',

@.new_cmptlevel=90

ALTER DATABASE [testdb] SET ANSI_NULL_DEFAULT OFF

ALTER DATABASE [testdb] SET ANSI_NULLS OFF

ALTER DATABASE [testdb] SET ANSI_PADDING OFF

ALTER DATABASE [testdb] SET ANSI_WARNINGS OFF

ALTER DATABASE [testdb] SET ARITHABORT OFF

ALTER DATABASE [testdb] SET AUTO_CLOSE OFF

ALTER DATABASE [testdb] SET AUTO_CREATE_STATISTICS ON

ALTER DATABASE [testdb] SET AUTO_SHRINK OFF

ALTER DATABASE [testdb] SET AUTO_UPDATE_STATISTICS ON

ALTER DATABASE [testdb] SET CURSOR_CLOSE_ON_COMMIT OFF

ALTER DATABASE [testdb] SET CURSOR_DEFAULT GLOBAL

ALTER DATABASE [testdb] SET CONCAT_NULL_YIELDS_NULL OFF

ALTER DATABASE [testdb] SET NUMERIC_ROUNDABORT OFF

ALTER DATABASE [testdb] SET QUOTED_IDENTIFIER OFF

ALTER DATABASE [testdb] SET RECURSIVE_TRIGGERS OFF

ALTER DATABASE [testdb] SET ENABLE_BROKER

ALTER DATABASE [testdb]
SET
AUTO_UPDATE_STATISTICS_ASYNC ON

ALTER DATABASE [testdb]
SET
DATE_CORRELATION_OPTIMIZATION OFF

ALTER DATABASE [testdb] SET TRUSTWORTHY OFF

ALTER DATABASE [testdb] SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE [testdb] SET PARAMETERIZATION SIMPLE

ALTER DATABASE [testdb] SET READ_WRITE

ALTER DATABASE [testdb] SET RECOVERY FULL

ALTER DATABASE [testdb] SET MULTI_USER

ALTER DATABASE [testdb] SET PAGE_VERIFY CHECKSUM

ALTER DATABASE [testdb] SET DB_CHAINING OFF

endAnother interesting thing, on the server that does not generate the
error, even I put SET QUOTED_IDENTIFIER ON inside the SP, the sp still
work without any error, so it looks on the particular sever the
QUOTED_IDENTIFIER has to be off OFF inside the SP, is this because of a
Server Side setting??

John wrote:

Quote:

Originally Posted by

I have a very strange problem, it only happen to one SQL Server, other
SQL Server seems to be fine
>
I got the following error when trying to run a sp against one of the
SQL Server:
>
SELECT failed because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for
use with indexed views and/or indexes on computed columns and/or query
notifications and/or xml data type methods.
>
If I put the SET QUOTED_IDENTIFIER OFF In the beginning of the sp,
everything works fine, but the questions is why should I do that? and
why it only happen to only one SQLServer ? The database option on
QUOTED_IDENTIFIER are off on all SQLServer. (I am using SQL2005)
>
Thanks in advance.
John
>
Enclose is the statement that create the database
>
if db_id('testdb') is not null
drop database [testdb]
go
begin
USE [master]
>
CREATE DATABASE [testdb] ON PRIMARY
( NAME = N'testdb',
FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\testdb.mdf' ,
SIZE = 8192KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testdb_log',
FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\testdb_log.ldf' ,
SIZE = 29504KB ,
MAXSIZE = 2048GB ,
FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
EXEC dbo.sp_dbcmptlevel @.dbname=N'testdb',
>
@.new_cmptlevel=90
>
ALTER DATABASE [testdb] SET ANSI_NULL_DEFAULT OFF
>
ALTER DATABASE [testdb] SET ANSI_NULLS OFF
>
ALTER DATABASE [testdb] SET ANSI_PADDING OFF
>
ALTER DATABASE [testdb] SET ANSI_WARNINGS OFF
>
ALTER DATABASE [testdb] SET ARITHABORT OFF
>
ALTER DATABASE [testdb] SET AUTO_CLOSE OFF
>
ALTER DATABASE [testdb] SET AUTO_CREATE_STATISTICS ON
>
ALTER DATABASE [testdb] SET AUTO_SHRINK OFF
>
ALTER DATABASE [testdb] SET AUTO_UPDATE_STATISTICS ON
>
ALTER DATABASE [testdb] SET CURSOR_CLOSE_ON_COMMIT OFF
>
ALTER DATABASE [testdb] SET CURSOR_DEFAULT GLOBAL
>
ALTER DATABASE [testdb] SET CONCAT_NULL_YIELDS_NULL OFF
>
ALTER DATABASE [testdb] SET NUMERIC_ROUNDABORT OFF
>
ALTER DATABASE [testdb] SET QUOTED_IDENTIFIER OFF
>
ALTER DATABASE [testdb] SET RECURSIVE_TRIGGERS OFF
>
ALTER DATABASE [testdb] SET ENABLE_BROKER
>
ALTER DATABASE [testdb]
SET
AUTO_UPDATE_STATISTICS_ASYNC ON
>
ALTER DATABASE [testdb]
SET
DATE_CORRELATION_OPTIMIZATION OFF
>
ALTER DATABASE [testdb] SET TRUSTWORTHY OFF
>
ALTER DATABASE [testdb] SET ALLOW_SNAPSHOT_ISOLATION ON
>
ALTER DATABASE [testdb] SET PARAMETERIZATION SIMPLE
>
ALTER DATABASE [testdb] SET READ_WRITE
>
ALTER DATABASE [testdb] SET RECOVERY FULL
>
ALTER DATABASE [testdb] SET MULTI_USER
>
ALTER DATABASE [testdb] SET PAGE_VERIFY CHECKSUM
>
ALTER DATABASE [testdb] SET DB_CHAINING OFF
>
end

|||I think that the state of QUOTED_IDENTIFIER that is used for a stored
procedure is the state that was in place WHEN the stored procedure was
created on the server. The QUOTED_IDENTIFIER state is saved with the stored
procedure metadata.

--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

"John" <johnxhc@.yahoo.comwrote in message
news:1153494983.858599.96180@.m79g2000cwm.googlegro ups.com...

Quote:

Originally Posted by

Another interesting thing, on the server that does not generate the
error, even I put SET QUOTED_IDENTIFIER ON inside the SP, the sp still
work without any error, so it looks on the particular sever the
QUOTED_IDENTIFIER has to be off OFF inside the SP, is this because of a
Server Side setting??
>
John wrote:

Quote:

Originally Posted by

>I have a very strange problem, it only happen to one SQL Server, other
>SQL Server seems to be fine
>>
>I got the following error when trying to run a sp against one of the
>SQL Server:
>>
>SELECT failed because the following SET options have incorrect
>settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for
>use with indexed views and/or indexes on computed columns and/or query
>notifications and/or xml data type methods.
>>
>If I put the SET QUOTED_IDENTIFIER OFF In the beginning of the sp,
>everything works fine, but the questions is why should I do that? and
>why it only happen to only one SQLServer ? The database option on
>QUOTED_IDENTIFIER are off on all SQLServer. (I am using SQL2005)
>>
>Thanks in advance.
>John
>>
>Enclose is the statement that create the database
>>
>if db_id('testdb') is not null
>drop database [testdb]
>go
>begin
>USE [master]
>>
>CREATE DATABASE [testdb] ON PRIMARY
>( NAME = N'testdb',
> FILENAME = N'C:\Program Files\Microsoft SQL
>Server\MSSQL.1\MSSQL\DATA\testdb.mdf' ,
> SIZE = 8192KB ,
> MAXSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
>LOG ON
>( NAME = N'testdb_log',
> FILENAME = N'C:\Program Files\Microsoft SQL
>Server\MSSQL.1\MSSQL\DATA\testdb_log.ldf' ,
> SIZE = 29504KB ,
> MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
>COLLATE SQL_Latin1_General_CP1_CI_AS
>EXEC dbo.sp_dbcmptlevel @.dbname=N'testdb',
>>
>@.new_cmptlevel=90
>>
>ALTER DATABASE [testdb] SET ANSI_NULL_DEFAULT OFF
>>
>ALTER DATABASE [testdb] SET ANSI_NULLS OFF
>>
>ALTER DATABASE [testdb] SET ANSI_PADDING OFF
>>
>ALTER DATABASE [testdb] SET ANSI_WARNINGS OFF
>>
>ALTER DATABASE [testdb] SET ARITHABORT OFF
>>
>ALTER DATABASE [testdb] SET AUTO_CLOSE OFF
>>
>ALTER DATABASE [testdb] SET AUTO_CREATE_STATISTICS ON
>>
>ALTER DATABASE [testdb] SET AUTO_SHRINK OFF
>>
>ALTER DATABASE [testdb] SET AUTO_UPDATE_STATISTICS ON
>>
>ALTER DATABASE [testdb] SET CURSOR_CLOSE_ON_COMMIT OFF
>>
>ALTER DATABASE [testdb] SET CURSOR_DEFAULT GLOBAL
>>
>ALTER DATABASE [testdb] SET CONCAT_NULL_YIELDS_NULL OFF
>>
>ALTER DATABASE [testdb] SET NUMERIC_ROUNDABORT OFF
>>
>ALTER DATABASE [testdb] SET QUOTED_IDENTIFIER OFF
>>
>ALTER DATABASE [testdb] SET RECURSIVE_TRIGGERS OFF
>>
>ALTER DATABASE [testdb] SET ENABLE_BROKER
>>
>ALTER DATABASE [testdb]
> SET
>AUTO_UPDATE_STATISTICS_ASYNC ON
>>
>ALTER DATABASE [testdb]
> SET
>DATE_CORRELATION_OPTIMIZATION OFF
>>
>ALTER DATABASE [testdb] SET TRUSTWORTHY OFF
>>
>ALTER DATABASE [testdb] SET ALLOW_SNAPSHOT_ISOLATION ON
>>
>ALTER DATABASE [testdb] SET PARAMETERIZATION SIMPLE
>>
>ALTER DATABASE [testdb] SET READ_WRITE
>>
>ALTER DATABASE [testdb] SET RECOVERY FULL
>>
>ALTER DATABASE [testdb] SET MULTI_USER
>>
>ALTER DATABASE [testdb] SET PAGE_VERIFY CHECKSUM
>>
>ALTER DATABASE [testdb] SET DB_CHAINING OFF
>>
>end

>

|||John (johnxhc@.yahoo.com) writes:

Quote:

Originally Posted by

I have a very strange problem, it only happen to one SQL Server, other
SQL Server seems to be fine
>
I got the following error when trying to run a sp against one of the
SQL Server:
>
SELECT failed because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for
use with indexed views and/or indexes on computed columns and/or query
notifications and/or xml data type methods.
>
If I put the SET QUOTED_IDENTIFIER OFF In the beginning of the sp,
everything works fine, but the questions is why should I do that? and
why it only happen to only one SQLServer ? The database option on
QUOTED_IDENTIFIER are off on all SQLServer. (I am using SQL2005)

There are a couple of features in SQL Server that requires that the
setting QUOTED_IDENTIIER is ON. They are:

o Indexed views.
o Indexed computed columns.
o XQuery.

Important to understand is that the setting of QUOTED_IDENTIFIER is saved
with the procedure. The same applies to the ANSI_NULLS setting, whereas
for other SET options the run-time setting apply. (Save ANSI_PADDING where
it depends on the setting when the table column was created.)

You can determine the create-time setting for a stored procedure with
this SELECT:

SELECT uses_quoted_identifier, uses_ansi_nulls
FROM sys.sql_modules
WHERE object_id = object_id('yoursp')

As to why a procedure was created with QUOTED_IDENTIFIER off, the most
likely reason in SQL 2005 is that the procedure was loaded through
SQLCMD, which by default has QUOTED_IDENTIFIER off. (Always use the -I
option with SQLCMD to circumvent this problem.) Another possibility is
that the database origins from SQL 2000, where also Enterprise Manager
had QUOTED_IDENTIFIER (and ANSI_NULLS) off by default.

I suspect that the reason it appearst to work if you put SET
QUOTED_IDENTIFIER OFF in the procedure is simply because you reload
the procedure with the correct setting.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Another improvement in your code is to use the ANSI/ISO double quote
marks instead of the dialect square brackets and single quotes.

Question on Plan Cache

I've got something awry in one of our servers. I'm getting SP:CacheMiss
like crazy in Profiler. The same procs repeatedly. The strange thing is
that these procs are in sys.syscacheobjects and the usagecount is growing
regularly so the cached plans are being used....why would I still be
getting CacheMiss? Some of these procs are only called from one specific
webservice the same way every time. The usage counts on some of these are
over 20k so the plan doesn't seem to be clearing it just doesn't seem to be
getting used every time...
Is it because the are being executed as "exec procname parm1, parm2,..."
from the .NET application instead of as a paramterized query? We are trying
to convert everything to parameterized as quickly as we can. But if this is
the cause, why is there a plan that *IS* getting used?
I'm really a bit stuck here and seeing a high number of SQL:Compiles that I
don't believe should be happening.
Thanks in advance!!
Possible reason:
EXEC procname
Above is a batch submitted to SQL Server. This is parsed and a plan is generated, but since it is a
dirt cheap plan it isn't cached. Note, I'm not talking about the proc plan, I'm referring to the
text "EXEC procname". Since it isn't cached, this happens every time you call the proc this way. I'm
tempted to test the difference between execution text and RPC, but since you are about to do it,
just let us know. I wouldn't be surprised if cache handling is different with RPC calls...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
> I've got something awry in one of our servers. I'm getting SP:CacheMiss like crazy in Profiler.
> The same procs repeatedly. The strange thing is that these procs are in sys.syscacheobjects and
> the usagecount is growing regularly so the cached plans are being used....why would I still be
> getting CacheMiss? Some of these procs are only called from one specific webservice the same way
> every time. The usage counts on some of these are over 20k so the plan doesn't seem to be
> clearing it just doesn't seem to be getting used every time...
> Is it because the are being executed as "exec procname parm1, parm2,..." from the .NET
> application instead of as a paramterized query? We are trying to convert everything to
> parameterized as quickly as we can. But if this is the cause, why is there a plan that *IS*
> getting used?
> I'm really a bit stuck here and seeing a high number of SQL:Compiles that I don't believe should
> be happening.
> Thanks in advance!!
>
|||Oh shoot...that is a simple explanation and makes total sense...
Thanks...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:704FC8B8-C95F-489A-A467-ED71F4223BD3@.microsoft.com...
> Possible reason:
> EXEC procname
> Above is a batch submitted to SQL Server. This is parsed and a plan is
> generated, but since it is a dirt cheap plan it isn't cached. Note, I'm
> not talking about the proc plan, I'm referring to the text "EXEC
> procname". Since it isn't cached, this happens every time you call the
> proc this way. I'm tempted to test the difference between execution text
> and RPC, but since you are about to do it, just let us know. I wouldn't be
> surprised if cache handling is different with RPC calls...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>

Question on Plan Cache

I've got something awry in one of our servers. I'm getting SP:CacheMiss
like crazy in Profiler. The same procs repeatedly. The strange thing is
that these procs are in sys.syscacheobjects and the usagecount is growing
regularly so the cached plans are being used....why would I still be
getting CacheMiss' Some of these procs are only called from one specific
webservice the same way every time. The usage counts on some of these are
over 20k so the plan doesn't seem to be clearing it just doesn't seem to be
getting used every time...
Is it because the are being executed as "exec procname parm1, parm2,..."
from the .NET application instead of as a paramterized query? We are trying
to convert everything to parameterized as quickly as we can. But if this is
the cause, why is there a plan that *IS* getting used?
I'm really a bit stuck here and seeing a high number of SQL:Compiles that I
don't believe should be happening.
Thanks in advance!!Possible reason:
EXEC procname
Above is a batch submitted to SQL Server. This is parsed and a plan is gener
ated, but since it is a
dirt cheap plan it isn't cached. Note, I'm not talking about the proc plan,
I'm referring to the
text "EXEC procname". Since it isn't cached, this happens every time you cal
l the proc this way. I'm
tempted to test the difference between execution text and RPC, but since you
are about to do it,
just let us know. I wouldn't be surprised if cache handling is different wit
h RPC calls...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
> I've got something awry in one of our servers. I'm getting SP:CacheMiss l
ike crazy in Profiler.
> The same procs repeatedly. The strange thing is that these procs are in s
ys.syscacheobjects and
> the usagecount is growing regularly so the cached plans are being used...
.why would I still be
> getting CacheMiss' Some of these procs are only called from one specific
webservice the same way
> every time. The usage counts on some of these are over 20k so the plan do
esn't seem to be
> clearing it just doesn't seem to be getting used every time...
> Is it because the are being executed as "exec procname parm1, parm2,..."
from the .NET
> application instead of as a paramterized query? We are trying to convert
everything to
> parameterized as quickly as we can. But if this is the cause, why is ther
e a plan that *IS*
> getting used?
> I'm really a bit stuck here and seeing a high number of SQL:Compiles that
I don't believe should
> be happening.
> Thanks in advance!!
>|||Oh shoot...that is a simple explanation and makes total sense...
Thanks...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:704FC8B8-C95F-489A-A467-ED71F4223BD3@.microsoft.com...
> Possible reason:
> EXEC procname
> Above is a batch submitted to SQL Server. This is parsed and a plan is
> generated, but since it is a dirt cheap plan it isn't cached. Note, I'm
> not talking about the proc plan, I'm referring to the text "EXEC
> procname". Since it isn't cached, this happens every time you call the
> proc this way. I'm tempted to test the difference between execution text
> and RPC, but since you are about to do it, just let us know. I wouldn't be
> surprised if cache handling is different with RPC calls...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>

Question on Plan Cache

I've got something awry in one of our servers. I'm getting SP:CacheMiss
like crazy in Profiler. The same procs repeatedly. The strange thing is
that these procs are in sys.syscacheobjects and the usagecount is growing
regularly so the cached plans are being used....why would I still be
getting CacheMiss' Some of these procs are only called from one specific
webservice the same way every time. The usage counts on some of these are
over 20k so the plan doesn't seem to be clearing it just doesn't seem to be
getting used every time...
Is it because the are being executed as "exec procname parm1, parm2,..."
from the .NET application instead of as a paramterized query? We are trying
to convert everything to parameterized as quickly as we can. But if this is
the cause, why is there a plan that *IS* getting used?
I'm really a bit stuck here and seeing a high number of SQL:Compiles that I
don't believe should be happening.
Thanks in advance!!Possible reason:
EXEC procname
Above is a batch submitted to SQL Server. This is parsed and a plan is generated, but since it is a
dirt cheap plan it isn't cached. Note, I'm not talking about the proc plan, I'm referring to the
text "EXEC procname". Since it isn't cached, this happens every time you call the proc this way. I'm
tempted to test the difference between execution text and RPC, but since you are about to do it,
just let us know. I wouldn't be surprised if cache handling is different with RPC calls...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
> I've got something awry in one of our servers. I'm getting SP:CacheMiss like crazy in Profiler.
> The same procs repeatedly. The strange thing is that these procs are in sys.syscacheobjects and
> the usagecount is growing regularly so the cached plans are being used....why would I still be
> getting CacheMiss' Some of these procs are only called from one specific webservice the same way
> every time. The usage counts on some of these are over 20k so the plan doesn't seem to be
> clearing it just doesn't seem to be getting used every time...
> Is it because the are being executed as "exec procname parm1, parm2,..." from the .NET
> application instead of as a paramterized query? We are trying to convert everything to
> parameterized as quickly as we can. But if this is the cause, why is there a plan that *IS*
> getting used?
> I'm really a bit stuck here and seeing a high number of SQL:Compiles that I don't believe should
> be happening.
> Thanks in advance!!
>|||Oh shoot...that is a simple explanation and makes total sense...
Thanks...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:704FC8B8-C95F-489A-A467-ED71F4223BD3@.microsoft.com...
> Possible reason:
> EXEC procname
> Above is a batch submitted to SQL Server. This is parsed and a plan is
> generated, but since it is a dirt cheap plan it isn't cached. Note, I'm
> not talking about the proc plan, I'm referring to the text "EXEC
> procname". Since it isn't cached, this happens every time you call the
> proc this way. I'm tempted to test the difference between execution text
> and RPC, but since you are about to do it, just let us know. I wouldn't be
> surprised if cache handling is different with RPC calls...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>> I've got something awry in one of our servers. I'm getting SP:CacheMiss
>> like crazy in Profiler. The same procs repeatedly. The strange thing is
>> that these procs are in sys.syscacheobjects and the usagecount is growing
>> regularly so the cached plans are being used....why would I still be
>> getting CacheMiss' Some of these procs are only called from one
>> specific webservice the same way every time. The usage counts on some of
>> these are over 20k so the plan doesn't seem to be clearing it just
>> doesn't seem to be getting used every time...
>> Is it because the are being executed as "exec procname parm1, parm2,..."
>> from the .NET application instead of as a paramterized query? We are
>> trying to convert everything to parameterized as quickly as we can. But
>> if this is the cause, why is there a plan that *IS* getting used?
>> I'm really a bit stuck here and seeing a high number of SQL:Compiles that
>> I don't believe should be happening.
>> Thanks in advance!!
>

Question on how to display data in this unique situation.....

Hi guys,

Man I do come up with strange scenarios, but that is the joy of working in software field right ? ;-)
First off, thanks to anyone taking their time to read this, and Ihope this post paints a clearer picture better than my previous posts.
I have an old stored procedure (which I didn't create) that produces a dataset of the following:-

((All names and values had been changed to protect confidentiality))

region agent_type mailpackage1 mailpackage2 mailpackage3
New York Agenttype1 2000 2300 0
New York Agenttype2 0 0 5
New York Agenttype3 150 2 4000
Central Agenttype2 1234 5678 9
Central Agenttype4 435 1 0
MidWest Agenttype1 555 0 0
West Agenttype1 1 45 0
West Agenttype2 0 2 3

A little bit of explanation:-
Each region can have any type of agents, specified by the number to distinguish different agent types. these agent types mail specific packages to their customers depending on the situation and what the customers asked for. the numbers in each mail package indicate the total that had been sent out by a particular type of an agent. So in this case we are not dealing wtih how many agents are there, just how many packages had been sent out by a specific type of an agent in a region.

Previously the report was produced like you would see in the above dataset. However the client would want it the other way around. Though I didn't show it here, there are plenty of other packages but I am picking three for clarity sake.

So the "new" Report would have to look something like this.

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 2000 0 150 0
Package2 2300 0 2 0
Package3 0 5 4000 0

break page

Region: Central
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 1234 0 435
Package2 0 5678 0 1
Package3 0 9 0 0

- break Page -- and so on

I had created a table in the RS that looked like the above with expressions written into the each cell that holds a value. The expression is

=IIF(Fields!agent_type = "AgentType1", mailpackage1.Value, Cint(0)) in the first row, first column of the table.
=IIF(Fields!agent_type = "AgentType1", mailpackage2.Value, Cint(0)) in the second row, first column of the table.
=IIF(Fields!agent_type = "AgentType1", mailpackage3.Value, Cint(0)) in the third row, First column of the table.
And so on....... alternating between agent_type and mailpackage for each cell.

Grouping1: Group by Region, insert page after each group.

What happened was the following:- ((I am putting the first region, because it is also happening for the other regions too)

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 2000 0 0 0
Package2 2300 0 0 0
Package3 0 0 0 0

break page
Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 0 0 0
Package2 0 0 0 0
Package3 0 5 0 0

break page

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 0 150 0
Package2 0 0 2 0
Package3 0 0 4000 0

break page
(on a side note, this region didn't print out AgentType4 because there were no data associated with it)

The question is, is there anything else I could have done to prevent this ? as you can see, the data is correct and placed in their right cells but somehow, they won't join together. I got a feelin that it has something to do with the expression that I had put in each cell.

Can someone help or point me in the right direction ? This is really bothering me and I couldn't figure out why it was doing this. Couldnt find any links or maybe i am putting in the wrong keywords in the search. Thanks muchly !

Bernard Ong

May it be that yor "new" Report is a changed copy of the old one and you didn't remove the grouping by AgentType?|||

Hi there Markus.

Thanks for the reply.

Actually, The old report was html generated. I am basically creating the new report with the new requirements/design using SSRS 2000 now.

At first I thought ti was the agent type too. But when I checked my groupings again, it only has the region grouping.

I even recreated the whole table, start fresh again and made certain that it is only grouped by Region. Same problem...

Thanks for pointing out the agent type grouping though!

Bernard Ong

|||Well it certainly looks more like a Matrix than a Table layout. If you switch from the table to a matrix, you do not need all the expressions at all. If you do not want to change your report design, try to do it in the query instead then by joining a sub-query for each agent. HTH|||

Thanks for the reply BYU

I am looking up on how to do a matrix, but it seems that I still need a recursive or repetitive row, in this case from what I had seen in the dataset, would be the agent-type. Unless there was another way to do it ?

However, due to the many packages that an agent could mail out, (even in landscape) it would be way too long for the printer or even the preview to look.

Thanks for taking a look into this. I appreciate any more tips or hints with this scenario.

Sincerely,

Bernard Ong

|||

Hello all again,

I am trying to figure out how to use the matrix (never really use them before, always with tables) and I am a little frustrated on how to get it to work or even trying to understand it.
Any references or help which I can refer to, to get me going ? my so called "Matrix" looks like some daVinci code that needs to be broken so that it could be interpreted ! :)

Thanks !

Bernard Ong

|||

Hi,

Could you please check if your initial SQL query has a 'order by region,agenttype' in it ?

If you clients are not really concerned about the excel export of this report - you can consider having a table with a grouping - 'region' and a matrix inside the group displaying data for each region repetitively.

so say table 1

create a group1 header -based on 'region'

add another header row for this group

In this second header row insert a matrix

row field- Agent type

column field- mailpackage types

let me know if you need more explanation. however, this report cannot be exported to excel since export of nested data regions is not supported.

Thanks,

PB

|||

Hi PB

Thanks for the reply !
I am going to try your suggestion and see whether that works. I never thought of using a matrix inside a table, and that might just work.
To answer your question:- Yes the query has a group by region AND agent_Type.

Thanks to the post by BYU, I am currently trying to wrestle with Matrix ("the blue or red pill" :) ) and it is slowly doing what I "think" it is doing.
It is just confusing because the matrix controls work VERY differently with the table. I guess I have to "Add Row" in the cell instead of highlighting the whole row.

I am not sure about this, but with the way I am doing it right now, sounds similar to what you saying except without the header. is that right ?

Sincerely,
Bernard Ong

|||

Hi,

I was talking more about an ORDER BY clause after the Group BY.

select region,etc,cetc

GROUP BY region, agent type,etc

ORDER BY Region,Agent Type.

That probably will make your original table layout also work.

Good luck,

PB

|||

Sorry ORDER BY REgion , Package , Agent Type

|||

Hi PB,

Sorry, must have read the post wrongly. and yes, i do have an order by clause, but with the package in there, it won't work.

The way it is as the dataset is shown, package1, package2 and so on, are the names of the packages(mailpackages column), and the data associated with them are counts or totals.

region agent_type mailpackage1 mailpackage2 mailpackage3
New York Agenttype1 2000 2300 0
New York Agenttype2 0 0 5
New York Agenttype3 150 2 4000
Central Agenttype2 1234 5678 9
Central Agenttype4 435 1 0
MidWest Agenttype1 555 0 0
West Agenttype1 1 45 0
West Agenttype2 0 2 3

Only region and agent_type are string characters, so using the package name in the order by won't work.

Thanks though !

Bernard Ong

|||

Hi Bernard,

I'm still a little unsure of your problem ....could you please explain how the data is stored...I mean how do you get the mailpackage1,mailpackage2 in the stored procedure ....is this a column indicating the type/category of package ?

In that case even if it is a number field , you should order by that before agent type

say you have a table with following columns

region id, mail_package_count, agent_type, packageNo.

newyork, 200, 1 45

new york 500 2 45

new york 30 3 500

This indicates that 200 counts of package45 were delivered by agent 1 , 500 counts of package45 were delivered by agent2 .....

IS that how it is?

I think you may have to make a small change in your stored procedure itself...returning the dataset ordered by region and packaget instead of agent type.

Please ignore this post if I got your problem wrong and Sorry if I confused you even more...

Thanks,

PB

|||

Hi Pbala,

Yeah ! that is exactly how it is without the packageNo. I am sorry that it wasn't clear enough, but in my situation, the numbers represent the count of the packages delivered. I still need the agent type because the client wants to see how many packages were sent out by a particular agent type.

Such as an Elite Agent type, or a basic agent, and they would make a decision out of it. To quote, this is how it is going to look like.

region id, mail_package_1_count, mail_package_2_count, mail_package_3_count, agent_type

newyork, 200 123 456 1

new york 500 342 688 2

new york 30 0 0 3

Hope this makes more sense now.

Still working on the problem but I am getting somewhere though. Right now, with using the matrix, i am able to get the right totals and how it would look like on the report with a "specific" region.

however, when I tried to have the all regions, it just clumped everything into one matrix without showing the other regions. this meaning that i got the total for ALL regions in one page, instead of them breaking out in different pages with each region.

Is there an expression or something am I missing to do ?

Thanks.
Bernard Ong

|||

Hello all once again :)

Just an update on the situation here.
First off, want to thank Pbala, BYU, and Mark for helping out with this thread.
After much reading and trying to figure out the matrix, I got the report working.
I was confused with how to use the Matrix which in the end did more harm than good and finally got the hang of it.
It was all about the groupings and how the Matrix does not act like a table. I resolve this issue by doing the following groupings.

For the Row Groupings:
I only needed to use ONE row Grouping and I ended up putting alot of row groupings thinking that they acted like rows from the table.
Boy, was I dead wrong. I found out that you have to select a cell (Row Section specifically) in the matrix to add a row in the column that would extend to the end.
BY clicking on the row header, it only allows you to add a row grouping and not a row. Either way. I grouped only the region in the
Matrix Properties->Groupings. NExt I keep adding the amount of rows needed to represent a package and drag their fields into the Data section.

For the Column Groupings:
Essentially the same thing, BUT I put the field!agent_type in the column section. Next I entered the the agent_type grouping in the column
grouping of the matrix.

The trouble i was having was not understanding the difference between the Row Groupings and Rows. I keep adding the row groupings which ended up not printing
What i needed for the report. After realizing that problem, I did a bit more research, and figured out that only the cell of the matrix allows Rows.

After that, I put the Subtotal field and it works ! My report printed exactly like the scenario.

Couldn't have done this without this forum and without the ideas and suggestions presented by the posters of this thread ! Thanks alot !

However, I do have another question concerning about the matrix, Is it possible to have an actual total of everything at the bottom of the screen like how the Table Footer would have ? Or the Matrix is only limited to having a subtotal ? The reason why I ask this because I have a option called "All Regions" which should give me the totals of all the regions including each individual region printed out on every page. I achieved this by having a table at the bottom of the matrix to collect the totals. I made the table into a table footer so that it encapsulates everything, and using the Visibility feature, Turn it off if the user only selected a specific region, but turn it on if the user selected "All region". was wondering whether the matrix could also achieve this but in an easier way like how the table would ?

thanks !

Bernard Ong

Question on how to display data in this unique situation.....

Hi guys,

Man I do come up with strange scenarios, but that is the joy of working in software field right ? ;-)
First off, thanks to anyone taking their time to read this, and Ihope this post paints a clearer picture better than my previous posts.
I have an old stored procedure (which I didn't create) that produces a dataset of the following:-

((All names and values had been changed to protect confidentiality))

region agent_type mailpackage1 mailpackage2 mailpackage3
New York Agenttype1 2000 2300 0
New York Agenttype2 0 0 5
New York Agenttype3 150 2 4000
Central Agenttype2 1234 5678 9
Central Agenttype4 435 1 0
MidWest Agenttype1 555 0 0
West Agenttype1 1 45 0
West Agenttype2 0 2 3

A little bit of explanation:-
Each region can have any type of agents, specified by the number to distinguish different agent types. these agent types mail specific packages to their customers depending on the situation and what the customers asked for. the numbers in each mail package indicate the total that had been sent out by a particular type of an agent. So in this case we are not dealing wtih how many agents are there, just how many packages had been sent out by a specific type of an agent in a region.

Previously the report was produced like you would see in the above dataset. However the client would want it the other way around. Though I didn't show it here, there are plenty of other packages but I am picking three for clarity sake.

So the "new" Report would have to look something like this.

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 2000 0 150 0
Package2 2300 0 2 0
Package3 0 5 4000 0

break page

Region: Central
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 1234 0 435
Package2 0 5678 0 1
Package3 0 9 0 0

- break Page -- and so on

I had created a table in the RS that looked like the above with expressions written into the each cell that holds a value. The expression is

=IIF(Fields!agent_type = "AgentType1", mailpackage1.Value, Cint(0)) in the first row, first column of the table.
=IIF(Fields!agent_type = "AgentType1", mailpackage2.Value, Cint(0)) in the second row, first column of the table.
=IIF(Fields!agent_type = "AgentType1", mailpackage3.Value, Cint(0)) in the third row, First column of the table.
And so on....... alternating between agent_type and mailpackage for each cell.

Grouping1: Group by Region, insert page after each group.

What happened was the following:- ((I am putting the first region, because it is also happening for the other regions too)

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 2000 0 0 0
Package2 2300 0 0 0
Package3 0 0 0 0

break page
Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 0 0 0
Package2 0 0 0 0
Package3 0 5 0 0

break page

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 0 150 0
Package2 0 0 2 0
Package3 0 0 4000 0

break page
(on a side note, this region didn't print out AgentType4 because there were no data associated with it)

The question is, is there anything else I could have done to prevent this ? as you can see, the data is correct and placed in their right cells but somehow, they won't join together. I got a feelin that it has something to do with the expression that I had put in each cell.

Can someone help or point me in the right direction ? This is really bothering me and I couldn't figure out why it was doing this. Couldnt find any links or maybe i am putting in the wrong keywords in the search. Thanks muchly !

Bernard Ong

May it be that yor "new" Report is a changed copy of the old one and you didn't remove the grouping by AgentType?|||

Hi there Markus.

Thanks for the reply.

Actually, The old report was html generated. I am basically creating the new report with the new requirements/design using SSRS 2000 now.

At first I thought ti was the agent type too. But when I checked my groupings again, it only has the region grouping.

I even recreated the whole table, start fresh again and made certain that it is only grouped by Region. Same problem...

Thanks for pointing out the agent type grouping though!

Bernard Ong

|||Well it certainly looks more like a Matrix than a Table layout. If you switch from the table to a matrix, you do not need all the expressions at all. If you do not want to change your report design, try to do it in the query instead then by joining a sub-query for each agent. HTH|||

Thanks for the reply BYU

I am looking up on how to do a matrix, but it seems that I still need a recursive or repetitive row, in this case from what I had seen in the dataset, would be the agent-type. Unless there was another way to do it ?

However, due to the many packages that an agent could mail out, (even in landscape) it would be way too long for the printer or even the preview to look.

Thanks for taking a look into this. I appreciate any more tips or hints with this scenario.

Sincerely,

Bernard Ong

|||

Hello all again,

I am trying to figure out how to use the matrix (never really use them before, always with tables) and I am a little frustrated on how to get it to work or even trying to understand it.
Any references or help which I can refer to, to get me going ? my so called "Matrix" looks like some daVinci code that needs to be broken so that it could be interpreted ! :)

Thanks !

Bernard Ong

|||

Hi,

Could you please check if your initial SQL query has a 'order by region,agenttype' in it ?

If you clients are not really concerned about the excel export of this report - you can consider having a table with a grouping - 'region' and a matrix inside the group displaying data for each region repetitively.

so say table 1

create a group1 header -based on 'region'

add another header row for this group

In this second header row insert a matrix

row field- Agent type

column field- mailpackage types

let me know if you need more explanation. however, this report cannot be exported to excel since export of nested data regions is not supported.

Thanks,

PB

|||

Hi PB

Thanks for the reply !
I am going to try your suggestion and see whether that works. I never thought of using a matrix inside a table, and that might just work.
To answer your question:- Yes the query has a group by region AND agent_Type.

Thanks to the post by BYU, I am currently trying to wrestle with Matrix ("the blue or red pill" :) ) and it is slowly doing what I "think" it is doing.
It is just confusing because the matrix controls work VERY differently with the table. I guess I have to "Add Row" in the cell instead of highlighting the whole row.

I am not sure about this, but with the way I am doing it right now, sounds similar to what you saying except without the header. is that right ?

Sincerely,
Bernard Ong

|||

Hi,

I was talking more about an ORDER BY clause after the Group BY.

select region,etc,cetc

GROUP BY region, agent type,etc

ORDER BY Region,Agent Type.

That probably will make your original table layout also work.

Good luck,

PB

|||

Sorry ORDER BY REgion , Package , Agent Type

|||

Hi PB,

Sorry, must have read the post wrongly. and yes, i do have an order by clause, but with the package in there, it won't work.

The way it is as the dataset is shown, package1, package2 and so on, are the names of the packages(mailpackages column), and the data associated with them are counts or totals.

region agent_type mailpackage1 mailpackage2 mailpackage3
New York Agenttype1 2000 2300 0
New York Agenttype2 0 0 5
New York Agenttype3 150 2 4000
Central Agenttype2 1234 5678 9
Central Agenttype4 435 1 0
MidWest Agenttype1 555 0 0
West Agenttype1 1 45 0
West Agenttype2 0 2 3

Only region and agent_type are string characters, so using the package name in the order by won't work.

Thanks though !

Bernard Ong

|||

Hi Bernard,

I'm still a little unsure of your problem ....could you please explain how the data is stored...I mean how do you get the mailpackage1,mailpackage2 in the stored procedure ....is this a column indicating the type/category of package ?

In that case even if it is a number field , you should order by that before agent type

say you have a table with following columns

region id, mail_package_count, agent_type, packageNo.

newyork, 200, 1 45

new york 500 2 45

new york 30 3 500

This indicates that 200 counts of package45 were delivered by agent 1 , 500 counts of package45 were delivered by agent2 .....

IS that how it is?

I think you may have to make a small change in your stored procedure itself...returning the dataset ordered by region and packaget instead of agent type.

Please ignore this post if I got your problem wrong and Sorry if I confused you even more...

Thanks,

PB

|||

Hi Pbala,

Yeah ! that is exactly how it is without the packageNo. I am sorry that it wasn't clear enough, but in my situation, the numbers represent the count of the packages delivered. I still need the agent type because the client wants to see how many packages were sent out by a particular agent type.

Such as an Elite Agent type, or a basic agent, and they would make a decision out of it. To quote, this is how it is going to look like.

region id, mail_package_1_count, mail_package_2_count, mail_package_3_count, agent_type

newyork, 200 123 456 1

new york 500 342 688 2

new york 30 0 0 3

Hope this makes more sense now.

Still working on the problem but I am getting somewhere though. Right now, with using the matrix, i am able to get the right totals and how it would look like on the report with a "specific" region.

however, when I tried to have the all regions, it just clumped everything into one matrix without showing the other regions. this meaning that i got the total for ALL regions in one page, instead of them breaking out in different pages with each region.

Is there an expression or something am I missing to do ?

Thanks.
Bernard Ong

|||

Hello all once again :)

Just an update on the situation here.
First off, want to thank Pbala, BYU, and Mark for helping out with this thread.
After much reading and trying to figure out the matrix, I got the report working.
I was confused with how to use the Matrix which in the end did more harm than good and finally got the hang of it.
It was all about the groupings and how the Matrix does not act like a table. I resolve this issue by doing the following groupings.

For the Row Groupings:
I only needed to use ONE row Grouping and I ended up putting alot of row groupings thinking that they acted like rows from the table.
Boy, was I dead wrong. I found out that you have to select a cell (Row Section specifically) in the matrix to add a row in the column that would extend to the end.
BY clicking on the row header, it only allows you to add a row grouping and not a row. Either way. I grouped only the region in the
Matrix Properties->Groupings. NExt I keep adding the amount of rows needed to represent a package and drag their fields into the Data section.

For the Column Groupings:
Essentially the same thing, BUT I put the field!agent_type in the column section. Next I entered the the agent_type grouping in the column
grouping of the matrix.

The trouble i was having was not understanding the difference between the Row Groupings and Rows. I keep adding the row groupings which ended up not printing
What i needed for the report. After realizing that problem, I did a bit more research, and figured out that only the cell of the matrix allows Rows.

After that, I put the Subtotal field and it works ! My report printed exactly like the scenario.

Couldn't have done this without this forum and without the ideas and suggestions presented by the posters of this thread ! Thanks alot !

However, I do have another question concerning about the matrix, Is it possible to have an actual total of everything at the bottom of the screen like how the Table Footer would have ? Or the Matrix is only limited to having a subtotal ? The reason why I ask this because I have a option called "All Regions" which should give me the totals of all the regions including each individual region printed out on every page. I achieved this by having a table at the bottom of the matrix to collect the totals. I made the table into a table footer so that it encapsulates everything, and using the Visibility feature, Turn it off if the user only selected a specific region, but turn it on if the user selected "All region". was wondering whether the matrix could also achieve this but in an easier way like how the table would ?

thanks !

Bernard Ong

Question on how to display data in this unique situation using Matrix.....

Hi guys,

Man I do come up with strange scenarios, but that is the joy of working in software field right ? ;-)
First off, thanks to anyone taking their time to read this, and Ihope this post paints a clearer picture better than my previous posts.
I have an old stored procedure (which I didn't create) that produces a dataset of the following:-

((All names and values had been changed to protect confidentiality))

region agent_type mailpackage1 mailpackage2 mailpackage3
New York Agenttype1 2000 2300 0
New York Agenttype2 0 0 5
New York Agenttype3 150 2 4000
Central Agenttype2 1234 5678 9
Central Agenttype4 435 1 0
MidWest Agenttype1 555 0 0
West Agenttype1 1 45 0
West Agenttype2 0 2 3

A little bit of explanation:-
Each region can have any type of agents, specified by the number to distinguish different agent types. these agent types mail specific packages to their customers depending on the situation and what the customers asked for. the numbers in each mail package indicate the total that had been sent out by a particular type of an agent. So in this case we are not dealing wtih how many agents are there, just how many packages had been sent out by a specific type of an agent in a region.

Previously the report was produced like you would see in the above dataset. However the client would want it the other way around. Though I didn't show it here, there are plenty of other packages but I am picking three for clarity sake.

So the "new" Report would have to look something like this.

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 2000 0 150 0
Package2 2300 0 2 0
Package3 0 5 4000 0

break page

Region: Central
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 1234 0 435
Package2 0 5678 0 1
Package3 0 9 0 0

- break Page -- and so on

I had created a table in the RS that looked like the above with expressions written into the each cell that holds a value. The expression is

=IIF(Fields!agent_type = "AgentType1", mailpackage1.Value, Cint(0)) in the first row, first column of the table.
=IIF(Fields!agent_type = "AgentType1", mailpackage2.Value, Cint(0)) in the second row, first column of the table.
=IIF(Fields!agent_type = "AgentType1", mailpackage3.Value, Cint(0)) in the third row, First column of the table.
And so on....... alternating between agent_type and mailpackage for each cell.

Grouping1: Group by Region, insert page after each group.

What happened was the following:- ((I am putting the first region, because it is also happening for the other regions too)

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 2000 0 0 0
Package2 2300 0 0 0
Package3 0 0 0 0

break page
Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 0 0 0
Package2 0 0 0 0
Package3 0 5 0 0

break page

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 0 150 0
Package2 0 0 2 0
Package3 0 0 4000 0

break page
(on a side note, this region didn't print out AgentType4 because there were no data associated with it)

The question is, is there anything else I could have done to prevent this ? as you can see, the data is correct and placed in their right cells but somehow, they won't join together. I got a feelin that it has something to do with the expression that I had put in each cell.

Can someone help or point me in the right direction ? This is really bothering me and I couldn't figure out why it was doing this. Couldnt find any links or maybe i am putting in the wrong keywords in the search. Thanks muchly !

Bernard Ong

May it be that yor "new" Report is a changed copy of the old one and you didn't remove the grouping by AgentType?|||

Hi there Markus.

Thanks for the reply.

Actually, The old report was html generated. I am basically creating the new report with the new requirements/design using SSRS 2000 now.

At first I thought ti was the agent type too. But when I checked my groupings again, it only has the region grouping.

I even recreated the whole table, start fresh again and made certain that it is only grouped by Region. Same problem...

Thanks for pointing out the agent type grouping though!

Bernard Ong

|||Well it certainly looks more like a Matrix than a Table layout. If you switch from the table to a matrix, you do not need all the expressions at all. If you do not want to change your report design, try to do it in the query instead then by joining a sub-query for each agent. HTH|||

Thanks for the reply BYU

I am looking up on how to do a matrix, but it seems that I still need a recursive or repetitive row, in this case from what I had seen in the dataset, would be the agent-type. Unless there was another way to do it ?

However, due to the many packages that an agent could mail out, (even in landscape) it would be way too long for the printer or even the preview to look.

Thanks for taking a look into this. I appreciate any more tips or hints with this scenario.

Sincerely,

Bernard Ong

|||

Hello all again,

I am trying to figure out how to use the matrix (never really use them before, always with tables) and I am a little frustrated on how to get it to work or even trying to understand it.
Any references or help which I can refer to, to get me going ? my so called "Matrix" looks like some daVinci code that needs to be broken so that it could be interpreted ! :)

Thanks !

Bernard Ong

|||

Hi,

Could you please check if your initial SQL query has a 'order by region,agenttype' in it ?

If you clients are not really concerned about the excel export of this report - you can consider having a table with a grouping - 'region' and a matrix inside the group displaying data for each region repetitively.

so say table 1

create a group1 header -based on 'region'

add another header row for this group

In this second header row insert a matrix

row field- Agent type

column field- mailpackage types

let me know if you need more explanation. however, this report cannot be exported to excel since export of nested data regions is not supported.

Thanks,

PB

|||

Hi PB

Thanks for the reply !
I am going to try your suggestion and see whether that works. I never thought of using a matrix inside a table, and that might just work.
To answer your question:- Yes the query has a group by region AND agent_Type.

Thanks to the post by BYU, I am currently trying to wrestle with Matrix ("the blue or red pill" :) ) and it is slowly doing what I "think" it is doing.
It is just confusing because the matrix controls work VERY differently with the table. I guess I have to "Add Row" in the cell instead of highlighting the whole row.

I am not sure about this, but with the way I am doing it right now, sounds similar to what you saying except without the header. is that right ?

Sincerely,
Bernard Ong

|||

Hi,

I was talking more about an ORDER BY clause after the Group BY.

select region,etc,cetc

GROUP BY region, agent type,etc

ORDER BY Region,Agent Type.

That probably will make your original table layout also work.

Good luck,

PB

|||

Sorry ORDER BY REgion , Package , Agent Type

|||

Hi PB,

Sorry, must have read the post wrongly. and yes, i do have an order by clause, but with the package in there, it won't work.

The way it is as the dataset is shown, package1, package2 and so on, are the names of the packages(mailpackages column), and the data associated with them are counts or totals.

region agent_type mailpackage1 mailpackage2 mailpackage3
New York Agenttype1 2000 2300 0
New York Agenttype2 0 0 5
New York Agenttype3 150 2 4000
Central Agenttype2 1234 5678 9
Central Agenttype4 435 1 0
MidWest Agenttype1 555 0 0
West Agenttype1 1 45 0
West Agenttype2 0 2 3

Only region and agent_type are string characters, so using the package name in the order by won't work.

Thanks though !

Bernard Ong

|||

Hi Bernard,

I'm still a little unsure of your problem ....could you please explain how the data is stored...I mean how do you get the mailpackage1,mailpackage2 in the stored procedure ....is this a column indicating the type/category of package ?

In that case even if it is a number field , you should order by that before agent type

say you have a table with following columns

region id, mail_package_count, agent_type, packageNo.

newyork, 200, 1 45

new york 500 2 45

new york 30 3 500

This indicates that 200 counts of package45 were delivered by agent 1 , 500 counts of package45 were delivered by agent2 .....

IS that how it is?

I think you may have to make a small change in your stored procedure itself...returning the dataset ordered by region and packaget instead of agent type.

Please ignore this post if I got your problem wrong and Sorry if I confused you even more...

Thanks,

PB

|||

Hi Pbala,

Yeah ! that is exactly how it is without the packageNo. I am sorry that it wasn't clear enough, but in my situation, the numbers represent the count of the packages delivered. I still need the agent type because the client wants to see how many packages were sent out by a particular agent type.

Such as an Elite Agent type, or a basic agent, and they would make a decision out of it. To quote, this is how it is going to look like.

region id, mail_package_1_count, mail_package_2_count, mail_package_3_count, agent_type

newyork, 200 123 456 1

new york 500 342 688 2

new york 30 0 0 3

Hope this makes more sense now.

Still working on the problem but I am getting somewhere though. Right now, with using the matrix, i am able to get the right totals and how it would look like on the report with a "specific" region.

however, when I tried to have the all regions, it just clumped everything into one matrix without showing the other regions. this meaning that i got the total for ALL regions in one page, instead of them breaking out in different pages with each region.

Is there an expression or something am I missing to do ?

Thanks.
Bernard Ong

|||

Hello all once again :)

Just an update on the situation here.
First off, want to thank Pbala, BYU, and Mark for helping out with this thread.
After much reading and trying to figure out the matrix, I got the report working.
I was confused with how to use the Matrix which in the end did more harm than good and finally got the hang of it.
It was all about the groupings and how the Matrix does not act like a table. I resolve this issue by doing the following groupings.

For the Row Groupings:
I only needed to use ONE row Grouping and I ended up putting alot of row groupings thinking that they acted like rows from the table.
Boy, was I dead wrong. I found out that you have to select a cell (Row Section specifically) in the matrix to add a row in the column that would extend to the end.
BY clicking on the row header, it only allows you to add a row grouping and not a row. Either way. I grouped only the region in the
Matrix Properties->Groupings. NExt I keep adding the amount of rows needed to represent a package and drag their fields into the Data section.

For the Column Groupings:
Essentially the same thing, BUT I put the field!agent_type in the column section. Next I entered the the agent_type grouping in the column
grouping of the matrix.

The trouble i was having was not understanding the difference between the Row Groupings and Rows. I keep adding the row groupings which ended up not printing
What i needed for the report. After realizing that problem, I did a bit more research, and figured out that only the cell of the matrix allows Rows.

After that, I put the Subtotal field and it works ! My report printed exactly like the scenario.

Couldn't have done this without this forum and without the ideas and suggestions presented by the posters of this thread ! Thanks alot !

However, I do have another question concerning about the matrix, Is it possible to have an actual total of everything at the bottom of the screen like how the Table Footer would have ? Or the Matrix is only limited to having a subtotal ? The reason why I ask this because I have a option called "All Regions" which should give me the totals of all the regions including each individual region printed out on every page. I achieved this by having a table at the bottom of the matrix to collect the totals. I made the table into a table footer so that it encapsulates everything, and using the Visibility feature, Turn it off if the user only selected a specific region, but turn it on if the user selected "All region". was wondering whether the matrix could also achieve this but in an easier way like how the table would ?

thanks !

Bernard Ong

Question on cube processing and deployment

Hi, all experts here,

Thank you very much for your kind attention.

I encountered a very strange problem processing and deploying the cube to the analysis services server. The fact table where the measure group coming from only got several hundred rows, while at the same time I have other 6 dimensions with many attributes. My system drive currently still has nearly 1 GB space left, but when I deployed the cube, it took ages to run and ended up with process failure because of running out of space on my system drive.

This problem never happened before and it is really frustrated and I dont know what is going on.

Would please any experts here give me any advices. (I have tried ages, but still nothing really changes).

I am looking forward to hearing from you and thanks a lot in advance.

With best regards,

Yours sincerely,

Any expers know what is the cause of that problem?

Thanks.

With best regards,

Yours sincerely,

Question on analysis services cube partiton duration

Hi, all experts here,

It is really strange as I only got a small partition for my cube which only got around 150 rows records. But when I processed the cueb, it took a long while to finish? Why is that? Any advices?

Thanks a lot and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

How long does it take to run the query the AS executes in the relational database?|||

I think that is because the low space on my local system drive.

Thanks a lot.

With best regards,

Yours sincerely,