Flaw in SQL Server Report Services Security

While I wouldn't take this approach in a hostile environment, Reporting Serv
ices exposes a security flaw in the way parameters are parsed.
To see this, follow the walk through for adding a parameter to a report, but
instead of setting "Available Values" to "From Query", set it to "Non-Query
"
The help file tells you to set up your SQL string as so:
="SELECT FirstName, LastName, Title FROM Employee" & IIf(Parameters!Departme
nt.Value = 0,""," WHERE (DepartmentID = " & Parameters!Department.Value &
")") & " ORDER BY LastName"
Then when you deploy the report you will see a prompt that says Department a
nd a text box.
If you enter a Department ID in the box everything works great. But, instead
if you enter something like:
'; Drop table Employee --
And click run report, say goodbye to your employee table.
While I would never use concatenated queries in a production environment, wi
th all of the talk about the security that went into this product, I would h
ave thought that something would have been done to prevent such a common sec
urity flaw.
LeeI haven't gotten around to testing RS yet, but are you connecting the user
as an admin? You must be otherwise they wouldn't be able to execute the
drop statement. This is just sql injection. All user access should be
restricted to stored procedures.
Eric
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:5A570020-575D-4B5F-A775-FC9F45416D50@.microsoft.com...

quote:

---

> While I wouldn't take this approach in a hostile environment, Reporting

---

Services exposes a security flaw in the way parameters are parsed.

quote:

---

> To see this, follow the walk through for adding a parameter to a report,

---

but instead of setting "Available Values" to "From Query", set it to
"Non-Query"

quote:

---

> The help file tells you to set up your SQL string as so:
> ="SELECT FirstName, LastName, Title FROM Employee" &

---

IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " &
Parameters!Department.Value & ")") & " ORDER BY LastName"

quote:

---

> Then when you deploy the report you will see a prompt that says Department

---

and a text box.

quote:

---

> If you enter a Department ID in the box everything works great. But,

---

instead if you enter something like:

quote:

---

> '; Drop table Employee --
> And click run report, say goodbye to your employee table.
> While I would never use concatenated queries in a production environment,

---

with all of the talk about the security that went into this product, I would
have thought that something would have been done to prevent such a common
security flaw.

quote:

---

> Lee

---

Flaw in SQL Server Report Services Security

While I wouldn't take this approach in a hostile environment, Reporting Services exposes a security flaw in the way parameters are parsed.
To see this, follow the walk through for adding a parameter to a report, but instead of setting "Available Values" to "From Query", set it to "Non-Query
The help file tells you to set up your SQL string as so
="SELECT FirstName, LastName, Title FROM Employee" & IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " & Parameters!Department.Value & ")") & " ORDER BY LastName
Then when you deploy the report you will see a prompt that says Department and a text box.
If you enter a Department ID in the box everything works great. But, instead if you enter something like
'; Drop table Employee -
And click run report, say goodbye to your employee table
While I would never use concatenated queries in a production environment, with all of the talk about the security that went into this product, I would have thought that something would have been done to prevent such a common security flaw
LeeI haven't gotten around to testing RS yet, but are you connecting the user
as an admin? You must be otherwise they wouldn't be able to execute the
drop statement. This is just sql injection. All user access should be
restricted to stored procedures.
Eric
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:5A570020-575D-4B5F-A775-FC9F45416D50@.microsoft.com...
> While I wouldn't take this approach in a hostile environment, Reporting
Services exposes a security flaw in the way parameters are parsed.
> To see this, follow the walk through for adding a parameter to a report,
but instead of setting "Available Values" to "From Query", set it to
"Non-Query"
> The help file tells you to set up your SQL string as so:
> ="SELECT FirstName, LastName, Title FROM Employee" &
IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " &
Parameters!Department.Value & ")") & " ORDER BY LastName"
> Then when you deploy the report you will see a prompt that says Department
and a text box.
> If you enter a Department ID in the box everything works great. But,
instead if you enter something like:
> '; Drop table Employee --
> And click run report, say goodbye to your employee table.
> While I would never use concatenated queries in a production environment,
with all of the talk about the security that went into this product, I would
have thought that something would have been done to prevent such a common
security flaw.
> Leesql

Flatten out an XML Document (Currently using OPENXML)

I am attempting to take a column with an XML datatype and make it available for reporting with as little code as possible. Specifically, we are storing credit report info in a column that has an XML datatype. We are using the OPENXML command to navigate the XML structure:

DECLARE @.idoc int
DECLARE @.doc xml
SET @.doc = 'GET THE XML COLUMN HERE
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc

SELECT *
FROM OPENXML (@.idoc, '/XML_INTERFACE/CREDITREPORT/OBJECTS/DBCOMMCREDITSCORE',2)
WITH (
Rating varchar(10) 'DBRATING',
OverFlow xml '@.mp:xmltext')
EXEC sp_xml_removedocument @.idoc

The result will be a column that shows the Rating field and an Overflow column that shows the XML string.

I can take the above code and put it in a stored procedure to return the values in a flat format. My questions:

Does anyone know how I can use a view to display the information? (Create View cannot have the above statements. Is there a way to create a tabular representation off of the SP result set, etc.)
Does anyone have a better way of taking an XML datatype and making this available for report writers?

Dan O

1. The XQuery "nodes()" function will work for this. It is similar to OPENXML, but it is just used from within a normal SELECT statement, so you could create views on top of it.

Here is an example using the nodes() function on the xml data type.

http://msdn2.microsoft.com/en-us/ms188282.aspx

2. nodes() seems like the way to go, but you could also try pre-shredding the xml into a more convenient relational structure if your XML has a lot of nesting that would require many joins to reassemble.

Flatten N-Tier Hierarchy for reporting

