Question About Scalability of Reporting Services

We use SQL Server 2000 Reporting Services to represent reports for our company.

Our boss complained to me about the poor performance of Reporting Services when he viewed reports. He suspected that RS Server is a single-thread server and can not be scaled up by adding more CPUs or memories to increase the reports process speed.

Is my boss correct?

And how can we increase the performance of Reporting Servces?

Traditional scale up can resolve the issue?

Appreciate your precious reply.

Your boss is not correct :)

We will scale up with more CPUs. Most of Reporting Services is managed code, so we are subject to whatever memory constraints are in place by the CLR. So on 32-bit machines we will use up to 2GB of memory, and we will use more on 64-bit platforms.

You can check the execution log to see which reports in particular are taking a long time to execute and what phases of those executions are long running:

http://msdn2.microsoft.com/en-us/library/ms159110.aspx

See Tudor's blog entry on performance here as well:

http://blogs.msdn.com/tudortr/archive/2004/06/28/167969.aspx

It has good tips for optimizing a poorly performing report.

|||

Excellent!

Thanks alot!

Question about Reports....

Hi all... I am doing Report in Business Intelligence Project. If I preview the report, why is it the data is shown only in 2 pages. It is actually 4 pages if I print it... what should I do to show the 4 pages report if I preview it? Please I need guidance....

thanks.

-Ron-

It is not possible to control the number of pages in any format, either html preview or pdf.

Shyam

|||

On the Layout tab of your report, under the Layout section on the Properties menu, try setting your InteractiveSize to 8.5in, 11in.

|||

triguns wrote:

Hi all... I am doing Report in Business Intelligence Project. If I preview the report, why is it the data is shown only in 2 pages. It is actually 4 pages if I print it... what should I do to show the 4 pages report if I preview it? Please I need guidance....

thanks.

-Ron-

I'm brand new to MS BI and have a similar problem but the even number pages don't have any detail on them.... just header/footer stuff. I've got interactive and page size both set to 8.5X11. Any thoughts?

Thanks!

Leif|||

Do you have any explicit page breaks set?

Another thing that can affect paging is having a control too close to the border of the page. Try leaving a quarter inch or eighth of an inch between the control and the margin.

|||

It is because the data from one page is getting spilled to second page. This happens if the report width in your designer + (left margin+right margin specified in Report Properties -> Layout tab) exceeds the page width that you specify in Report Properties -> Layout tab. For example, if your report is 8 inches wide in your designer and in report properties, you have specified page width=8.5 inch and left margin=1inch, right margin=1inch, data from your pages will spill to next pages because 8 inch (designer report width) + 1 inch (left margin) + inch (right amrgin) > 8.5 inch (page width).

Shyam

Question about Reporting Services...

I used Visual Studio 2003 to create several reports and those reports are
saved to a virtual directory at the IIS server. At present I can only open
those reports using the Report Manager came with the Reporting Services. I
wonder if I can open those reports straight from the web site with a
hyperlink and without opening the Report ManagerUI. If it is the case how
dose it work to connect to the SQL database? My understanding is the
connection/other information associated with the report is saved in a table
"Report Server" at the SQL database. Can I attach this connection string
information in a file at IIS or can it be attached right to the report
itself? Also how will it work in printing those reports in different file
formats as shown in the Reporting Manager if I allow to by-pass using the
Report Manager UI? Thank you for your infor.Hi Paul,
I assume when you mean "open" reports, you mean you want to retrieve a rendered
copy of the report (not retrieve the report definition, etc).
You can access the reports by URL quite easily. If say you have a report
"Purchase Order" in the folder "Demos" (/Demos/Purchase Order). Use this
URL:
http://<yourServer>/ReportServer/?/Demos/Purchase+Order
Then you can attach various URL parameters to control what happens. To render
the report in PDF, use this URL:
http://<yourServer>/ReportServer/?/Demos/Purchase+Order&rs:Command=Render&rs:Format=PDF
All of the database connection information is either stored in the RDL file
on the server, or as part of a Shared Data Source. So, there's no need to
send that info through the URL. If you have additional report parameters,
append them like you would any other URL querystring value. For example,
if you have a parameter "SalesYear":
http://<yourServer>/ReportServer/?/Demos/Purchase+Order&rs:Command=Render&rs:Format=PDF&SalesYear=2005
You can also use the RS web-services API:
http://support.microsoft.com/?kbid=875447
I've always thought that this was the way MSFT intended for users to get
to reports... built seamlessly into web applications, while Report Manager
was for report management/administration.
-Chris @. SoftArtisans
> I used Visual Studio 2003 to create several reports and those reports
> are saved to a virtual directory at the IIS server. At present I can
> only open those reports using the Report Manager came with the
> Reporting Services. I wonder if I can open those reports straight from
> the web site with a hyperlink and without opening the Report
> ManagerUI. If it is the case how dose it work to connect to the SQL
> database? My understanding is the connection/other information
> associated with the report is saved in a table "Report Server" at the
> SQL database. Can I attach this connection string information in a
> file at IIS or can it be attached right to the report itself? Also how
> will it work in printing those reports in different file formats as
> shown in the Reporting Manager if I allow to by-pass using the Report
> Manager UI? Thank you for your infor.
>

