Chapter 21 Ten Cool Tricks with Reporting Services

Chapter 21 Ten Cool Tricks with Reporting Services In This Chapter Greenbar paper reporting Controlling page breaks Running totals Simulating end-user sorting Simulating the All parameter value Dynamically creating a report query Changing the Report Manager home page icon Suppressing objects or formulas in Excel report rendering Securing reports with the UserID parameter Measuring and improving report performance You can do a bunch of things in Reporting Services that may involve some custom expressions or customization involving a little extra coding to your reports. This section introduces you to some interesting (and cool) tricks you can use to make your reports either stand out or fit into the existing standards you may be required to fulfill. Greenbar Paper Formatting If you work at a company that still runs mainframe computers, you may have seen the old greenbar paper stock. This is the wide paper that mainframe computers use to print out their results. The paper is lined alternatively with green and white stripes to assist in reading across the columns of a report.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Inexpensive Web Hosting services

Part VII The Part of Tens

In this part . . . The Part of Tens distills the information found in various sections throughout the book into lists of ten telling tidbits to enhance your reports. From cool tricks and helpful resources on the Web to more aspects of the BI platform from Microsoft and a list of third party tools that can complement your repertoire, these items can extend you into the ranks of a professional report developer.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Inexpensive Web Hosting services

The remaining prompts enable you to get an

The remaining prompts enable you to get an SQL Server 2005 database all set up and and ready to go for reporting. Then you can either create reports from scratch in Reporting Services, or follow the conversion routine described earlier in this chapter to get a running start at the report layouts for a one-for-one conversion. Figure 20-5: Find the Upsizing Wizard in the Database Utilities menu. Chapter 20: Converting Reports from Access 363

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

Upsizing an Access Database and Reports Over time,

Immediate recoverability In case of system failure (such as an operating system crash or power outage), SQL Server has an automatic recovery mechanism that recovers a database to the last state of consistency in a matter of minutes, with no database administrator intervention. Critical applications can be up and running again right away. Server-based processing Microsoft designed SQL Server from the beginning as a client/server database, where data and indexes reside on a single server computer that is often accessed over the network by many client computers. SQL Server reduces network traffic by processing database queries on the server before sending results to the client. Thus, your client/server application can do processing where it s done best, on the server. Your application can also use user-defined functions, stored procedures, and triggers to centralize and share application logic, business rules and policies, complex queries, and data validation and referential integrity code on the server, rather than on the client. Upsizing your Access database The best way to upsize an Access database is by running the wizard in Access. Not only does running the wizard save you time, the process is done right the first time. After you upsize your database to a more powerful database platform, using Reporting Services will be that much easier to implement for the reports formerly implemented in your Access database. To begin the Upsizing Wizard, find it in the Tools menu within Access (see Figure 20-5) and follow its prompts. The initial screen of the Upsizing Wizard will give you a choice between using an existing database or creating a new database. I would suggest starting with creating a new database. 362 Part VI: Migrating from Other Reporting Tools

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

Upsizing an Access Database and Reports Over time,

Upsizing an Access Database and Reports Over time, most database applications grow, become more complex, and need to support more users. At some point in the life of your Access database application, consider upsizing to SQL Server to optimize database and application performance, scalability, availability, security, reliability, and recoverability. High performance and scalability In many situations, SQL Server offers better performance than an Access database. SQL Server also provides support for very large, terabyte-sized databases, which is much larger than the current limit for an Access database of 2GB. Finally, SQL Server works very efficiently on Microsoft Windows 2000 or later by processing queries in parallel (using multiple native threads within a single process to handle user requests) and minimizing additional memory requirements when more users are added. Increased availability SQL Server allows you to do a dynamic backup, either incremental or complete, of the database while it s in use. Consequently, you do not have to force users to exit the database to back up data. This means your database can be running up to 24 hours a day, seven days a week. Improved security Using a trusted connection, SQL Server can integrate with the Windows 2000 or later system security to provide a single access to the network and the database, employing the best of both security systems. This makes it much easier to administer complex security schemes. An SQL Server database on a server also employs innovative security features, which helps prevent unauthorized users from getting to the database file directly, but rather they must access the server first. Chapter 20: Converting Reports from Access 361

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

You see some interesting conversion artifacts in Reporting

Figure 20-4: Report Designer layout of the modified Sales Summary layout, also showing the report header properties. Figure 20-3: Report Designer layout of the modified Sales Summary layout, also showing the report header properties. 360 Part VI: Migrating from Other Reporting Tools

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

You see some interesting conversion artifacts in Reporting

You see some interesting conversion artifacts in Reporting Services. The first is the columns have gone to the report header section but the report title is in the report body. The converted report initially looks like the report shown in Figure 20-2. You can clean up an errant report in many ways. Suppose that you want the report title and header to appear at the top of each page. Then you would drag the report title text box up above the column names in the header section, as shown in Figure 20-3. Also note in the figure that the properties on the report header indicate that nothing is printed on the first page or the last page. If you change the PrintOn FirstPage and PrintOnLastPage attribute properties to true for both, you will find that the result looks similar to the Access report from which it came (see Figure 20-4). Figure 20-2: Report Designer layout of the Sales Summary report after it has been converted and imported into the Report Designer. Chapter 20: Converting Reports from Access 359

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

Supporting cast for Reporting Services The following important

Also, keep in mind that there are several Access objects that are not supported by Reporting Services. Examples of the report controls that Reporting Services does not support include the following controls: BoundObjectFrame CheckBox ComboBox CommandButton CustomControl ObjectFrame OptionButton TabControl ToggleButton Tightening up reports after conversion After you convert a report from Access, preview it. You may see error messages in the Error List window. You may find that the basic layout doesn t convert correctly and needs some adjustments after the import process. Consider the following example where you import XMarket.mdb from an Access database. The Sales Summary report in the Access database is shown in Figure 20-1. Figure 20-1: Access Report Designer showing the layout within Microsoft Access. 358 Part VI: Migrating from Other Reporting Tools

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services