Tudor's WebLog

About 2 years ago, as we were wrapping up SQL Server 2005 and Visual Studio 2005, I wrote a reporting services chapter in Sams' SQL Server 2005 unleashed book, As I haven't heard from the editors for a long time, I assumed it had shared the same ill-fate with many software projects - nothing I would have any first-hand experience with J.

It bounced back, however, and was eventually published this spring. They have saved the best for lastJ, so go directly to Chapter 49 and get some hands-on info on using SQL reporting services, and the Visual Studio report controls, complete with working code.

Enjoy...

This is also the last post on Reporting Services you will see on this blog. About 1 year ago, I decided I should try something new, and joined the then-newly created AdCenter team - Microsoft's online advertising unit.

I am involved in AdCenter fraud-prevention efforts and in relevance and infrastructure work - mostly things you notice only when they don't workJ. I might write some posts about AdCenter as I get more time.

The wait is over. The new edition of SQL Express - (free) that includes Reporting Services is available. Look here for download details.

For a list of features available in all SQL editions see this article

Happy reporting!

I made a couple of changes in the object data source sample on www.gotreportviewer.com to illustrate the use of nested objects in report fields

I have added a ProductSales sub-class to the Product class. ProductSales has two fields: Quantity and Price.

In the report - you can reference them via:

Fields!Sales.Value.Quantity and Fields!Sales.Value.Price

You can similarly use multiple levels of object nesting, for example:

Fields!Sales.Value.Customer.Name

See attachment for details.

.

Great starting point for using the new Report Viewer controls in Visual Studio 2005

http://www.gotreportviewer.com/

If you grant Reporting services permissions (via http://servername/Reports  | folder properties | security) to Windows security groups, you may find out that people belonging to those groups don't immediately get the expected access rights.

For performance reasons IIS caches authorization tokens. For more details on how to configure it, see the following KB article:

http://support.microsoft.com/kb/q152526/

I have seen enough questions on how to protect parameters passed to reports to warrant a blog entry.

Let’s say if you want to integrate a report containing HR data in your application. Let’s assume the data in the HR database is keyed by the EmployeeID field.

Your application will figure out the value of EmployeeID for the current user, and you could pass EmployeeID as a report parameter that’ll drive its queries.

The problem with this approach is that your users could change the parameter and pass in another EmployeeID, which leads to unwanted information disclosure. You can try to hide the parameter, so it will not show up in the report viewer toolbar, or not show the toolbar at all, but parameter values will still show up in the URLs generated in the report.

Then you may start thinking about making the values random – so they’re harder to guess, or encrypt their values, etc, etc…

The truth is, you should never make security decisions based on report parameters. Report parameters are eminently spoof-able, hiding them will not work for sophisticated users, and custom-made encryption are seldom strong enough to withstand a serious hacker.

Instead of using parameters, base your security decisions on User!UserID. UserID is populated as the result of the authentication mechanism so it is inherently more reliable than parameters.

In the example above, you can add code in your report that retrieves the EmployeeID based on UserID.

Note:

If you can’t establish a relationship between your user’s Windows accounts and your report data, you can implement a custom authentication extension as described here. The value for UserID will then be determined by your custom auth extension.

If you upgraded to RS SP1 and noticed some of your reports that used to work fail now with the following error: “The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting)” here is the what to do:

1. if your report’s data sources do not support authentication – for instance, an anonymous web service, an XML/CSV file, Microsoft Access data source, then use rsconfig –e –u –p to configure the unattended execution account. Make sure to use a low privilege user account. Access to the data sources will be made as that user account.
2. if your report’s data sources support authentication – for instance Microsoft SQL Server data sources, or other RDBMS, switch to using the one of the other credentials options, as they provide better security.

§       Optimize your report queries. Usually the bulk of report execution time is spent executing queries and retrieving results. If you are using SQL Server, tools such as Query Analyzer and Profiler can help you optimize your queries and Database Tuning Advisor can suggest better indexes for your database.

§       Measure: The Report Server ExecutionLog table contains data on reports performance. The following query can give you a quick look at how long it took to execute certain reports, and where the bulk of the time was spent. TimeDataRetrieval contains the number of ms. spent getting data from the report's data source(s).

use ReportServer

Select * from ExecutionLog with (nolock) order by TimeStart DESC

Make sure to include the “nolock” hint. The ExecutionLog table is used by the RS runtime and locking it can severely degrade your server’s performance.

A better solution is to run the DTS package that comes with Reporting Services. It will move data out of the runtime tables into a set of reporting tables. That way you are minimizing the amount of interference with the RS runtime. You can also take advantage of the built-in reports based on the execution logs, and/or build your own reports.

 §       If you don’t need data in your report, don’t retrieve it. Levering database operations such as filtering, grouping, and aggregates can reduce the amount of data that is processed in the report, improving performance.

§       Keep your reports modest in size and complexity. Do users really want to look at a 1,000 page report?

§       If performance is extremely bad even for single users, check the Application Restarts counter in the ASP.NET category; some antivirus software is known to “touch” configuration files, thus causing expansive Application Domain restarts in the report server web service.  For more information, search http://support.microsoft.com/ for articles relating to “antivirus and ASP.NET”.

§       If performance is slow on the first web service access after there have not been any accesses for a certain time period, disable the idle timeout on the Performance tab in the Application Pool in IIS Manager.

§       Execute reports from cached / snapshot data as opposed to live whenever possible.

§       Limit non-essential background processing to off-peak hours in order to avoid contention with on-line users.

§       If you load your report server up with 4GB memory, remember to set the /3GB switch in C:\boot.ini so application processes can make use of it. 

§       If a single server can’t handle the workload, consider locating the Reporting Services catalog on a remote SQL Server instance as your first step toward increasing system capacity.

§       If one report server configured with a remote catalog still doesn’t adequately support your workload, consider increasing the available resources on the system hosting your report server (scale-up) or setting up a clustered web farm of report servers (scale-out).