MS Access for the Business Environment: "Upsize" MS Access Reports to MS Reporting Services - Page 2

A Brief Introduction to Reporting Services

Reporting Services is a comprehensive reporting solution that integrates numerous components within an elastic, web-based architecture. These components include:

• The tool set necessary to create, manage, view and deliver reports throughout the organization;
• Open application programming interfaces (APIs) for the integration of reports, or an overall business intelligence solution, in a wide range of diverse business environments;
• A server-based, highly scalable engine for processing and hosting reports.

When we adopt the MSSQL Server 2000 / Reporting Services combination, regardless of the extent of the evolution of our BI efforts before, we avail ourselves of a highly integrated, powerful aggregation of BI components that includes:

• The MSSQL Server RDBMS, within which we can house organizational data for analysis;
• MSSQL Server Analysis Services, composed of a robust OLAP engine that provides a means of developing and creating cubes for highly effective, interactive analysis;
• The Data Transformation Services tools with which we can perform flexible, yet powerful, extraction, transformation and loading ("ETL") of data between operational data stores and the BI warehouses and marts;
• Data-mining capabilities for finding patterns and correlations in large amounts of data;
• Other support and development tools that allow us to rapidly develop and deploy BI applications within our organizations.

Although its own central reporting database is housed within MSSQL Server, Reporting Services can easily access data from a wide range of data sources via ODBC and OLE DE. Reporting Services was designed with seamless integration in mind, and it will become known as a universal solution among a diverse range of environments. While Reporting Services also integrates with a wide variety of report creation tools and environments, it contains everything administrators and authors need to create and deliver reports without the requirement to write the code involved. The development environment is contained within a graphical tool called Report Designer, which works inside the project framework of the feature-rich Visual Studio .NET development environment. Illustration 1 depicts a report that is under development in the Report Designer.

Report Designer plays an important role in the upsizing of MS Access reports into Reporting Services. We use Report Designer to import MS Access reports, either from an MS Access database (.mdb) or MS Access project (.adp) file. Each imported report is converted to Report Definition Language ("RDL") by the Report Designer. RDL is an XML-based standard for report definition, developed by Microsoft and several industry partners.

All reports created in Reporting Services are defined in the RDL format, which generates XML in a way that is transparent to the report author. The RDL standard is being adopted rapidly by ISV's from the perspective of support, so upsizing reports from MS Access, just like writing them "from scratch" in the Report Designer (or a custom authoring tool, if appropriate) only means more integration and enhanced usability of our information products.

We will be examining the import / conversion process in our practice exercise later. Let's take a look at some of the realities of conversion that we will need to understand before undertaking the conversion of our MS Access reports to Reporting Services.

The Realities of Conversion

The Basics

As we said, we can import reports from an .mdb or .adp file. In either case, the full set of reports associated with the file will be imported, and converted to .rdl files. In our practice example, we will import a full set of reports from a copy of the sample Northwind database. Other ways to manage this might be to create a "halfway house" .mdb, through which we can pass the reports at a controlled pace. We can also import the entire set of reports into a surrogate report project that we create beforehand in Report Designer, then, once the importation / conversion to .rdl files has occurred for the full set, open the individual reports in another report project, for a degree of insulation or staging, if that is useful for some reason. In any case, the original reports, and the MS Access database that houses them, are safe from corruption or otherwise disrupted utility.

Keep in mind that Access 2002 and above are supported as import targets of Reporting Services. Access 2002 or later is the only native import option, with regard to having a menu item reserved for that purpose, but the import and conversion of other popular report formats will soon become common options. I have already worked with multiple organizations that are in the process of creating such capabilities for Crystal Reports (several parallel ventures are underway in this arena, apparently), as well as for specific products of Cognos, BO and other "Big Sister" applications. Utilities of this sort will become commonplace as organizations all over the planet begin to see the huge savings involved in making the switch - and especially see others doing so around them. Once this train gets rolling ... well, we all know the metaphor.

In addition to being housed within an Access 2002 or later database, Reporting Services will require that the data sources associated with any reports designated for import must be available for the conversions to be successful. Reporting Services, as we shall see, actually converts the data source to a Reporting Services source that is active as soon as the report is converted and available.

Some MS Access Components are Not Supported

As we might expect, some modules, controls and other components found in import-targeted MS Access reports are not compatible with Reporting Services. The resulting build errors are presented in the Task List of the Design Environment (normally the lower window), where we can review what was culled out of a given report to ascertain whether its rejection was crippling to the report in its new incarnation in Reporting Services. Because the two reporting environments are significantly different, Reporting Services may modify some of the features of the imported reports, or may reject the components entirely.

Supported features are detailed in the Reporting Services Books Online. In addition, an overview is provided that treats the manner in which MS Access features and components are converted to RDL. The documentation explains, in many cases, what the action taken by Reporting Services can be expected to be, when it encounters an unacceptable item, and whether a warning or error is likely in the Task List. The components that are discussed include:

• Data Sources
• Report Modules
• Report Controls
• Report Properties
• Grouping Properties
• Expressions
• Functions, including
  • Array
  • Conversion
  • Database
  • Date/Time
  • DDE/OLE
  • Domain Aggregate
  • Error Handling
  • Financial
  • Interaction
  • Inspection
  • Math
  • Message
  • Program Flow
  • SQL Aggregate
  • Text
• Constants
• Parameters
• Object Names
• Rectangles and Containment
• Bitmaps

Other considerations are discussed here and elsewhere in the Books Online, as well, including the fact that conditional formatting is not automatically converted, that the report properties description field in MS Access is not converted, and so forth. This section of the Books Online is a "must read" for any practitioner who seeks to prepare for the sometimes tricky process of importing his or her organization's reports into Reporting Services. (For that matter, as of this writing the initial offerings on the book market for SQL Server Reporting Services is, in my opinion, so poor that the Books Online - which they essentially recast on paper - is still the best source of information that exists in a single document. In this case, I feel that the independent reviews we see on the major online booksellers are mostly quite accurate.)

Despite the differences in the two applications, and the features that may exist in our MS Access reports that will not carry over to identical functionality in Reporting Services, we can still often obtain efficiencies by using the import feature to upsize our reports. This is particularly true when our reports are not heavily endowed with the features or components that do not readily translate, or if the number of non-convertible items is minimal. If we have resources on hand that make the "import and modify" process more efficient than fresh rewrites, then the import capability obviously still offers a boost in the upsizing effort. It is, however, important to consider that the reports that we translate via this means may still warrant examination by knowledgeable practitioners to determine if enhancements or additions may be available in the far more powerful Reporting Services environment, and if the upsizing process might present an opportunity to make our reports more useful and performance oriented, as well as more easily deployed, delivered, and so forth via the new Reporting Services paradigm.

Let's begin our practice exercise at this point, preparing a database with reports to convert, and then getting hands-on exposure to the process in general. This will provide some insight into the decision to perform this on a larger scale later in our respective organizations.