Question about parametres passing between reports

Hi, all experts here,

Thank you very much for your kind attention.

I am having a question about the parametres passing between reports. Do we need to create totally the same parametres for the two reports even we only want to jump to the desired report on a particular parametre? E.g. report A got 2 parametres P1 and P2, we only want to jump to report B based on parameter P1 (that is, once I click on the value of P1 in report A, it will birng us to report B with that particular parametre value only), in this case, do we need to create totally the same parametres P1 and P2 in report B as well? or we only need to create P1 in report B? Hope the question is clear for your help.

Thank you very much in advance for your kind help and advices. And I am looking forward to hearing from you soon.

WIth best regards,

Yours sincerely,

As long as P2 can have an empty or null value, or has a default value, you can pass that from the first report.

Or you can set P2 to be a valid value from P1. Sometimes this could be an ALL value.

cheers,

Andrew

|||

Hi,Andrew,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

sql

Question about Hyperlink

Hi!

We have over 500 reports working with report server. In most of those reports we want to use the Hyperlink option to link those reports to some pages of our web application. So i've added javascript code to do a window.open. My problem is that i have to put all the URL for example www.myApp.com/page1.aspx. Our application will be install to different server (for different client) with different name so the adresse www.myApp.com will change with the server. In our application we use the webconfig file so when the server change all our page still working because there are using the connection in the webconfig. Is it possible to do something like that for the reports ? I know that i could create a dll then add a reference in our report. I,ve tried to do it using this http://msdn2.microsoft.com/en-us/library/ms155034.aspx but it never really work and I found this solution a bit complicated. What i mean is when we have a new client and that we want to install the application on his server we want to have a solution that will be quick and easy. We don't want to recreate the a new dll then change all config file to allow this dll.

Is there a simple way to change the reference of hyperlink in all our report. ? Right now the best solution i've think is to make a script that will read the xml file of the report and change all the name for the new one.

Thanks and sorry about my English !

There is value in the global collection that should give you what you are looking for. Try using the following expression in your hyperlink field.

=Globals!ReportServerUrl & "page1.aspx"

|||Thanks !! This is exactly what i was looking for !!!

Question about counting records based on conditions

Hi all,
I'm doing some reports where counting records is extensively used; some of these counts have to be done based on some data in the returning records; for example, I've got the following data set:

Name Sex IsMember
-- -- ----
... M Y
... F N
... M N

and so on...

If I group my report based on 'Sex' field, for example, I would like my report to show group name (M or F), number of members in this group (1 male member) and number of non-members in this group (1 non-member male), so the report should be something like:

Male - 1 member and 1 non-member
... *records where Sex = M will go here* ...

Female - 0 member and 1 non member
... *records where sex = F will go here* ...

So, how can I do counting based on some conditions in the retured data within a group?

Any hint will be very much appreciated,

Thanks in advance,

Essamyou can create a formula field that calulates where the person is male or female and member or non-member

if sex = "M" and Member = "Y" then
"Male Member"
else sex = "M" and Member = "N" then
"Male Non-Member"

etc.

You can then create formulas to count the number of each value or you can create a cross tab report that will group them for you.|||Thanks abyerly for your reply, and sorry I saw your post just today!

Well, my problem was solved by this reply I got from someone called MrBill somewhere in the web :) and yes it is what I was looking for, and as I was doing it in groups, it was better to do it this way:

You would group on sex and create two formulas:
// formual @.MemberCount
If {table.IsMember} = "y" then 1 else 0

// formula @.NonMemberCount
If {table.IsMember} = "n" then 1 else 0

Place the formulas in the detail section. You can suppress them if you want. For each formula do the following:
Right click > Insert > Summary. In the "calculate this summary" box select 'sum'. In the summary location box select group 1 (sex). Click ok. This will place a sum of your formula fields in the group 1 footer. Drag the 'sum' fields to the group header if you want or leave in the footer.

Question about authentication

Hello :).

I have a web application running under .NET 1.1 and Reporting Services 2005. What I need is to display reports in my app.

Here I have two problems:

1. SSRS uses web service as a data source (it is in my application). Access to the service should be permitted only for known (and logged in) users.

2. Access to SSRS should be permitted only for known (and logged in) users.

So, what is the best approach to integrate Reporting Services and my application according to the security limitations?

Any advise on this subject is more than welcome :)

