Reporting services 2005 crashes

Hi All ,
I installed SQL server 2005 Enterprise edition 64 bit on windows server 2003
enterprise edition 64 bit machine.
I created a project and using a wizard created a report with Analysis
services cube as a datasource.Preview works fine but when I try to fix the
fonts or add elements and try to save it it crashes.How ever with out saving
I build the report or use the debug it works fine for the first time the next
time all the columns are deleted from the dataset.
I reproduced this problem thrice using diffrent prifile logons and creating
the project in different drives.
Thank you .
Rose.Applied Service pack noweverything seems good.
"Rose" wrote:
> Hi All ,
> I installed SQL server 2005 Enterprise edition 64 bit on windows server 2003
> enterprise edition 64 bit machine.
> I created a project and using a wizard created a report with Analysis
> services cube as a datasource.Preview works fine but when I try to fix the
> fonts or add elements and try to save it it crashes.How ever with out saving
> I build the report or use the debug it works fine for the first time the next
> time all the columns are deleted from the dataset.
> I reproduced this problem thrice using diffrent prifile logons and creating
> the project in different drives.
>
> Thank you .
> Rose.

Reporting Services 2005 BestPractises Question

Hi,

We're starting to migrate from Crystal Enterprise to SSRS2005, and had some performance-related questions in relation to complex reports running against large tables.

Scenario:

. We have fairly large source tables, some greater than 5 million rows

. We use fairly complex stored procs which use dynamic sql based on about 10-15 search criterias entered on web page

. Currently our web middle tier calls a stored proc which inserts into a report table. Then UI calls the report which has SQL query to filter the report table on the UserId and shows only the rows for that user.

Question:

. Is this method - running stored proc in app which inserts into table, then asking report to filter the table based on UserID - considered the best way for designing and running complex SSRS2005 reports with lots of data?

(or)

. Is it better to directly run the stored proc from the report and return the resultset directly to the report?

Advantages or disadvantages of either method, useful articles, best practises...all are appreciated.

Thanks,

JGP

In general, only feed the data you want to present into the report. It's much faster to filter data out on the back end than it is to package it up (even if it's not going across the wire) and send it to Report Server.

You can replace the default parameter handling (by using the WebForms control or Url Access) if you don't like the way our parameters are shown (with 10-15 search criteria, this is probably a good idea).

Why not have the report itself call your stored procedure directly w/o the need for the intermediate table? If the number of rows returned is reasonable (ie, criterial + userid is passed to the stored proc) then this should be fine. How many rows per criteria/userid will be returned? How many KB? Query Analyzer will tell you this.

I would make sure to test whatever choices you make. Don't take anyone elses word for how your machines will perform. Use Microsoft ACT or Visual Studio 2005 and validate all assumptions. Please.

Thanks, Donovan.

|||

I must have a misunderstanding on this. I thought it was better to package it up and send it over to RS and use filters so that RS could cache the result set. Do I have a disconnect here?

R

|||

Sending 5m rows across the wire is slow. Filtering/aggregating in SQL is much faster than the overhead of serializing/deserializing and hitting the wire with a bunch of data a particular rendering isn't going to use. Live reports work much better if the only data RS has to deal with is the data it actualy displays. This isn't just true of RS but any application that deals with a database backend.

Of course, I'm generalizing and simplifying and the real answer is more of an "it depends on your report and your usage". But hopefully this explains my comment better.

Thanks, Donovan.

|||

I think the value in cache is negated by the potentially large recordsets that RS must sift through for grouping/sorting. Use procs for everything, we use temp tables rathen than real table with each temp table being called from a perspective proc and destroyed at connection close. For example, we have reports that return a weeks worth of data(40,000 rows), I organize it by day/product by selecting from the base table into a temp and then returning the 7 rows for the day report to RS.

I also find manging paramters in RS beteer than procs. For example, I ask for one date parameter and the derive all the others from it where I can. For example, a report will show this week, last week and last six months turn around time for a product(s). By managing the parameters in RS, I can just pass the different params to the same sproc although they are set up a different datasets. I've toyed with the idea of adding another input parameter to the procs to push the date manipulation back into the backend.

My general philosphy is to use RS(or any other tool) as a presentation layer and do all calcs except for basic summing, avergaing, etc.. in the DB.

sql

Reporting Services 2005 BestPractises Question

Hi,

We're starting to migrate from Crystal Enterprise to SSRS2005, and had some performance-related questions in relation to complex reports running against large tables.

Scenario:

. We have fairly large source tables, some greater than 5 million rows

. We use fairly complex stored procs which use dynamic sql based on about 10-15 search criterias entered on web page

. Currently our web middle tier calls a stored proc which inserts into a report table. Then UI calls the report which has SQL query to filter the report table on the UserId and shows only the rows for that user.

Question:

