sql server reporting services: We have a plan to migrate our Existing Crystal Enterprise 10 system to Sql
server 2005 reporting services(SSRS).

Following are the details about the existing system/requirements:-
Existing stats:-
No of reports- 1500+
Max NO. OF RECORDS in a report REPORT-500K
total number of users- 500
average no of concurrent users-200
Existing CE features/REQUIREMENTS used :-
1. Report scheduling.
2. Security.(Profile based access).
3. Report data caching.(as per schedule)
4. Event based report generation ( e.g file based events)
5. Notifiactions on report generation/refresh/error.
6. Report delivery methods (e.g http,email,ftp etc)
7. Support for various report formats viz MS excel,MS
word,PDF,csv,rtf,txt,MS mail,html,XML etc.
8. Basic reports functionalities include- Drill down(Nth level),
Aggregation of aggregate/groups,programatically handling report events.
9. Retention of previously generated report instances as snapshots(N
instances).
10.UI for report administration and viewing.
11.Report performance optimisation/tuning feature.


Will SSRS be able to handle all of these of requirements and load efficiently?
Is their any tool/utility available in SSRS to easily migrate Crystal
reports(*.rpt) into Report definition language (*.RDl)

Please let me know your views on the feasibility.


Cheers,

[quoted text, click to view]


For the most part, yes it is all covered, though I can't speak
specifically to the number of users and concurrent users. Normally,
you would want to have a 3rd party company handle migrating the
reports from CR to SSRS (http://www.microsoft.com/sql/technologies/
reporting/partners/crystal-migration.mspx ). Unless you want to build
a custom application that can parse CR reports and recreate them into
RDL/specialized XML files. I have worked with both CR and SSRS
extensively and they are very similar in functionality, though SSRS is
free if installed on the same box as SQL Server (more specifically the
ReportServer and ReportServerTempDB databases). The larger difference
is that SSRS requires IIS. Also, CR is a little more mature; however,
SSRS is catching up quickly. Also, CR has a little different export
options than SSRS, but external programmatic customization is
possible. Hopefully this will help in your decision making process.

Regards,

Enrique Martinez
Sr. Software Consultant

Looking over this list my suggestion is to plan on using RS 2008. They are
adding many features that will make the product more suitable for you. Right
now you would need a 3rd party add-on for some of these issues. For
instance, MS Word rendering. MS Excel is there but MS Word is coming with RS
2008. Reports can be delivered with http or email. Not FTP, however, it is
not hard to write a delivery extension is FTP is a requirement.

Number of reports and concurrent users if you use Enterprise and a webfarm
(which Enterprise supports). With webfarm all the servers running RS use the
same database for metadata and object caching but you have a web farm
hosting. My guess is with 500 users you have much less than 200 concurrent.
It is doubtful that high a percent are continuously using RS. Max number of
records of 500K. If this is an export for one system to another then I
suggest you utilize some other method. This would be a 100,000 page report.
RS 2005 does all rendering in RAM. RS 2008 is more intelligent with how it
renders. I think that RS 2008 will be able to do this.

RS 2005 today handles:
1, 2, 3, 6 (no ftp but can write extension), 7 (except for Word and rtf), 8
(not sure about programatically handle report events, RS has a variety of
places you can use expressions to hide or show, format, etc, 9, 10

I am not aware of a way for notifications on report
generation/refresh/error. You can report on these things but not send email
based on the event of it happening. I don't understand #4 (file based
events). You could easily write a VB program that looks for a file based
event and then kicks off a report. It is very easy to integrate in RS. There
is a winform and webform via VS 2005. You can use webservices and you can
use URL integration.

As far as 11. You can optimize your query but after that it is out of your
hands.

Coming from Crystal reports you need to change your thinking. They have
different architectures. RS is designed from the ground up as a SOA (Service
Oriented Architecture). You define parameters, dataset definitions (either
SQL statements or a call to a stored procedure), define the format
independent design of the report. Specify the available actions like drill
down, drill through (one of RS strengths) etc. Then RS handles everything
else. You cannot be of the mindset of wanting to create all the datasets
with your own code/objects, responding to events etc. There are no events to
respond to.

Some of your reports will need to be redesigned. If what you want to do is
have all your reports act and look exactly as before then you are going to
be disappointed. Crystal does some things that RS doesn't and RS does some
things Crystal does not. Some of the reports will be a straight port but
some will not be.

My suggestion is to get some typical reports and try and learn with RS 2005.
I'm not sure what publicly available betas there are but RS 2008 will have
additional design features that will be useful for complicated reports. They
have a tablix control which is a cross between matrix and table controls.
Very useful and powerful.

http://www.microsoft.com/sql/2008/technologies/reporting.mspx


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services


[quoted text, click to view]