Basic Server Reporting Functionality in SQL Server 2005 Express Edition

Tuesday May 26th 2009 by Marcin Policht

In the recent installments of our series dedicated to the most prominent features of SQL Server 2005 Express Edition, we have started an overview of its reporting capabilities. This article focuses on elementary methods you can employ to generate custom reports.

In the recent installments of our series dedicated to the most prominent features of SQL Server 2005 Express Edition, we have started an overview of its reporting capabilities. So far, we have described its basic architecture and characteristics, as well as guided you through its setup procedure and post installation modifications leveraging the Reporting Services Configuration Manager utility. We will now focus on elementary methods you can employ to generate custom reports (we will be discussing more advanced techniques in upcoming articles).

We have concluded our previous presentation at the point where Reporting Server became operational, with all of its essential components present and configured with their default settings. To verify that the outcome has been successful, launch Internet Explorer on the target computer and point to the Report Manager Virtual Directory as the target URL (this is, by default, http://localhost/Reports$instance_name, where instance_name designates the SQL Server 2005 Express Edition instance hosting the Report Server databases), while logged on with an account that is a member of its Sysadmin role. Such action should automatically redirect you to http://localhost/Reports$instance_name/Pages/Folder.aspx page, serving as the SQL Server Reporting Services Home page, from which you can manage site settings (including hierarchy, security, and report execution policies), designate data sources, as well as upload files (report definitions or resources). You might also want to launch Local Users and Groups console (by invoking LUSRMGR.MSC in elevated Admin mode) and ensure that SQLServer2005ReportServerUser$ComputerName$InstanceName and SQLServer2005ReportingServicesWebServiceUser$ComputerName$InstanceName local groups exist and contain, respectively, the ReportServer Windows and Web Services accounts you have identified earlier.

In order to create reports based on SQL Server 2005 Express Edition Reporting Services functionality, we have to rely on features provided by Business Intelligence Development Studio. Its source files are included in the SQL Server 2005 Express Edition Toolkit, available from the Microsoft Download Center in the form of a self-extracting executable SQLEXPR_TOOLKIT.EXE. Once you have downloaded and launched it, you will be presented with the familiar Microsoft SQL Server 2005 Setup wizard, prompting you to accept the End User License Agreement, checking hardware and software prerequisites, requesting registration information, and, most importantly, giving you the opportunity to choose desired features, including connectivity components, Software Development Kit, Management Studio Express, and Business Intelligence Development Studio (including Report Designer).

Note that this rather straightforward process could turn into a considerably more complex undertaking in situations where you had previously installed instances of Visual Studio 2005 (such as, Visual Studio 2005 Express Edition). In such cases, you will likely encounter a problem with a misdirected or missing shortcut to devenv.exe (the primary executable of SQL Server Business Intelligence Development Studio) after completion of a seemingly successful setup process. To resolve it, you might have to remove the earlier instance of Visual Studio (and potentially, SQL Server 2005 Express Edition client components and Reporting Services), extract SQLEXPR_TOOLKIT.EXE to a separate folder (using /X parameter), and invoke vs_setup.msi from the setup subfolder of the directory containing the extracted source files (and, subsequently, reinstall previously removed software). In addition, if you are using Windows Vista, you should also install Visual Studio 2005 Service Pack 1 Update intended for this operating system, and ensure that you operate in elevated administrative mode whenever you design, build, and deploy reports (which, incidentally, also applies also to launching other management tools, such as SQL Server Management Studio Express and Reporting Services Configuration console).

Our first sample report will be based on the AdventureWorks database, which in the case of SQL Server 2005 Express Edition needs to be separately downloaded from the CodePlex Web site (there a number of different versions, including those intended specifically for lightweight OLTP samples running on both x32 and x64 bit platforms). After you have downloaded the AdventureWorksDB.msi Windows Installer package, launch its execution using an account with local Administrator privileges. This will trigger AdventureWorksDB InstallShield wizard, taking you through a short sequence of steps, including accepting license agreement and specifying target folder. Once this process is completed, attach the database (using Attach... entry from the context sensitive menu of the Databases node of your SQL Server 2005 Express Edition instance) within SQL Server Management Studio Express console.

After launching Business Intelligence Development Studio, you should be able to find the two Reporting Services-specific items listed under the Visual Studio Templates section of the New Project dialog box under the Business Intelligence Projects node (once you choose either New Project entry from the File top level menu or Create: Project... from the Recent Projects pane on the Start Page of the console). The first one, labeled Report Server Project Wizard, guides you through a report generation, assisting with each necessary step, including data source selection (automatically assigning connection string based on parameters you specify), designing a query against it (via friendly Query Builder interface), as well as choosing a report type (tabular vs. matrix), layout (stepped vs. block), style, and formatting. Throughout this process, you will also be asked to provide the path to the Report Server (note that in our case, you might need to alter it from its default http://localhost/Reports to http://localhost/Reports$instance_name) and deployment folder (located within the Report Server virtual directory). After clicking on the Finish command button on the final page of the wizard, you will be presented with the layout of the newly created report in the main window of Business Intelligence Development Studio. You can further modify your design by taking advantage of functionality available from this interface and, once your report is ready, select the Deploy option in the context sensitive menu of your project in the Solution Explorer window. However, in order to avoid any issues, you should first verify the accuracy of entries in the Project Properties dialog box (in particular, ensure that the Target Server URL entry is set to http://localhost/ReportServer$instance_name, assuming the default naming convention). Providing that no errors are reported in the Output window, a new folder (named the same as your project name) should appear on the Report Manager Web page. By opening it, you will access another page with a link to the newly deployed report, displaying data in the format you selected. Users can access reports via an equivalent method (although they will be pointing their browsers to http://Web_server_name/Reports$instance_name).

Let's demonstrate this process by walking through each of its steps while designing a sample report based on the content of AdventureWorks database. In particular, we will generate a report listing address data for individual customers (leveraging the query described in the Sales and Marketing Scenario article on the MSDN Web site) in alphabetical order (based on last and first names) but grouped according to country/region and state/province. To start, launch Business Intelligence Development Studio and select the Report Project Wizard from the New Project window. After you acknowledge the introductory (and purely informational) page, you will be prompted to Select the Data Source. Click on the Edit... command button, in the Server name listbox locate the SQL Server 2005 Express Edition instance (if you do not see it on the list, ensure that SQL Server Browser service is running), accept the default log on mechanism (based on Windows Authentication), and connect to the AdventureWorks database by choosing its name in the listbox labeled Select or enter a database name. (You can take advantage of Test Connection feature to ensure that information you specified so far is correct). After confirming your selection, proceed to the next page labeled Design the Query. While you can enter it directly in the Query string text box, we will use the Query Builder for this purpose. By default, you will be presented at this point with the Generic Query Designer. To switch to its graphical counterpart, click on the Query Designer icon located in the upper left corner of the main window. From its interface, add the Person.Contact (with FirstName and LastName columns), Sales.Individual (with CustomerID column), Sales.CustomerAdddress, Person.Address (along with its AddressLine1 and City columns), Person.StateProvince (with Name column aliased as StateProvince), and Person.CountryRegion (with Name column aliased as CountryRegion) tables, sorting their combination in ascending order based on LastName and FirstName pair. Confirm your choices and return to the Design the Query page. Choose the Tabular option when prompted for the report type. On the Design the Table page, choose CountryRegion as the Page label, StateProvince as the Group criteria, and add all remaining fields (CustomerID, FirstName, LastName, AddressLine1, and City) to the Details section. Decide on the table layout (by picking either Stepped or Block entry) and a visually preferred table style. Finally, in the Report server listbox, type in the URL pointing to your Report Server (by default set to http://localhost/ReportServer$instance_name), deployment folder (where your report will be stored), and the report name (which will identify it on a Web page accessed by your clients once the deployment is completed). To make the report available via Report Server Web site, use the Deploy option from the context-sensitive menu of your project (in the Solution Explorer window) we have mentioned earlier.

You can produce the same outcome using the second project template available from the New Project dialog box in Business Intelligence Development Studio interface (called simply Report Server Project), although in this case, without relying on rather inflexible rules imposed by the wizard. We will provide an example demonstrating this approach in our next article of this series and fix some visual deficiencies of the report we just created.

» See All Articles by Columnist Marcin Policht

Mobile Site | Full Site