. Is this method - running stored proc in app which inserts into table, then asking report to filter the table based on UserID - considered the best way for designing and running complex SSRS2005 reports with lots of data?

(or)

. Is it better to directly run the stored proc from the report and return the resultset directly to the report?

Advantages or disadvantages of either method, useful articles, best practises...all are appreciated.

Thanks,

JGP

In general, only feed the data you want to present into the report. It's much faster to filter data out on the back end than it is to package it up (even if it's not going across the wire) and send it to Report Server.

You can replace the default parameter handling (by using the WebForms control or Url Access) if you don't like the way our parameters are shown (with 10-15 search criteria, this is probably a good idea).

Why not have the report itself call your stored procedure directly w/o the need for the intermediate table? If the number of rows returned is reasonable (ie, criterial + userid is passed to the stored proc) then this should be fine. How many rows per criteria/userid will be returned? How many KB? Query Analyzer will tell you this.

I would make sure to test whatever choices you make. Don't take anyone elses word for how your machines will perform. Use Microsoft ACT or Visual Studio 2005 and validate all assumptions. Please.

Thanks, Donovan.

|||

I must have a misunderstanding on this. I thought it was better to package it up and send it over to RS and use filters so that RS could cache the result set. Do I have a disconnect here?

R

|||

Sending 5m rows across the wire is slow. Filtering/aggregating in SQL is much faster than the overhead of serializing/deserializing and hitting the wire with a bunch of data a particular rendering isn't going to use. Live reports work much better if the only data RS has to deal with is the data it actualy displays. This isn't just true of RS but any application that deals with a database backend.

Of course, I'm generalizing and simplifying and the real answer is more of an "it depends on your report and your usage". But hopefully this explains my comment better.

Thanks, Donovan.

|||

I think the value in cache is negated by the potentially large recordsets that RS must sift through for grouping/sorting. Use procs for everything, we use temp tables rathen than real table with each temp table being called from a perspective proc and destroyed at connection close. For example, we have reports that return a weeks worth of data(40,000 rows), I organize it by day/product by selecting from the base table into a temp and then returning the 7 rows for the day report to RS.

I also find manging paramters in RS beteer than procs. For example, I ask for one date parameter and the derive all the others from it where I can. For example, a report will show this week, last week and last six months turn around time for a product(s). By managing the parameters in RS, I can just pass the different params to the same sproc although they are set up a different datasets. I've toyed with the idea of adding another input parameter to the procs to push the date manipulation back into the backend.

My general philosphy is to use RS(or any other tool) as a presentation layer and do all calcs except for basic summing, avergaing, etc.. in the DB.

Reporting Services

I have SQL Enterprise 2000 installed on my computer to access companies database and chage data as needed. They recently asked me to look into the reporting services function available with sql. How can I play around with it and practice. Is this a program I buy from microsoft?

Thanks,

Carl

Hi Carl,

Here is a link :

http://msdn.microsoft.com/sql/bi/reporting/default.aspx

you can find everything here starting from articles to very helpful and intersting webcasts.

Enjoy

--Amde

Reporting Services

Is it necessary that we need to have enterprise edition of Sql Server to install reporting Services?.I am trying to install reporting services on a xp desktop with .net 2003 sql server 2000 dev edition with sp3a.But unsuccessful to install the server components of the reporting services Please advice.SQL Server 2000 Reporting Services can run on the following operating systems:
Windows Server 2003, Standard Edition
Windows Server 2003, Enterprise Edition
Windows Server 2003, Datacenter Edition
Windows 2000 Server with Service Pack 4 (SP4) or later
Windows 2000 Advanced Server with SP4 or later
Windows 2000 Datacenter Server with SP4 or later
Windows XP Professional with Service Pack 1 (SP1) or later1
Windows 2000 Professional with SP4 or later1

Can run on :
SQL Server 2000 Standard Edition with SP3 or later3
SQL Server 2000 Enterprise Edition with Service Pack 3 (SP3) or later3
SQL Server 2000 Developer Edition with SP3 or later3

Additional requirement :Microsoft Visual Studio .NET 2003 (any edition) or the standard edition of Visual Basic .NET, Visual C# .NET, Visual C++ .NET Standard, or Visual J# .NET to use Report Designer. Report Designer can be installed on the same computer as Report Server components or on a separate computer.

1 - Windows XP Professional and Windows 2000 Professional only support Reporting Services Developer Edition.
[hr]
What was the error during installation?|||It says the Reporting Services require sp3a even though I have installed sp3a several times.|||Originally posted by Cinil
It says the Reporting Services require sp3a even though I have installed sp3a several times.

Thanks Satya ..I got it figured out.Till now I was trying with reinstalling sp3a.But now I tried sp3 instead.It works.I thought sp3a has everything sp3 has.Pretty weird..uh?

Thanks
Cinil|||weird indeed.