Thank you.

You have basically two options - trusted account and custom security. See this article for more info. I am curious to know how SSRS uses a web service as a data source. Did you write a custom data extension?|||

Teo, thank you very much for your great articles and reply. They were very helpful for me.

Talking about web service as a data source I mean simple XML data source. I.e. DataProvider is 'XML', and connection string refers to the web service. So, I didn't write any custom data extension, as it was already written in the SSRS 2005 :).

Sorry if original post was unclear (maybe it's because I'm really novice in the Reporting Services :) ).

Question about architecting applications with Report Designer

I'm very new to Report Designer and .Net and someone told me it was best if I just used Stored Procedures to get data for the reports. In my previous work experience, we always avoid tying the presentation layer directly to the Data Access layer. My thought is that it would have been a far better architecture to have the Report Designer pass a request to a Service class which would then invoke the appropriate business object. The C# business object would then access one or many DAOs (Data access objects) to bundle the data from various tables into a value object. The Report Designer could then get it's data from the value object. Our data model is very complex and the Stored proc solution is using far too many intermediate temp tables to hold intermediate results. So, what is the best approach to use when data on a single report page can come from as many as 12 different tables? The report layout requirements are complex too.

Have you tried creating a Report Model?

|||

I'm in the same situation. Did report model help you? Please let me know what decision you have made?

I'm thinking of Custom Data Extensions that retreive entity from BLL and attaches it to the report for display. Did you look at that option of accessing custom .NET code (which will be BLL in our case) from within the reports using assembly references and embedded code. The custom .NET code will return a list or array for display within the reports. I have not tried this and not sure whether this will work.

|||

Hi,

I never had a chance to create the Report Model because of lots of pre-production stuff to do. The project is ending in 2 days now and then I'm off to another client. For the benefit of everyone else, if anyone has ideas on flexible architecture for reports, please post.

|||

Perfect example for using web services.
Create a web service that would expose methods to query data from your complex data storage. Internally your webservice will use your business objects for data access then serialize results into xml and return to the caller.
Then you can use Xml Data Provider in your reports to call SOAP APIs and convert returned xml back into rows and columns. Xml DP will let you pass parameters into soap calls, so it's fully flexible. You can search msdn articles and this forum for more info on XmlDP.

|||

Thanks for your response.

I'm planning on using the Custom Data Extensions. The custom DataReader implementation will instantiate a domain class within the business layer and the class will have method which will return the relevant business entity list or some collection of objects. The collection of business entities will be consumed by the DataReader and iterated within the 'Read' method to display the contents within the report. This way i can leverage my business layer and produce the reports.

Do you see any issues with this approach? Please let me know

|||looks good to me

Question about architecting applications with Report Designer

I'm very new to Report Designer and .Net and someone told me it was best if I just used Stored Procedures to get data for the reports. In my previous work experience, we always avoid tying the presentation layer directly to the Data Access layer. My thought is that it would have been a far better architecture to have the Report Designer pass a request to a Service class which would then invoke the appropriate business object. The C# business object would then access one or many DAOs (Data access objects) to bundle the data from various tables into a value object. The Report Designer could then get it's data from the value object. Our data model is very complex and the Stored proc solution is using far too many intermediate temp tables to hold intermediate results. So, what is the best approach to use when data on a single report page can come from as many as 12 different tables? The report layout requirements are complex too.

Have you tried creating a Report Model?

|||

I'm in the same situation. Did report model help you? Please let me know what decision you have made?

I'm thinking of Custom Data Extensions that retreive entity from BLL and attaches it to the report for display. Did you look at that option of accessing custom .NET code (which will be BLL in our case) from within the reports using assembly references and embedded code. The custom .NET code will return a list or array for display within the reports. I have not tried this and not sure whether this will work.

|||

Hi,

I never had a chance to create the Report Model because of lots of pre-production stuff to do. The project is ending in 2 days now and then I'm off to another client. For the benefit of everyone else, if anyone has ideas on flexible architecture for reports, please post.

|||

Perfect example for using web services.
Create a web service that would expose methods to query data from your complex data storage. Internally your webservice will use your business objects for data access then serialize results into xml and return to the caller.
Then you can use Xml Data Provider in your reports to call SOAP APIs and convert returned xml back into rows and columns. Xml DP will let you pass parameters into soap calls, so it's fully flexible. You can search msdn articles and this forum for more info on XmlDP.

|||

Thanks for your response.

I'm planning on using the Custom Data Extensions. The custom DataReader implementation will instantiate a domain class within the business layer and the class will have method which will return the relevant business entity list or some collection of objects. The collection of business entities will be consumed by the DataReader and iterated within the 'Read' method to display the contents within the report. This way i can leverage my business layer and produce the reports.

Do you see any issues with this approach? Please let me know

|||looks good to me