Tuesday, March 27, 2012

Change from Crystal Reports to SQL Server 2005 Reporting Services?

Hi all,
We have a web application that generates statistical reports in PDF format
based on customer data and we're thinking of migrating to SQL Server 2005
Reporting Services but I'd like to know if what we are doing is possible.
Basically, the customer logs onto the system via a regular browser, this
creates a folder on the backend based on their session info. Then they
select a couple of different reports, enter the required parameters and hit
print. The web app runs a VB program that loads the RPT file, modifies the
connections so it's getting the data from the correct customer database,
then exports the report as a PDF into the previously created folder. The
user is then redirected to that file so they can download it.
Whether the report is shown in a browser window or not is all dependent on
the customers settings for PDF documents. We don't need interactive
web-based reports, we don't need to drill-down, we don't need OLAP. It's a
simple "I want a report", "Here it is" scenario, but there is the definate
potential of having several hundred customers all requesting reports at the
same time, which is something our current solution can't handle.
Comments and suggestions are welcome.2005 has the ability to change the datasource at runtime. You do not need to
do any of this creating a folder in the background etc. Everything that you
want is really very simple.When your web app renders it can request it as
PDF. Also, you can send the report a parameter and based on that parameter
pick the appropriate database.
Implementing your security is the biggest issue (and what comes up the most
with an internet application). You will need to do forms based
authentication (which I have not done and do not know much about).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OOzh8DapGHA.4408@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> We have a web application that generates statistical reports in PDF format
> based on customer data and we're thinking of migrating to SQL Server 2005
> Reporting Services but I'd like to know if what we are doing is possible.
> Basically, the customer logs onto the system via a regular browser, this
> creates a folder on the backend based on their session info. Then they
> select a couple of different reports, enter the required parameters and
> hit print. The web app runs a VB program that loads the RPT file, modifies
> the connections so it's getting the data from the correct customer
> database, then exports the report as a PDF into the previously created
> folder. The user is then redirected to that file so they can download it.
> Whether the report is shown in a browser window or not is all dependent on
> the customers settings for PDF documents. We don't need interactive
> web-based reports, we don't need to drill-down, we don't need OLAP. It's a
> simple "I want a report", "Here it is" scenario, but there is the definate
> potential of having several hundred customers all requesting reports at
> the same time, which is something our current solution can't handle.
> Comments and suggestions are welcome.
>|||Thanks for the reply Bruce. Application security is already handled.
Maybe you can guide me through my understanding of how SSRS would handle
this?
a) User opens web-page.
b) User selects report from drop-down list, and the postback shows the
correct parameter options.
c) User enters the parameter values he needs.
d) User clicks on print.
e) Pop-up appears asking the user to wait while their report is prepared.
f) A few minutes later, the pop-up disappears to be replaced by the usual
File Download dialog.
g) User clicks Save and the PDF is saved to their PC.
We can do this in Crystal Reports without any fuss, but when more than 30
users are performing the same actions at the same time, the queue of report
requests ends up being pretty large as it takes quite some time to produce
each report. We're hoping that SSRS will let us "farm" out the report
generation to multiple servers.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OJlmwOcpGHA.3324@.TK2MSFTNGP05.phx.gbl...
> 2005 has the ability to change the datasource at runtime. You do not need
> to do any of this creating a folder in the background etc. Everything that
> you want is really very simple.When your web app renders it can request it
> as PDF. Also, you can send the report a parameter and based on that
> parameter pick the appropriate database.
> Implementing your security is the biggest issue (and what comes up the
> most with an internet application). You will need to do forms based
> authentication (which I have not done and do not know much about).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
> news:OOzh8DapGHA.4408@.TK2MSFTNGP04.phx.gbl...
>> Hi all,
>> We have a web application that generates statistical reports in PDF
>> format based on customer data and we're thinking of migrating to SQL
>> Server 2005 Reporting Services but I'd like to know if what we are doing
>> is possible.
>> Basically, the customer logs onto the system via a regular browser, this
>> creates a folder on the backend based on their session info. Then they
>> select a couple of different reports, enter the required parameters and
>> hit print. The web app runs a VB program that loads the RPT file,
>> modifies the connections so it's getting the data from the correct
>> customer database, then exports the report as a PDF into the previously
>> created folder. The user is then redirected to that file so they can
>> download it.
>> Whether the report is shown in a browser window or not is all dependent
>> on the customers settings for PDF documents. We don't need interactive
>> web-based reports, we don't need to drill-down, we don't need OLAP. It's
>> a simple "I want a report", "Here it is" scenario, but there is the
>> definate potential of having several hundred customers all requesting
>> reports at the same time, which is something our current solution can't
>> handle.
>> Comments and suggestions are welcome.
>>
>|||Although implemented in a different way, the scenario described below is no
problem. RS has support for web farms. The Reporting Services app (which is
a 2.0 dotnet application) can run on a web farm. They all use the same
backend database for object/metadata caching. You do have to purchase a SQL
Server 2005 server license for each box running
PDF processing is more resource intensive than html or csv but RS 2005 has
improved this over RS 2000.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:eqzqBohpGHA.1548@.TK2MSFTNGP04.phx.gbl...
> Thanks for the reply Bruce. Application security is already handled.
> Maybe you can guide me through my understanding of how SSRS would handle
> this?
> a) User opens web-page.
> b) User selects report from drop-down list, and the postback shows the
> correct parameter options.
> c) User enters the parameter values he needs.
> d) User clicks on print.
> e) Pop-up appears asking the user to wait while their report is prepared.
> f) A few minutes later, the pop-up disappears to be replaced by the usual
> File Download dialog.
> g) User clicks Save and the PDF is saved to their PC.
> We can do this in Crystal Reports without any fuss, but when more than 30
> users are performing the same actions at the same time, the queue of
> report requests ends up being pretty large as it takes quite some time to
> produce each report. We're hoping that SSRS will let us "farm" out the
> report generation to multiple servers.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OJlmwOcpGHA.3324@.TK2MSFTNGP05.phx.gbl...
>> 2005 has the ability to change the datasource at runtime. You do not need
>> to do any of this creating a folder in the background etc. Everything
>> that you want is really very simple.When your web app renders it can
>> request it as PDF. Also, you can send the report a parameter and based on
>> that parameter pick the appropriate database.
>> Implementing your security is the biggest issue (and what comes up the
>> most with an internet application). You will need to do forms based
>> authentication (which I have not done and do not know much about).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
>> news:OOzh8DapGHA.4408@.TK2MSFTNGP04.phx.gbl...
>> Hi all,
>> We have a web application that generates statistical reports in PDF
>> format based on customer data and we're thinking of migrating to SQL
>> Server 2005 Reporting Services but I'd like to know if what we are doing
>> is possible.
>> Basically, the customer logs onto the system via a regular browser, this
>> creates a folder on the backend based on their session info. Then they
>> select a couple of different reports, enter the required parameters and
>> hit print. The web app runs a VB program that loads the RPT file,
>> modifies the connections so it's getting the data from the correct
>> customer database, then exports the report as a PDF into the previously
>> created folder. The user is then redirected to that file so they can
>> download it.
>> Whether the report is shown in a browser window or not is all dependent
>> on the customers settings for PDF documents. We don't need interactive
>> web-based reports, we don't need to drill-down, we don't need OLAP. It's
>> a simple "I want a report", "Here it is" scenario, but there is the
>> definate potential of having several hundred customers all requesting
>> reports at the same time, which is something our current solution can't
>> handle.
>> Comments and suggestions are welcome.
>>
>>
>

No comments:

Post a Comment