Hello. I have a specification for a new application that states that a
particular item structure should support a series of arbitrary hierarchies.
Hierarchy types would be defined in a lookup table, such that items and chil
d
items would be of a particular "type," but this is really only for UI displa
y
purposes -- all items will stored in the same table in the database. Also,
the lowest level of a given heirarchy type should also support cost
accumulation. My hierarchy setup is a standard Id/ParentId model, and I
created an ItemDetail table (FK on the ItemID) to store the cost records.
I have built a c# prototype application that supports this structure, and
everything works like a champ. Here's the problem I have and question on
which I need input: I don't know how to report on it. Since the hierarchy
types have an arbitrary number of levels, how, thru TSQL, do I create a view
on which users can create reports? Through code (c#), I can recurse the
Items table and compare against the ItemTypes table to inspect the hierarchy
types and create tabular (flattened) data. My goal is to expose a view (or
set of views) for end users to use for ad-hoc reporting.
Below is a simplified/truncated data structure similar to my prototype
structure, and my desired end-result. Since I'm still in a prototype stage,
I'm not stuck to the data model, so I'm open to suggestions for improvement
to the design or suggestions on the reporting issue. I hope all this makes
sense, and thanks in advance.
Note: All the primary keys are simple identity columns, because the form of
the data that user knows as the key will vary.
CREATE TABLE [GroupTypes] (
[grptypPk] [int] IDENTITY (1, 1) NOT NULL ,
[grptypName] [varchar] (50) NOT NULL ,
)
CREATE TABLE [Groups] (
[grpPk] [int] IDENTITY (1, 1) NOT NULL ,
[grpName] [varchar] (50) NOT NULL ,
[grpGroupType_fk] [int] NOT NULL
)
CREATE TABLE [ItemTypes] (
[itmtypPk] [int] IDENTITY (1, 1) NOT NULL ,
[itmtypName] [varchar] (50) NOT NULL ,
[itmtypParent] [int] NULL ,
[itmtypGroupType_fk] [int] NOT NULL
)
CREATE TABLE [Items] (
[itmPk] [int] IDENTITY (1, 1) NOT NULL ,
[itmName] [varchar] (50) NOT NULL ,
[itmItemType_fk] [int] NOT NULL ,
[itmGroup_fk] [int] NOT NULL ,
[itmParent] [int] NULL
)
CREATE TABLE [ItemDetailTypes] (
[dtltypPk] [int] IDENTITY (1, 1) NOT NULL ,
[dtltypName] [varchar] (50) NOT NULL ,
)
CREATE TABLE [ItemDetail] (
[itmdtlPk] [int] IDENTITY (1, 1) NOT NULL ,
[itmdtlCost] [int] NOT NULL ,
[itmdtlItem_fk] [int] NOT NULL ,
[itmdtlDetailType_fk] [int] NOT NULL
)
/*----*/
DECLARE @.itmtyp2 INT
DECLARE @.itmtyp3 INT
DECLARE @.itm3 INT
DECLARE @.itm6 INT
DECLARE @.dtltyp1 INT
INSERT INTO GroupTypes (grptypName) VALUES ('Group Type 1')
SET @.grptyp1 = @.@.IDENTITY
INSERT INTO Groups (grpName, grpGroupType_fk) VALUES ('Group1', @.grptyp1)
SET @.grp1 = @.@.IDENTITY
INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
('Item Type 1', NULL, @.grptyp1)
SET @.itmtyp1 = @.@.IDENTITY
INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
('Item Type 2', @.itmtyp1, @.grptyp1)
SET @.itmtyp2 = @.@.IDENTITY
INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
('Item Type 3', @.itmtyp2, @.grptyp1)
SET @.itmtyp3 = @.@.IDENTITY
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item1', @.itmtyp1, @.grp1, NULL)
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item2', @.itmtyp2, @.grp1, @.@.IDENTITY)
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item3', @.itmtyp3, @.grp1, @.@.IDENTITY)
SET @.itm3 = @.@.IDENTITY
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item4', @.itmtyp1, @.grp1, NULL)
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item5', @.itmtyp2, @.grp1, @.@.IDENTITY)
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item6', @.itmtyp3, @.grp1, @.@.IDENTITY)
SET @.itm6 = @.@.IDENTITY
INSERT INTO ItemDetailTypes (dtltypName) VALUES ('Detail Type 1')
SET @.dtltyp1 = @.@.IDENTITY
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(10, @.itm3, @.dtltyp1)
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(20, @.itm3, @.dtltyp1)
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(30, @.itm3, @.dtltyp1)
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(15, @.itm6, @.dtltyp1)
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(25, @.itm6, @.dtltyp1)
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(30, @.itm6, @.dtltyp1)
/*----*/
Flattened data for Group1:
Group1 Item1 Item2 Item3
Group1 Item4 Item5 Item6
I believe this is an easy structure on which to report, and I could just
INNER JOIN on the ItemDetail table for Cost information.Hi
The best way to do this is to traverse the hierachy and build up the rows on
the the client.
There are many posts regarding hierarchies in SQL Server, so you may also
want to search Google for previous posts.
John
"Steve" wrote:

> Hello. I have a specification for a new application that states that a
> particular item structure should support a series of arbitrary hierarchies
.
> Hierarchy types would be defined in a lookup table, such that items and ch
ild
> items would be of a particular "type," but this is really only for UI disp
lay
> purposes -- all items will stored in the same table in the database. Also
,
> the lowest level of a given heirarchy type should also support cost
> accumulation. My hierarchy setup is a standard Id/ParentId model, and I
> created an ItemDetail table (FK on the ItemID) to store the cost records.
> I have built a c# prototype application that supports this structure, and
> everything works like a champ. Here's the problem I have and question on
> which I need input: I don't know how to report on it. Since the hierarchy
> types have an arbitrary number of levels, how, thru TSQL, do I create a vi
ew
> on which users can create reports? Through code (c#), I can recurse the
> Items table and compare against the ItemTypes table to inspect the hierarc
hy
> types and create tabular (flattened) data. My goal is to expose a view (o
r
> set of views) for end users to use for ad-hoc reporting.
> Below is a simplified/truncated data structure similar to my prototype
> structure, and my desired end-result. Since I'm still in a prototype stag
e,
> I'm not stuck to the data model, so I'm open to suggestions for improvemen
t
> to the design or suggestions on the reporting issue. I hope all this make
s
> sense, and thanks in advance.
>
> Note: All the primary keys are simple identity columns, because the form o
f
> the data that user knows as the key will vary.
> CREATE TABLE [GroupTypes] (
> [grptypPk] [int] IDENTITY (1, 1) NOT NULL ,
> [grptypName] [varchar] (50) NOT NULL ,
> )
> CREATE TABLE [Groups] (
> [grpPk] [int] IDENTITY (1, 1) NOT NULL ,
> [grpName] [varchar] (50) NOT NULL ,
> [grpGroupType_fk] [int] NOT NULL
> )
> CREATE TABLE [ItemTypes] (
> [itmtypPk] [int] IDENTITY (1, 1) NOT NULL ,
> [itmtypName] [varchar] (50) NOT NULL ,
> [itmtypParent] [int] NULL ,
> [itmtypGroupType_fk] [int] NOT NULL
> )
> CREATE TABLE [Items] (
> [itmPk] [int] IDENTITY (1, 1) NOT NULL ,
> [itmName] [varchar] (50) NOT NULL ,
> [itmItemType_fk] [int] NOT NULL ,
> [itmGroup_fk] [int] NOT NULL ,
> [itmParent] [int] NULL
> )
> CREATE TABLE [ItemDetailTypes] (
> [dtltypPk] [int] IDENTITY (1, 1) NOT NULL ,
> [dtltypName] [varchar] (50) NOT NULL ,
> )
> CREATE TABLE [ItemDetail] (
> [itmdtlPk] [int] IDENTITY (1, 1) NOT NULL ,
> [itmdtlCost] [int] NOT NULL ,
> [itmdtlItem_fk] [int] NOT NULL ,
> [itmdtlDetailType_fk] [int] NOT NULL
> )
>
> /*----*
/
>
> DECLARE @.itmtyp2 INT
> DECLARE @.itmtyp3 INT
> DECLARE @.itm3 INT
> DECLARE @.itm6 INT
> DECLARE @.dtltyp1 INT
>
> INSERT INTO GroupTypes (grptypName) VALUES ('Group Type 1')
> SET @.grptyp1 = @.@.IDENTITY
> INSERT INTO Groups (grpName, grpGroupType_fk) VALUES ('Group1', @.grptyp1)
> SET @.grp1 = @.@.IDENTITY
> INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
> ('Item Type 1', NULL, @.grptyp1)
> SET @.itmtyp1 = @.@.IDENTITY
> INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
> ('Item Type 2', @.itmtyp1, @.grptyp1)
> SET @.itmtyp2 = @.@.IDENTITY
> INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
> ('Item Type 3', @.itmtyp2, @.grptyp1)
> SET @.itmtyp3 = @.@.IDENTITY
>
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item1', @.itmtyp1, @.grp1, NULL)
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item2', @.itmtyp2, @.grp1, @.@.IDENTITY)
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item3', @.itmtyp3, @.grp1, @.@.IDENTITY)
> SET @.itm3 = @.@.IDENTITY
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item4', @.itmtyp1, @.grp1, NULL)
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item5', @.itmtyp2, @.grp1, @.@.IDENTITY)
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item6', @.itmtyp3, @.grp1, @.@.IDENTITY)
> SET @.itm6 = @.@.IDENTITY
> INSERT INTO ItemDetailTypes (dtltypName) VALUES ('Detail Type 1')
> SET @.dtltyp1 = @.@.IDENTITY
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (10, @.itm3, @.dtltyp1)
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (20, @.itm3, @.dtltyp1)
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (30, @.itm3, @.dtltyp1)
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (15, @.itm6, @.dtltyp1)
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (25, @.itm6, @.dtltyp1)
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (30, @.itm6, @.dtltyp1)
> /*----*
/
> Flattened data for Group1:
> Group1 Item1 Item2 Item3
> Group1 Item4 Item5 Item6
> I believe this is an easy structure on which to report, and I could just
> INNER JOIN on the ItemDetail table for Cost information.
>|||Thanks John. I've actually done my due-diligence Googling, but I couldn't
find what I was looking for. My Google results returned _lots_ of results o
n
how to transform a flat dataset into a hierarchical one, but not the reverse
!
I'd like for the users to be able to use generic reporting tools, such as
Access, etc., to be able to report on the data. So I'm looking for a way, i
n
SQL, or more generally, at the database-level, to present the data in a way
that doesn't require special code in order to group/subtotal.
"John Bell" wrote:
> Hi
> The best way to do this is to traverse the hierachy and build up the rows
on
> the the client.
> There are many posts regarding hierarchies in SQL Server, so you may also
> want to search Google for previous posts.
> John
> "Steve" wrote:
>|||Hi
If you return your hierarchy in order then the client can flatten it. This
will be the fastest solution!
For traversing the hierarchy posts like http://tinyurl.com/o3rc are a good
start.
To produce a crosstab output from the above results try something like
http://www.windowsitpro.com/SQLServ...5608/15608.html
John
"Steve" wrote:
> Thanks John. I've actually done my due-diligence Googling, but I couldn't
> find what I was looking for. My Google results returned _lots_ of results
on
> how to transform a flat dataset into a hierarchical one, but not the rever
se!
> I'd like for the users to be able to use generic reporting tools, such as
> Access, etc., to be able to report on the data. So I'm looking for a way,
in
> SQL, or more generally, at the database-level, to present the data in a wa
y
> that doesn't require special code in order to group/subtotal.
>
> "John Bell" wrote:
>

Fixed reports in Reporting Services using SSAS

I am quite new with Reporting Services so appologies if I'm asking a
really simple question without relaising it.
I have a Analysis Services cube that I'm querying from Reporting
Services 2005, I'm wondering how I can create a fixed style report that
has specfic members from one dimension in one axis and specific members
from another dimension in another.
Eg:
Jan Feb Mar Apr May Jun Jul Aug
Cost1 2 6 5 8 88 0 8 8
Cost15 0 0 0 0 0 6 54 5
Cost65 52 5 545 1 42 55 54 455
Cost15 545 0 555 0 22 2252 5 55
Cost95
I have found that it's quite easy to create this format in a dymamic
fashion but I have had no hope in doing so with fixed members in both
axis.
Any help would be much appreciated.
Thanks
SimonI think that you can make 1,15,65,15,95 members as a 'named set'
hope that helps
-Aaron
sk wrote:
> I am quite new with Reporting Services so appologies if I'm asking a
> really simple question without relaising it.
> I have a Analysis Services cube that I'm querying from Reporting
> Services 2005, I'm wondering how I can create a fixed style report that
> has specfic members from one dimension in one axis and specific members
> from another dimension in another.
> Eg:
> Jan Feb Mar Apr May Jun Jul Aug
> Cost1 2 6 5 8 88 0 8 8
> Cost15 0 0 0 0 0 6 54 5
> Cost65 52 5 545 1 42 55 54 455
> Cost15 545 0 555 0 22 2252 5 55
> Cost95
> I have found that it's quite easy to create this format in a dymamic
> fashion but I have had no hope in doing so with fixed members in both
> axis.
> Any help would be much appreciated.
> Thanks
> Simon|||I think you need to organize the way your data gets outputed. If you manage
to create a "grid" of data in the form you want it to, you could just use a
fixed table and add the fields to the column or row that you want it to.
That would probably mean a lot of crossjoins or unions on the coloumns to
get the months tagged right, and have the different costs as rows.
Sorry to ask, but why do you want to do it fixed? This is sort of what
matrixes are made for, contrary to the tables.
Kaisa M. Lindahl Lervik
"sk" <simon.m.knight@.gmail.com> wrote in message
news:1159979952.751107.275360@.m73g2000cwd.googlegroups.com...
>I am quite new with Reporting Services so appologies if I'm asking a
> really simple question without relaising it.
> I have a Analysis Services cube that I'm querying from Reporting
> Services 2005, I'm wondering how I can create a fixed style report that
> has specfic members from one dimension in one axis and specific members
> from another dimension in another.
> Eg:
> Jan Feb Mar Apr May Jun Jul Aug
> Cost1 2 6 5 8 88 0 8 8
> Cost15 0 0 0 0 0 6 54 5
> Cost65 52 5 545 1 42 55 54 455
> Cost15 545 0 555 0 22 2252 5 55
> Cost95
> I have found that it's quite easy to create this format in a dymamic
> fashion but I have had no hope in doing so with fixed members in both
> axis.
> Any help would be much appreciated.
> Thanks
> Simon
>|||Thanks for your reply,
The report needs to look exactly the same as an Excel based matrix
report that we use. the format is defined by our corporate entity so I
can't change it.
Thanks
Simon|||Excel isn't a reporting platform.
Tell them to eat shit
-Aaron
sk wrote:
> Thanks for your reply,
> The report needs to look exactly the same as an Excel based matrix
> report that we use. the format is defined by our corporate entity so I
> can't change it.
>
> Thanks
> Simon|||Thanks for your suggestion, I'll give it a try!
Still, it must br possible to create a fixed grid report. I'll try your
suggestions and the others posted here..
Simon|||There's a discription in Chris Hays' blog about Horizontal Tables. Might be
what you need:
http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx
As for using a matrix, what parts are different between your Excel report
and a matix based report?
Kaisa M. Lindahl Lervik
"sk" <simon.m.knight@.gmail.com> wrote in message
news:1160074016.884219.105770@.i42g2000cwa.googlegroups.com...
> Thanks for your suggestion, I'll give it a try!
> Still, it must br possible to create a fixed grid report. I'll try your
> suggestions and the others posted here..
>
> Simon
>|||Thanks for that - I'll look into it.
The difference between my Excel report and a matrix based report is
that the Excel report is completely static. just a table with fixed
members in each axis. The members in each axis need to be fixed like a
PL report (see example)
http://www.ilytix.com/ImageFiles/Popup_P&L_Report.gif|||OK, the picture looks like a standard RS table. As long as your output is in
a usefull format, you could use a RS table. If you make sure you always get
the right data out (like you know you only get the same 4 rows of growth),
you can probably use a dynamic table. Or you can really hard code it,
returning several data sets with one row of data in each and creating
several single line tables put close in the report designer so they look
like one big table.
If your data set returns both dynamic growth and dates (months in your first
example), you could solve it by using a matrix. Make a row group for your
growth, and a column group for dates. "Pad" your data set to make sure you
always return the same number of months for dates, so you always get the
same number of month columns, and the same for the growth numbers. You might
have to tweak your mdx statemement a bit, but that's probably more usefull
than customizing the table.
Kaisa M. Lindahl Lervik
"sk" <simon.m.knight@.gmail.com> wrote in message
news:1160083140.237336.110450@.i3g2000cwc.googlegroups.com...
> Thanks for that - I'll look into it.
> The difference between my Excel report and a matrix based report is
> that the Excel report is completely static. just a table with fixed
> members in each axis. The members in each axis need to be fixed like a
> PL report (see example)
> http://www.ilytix.com/ImageFiles/Popup_P&L_Report.gif
>

Fixed number of Intervals

Hi friends,

We are using SQL Server 2005 Reporting Services for developing our Reports. In one of our Reports having chart can we show fixed number of Intervals on X axis or Y axis ?

-sandyee

I believe that you can. In the y axis and x axis properties it gives you a minimum value, maximum value, and interval property to set.

Fixed header rowin Reporting Services

Hi,
I've been devloping reports using Reporting Services. It's useful and fast.
However, I've this group of users who would like to fix the header row while
the data is able to scoll down and up without moving the header row. Is
there any configuration that can solve my problem? Below is a simple
illustration, hope anyone can help me in this.
| X header | Y header | Z header | ... | => This row must be fixed
| X data1 | Y data1 | Z data1 |... | ^
| X data2 | Y data2 | Z data2 |... | |
| X data3 | Y data3 | Z data3 |... | |
| X data4 | Y data4 | Z data4 |... | | These data rows
should be able to
| X data5 | Y data5 | Z data5 |... | | scroll up and down
| ........ | ........ | ........ |... | |
| X dataN | Y dataN | Z dataN |... | v
Best Regards,
Samie
I don't know how to do this.
I suspect that you will have a better response within the Reporting Services
newsgroup. I am including that group in this reply. Hopefully someone in
that group will be able to help.
Keith
"Samie" <Samie@.discussions.microsoft.com> wrote in message
news:D12C8438-E59D-49D5-9E2D-31C4D3A6B17C@.microsoft.com...
> Hi,
> I've been devloping reports using Reporting Services. It's useful and
fast.
> However, I've this group of users who would like to fix the header row
while
> the data is able to scoll down and up without moving the header row. Is
> there any configuration that can solve my problem? Below is a simple
> illustration, hope anyone can help me in this.
> | X header | Y header | Z header | ... | => This row must be fixed
> | X data1 | Y data1 | Z data1 |... | ^
> | X data2 | Y data2 | Z data2 |... | |
> | X data3 | Y data3 | Z data3 |... | |
> | X data4 | Y data4 | Z data4 |... | | These data rows
> should be able to
> | X data5 | Y data5 | Z data5 |... | | scroll up and down
> | ........ | ........ | ........ |... | |
> | X dataN | Y dataN | Z dataN |... | v
>
> Best Regards,
> Samie
>
|||Thanks Keith...
I'm hoping someone can help me too.
Regards,
samie
"Keith Kratochvil" wrote:

> I don't know how to do this.
> I suspect that you will have a better response within the Reporting Services
> newsgroup. I am including that group in this reply. Hopefully someone in
> that group will be able to help.
> --
> Keith
>
> "Samie" <Samie@.discussions.microsoft.com> wrote in message
> news:D12C8438-E59D-49D5-9E2D-31C4D3A6B17C@.microsoft.com...
> fast.
> while
>
|||Samie,
Fixed headers are not supported with version 1.0 but on the wish list for
2005.
Hope this helps.
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
"Samie" <Samie@.discussions.microsoft.com> wrote in message
news:119F060E-C479-4AF6-97E2-B59600B8D383@.microsoft.com...[vbcol=seagreen]
> Thanks Keith...
> I'm hoping someone can help me too.
> Regards,
> samie
>
> "Keith Kratochvil" wrote:
Services[vbcol=seagreen]
in[vbcol=seagreen]
row[vbcol=seagreen]
Is[vbcol=seagreen]
fixed[vbcol=seagreen]
rows[vbcol=seagreen]
down[vbcol=seagreen]

Fixed header in Sql Reporting

Hi All,
I have an issue with SQL reports. Once I set FixedHeader Property of Table
used in SQl report TURE. The detail of data getting overlapped on fixed
header of the report.
Is there any way to resolve this?
Thankin advance.
ShaileshSet you header line(s) BackgroundColor to White instead of the default
Transparent.
"Shailesh K" wrote:
> Hi All,
> I have an issue with SQL reports. Once I set FixedHeader Property of Table
> used in SQl report TURE. The detail of data getting overlapped on fixed
> header of the report.
> Is there any way to resolve this?
> Thankin advance.
> Shailesh|||Thanks William...It works well.
"William" wrote:
> Set you header line(s) BackgroundColor to White instead of the default
> Transparent.
>
> "Shailesh K" wrote:
> > Hi All,
> >
> > I have an issue with SQL reports. Once I set FixedHeader Property of Table
> > used in SQl report TURE. The detail of data getting overlapped on fixed
> > header of the report.
> > Is there any way to resolve this?
> > Thankin advance.
> >
> > Shailesh

Fixed Header in Reporting Service 2000

I want to create a report with SQL Server Reporting Service 2000 where I need to have a fixed header and one fixed column containg names of each row items. I tried to find "Fixed Header" property in table but could not locate it anywhere.Can anyone please kindly tell me where should i look for it?Or how to achieve this functionality?

Thank you in advance.

Bharat Gadhia.

FixedHeader is a new feature that got added in RS 2005. It was not available in 2000.|||

Hi Fang,

Thank you for the reply.

Can you tell me how to achieve this functionality in SQL Server Reporting Service 2000?

Bharat Gadhia.

Fixed Column Headers

I have a report in Reporting Services that I want the users to be able to
scroll up and down with the column headers fixed, just like freezing a window
in Excel. I'd like to have the far left remain fixed as well, but that's not
as important as the headers. Does anyone know how to do that?This feature is not in RS 2000, but will be supported in the next release.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim" <Jim@.discussions.microsoft.com> wrote in message
news:E161DB2B-5739-4816-B1BF-0222C3E51C79@.microsoft.com...
>I have a report in Reporting Services that I want the users to be able to
> scroll up and down with the column headers fixed, just like freezing a
> window
> in Excel. I'd like to have the far left remain fixed as well, but that's
> not
> as important as the headers. Does anyone know how to do that?

FIX TO: Reporting server 2005 prompting for user name /ID and domain when accessing http:localho

If you get the error message: The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version.

If you then scan the recent logfiles in C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\LogFiles and find the following exception: Microsoft.SqlServer.ReportingServices2005.RSConnection+MissingEndpointException: The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version. > System.Net.WebException: The request failed with HTTP status 404: .

it means that you have configured the /reportserver/ app to not be reachable via http://localhost/... - there are two options from here:

1) add the localhost hostheader to the web-site which hosts the /reportserver/ and make sure the app is then reachable via that path

2) edit the file C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\ReportManager\RSWebApplication.config and add the full url to the ReportServer into the ReportServerUrl tag (no ending / nessesary) AND remove the value from ReportServerVirtualDirectory (otherwise you get nasty, unprecise exceptions). THEN you must recycle the app-pool used by the Report-Manager so that config file is being reloaded.

THANK YOU VERY MUCH FOR YOUR HELP.

|||

Thank You for the FIX TO: message - just what I needed to implement Reporting services on a secondary web site (with different host header)

Added the complete url and removed the entry from the ReportServerVirtualDirectory and everything worked - glad I found this - wish I would have found it before I reinstalled RS 2005 a couple times..

thanks tilfried for your contribution

John F

|||

Glad I could help :)

When one messes around a couple of hours trying to fix a problem, I think it's worth the extra 5 mins to post the solution!

|||Many thanks for your post, Tilfried Weissenberger. thanks|||

Hello all,

Could some one please explain the security piece of reporting services? What type of IDs do I need, do I need to use SPN accounts if yes how and where would I put/specify this SPN account. Basically I am trying to understand how to setup security when I install reporting services.

Thanks

MA

|||You would have to edit the rsreportserver.config file with the full fqdn of the report server as well.|||

Tilfried:

Thank you! Thank you! If you are ever in southwest Florida, look me up, and I'll buy you a beer! I mean it. And there aren't many Vangors in the phone book down here, so I'll be easy to find. Or google me.

This was the final piece of the puzzle that I needed to get SSRS 2005 working on my Win2K Server with SQL Server 2000.

In my case, I made the following changes to the RSWebApplication.config file:

From: <ReportServerUrl></ReportServerUrl>
To: <ReportServerUrl>http://www.mydomain.com/ReportServer</ReportServerUrl>

From: <ReportServerVirtualDirectory>ReportServer</ReportServerVirtualDirectory>
To: <ReportServerVirtualDirectory></ReportServerVirtualDirectory>

Thanks, again!

Van

Fit Table on one page if possible

has anyone had any luck with the table property option 'Fit Table on one page if possible'. Reporting services seems to ignore this. Anyone familiar with issues surrounding this property? RS seems to break the table at will. I am MOST concerned with this issue as it relates to exporting to .pdf. Any ideas or workarounds would be helpful (we are running sp2).

Thanks!

I usually use table with defined width and page size for A4 paper format.
|||Are you talking about the table's width and height properties? For me, this issue is height as that is what is causing the breaking to the next page. My table width is 7.5, and the height varies, but is usually set for 1.275. This of course doesn't truly reflect the height of the table once it's populated with a bunch of rows!|||I think I'm having the same problem as you. The report is fine except when exported to pdf/tiff where the page breaks occur across the middle of the table details. I enabled this property as suggested elsewhere but it didn't seem to have any effect.|||Have you tried putting the table inside a list an then play with the list properties? I use the keep together property and all my tables are on the same page.
|||I had the same problem before. When I exported the report to PDF, the last several columns were separeted onto next page. After lots of testing, I set the width of the report to 8, and keep the width of the table to 7.5, every field can be kept on the same page.|||

Hi ellenf ,

Habe you solved your "Fit Table" Problem?..I am also having this problem.

Is this a microsoft bug? if it is, is this fixed in RS2005?

Thanks in Advance

|||

No, I never did get this to work. I didn't attempt to put my tables into lists, because this report with about 100 tables is already difficult enough to maintain. I haven't played with 2005 yet, so i do not know if it is resolved.

sorry it's taken so long to answer, I haven't checked my threads in a while!

Fit Table on one page if possible

has anyone had any luck with the table property option 'Fit Table on one page if possible'. Reporting services seems to ignore this. Anyone familiar with issues surrounding this property? RS seems to break the table at will. I am MOST concerned with this issue as it relates to exporting to .pdf. Any ideas or workarounds would be helpful (we are running sp2).

Thanks!

I usually use table with defined width and page size for A4 paper format.|||Are you talking about the table's width and height properties? For me, this issue is height as that is what is causing the breaking to the next page. My table width is 7.5, and the height varies, but is usually set for 1.275. This of course doesn't truly reflect the height of the table once it's populated with a bunch of rows!|||I think I'm having the same problem as you. The report is fine except when exported to pdf/tiff where the page breaks occur across the middle of the table details. I enabled this property as suggested elsewhere but it didn't seem to have any effect.|||Have you tried putting the table inside a list an then play with the

list properties? I use the keep together property and all my tables are

on the same page.|||I had the same problem before. When I exported the report to PDF, the last several columns were separeted onto next page. After lots of testing, I set the width of the report to 8, and keep the width of the table to 7.5, every field can be kept on the same page.|||

Hi ellenf ,

Habe you solved your "Fit Table" Problem?..I am also having this problem.

Is this a microsoft bug? if it is, is this fixed in RS2005?

Thanks in Advance

|||

No, I never did get this to work. I didn't attempt to put my tables into lists, because this report with about 100 tables is already difficult enough to maintain. I haven't played with 2005 yet, so i do not know if it is resolved.

sorry it's taken so long to answer, I haven't checked my threads in a while!

Fit Table on one page if possible

has anyone had any luck with the table property option 'Fit Table on one page if possible'. Reporting services seems to ignore this. Anyone familiar with issues surrounding this property? RS seems to break the table at will. I am MOST concerned with this issue as it relates to exporting to .pdf. Any ideas or workarounds would be helpful (we are running sp2).

Thanks!

I usually use table with defined width and page size for A4 paper format.
|||Are you talking about the table's width and height properties? For me, this issue is height as that is what is causing the breaking to the next page. My table width is 7.5, and the height varies, but is usually set for 1.275. This of course doesn't truly reflect the height of the table once it's populated with a bunch of rows!|||I think I'm having the same problem as you. The report is fine except when exported to pdf/tiff where the page breaks occur across the middle of the table details. I enabled this property as suggested elsewhere but it didn't seem to have any effect.|||Have you tried putting the table inside a list an then play with the list properties? I use the keep together property and all my tables are on the same page.
|||I had the same problem before. When I exported the report to PDF, the last several columns were separeted onto next page. After lots of testing, I set the width of the report to 8, and keep the width of the table to 7.5, every field can be kept on the same page.|||

Hi ellenf ,

Habe you solved your "Fit Table" Problem?..I am also having this problem.

Is this a microsoft bug? if it is, is this fixed in RS2005?

Thanks in Advance

|||

No, I never did get this to work. I didn't attempt to put my tables into lists, because this report with about 100 tables is already difficult enough to maintain. I haven't played with 2005 yet, so i do not know if it is resolved.

sorry it's taken so long to answer, I haven't checked my threads in a while!

First time user

I was told that Reporting Services is included in SQL server 2000 but
I have not been able to locate it if it is. Do I have to load it
separately when I load SQL server?SQL Server Reporting Services is licensed with SQL Server 2000, but it is
separate software. If you have an MSDN subscription it is on the September
2004 DVD (Disc 2432.6, it's a red disc). Otherwise, you will need to contact
the vendor from whom you purchased SQL Server 2000 to obtain the media. You
will also want to download and install Service Pack 1 which is available at:
http://www.microsoft.com/sql/reporting/downloads/default.asp
"George" wrote:
> I was told that Reporting Services is included in SQL server 2000 but
> I have not been able to locate it if it is. Do I have to load it
> separately when I load SQL server?
>|||On 15 Nov 2004 11:41:48 -0800, george.hunter@.conagrafoods.com (George)
wrote:
>I was told that Reporting Services is included in SQL server 2000 but
>I have not been able to locate it if it is. Do I have to load it
>separately when I load SQL server?
George,
You can download a trial version from
http://www.microsoft.com/sql/reporting/downloads/default.asp. (Scroll
down a little)
As far I am aware, the full version is available only on CD or on MSDN
(not sure what levels).
Andrew Watt
MVP - InfoPath|||Dear George and NG,
I am in the same boat you are in. In addition, I could not read this
NewsGroup using Outlook Express and netnews.attbi.com. Perhaps Comcast has
put it somewhere else. I am using IE and the MSDN subscription to reach this
message. The real purpose of this post is to see if this approach to
newsgroups works. I am going to try to set up RS this morning from my MSDN
DVDs. In order to use RS I am going to have to transfer my application
development from MSDE to full blown SQL Server 2000.
Thanks for your question and the replies,
Bob
--
Robert Schuldenfrei
S. I. Inc.
bob@.s-i-inc.com
"George" wrote:
> I was told that Reporting Services is included in SQL server 2000 but
> I have not been able to locate it if it is. Do I have to load it
> separately when I load SQL server?
>

First Time Delay on client systems

Sorry to repost this, but need to in order to hopefully get a response from
Microsoft:
I've just set up a SQL Reporting Services server.
All seems to be working fine, however I've noticed that when a report is
opened from a user system accessing the report server for the first time, the
report takes a very long time to open (a few minutes in some cases), and
sometimes I have to abort it and start again.
This is a report that should take a couple of seconds at most. Once the
report does finally open, it opens fine from then on, even when refreshed, or
run by different parameters. Also all other reports availale to the user also
run fine after that first one finally opens.
There are no errors, just the Report is being generated message. It's like
the server has to get aquainted with the machine accessing it the first
time...
Again, once the report has run, this problem goes away. I have tested this
on around 10 different user systems, with the same results on all of them,
some are Win2k, some are XP. The report server is running on a dual
processor, clean install Server 2003, SQL Server 2000 sp 3a.
So far it appears to be machine, rather than user account, related...
Any assistance would be greatly appreciated. I'm holding off a full on roll
out of this until I get this solved, as it will certainly drive users crazy.
Thanks,
TomTHi Tomt,
Thanks for your posting!
From your descriptions, I understood that you would like to know why there
will be a delay (a few minutes in some cases) when you first time access
Reports. Have I understood you? Correct me if I was wrong.
Based on my konwledge, this is Reporting Services by design behavior. The
first user who runs the report with a unique region code creates a cached
report that contains data for that region. Subsequent users who request a
report using the same region code get the cached copy. create that report
will cost a lot of time.
The report server caches reports based on report execution options.
Execution options determine whether a report is cached and the length of
time it stays in cache. After some number of minutes or at a scheduled
time, the cache is emptied. The cache stays empty until a new report
execution operation occurs and a new copy of the report is cached.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Michael,
I'm not sure what you mean by region code...
What I'm seeing is for example: I open a report on my system, which has
previously accessed the report server and experience no delay. I go to
another system, which has never accessed that server, and run the same
report, and get a delay of up to a few minutes.
Hope that is clear...
Thanks for your help,
Tom
"Michael Cheng [MSFT]" wrote:
> Hi Tomt,
> Thanks for your posting!
> From your descriptions, I understood that you would like to know why there
> will be a delay (a few minutes in some cases) when you first time access
> Reports. Have I understood you? Correct me if I was wrong.
> Based on my konwledge, this is Reporting Services by design behavior. The
> first user who runs the report with a unique region code creates a cached
> report that contains data for that region. Subsequent users who request a
> report using the same region code get the cached copy. create that report
> will cost a lot of time.
> The report server caches reports based on report execution options.
> Execution options determine whether a report is cached and the length of
> time it stays in cache. After some number of minutes or at a scheduled
> time, the cache is emptied. The cache stays empty until a new report
> execution operation occurs and a new copy of the report is cached.
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||I'm sure he meant report query parameters and region code was the specific
one in his head for an example.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"TomT" <tomt@.newsgroup.nospam> wrote in message
news:FC1995F8-D81D-4049-87EC-4B88A4DDB8BB@.microsoft.com...
> Michael,
> I'm not sure what you mean by region code...
> What I'm seeing is for example: I open a report on my system, which has
> previously accessed the report server and experience no delay. I go to
> another system, which has never accessed that server, and run the same
> report, and get a delay of up to a few minutes.
> Hope that is clear...
> Thanks for your help,
> Tom
> "Michael Cheng [MSFT]" wrote:
>> Hi Tomt,
>> Thanks for your posting!
>> From your descriptions, I understood that you would like to know why
>> there
>> will be a delay (a few minutes in some cases) when you first time access
>> Reports. Have I understood you? Correct me if I was wrong.
>> Based on my konwledge, this is Reporting Services by design behavior. The
>> first user who runs the report with a unique region code creates a cached
>> report that contains data for that region. Subsequent users who request a
>> report using the same region code get the cached copy. create that report
>> will cost a lot of time.
>> The report server caches reports based on report execution options.
>> Execution options determine whether a report is cached and the length of
>> time it stays in cache. After some number of minutes or at a scheduled
>> time, the cache is emptied. The cache stays empty until a new report
>> execution operation occurs and a new copy of the report is cached.
>> Thank you for your patience and corporation. If you have any questions or
>> concerns, don't hesitate to let me know. We are always here to be of
>> assistance!
>>
>> Sincerely yours,
>> Michael Cheng
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>|||Ok, thanks. If that is the case, that is not the situation I'm talking about,
it seems to be related to whether or not a system has ever accessed the
server at all, there are no delays on systems that have, regardless of
parameters.
Thanks
"Jeff A. Stucker" wrote:
> I'm sure he meant report query parameters and region code was the specific
> one in his head for an example.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "TomT" <tomt@.newsgroup.nospam> wrote in message
> news:FC1995F8-D81D-4049-87EC-4B88A4DDB8BB@.microsoft.com...
> > Michael,
> >
> > I'm not sure what you mean by region code...
> >
> > What I'm seeing is for example: I open a report on my system, which has
> > previously accessed the report server and experience no delay. I go to
> > another system, which has never accessed that server, and run the same
> > report, and get a delay of up to a few minutes.
> >
> > Hope that is clear...
> >
> > Thanks for your help,
> >
> > Tom
> >
> > "Michael Cheng [MSFT]" wrote:
> >
> >> Hi Tomt,
> >>
> >> Thanks for your posting!
> >>
> >> From your descriptions, I understood that you would like to know why
> >> there
> >> will be a delay (a few minutes in some cases) when you first time access
> >> Reports. Have I understood you? Correct me if I was wrong.
> >>
> >> Based on my konwledge, this is Reporting Services by design behavior. The
> >> first user who runs the report with a unique region code creates a cached
> >> report that contains data for that region. Subsequent users who request a
> >> report using the same region code get the cached copy. create that report
> >> will cost a lot of time.
> >>
> >> The report server caches reports based on report execution options.
> >> Execution options determine whether a report is cached and the length of
> >> time it stays in cache. After some number of minutes or at a scheduled
> >> time, the cache is emptied. The cache stays empty until a new report
> >> execution operation occurs and a new copy of the report is cached.
> >>
> >> Thank you for your patience and corporation. If you have any questions or
> >> concerns, don't hesitate to let me know. We are always here to be of
> >> assistance!
> >>
> >>
> >> Sincerely yours,
> >>
> >> Michael Cheng
> >> Microsoft Online Partner Support
> >>
> >> When responding to posts, please "Reply to Group" via your newsreader so
> >> that others may learn and benefit from your issue.
> >> =====================================================> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
>
>|||Look for an earlier thread on this topic. Bruce and others have invented a
keep-alive type solution that periodically runs a trivial report on schedule
to keep the process from unloading.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"TomT" <tomt@.newsgroup.nospam> wrote in message
news:FACEA9F4-41FE-4C02-AD27-8FB118FB73AB@.microsoft.com...
> Ok, thanks. If that is the case, that is not the situation I'm talking
> about,
> it seems to be related to whether or not a system has ever accessed the
> server at all, there are no delays on systems that have, regardless of
> parameters.
> Thanks
> "Jeff A. Stucker" wrote:
>> I'm sure he meant report query parameters and region code was the
>> specific
>> one in his head for an example.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "TomT" <tomt@.newsgroup.nospam> wrote in message
>> news:FC1995F8-D81D-4049-87EC-4B88A4DDB8BB@.microsoft.com...
>> > Michael,
>> >
>> > I'm not sure what you mean by region code...
>> >
>> > What I'm seeing is for example: I open a report on my system, which has
>> > previously accessed the report server and experience no delay. I go to
>> > another system, which has never accessed that server, and run the same
>> > report, and get a delay of up to a few minutes.
>> >
>> > Hope that is clear...
>> >
>> > Thanks for your help,
>> >
>> > Tom
>> >
>> > "Michael Cheng [MSFT]" wrote:
>> >
>> >> Hi Tomt,
>> >>
>> >> Thanks for your posting!
>> >>
>> >> From your descriptions, I understood that you would like to know why
>> >> there
>> >> will be a delay (a few minutes in some cases) when you first time
>> >> access
>> >> Reports. Have I understood you? Correct me if I was wrong.
>> >>
>> >> Based on my konwledge, this is Reporting Services by design behavior.
>> >> The
>> >> first user who runs the report with a unique region code creates a
>> >> cached
>> >> report that contains data for that region. Subsequent users who
>> >> request a
>> >> report using the same region code get the cached copy. create that
>> >> report
>> >> will cost a lot of time.
>> >>
>> >> The report server caches reports based on report execution options.
>> >> Execution options determine whether a report is cached and the length
>> >> of
>> >> time it stays in cache. After some number of minutes or at a scheduled
>> >> time, the cache is emptied. The cache stays empty until a new report
>> >> execution operation occurs and a new copy of the report is cached.
>> >>
>> >> Thank you for your patience and corporation. If you have any questions
>> >> or
>> >> concerns, don't hesitate to let me know. We are always here to be of
>> >> assistance!
>> >>
>> >>
>> >> Sincerely yours,
>> >>
>> >> Michael Cheng
>> >> Microsoft Online Partner Support
>> >>
>> >> When responding to posts, please "Reply to Group" via your newsreader
>> >> so
>> >> that others may learn and benefit from your issue.
>> >> =====================================================>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>>|||Thanks Jeff, that was my thread, I believe, which I started over because of
profile issues - and to get MS involved.
Unfortunately, that solution is not applicable to the problem I am
describing, apparently not very well...:-)
Here's a clearer scenario (I hope): I run report A on my system, it opens
immediately (my system has run reports previously, not necessarily report A,
however).
I goimmediately to another system , which has never run any reports at all,
and run report A. In many (although not all) cases, minutes will pass before
the report processing is competed. Since the time between running the report
on one system and the other is miniscule, I don't think the process is
unloading - there appears to be something else going on...
"Jeff A. Stucker" wrote:
> Look for an earlier thread on this topic. Bruce and others have invented a
> keep-alive type solution that periodically runs a trivial report on schedule
> to keep the process from unloading.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "TomT" <tomt@.newsgroup.nospam> wrote in message
> news:FACEA9F4-41FE-4C02-AD27-8FB118FB73AB@.microsoft.com...
> > Ok, thanks. If that is the case, that is not the situation I'm talking
> > about,
> > it seems to be related to whether or not a system has ever accessed the
> > server at all, there are no delays on systems that have, regardless of
> > parameters.
> >
> > Thanks
> >
> > "Jeff A. Stucker" wrote:
> >
> >> I'm sure he meant report query parameters and region code was the
> >> specific
> >> one in his head for an example.
> >>
> >> --
> >> Cheers,
> >>
> >> '(' Jeff A. Stucker
> >> \
> >>
> >> Business Intelligence
> >> www.criadvantage.com
> >> ---
> >> "TomT" <tomt@.newsgroup.nospam> wrote in message
> >> news:FC1995F8-D81D-4049-87EC-4B88A4DDB8BB@.microsoft.com...
> >> > Michael,
> >> >
> >> > I'm not sure what you mean by region code...
> >> >
> >> > What I'm seeing is for example: I open a report on my system, which has
> >> > previously accessed the report server and experience no delay. I go to
> >> > another system, which has never accessed that server, and run the same
> >> > report, and get a delay of up to a few minutes.
> >> >
> >> > Hope that is clear...
> >> >
> >> > Thanks for your help,
> >> >
> >> > Tom
> >> >
> >> > "Michael Cheng [MSFT]" wrote:
> >> >
> >> >> Hi Tomt,
> >> >>
> >> >> Thanks for your posting!
> >> >>
> >> >> From your descriptions, I understood that you would like to know why
> >> >> there
> >> >> will be a delay (a few minutes in some cases) when you first time
> >> >> access
> >> >> Reports. Have I understood you? Correct me if I was wrong.
> >> >>
> >> >> Based on my konwledge, this is Reporting Services by design behavior.
> >> >> The
> >> >> first user who runs the report with a unique region code creates a
> >> >> cached
> >> >> report that contains data for that region. Subsequent users who
> >> >> request a
> >> >> report using the same region code get the cached copy. create that
> >> >> report
> >> >> will cost a lot of time.
> >> >>
> >> >> The report server caches reports based on report execution options.
> >> >> Execution options determine whether a report is cached and the length
> >> >> of
> >> >> time it stays in cache. After some number of minutes or at a scheduled
> >> >> time, the cache is emptied. The cache stays empty until a new report
> >> >> execution operation occurs and a new copy of the report is cached.
> >> >>
> >> >> Thank you for your patience and corporation. If you have any questions
> >> >> or
> >> >> concerns, don't hesitate to let me know. We are always here to be of
> >> >> assistance!
> >> >>
> >> >>
> >> >> Sincerely yours,
> >> >>
> >> >> Michael Cheng
> >> >> Microsoft Online Partner Support
> >> >>
> >> >> When responding to posts, please "Reply to Group" via your newsreader
> >> >> so
> >> >> that others may learn and benefit from your issue.
> >> >> =====================================================> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Hi Tom,
What kind of credentials are used against the datasource?
Each IE and IIS do some hand shaking on the first request. It is also
possible that domain authentication or the first connection to the data
source lead to this kind of delay.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Michael,
Thanks for your response. It does appear to be related to the user. I logged
onto a system which had never accessed the report server, and got the report
right away. I then had another person log on to the same machine, and he got
a delay.
I am using a shared data source to access the actual sql server data, so
that is common to everyone, in other words the authentication for the reports
to the sql server data is not specific to individual users, their credentials
are not used.
I wonder if the IIS server (which is the same machine as the Report Server)
is a factor in the delay? People can get to the server without delays, it
just happens when they run their first report...
"Michael Cheng [MSFT]" wrote:
> Hi Tom,
> What kind of credentials are used against the datasource?
> Each IE and IIS do some hand shaking on the first request. It is also
> possible that domain authentication or the first connection to the data
> source lead to this kind of delay.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

First Time Delay on Client Systems

I've just set up a SQL Reporting Services server.
All seems to be working fine, however I've noticed that when a report is
opened from a user system accessing the report server for the first time, the
report takes a very long time to open (a few minutes in some cases), and
sometimes I have to abort it and start again.
This is a report that should take a couple of seconds at most. Once the
report does finally open, it opens fine from then on, even when refreshed, or
run by different parameters. Also all other reports availale to the user also
run fine after that first one finally opens.
There are no errors, just the Report is being generated message. It's like
the server has to get aquainted with the machine accessing it the first
time...
Again, once the report has run, this problem goes away. I have tested this
on around 10 different user systems, with the same results on all of them,
some are Win2k, some are XP. The report server is running on a dual
processor, clean install Server 2003, SQL Server 2000 sp 3a.
Any assistance would be greatly appreciated. I'm holding off a full on roll
out of this until I get this solved, as it will certainly drive users crazy.
Thanks,
TomTIs this the first time *each* user runs the same report, or the first time
*any* user runs the same report?
If it's the first time each user runs the report (which your message seems
to say), it may be some overhead in setting up a user cache (just grasping
at straws here). It would be interesting to run a SQL performance monitor
and some other administrative performance tools when this happens. Perhaps
you could set logging to verbose for a moment.
If it's the first time any user runs the report, then it could be a source
database (back end) issue. Perhaps it's compiling the SQL statement for the
first time, and then the statement is in cache from then on. Or perhaps
it's setting up automatic indexes on the data the first time, and they're
there after that. Or, ...
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"TomT" <tomt@.tomt.com> wrote in message
news:3031983D-75F9-466C-AD3A-3D76F0BEBDC5@.microsoft.com...
> I've just set up a SQL Reporting Services server.
> All seems to be working fine, however I've noticed that when a report is
> opened from a user system accessing the report server for the first time,
> the
> report takes a very long time to open (a few minutes in some cases), and
> sometimes I have to abort it and start again.
> This is a report that should take a couple of seconds at most. Once the
> report does finally open, it opens fine from then on, even when refreshed,
> or
> run by different parameters. Also all other reports availale to the user
> also
> run fine after that first one finally opens.
> There are no errors, just the Report is being generated message. It's like
> the server has to get aquainted with the machine accessing it the first
> time...
> Again, once the report has run, this problem goes away. I have tested this
> on around 10 different user systems, with the same results on all of them,
> some are Win2k, some are XP. The report server is running on a dual
> processor, clean install Server 2003, SQL Server 2000 sp 3a.
> Any assistance would be greatly appreciated. I'm holding off a full on
> roll
> out of this until I get this solved, as it will certainly drive users
> crazy.
> Thanks,
> TomT|||Jeff,
Thanks for your reply. This is when each user runs any report from their
system for the first time. E.g., I could run it from my system and it runs
properly, however if a user who has never accessed the report server before
runs the same report, there is a significant delay. Once it finally opens,
things run properly for that user from then on (at least so far). However,
user B, who has never accessed the server, gets the delay.
A bit disappointing, really...
"Jeff A. Stucker" wrote:
> Is this the first time *each* user runs the same report, or the first time
> *any* user runs the same report?
> If it's the first time each user runs the report (which your message seems
> to say), it may be some overhead in setting up a user cache (just grasping
> at straws here). It would be interesting to run a SQL performance monitor
> and some other administrative performance tools when this happens. Perhaps
> you could set logging to verbose for a moment.
> If it's the first time any user runs the report, then it could be a source
> database (back end) issue. Perhaps it's compiling the SQL statement for the
> first time, and then the statement is in cache from then on. Or perhaps
> it's setting up automatic indexes on the data the first time, and they're
> there after that. Or, ...
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "TomT" <tomt@.tomt.com> wrote in message
> news:3031983D-75F9-466C-AD3A-3D76F0BEBDC5@.microsoft.com...
> > I've just set up a SQL Reporting Services server.
> >
> > All seems to be working fine, however I've noticed that when a report is
> > opened from a user system accessing the report server for the first time,
> > the
> > report takes a very long time to open (a few minutes in some cases), and
> > sometimes I have to abort it and start again.
> >
> > This is a report that should take a couple of seconds at most. Once the
> > report does finally open, it opens fine from then on, even when refreshed,
> > or
> > run by different parameters. Also all other reports availale to the user
> > also
> > run fine after that first one finally opens.
> >
> > There are no errors, just the Report is being generated message. It's like
> > the server has to get aquainted with the machine accessing it the first
> > time...
> >
> > Again, once the report has run, this problem goes away. I have tested this
> > on around 10 different user systems, with the same results on all of them,
> > some are Win2k, some are XP. The report server is running on a dual
> > processor, clean install Server 2003, SQL Server 2000 sp 3a.
> >
> > Any assistance would be greatly appreciated. I'm holding off a full on
> > roll
> > out of this until I get this solved, as it will certainly drive users
> > crazy.
> >
> > Thanks,
> >
> > TomT
>
>|||This is my experience. What I have seen is what matters is whether anyone
has hit the report server in awhile. It is not user or report specific. To
keep things predicatable I have a report open that is a very simple and
quick report that I set to autorefresh once a minute. This then prevents the
delay from happening.
You set autorefresh in the report properties. Try creating a report used
just by yourself to test this out.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"TomT" <tomt@.tomt.com> wrote in message
news:7FAEFC07-F89A-42BC-B095-28AD440314A5@.microsoft.com...
> Jeff,
> Thanks for your reply. This is when each user runs any report from their
> system for the first time. E.g., I could run it from my system and it runs
> properly, however if a user who has never accessed the report server
before
> runs the same report, there is a significant delay. Once it finally opens,
> things run properly for that user from then on (at least so far). However,
> user B, who has never accessed the server, gets the delay.
> A bit disappointing, really...
> "Jeff A. Stucker" wrote:
> > Is this the first time *each* user runs the same report, or the first
time
> > *any* user runs the same report?
> >
> > If it's the first time each user runs the report (which your message
seems
> > to say), it may be some overhead in setting up a user cache (just
grasping
> > at straws here). It would be interesting to run a SQL performance
monitor
> > and some other administrative performance tools when this happens.
Perhaps
> > you could set logging to verbose for a moment.
> >
> > If it's the first time any user runs the report, then it could be a
source
> > database (back end) issue. Perhaps it's compiling the SQL statement for
the
> > first time, and then the statement is in cache from then on. Or perhaps
> > it's setting up automatic indexes on the data the first time, and
they're
> > there after that. Or, ...
> >
> > Cheers,
> >
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "TomT" <tomt@.tomt.com> wrote in message
> > news:3031983D-75F9-466C-AD3A-3D76F0BEBDC5@.microsoft.com...
> > > I've just set up a SQL Reporting Services server.
> > >
> > > All seems to be working fine, however I've noticed that when a report
is
> > > opened from a user system accessing the report server for the first
time,
> > > the
> > > report takes a very long time to open (a few minutes in some cases),
and
> > > sometimes I have to abort it and start again.
> > >
> > > This is a report that should take a couple of seconds at most. Once
the
> > > report does finally open, it opens fine from then on, even when
refreshed,
> > > or
> > > run by different parameters. Also all other reports availale to the
user
> > > also
> > > run fine after that first one finally opens.
> > >
> > > There are no errors, just the Report is being generated message. It's
like
> > > the server has to get aquainted with the machine accessing it the
first
> > > time...
> > >
> > > Again, once the report has run, this problem goes away. I have tested
this
> > > on around 10 different user systems, with the same results on all of
them,
> > > some are Win2k, some are XP. The report server is running on a dual
> > > processor, clean install Server 2003, SQL Server 2000 sp 3a.
> > >
> > > Any assistance would be greatly appreciated. I'm holding off a full on
> > > roll
> > > out of this until I get this solved, as it will certainly drive users
> > > crazy.
> > >
> > > Thanks,
> > >
> > > TomT
> >
> >
> >|||Ditto. I noticed it occurring more on the very first time RS was accessed
or after sufficient 'idle' time. I hadn't got around to working around the
issue just yet, but your work around seems good. In the back of my mind I
was thinking of writing a simple .rss script that does something like
ListChildren at the root folder and putting it on a schedule (once every n
minutes or so). My thinking was to just make a web service call and force
RS to re-cache whatever it had expired from its cached information.
Of course, I don't know if RS is expiring cached info or what...it would be
nice if MS would jump in an let us know exactly was happening here...
--
Adrian M.
MCP
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:uoFR3HdFFHA.3272@.TK2MSFTNGP10.phx.gbl...
> This is my experience. What I have seen is what matters is whether anyone
> has hit the report server in awhile. It is not user or report specific. To
> keep things predicatable I have a report open that is a very simple and
> quick report that I set to autorefresh once a minute. This then prevents
> the
> delay from happening.
> You set autorefresh in the report properties. Try creating a report used
> just by yourself to test this out.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "TomT" <tomt@.tomt.com> wrote in message
> news:7FAEFC07-F89A-42BC-B095-28AD440314A5@.microsoft.com...
>> Jeff,
>> Thanks for your reply. This is when each user runs any report from their
>> system for the first time. E.g., I could run it from my system and it
>> runs
>> properly, however if a user who has never accessed the report server
> before
>> runs the same report, there is a significant delay. Once it finally
>> opens,
>> things run properly for that user from then on (at least so far).
>> However,
>> user B, who has never accessed the server, gets the delay.
>> A bit disappointing, really...
>> "Jeff A. Stucker" wrote:
>> > Is this the first time *each* user runs the same report, or the first
> time
>> > *any* user runs the same report?
>> >
>> > If it's the first time each user runs the report (which your message
> seems
>> > to say), it may be some overhead in setting up a user cache (just
> grasping
>> > at straws here). It would be interesting to run a SQL performance
> monitor
>> > and some other administrative performance tools when this happens.
> Perhaps
>> > you could set logging to verbose for a moment.
>> >
>> > If it's the first time any user runs the report, then it could be a
> source
>> > database (back end) issue. Perhaps it's compiling the SQL statement
>> > for
> the
>> > first time, and then the statement is in cache from then on. Or
>> > perhaps
>> > it's setting up automatic indexes on the data the first time, and
> they're
>> > there after that. Or, ...
>> >
>> > Cheers,
>> >
>> > '(' Jeff A. Stucker
>> > \
>> >
>> > Business Intelligence
>> > www.criadvantage.com
>> > ---
>> > "TomT" <tomt@.tomt.com> wrote in message
>> > news:3031983D-75F9-466C-AD3A-3D76F0BEBDC5@.microsoft.com...
>> > > I've just set up a SQL Reporting Services server.
>> > >
>> > > All seems to be working fine, however I've noticed that when a report
> is
>> > > opened from a user system accessing the report server for the first
> time,
>> > > the
>> > > report takes a very long time to open (a few minutes in some cases),
> and
>> > > sometimes I have to abort it and start again.
>> > >
>> > > This is a report that should take a couple of seconds at most. Once
> the
>> > > report does finally open, it opens fine from then on, even when
> refreshed,
>> > > or
>> > > run by different parameters. Also all other reports availale to the
> user
>> > > also
>> > > run fine after that first one finally opens.
>> > >
>> > > There are no errors, just the Report is being generated message. It's
> like
>> > > the server has to get aquainted with the machine accessing it the
> first
>> > > time...
>> > >
>> > > Again, once the report has run, this problem goes away. I have tested
> this
>> > > on around 10 different user systems, with the same results on all of
> them,
>> > > some are Win2k, some are XP. The report server is running on a dual
>> > > processor, clean install Server 2003, SQL Server 2000 sp 3a.
>> > >
>> > > Any assistance would be greatly appreciated. I'm holding off a full
>> > > on
>> > > roll
>> > > out of this until I get this solved, as it will certainly drive users
>> > > crazy.
>> > >
>> > > Thanks,
>> > >
>> > > TomT
>> >
>> >
>> >
>|||If you are running Windows 2003 server for your IIS reportserver, then this
is a simple issue - I'll explain what happens:
The report service engine, once it is idle for more than the default 20
minutes, the worker process is shutdown.
This is controlled by IIS.
Open up the Internet Information Services (IIS) Manager
Expand the server node then the application pools.
On my IIS machine, I created an application pool dedicated to the
reportserver & reportmanager virtual webs.
But anyways, for the application pool that the reportserver is pointing to
if you left everything to their defaults will be the DefaultAppPool.
Right click the default app pool and select properties.
There are two things that are checked by default - On the recycling tab
there is a checkbox for recycling worker processes - it is currently set to
1740 minutes (29 hours). Leave it.
The other one is on the performance tab - which is the one you are
interested in changing...
See the "Idle Timeout" section and increase the number of minutes to be 8
hours a typical working day - 8*60 = 480 minutes.
Next, to be sure the "morning person" that runs the first report doesn't get
the delay, set up a schedule for either a dummy or adhoc report to fire off
like at 6am so that the report component worker processes get loaded.
I hope this helps you.
There is no need to have a report fire off every minute to keep things
alive - it is just that the report service was "unloaded" and needed to load
back up.
=-Chris
"TomT" <tomt@.tomt.com> wrote in message
news:3031983D-75F9-466C-AD3A-3D76F0BEBDC5@.microsoft.com...
> I've just set up a SQL Reporting Services server.
> All seems to be working fine, however I've noticed that when a report is
> opened from a user system accessing the report server for the first time,
> the
> report takes a very long time to open (a few minutes in some cases), and
> sometimes I have to abort it and start again.
> This is a report that should take a couple of seconds at most. Once the
> report does finally open, it opens fine from then on, even when refreshed,
> or
> run by different parameters. Also all other reports availale to the user
> also
> run fine after that first one finally opens.
> There are no errors, just the Report is being generated message. It's like
> the server has to get aquainted with the machine accessing it the first
> time...
> Again, once the report has run, this problem goes away. I have tested this
> on around 10 different user systems, with the same results on all of them,
> some are Win2k, some are XP. The report server is running on a dual
> processor, clean install Server 2003, SQL Server 2000 sp 3a.
> Any assistance would be greatly appreciated. I'm holding off a full on
> roll
> out of this until I get this solved, as it will certainly drive users
> crazy.
> Thanks,
> TomT|||Very good. I figured something like that was happening. Of course I can also
just set the report to be refreshed every 15 minutes instead of every
minute.
Thanks for the info. Your solution is the correct way. Mine is a hack but
works.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Christopher Conner" <someone@.someplace.com> wrote in message
news:e9GOK2dFFHA.1084@.tk2msftngp13.phx.gbl...
> If you are running Windows 2003 server for your IIS reportserver, then
this
> is a simple issue - I'll explain what happens:
> The report service engine, once it is idle for more than the default 20
> minutes, the worker process is shutdown.
> This is controlled by IIS.
> Open up the Internet Information Services (IIS) Manager
> Expand the server node then the application pools.
> On my IIS machine, I created an application pool dedicated to the
> reportserver & reportmanager virtual webs.
> But anyways, for the application pool that the reportserver is pointing to
> if you left everything to their defaults will be the DefaultAppPool.
> Right click the default app pool and select properties.
> There are two things that are checked by default - On the recycling tab
> there is a checkbox for recycling worker processes - it is currently set
to
> 1740 minutes (29 hours). Leave it.
> The other one is on the performance tab - which is the one you are
> interested in changing...
> See the "Idle Timeout" section and increase the number of minutes to be 8
> hours a typical working day - 8*60 = 480 minutes.
> Next, to be sure the "morning person" that runs the first report doesn't
get
> the delay, set up a schedule for either a dummy or adhoc report to fire
off
> like at 6am so that the report component worker processes get loaded.
> I hope this helps you.
> There is no need to have a report fire off every minute to keep things
> alive - it is just that the report service was "unloaded" and needed to
load
> back up.
> =-Chris
>
>
>
> "TomT" <tomt@.tomt.com> wrote in message
> news:3031983D-75F9-466C-AD3A-3D76F0BEBDC5@.microsoft.com...
> > I've just set up a SQL Reporting Services server.
> >
> > All seems to be working fine, however I've noticed that when a report is
> > opened from a user system accessing the report server for the first
time,
> > the
> > report takes a very long time to open (a few minutes in some cases), and
> > sometimes I have to abort it and start again.
> >
> > This is a report that should take a couple of seconds at most. Once the
> > report does finally open, it opens fine from then on, even when
refreshed,
> > or
> > run by different parameters. Also all other reports availale to the user
> > also
> > run fine after that first one finally opens.
> >
> > There are no errors, just the Report is being generated message. It's
like
> > the server has to get aquainted with the machine accessing it the first
> > time...
> >
> > Again, once the report has run, this problem goes away. I have tested
this
> > on around 10 different user systems, with the same results on all of
them,
> > some are Win2k, some are XP. The report server is running on a dual
> > processor, clean install Server 2003, SQL Server 2000 sp 3a.
> >
> > Any assistance would be greatly appreciated. I'm holding off a full on
> > roll
> > out of this until I get this solved, as it will certainly drive users
> > crazy.
> >
> > Thanks,
> >
> > TomT
>|||thx Christopher...
--
Adrian M.
MCP
"Christopher Conner" <someone@.someplace.com> wrote in message
news:e9GOK2dFFHA.1084@.tk2msftngp13.phx.gbl...
> If you are running Windows 2003 server for your IIS reportserver, then
> this is a simple issue - I'll explain what happens:
> The report service engine, once it is idle for more than the default 20
> minutes, the worker process is shutdown.
> This is controlled by IIS.
> Open up the Internet Information Services (IIS) Manager
> Expand the server node then the application pools.
> On my IIS machine, I created an application pool dedicated to the
> reportserver & reportmanager virtual webs.
> But anyways, for the application pool that the reportserver is pointing to
> if you left everything to their defaults will be the DefaultAppPool.
> Right click the default app pool and select properties.
> There are two things that are checked by default - On the recycling tab
> there is a checkbox for recycling worker processes - it is currently set
> to 1740 minutes (29 hours). Leave it.
> The other one is on the performance tab - which is the one you are
> interested in changing...
> See the "Idle Timeout" section and increase the number of minutes to be 8
> hours a typical working day - 8*60 = 480 minutes.
> Next, to be sure the "morning person" that runs the first report doesn't
> get the delay, set up a schedule for either a dummy or adhoc report to
> fire off like at 6am so that the report component worker processes get
> loaded.
> I hope this helps you.
> There is no need to have a report fire off every minute to keep things
> alive - it is just that the report service was "unloaded" and needed to
> load back up.
> =-Chris
>
>
>
> "TomT" <tomt@.tomt.com> wrote in message
> news:3031983D-75F9-466C-AD3A-3D76F0BEBDC5@.microsoft.com...
>> I've just set up a SQL Reporting Services server.
>> All seems to be working fine, however I've noticed that when a report is
>> opened from a user system accessing the report server for the first time,
>> the
>> report takes a very long time to open (a few minutes in some cases), and
>> sometimes I have to abort it and start again.
>> This is a report that should take a couple of seconds at most. Once the
>> report does finally open, it opens fine from then on, even when
>> refreshed, or
>> run by different parameters. Also all other reports availale to the user
>> also
>> run fine after that first one finally opens.
>> There are no errors, just the Report is being generated message. It's
>> like
>> the server has to get aquainted with the machine accessing it the first
>> time...
>> Again, once the report has run, this problem goes away. I have tested
>> this
>> on around 10 different user systems, with the same results on all of
>> them,
>> some are Win2k, some are XP. The report server is running on a dual
>> processor, clean install Server 2003, SQL Server 2000 sp 3a.
>> Any assistance would be greatly appreciated. I'm holding off a full on
>> roll
>> out of this until I get this solved, as it will certainly drive users
>> crazy.
>> Thanks,
>> TomT
>|||Christopher,
Thanks for your help on this. I wonder though, if this is what is going on
in my case. E.g., if I open the report from my system, it runs normally.
Then, if I were to go to a system that had never accessed the report server
before, (right after I had opened it on my system), there is a delay, with
the Report Processing message lasting for up to several minutes.
Since the report had just run on my system, unless I'm missing something, I
don't see how what you described causes the delay on the other system.
Any ideas?
Thanks again,
Tom
"Christopher Conner" wrote:
> If you are running Windows 2003 server for your IIS reportserver, then this
> is a simple issue - I'll explain what happens:
> The report service engine, once it is idle for more than the default 20
> minutes, the worker process is shutdown.
> This is controlled by IIS.
> Open up the Internet Information Services (IIS) Manager
> Expand the server node then the application pools.
> On my IIS machine, I created an application pool dedicated to the
> reportserver & reportmanager virtual webs.
> But anyways, for the application pool that the reportserver is pointing to
> if you left everything to their defaults will be the DefaultAppPool.
> Right click the default app pool and select properties.
> There are two things that are checked by default - On the recycling tab
> there is a checkbox for recycling worker processes - it is currently set to
> 1740 minutes (29 hours). Leave it.
> The other one is on the performance tab - which is the one you are
> interested in changing...
> See the "Idle Timeout" section and increase the number of minutes to be 8
> hours a typical working day - 8*60 = 480 minutes.
> Next, to be sure the "morning person" that runs the first report doesn't get
> the delay, set up a schedule for either a dummy or adhoc report to fire off
> like at 6am so that the report component worker processes get loaded.
> I hope this helps you.
> There is no need to have a report fire off every minute to keep things
> alive - it is just that the report service was "unloaded" and needed to load
> back up.
> =-Chris
>
>
>
> "TomT" <tomt@.tomt.com> wrote in message
> news:3031983D-75F9-466C-AD3A-3D76F0BEBDC5@.microsoft.com...
> > I've just set up a SQL Reporting Services server.
> >
> > All seems to be working fine, however I've noticed that when a report is
> > opened from a user system accessing the report server for the first time,
> > the
> > report takes a very long time to open (a few minutes in some cases), and
> > sometimes I have to abort it and start again.
> >
> > This is a report that should take a couple of seconds at most. Once the
> > report does finally open, it opens fine from then on, even when refreshed,
> > or
> > run by different parameters. Also all other reports availale to the user
> > also
> > run fine after that first one finally opens.
> >
> > There are no errors, just the Report is being generated message. It's like
> > the server has to get aquainted with the machine accessing it the first
> > time...
> >
> > Again, once the report has run, this problem goes away. I have tested this
> > on around 10 different user systems, with the same results on all of them,
> > some are Win2k, some are XP. The report server is running on a dual
> > processor, clean install Server 2003, SQL Server 2000 sp 3a.
> >
> > Any assistance would be greatly appreciated. I'm holding off a full on
> > roll
> > out of this until I get this solved, as it will certainly drive users
> > crazy.
> >
> > Thanks,
> >
> > TomT
>
>|||Bruce,
My case is not whether the server has been hit recently or not, it's whether
a particular client has hit it. I.e., if I open a report from my system, it's
fine. If I immediately go to a system which has never hit the server before,
it takes quite awhile for the same report, or any other for that matter, to
open...
Thanks for your help
"Bruce L-C [MVP]" wrote:
> This is my experience. What I have seen is what matters is whether anyone
> has hit the report server in awhile. It is not user or report specific. To
> keep things predicatable I have a report open that is a very simple and
> quick report that I set to autorefresh once a minute. This then prevents the
> delay from happening.
> You set autorefresh in the report properties. Try creating a report used
> just by yourself to test this out.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "TomT" <tomt@.tomt.com> wrote in message
> news:7FAEFC07-F89A-42BC-B095-28AD440314A5@.microsoft.com...
> > Jeff,
> >
> > Thanks for your reply. This is when each user runs any report from their
> > system for the first time. E.g., I could run it from my system and it runs
> > properly, however if a user who has never accessed the report server
> before
> > runs the same report, there is a significant delay. Once it finally opens,
> > things run properly for that user from then on (at least so far). However,
> > user B, who has never accessed the server, gets the delay.
> >
> > A bit disappointing, really...
> >
> > "Jeff A. Stucker" wrote:
> >
> > > Is this the first time *each* user runs the same report, or the first
> time
> > > *any* user runs the same report?
> > >
> > > If it's the first time each user runs the report (which your message
> seems
> > > to say), it may be some overhead in setting up a user cache (just
> grasping
> > > at straws here). It would be interesting to run a SQL performance
> monitor
> > > and some other administrative performance tools when this happens.
> Perhaps
> > > you could set logging to verbose for a moment.
> > >
> > > If it's the first time any user runs the report, then it could be a
> source
> > > database (back end) issue. Perhaps it's compiling the SQL statement for
> the
> > > first time, and then the statement is in cache from then on. Or perhaps
> > > it's setting up automatic indexes on the data the first time, and
> they're
> > > there after that. Or, ...
> > >
> > > Cheers,
> > >
> > > '(' Jeff A. Stucker
> > > \
> > >
> > > Business Intelligence
> > > www.criadvantage.com
> > > ---
> > > "TomT" <tomt@.tomt.com> wrote in message
> > > news:3031983D-75F9-466C-AD3A-3D76F0BEBDC5@.microsoft.com...
> > > > I've just set up a SQL Reporting Services server.
> > > >
> > > > All seems to be working fine, however I've noticed that when a report
> is
> > > > opened from a user system accessing the report server for the first
> time,
> > > > the
> > > > report takes a very long time to open (a few minutes in some cases),
> and
> > > > sometimes I have to abort it and start again.
> > > >
> > > > This is a report that should take a couple of seconds at most. Once
> the
> > > > report does finally open, it opens fine from then on, even when
> refreshed,
> > > > or
> > > > run by different parameters. Also all other reports availale to the
> user
> > > > also
> > > > run fine after that first one finally opens.
> > > >
> > > > There are no errors, just the Report is being generated message. It's
> like
> > > > the server has to get aquainted with the machine accessing it the
> first
> > > > time...
> > > >
> > > > Again, once the report has run, this problem goes away. I have tested
> this
> > > > on around 10 different user systems, with the same results on all of
> them,
> > > > some are Win2k, some are XP. The report server is running on a dual
> > > > processor, clean install Server 2003, SQL Server 2000 sp 3a.
> > > >
> > > > Any assistance would be greatly appreciated. I'm holding off a full on
> > > > roll
> > > > out of this until I get this solved, as it will certainly drive users
> > > > crazy.
> > > >
> > > > Thanks,
> > > >
> > > > TomT
> > >
> > >
> > >
>
>|||Anyone from Microsoft care to comment on this?
"TomT" wrote:
> Christopher,
> Thanks for your help on this. I wonder though, if this is what is going on
> in my case. E.g., if I open the report from my system, it runs normally.
> Then, if I were to go to a system that had never accessed the report server
> before, (right after I had opened it on my system), there is a delay, with
> the Report Processing message lasting for up to several minutes.
> Since the report had just run on my system, unless I'm missing something, I
> don't see how what you described causes the delay on the other system.
> Any ideas?
> Thanks again,
> Tom
> "Christopher Conner" wrote:
> > If you are running Windows 2003 server for your IIS reportserver, then this
> > is a simple issue - I'll explain what happens:
> >
> > The report service engine, once it is idle for more than the default 20
> > minutes, the worker process is shutdown.
> >
> > This is controlled by IIS.
> >
> > Open up the Internet Information Services (IIS) Manager
> > Expand the server node then the application pools.
> > On my IIS machine, I created an application pool dedicated to the
> > reportserver & reportmanager virtual webs.
> > But anyways, for the application pool that the reportserver is pointing to
> > if you left everything to their defaults will be the DefaultAppPool.
> > Right click the default app pool and select properties.
> > There are two things that are checked by default - On the recycling tab
> > there is a checkbox for recycling worker processes - it is currently set to
> > 1740 minutes (29 hours). Leave it.
> >
> > The other one is on the performance tab - which is the one you are
> > interested in changing...
> > See the "Idle Timeout" section and increase the number of minutes to be 8
> > hours a typical working day - 8*60 = 480 minutes.
> >
> > Next, to be sure the "morning person" that runs the first report doesn't get
> > the delay, set up a schedule for either a dummy or adhoc report to fire off
> > like at 6am so that the report component worker processes get loaded.
> >
> > I hope this helps you.
> >
> > There is no need to have a report fire off every minute to keep things
> > alive - it is just that the report service was "unloaded" and needed to load
> > back up.
> >
> > =-Chris
> >
> >
> >
> >
> >
> >
> >
> > "TomT" <tomt@.tomt.com> wrote in message
> > news:3031983D-75F9-466C-AD3A-3D76F0BEBDC5@.microsoft.com...
> > > I've just set up a SQL Reporting Services server.
> > >
> > > All seems to be working fine, however I've noticed that when a report is
> > > opened from a user system accessing the report server for the first time,
> > > the
> > > report takes a very long time to open (a few minutes in some cases), and
> > > sometimes I have to abort it and start again.
> > >
> > > This is a report that should take a couple of seconds at most. Once the
> > > report does finally open, it opens fine from then on, even when refreshed,
> > > or
> > > run by different parameters. Also all other reports availale to the user
> > > also
> > > run fine after that first one finally opens.
> > >
> > > There are no errors, just the Report is being generated message. It's like
> > > the server has to get aquainted with the machine accessing it the first
> > > time...
> > >
> > > Again, once the report has run, this problem goes away. I have tested this
> > > on around 10 different user systems, with the same results on all of them,
> > > some are Win2k, some are XP. The report server is running on a dual
> > > processor, clean install Server 2003, SQL Server 2000 sp 3a.
> > >
> > > Any assistance would be greatly appreciated. I'm holding off a full on
> > > roll
> > > out of this until I get this solved, as it will certainly drive users
> > > crazy.
> > >
> > > Thanks,
> > >
> > > TomT
> >
> >
> >|||Actually, this is a different issue - remember, the report manager uses NT
authentication - so it has to validate the user before allowing them access
to those resources - in this case - the report manager so you can view the
reports.
I have some customers who have domain controllers that take forever (i.e. a
few minutes "delay" on logon) when the user signs onto the domain.
On the web server that is running your report manager, find out what domain
controller is authenicating the users - you can do this by going to the
command prompt and typing "Set logonserver" and it will tell you the name of
the DC that is authenticating your users for the resources on the machine.
I use the "divide & conquer" method to troubleshooting. Lets try to
eliminate what the cause is. I would recommend, ONLY FOR A TEST, that you
access the report anonymously (allow anonymous) and bypass the
authentication. Of course, I do not know if this is possible. But at least
this would tell you whether the slow down is based on getting users
authenticated with the resources, or a different issue entirely.
I wish I could provide more information.
=-Chris
"TomT" <tomt@.tomt.com> wrote in message
news:EF3E6243-43D8-42A0-A027-84C58B638553@.microsoft.com...
> Christopher,
> Thanks for your help on this. I wonder though, if this is what is going on
> in my case. E.g., if I open the report from my system, it runs normally.
> Then, if I were to go to a system that had never accessed the report
> server
> before, (right after I had opened it on my system), there is a delay, with
> the Report Processing message lasting for up to several minutes.
> Since the report had just run on my system, unless I'm missing something,
> I
> don't see how what you described causes the delay on the other system.
> Any ideas?
> Thanks again,
> Tom
> "Christopher Conner" wrote:
>> If you are running Windows 2003 server for your IIS reportserver, then
>> this
>> is a simple issue - I'll explain what happens:
>> The report service engine, once it is idle for more than the default 20
>> minutes, the worker process is shutdown.
>> This is controlled by IIS.
>> Open up the Internet Information Services (IIS) Manager
>> Expand the server node then the application pools.
>> On my IIS machine, I created an application pool dedicated to the
>> reportserver & reportmanager virtual webs.
>> But anyways, for the application pool that the reportserver is pointing
>> to
>> if you left everything to their defaults will be the DefaultAppPool.
>> Right click the default app pool and select properties.
>> There are two things that are checked by default - On the recycling tab
>> there is a checkbox for recycling worker processes - it is currently set
>> to
>> 1740 minutes (29 hours). Leave it.
>> The other one is on the performance tab - which is the one you are
>> interested in changing...
>> See the "Idle Timeout" section and increase the number of minutes to be 8
>> hours a typical working day - 8*60 = 480 minutes.
>> Next, to be sure the "morning person" that runs the first report doesn't
>> get
>> the delay, set up a schedule for either a dummy or adhoc report to fire
>> off
>> like at 6am so that the report component worker processes get loaded.
>> I hope this helps you.
>> There is no need to have a report fire off every minute to keep things
>> alive - it is just that the report service was "unloaded" and needed to
>> load
>> back up.
>> =-Chris
>>
>>
>>
>> "TomT" <tomt@.tomt.com> wrote in message
>> news:3031983D-75F9-466C-AD3A-3D76F0BEBDC5@.microsoft.com...
>> > I've just set up a SQL Reporting Services server.
>> >
>> > All seems to be working fine, however I've noticed that when a report
>> > is
>> > opened from a user system accessing the report server for the first
>> > time,
>> > the
>> > report takes a very long time to open (a few minutes in some cases),
>> > and
>> > sometimes I have to abort it and start again.
>> >
>> > This is a report that should take a couple of seconds at most. Once the
>> > report does finally open, it opens fine from then on, even when
>> > refreshed,
>> > or
>> > run by different parameters. Also all other reports availale to the
>> > user
>> > also
>> > run fine after that first one finally opens.
>> >
>> > There are no errors, just the Report is being generated message. It's
>> > like
>> > the server has to get aquainted with the machine accessing it the first
>> > time...
>> >
>> > Again, once the report has run, this problem goes away. I have tested
>> > this
>> > on around 10 different user systems, with the same results on all of
>> > them,
>> > some are Win2k, some are XP. The report server is running on a dual
>> > processor, clean install Server 2003, SQL Server 2000 sp 3a.
>> >
>> > Any assistance would be greatly appreciated. I'm holding off a full on
>> > roll
>> > out of this until I get this solved, as it will certainly drive users
>> > crazy.
>> >
>> > Thanks,
>> >
>> > TomT
>>|||Thanks Chris, I'll see if I can test that. According to our Domain guy tho,
we have a fast login validation. Also, so far it seems to be more machine
specific, as opposed to user accounts...
"Christopher Conner" wrote:
> Actually, this is a different issue - remember, the report manager uses NT
> authentication - so it has to validate the user before allowing them access
> to those resources - in this case - the report manager so you can view the
> reports.
> I have some customers who have domain controllers that take forever (i.e. a
> few minutes "delay" on logon) when the user signs onto the domain.
> On the web server that is running your report manager, find out what domain
> controller is authenicating the users - you can do this by going to the
> command prompt and typing "Set logonserver" and it will tell you the name of
> the DC that is authenticating your users for the resources on the machine.
> I use the "divide & conquer" method to troubleshooting. Lets try to
> eliminate what the cause is. I would recommend, ONLY FOR A TEST, that you
> access the report anonymously (allow anonymous) and bypass the
> authentication. Of course, I do not know if this is possible. But at least
> this would tell you whether the slow down is based on getting users
> authenticated with the resources, or a different issue entirely.
> I wish I could provide more information.
> =-Chris
>
> "TomT" <tomt@.tomt.com> wrote in message
> news:EF3E6243-43D8-42A0-A027-84C58B638553@.microsoft.com...
> > Christopher,
> >
> > Thanks for your help on this. I wonder though, if this is what is going on
> > in my case. E.g., if I open the report from my system, it runs normally.
> > Then, if I were to go to a system that had never accessed the report
> > server
> > before, (right after I had opened it on my system), there is a delay, with
> > the Report Processing message lasting for up to several minutes.
> >
> > Since the report had just run on my system, unless I'm missing something,
> > I
> > don't see how what you described causes the delay on the other system.
> >
> > Any ideas?
> >
> > Thanks again,
> >
> > Tom
> >
> > "Christopher Conner" wrote:
> >
> >> If you are running Windows 2003 server for your IIS reportserver, then
> >> this
> >> is a simple issue - I'll explain what happens:
> >>
> >> The report service engine, once it is idle for more than the default 20
> >> minutes, the worker process is shutdown.
> >>
> >> This is controlled by IIS.
> >>
> >> Open up the Internet Information Services (IIS) Manager
> >> Expand the server node then the application pools.
> >> On my IIS machine, I created an application pool dedicated to the
> >> reportserver & reportmanager virtual webs.
> >> But anyways, for the application pool that the reportserver is pointing
> >> to
> >> if you left everything to their defaults will be the DefaultAppPool.
> >> Right click the default app pool and select properties.
> >> There are two things that are checked by default - On the recycling tab
> >> there is a checkbox for recycling worker processes - it is currently set
> >> to
> >> 1740 minutes (29 hours). Leave it.
> >>
> >> The other one is on the performance tab - which is the one you are
> >> interested in changing...
> >> See the "Idle Timeout" section and increase the number of minutes to be 8
> >> hours a typical working day - 8*60 = 480 minutes.
> >>
> >> Next, to be sure the "morning person" that runs the first report doesn't
> >> get
> >> the delay, set up a schedule for either a dummy or adhoc report to fire
> >> off
> >> like at 6am so that the report component worker processes get loaded.
> >>
> >> I hope this helps you.
> >>
> >> There is no need to have a report fire off every minute to keep things
> >> alive - it is just that the report service was "unloaded" and needed to
> >> load
> >> back up.
> >>
> >> =-Chris
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> "TomT" <tomt@.tomt.com> wrote in message
> >> news:3031983D-75F9-466C-AD3A-3D76F0BEBDC5@.microsoft.com...
> >> > I've just set up a SQL Reporting Services server.
> >> >
> >> > All seems to be working fine, however I've noticed that when a report
> >> > is
> >> > opened from a user system accessing the report server for the first
> >> > time,
> >> > the
> >> > report takes a very long time to open (a few minutes in some cases),
> >> > and
> >> > sometimes I have to abort it and start again.
> >> >
> >> > This is a report that should take a couple of seconds at most. Once the
> >> > report does finally open, it opens fine from then on, even when
> >> > refreshed,
> >> > or
> >> > run by different parameters. Also all other reports availale to the
> >> > user
> >> > also
> >> > run fine after that first one finally opens.
> >> >
> >> > There are no errors, just the Report is being generated message. It's
> >> > like
> >> > the server has to get aquainted with the machine accessing it the first
> >> > time...
> >> >
> >> > Again, once the report has run, this problem goes away. I have tested
> >> > this
> >> > on around 10 different user systems, with the same results on all of
> >> > them,
> >> > some are Win2k, some are XP. The report server is running on a dual
> >> > processor, clean install Server 2003, SQL Server 2000 sp 3a.
> >> >
> >> > Any assistance would be greatly appreciated. I'm holding off a full on
> >> > roll
> >> > out of this until I get this solved, as it will certainly drive users
> >> > crazy.
> >> >
> >> > Thanks,
> >> >
> >> > TomT
> >>
> >>
> >>
>
>