| Rev Up "i" Reporting with SQL Server 2008 Reporting Services |
 |
 |
 |
| Application Software - Microsoft | |||||
| Written by Michael Sansoterra | |||||
| Wednesday, 27 May 2009 01:00 | |||||
|
Take advantage of the excellent reporting capabilities that Microsoft brings to the table.
Originally introduced as an add-on to SQL Server 2000, SQL Server Reporting Services (SSRS) is a stable product currently in its third version with SQL Server 2008. This article offers a high-level overview of SSRS and explains how to create reports against a DB2 for i data source. If your organization has significant reporting needs, SSRS could be a great fit.
Why Use SQL Server Reporting Services?
There are plenty of products on the market that can report from DB2 for i data, including Microsoft Access and Excel, Crystal Reports, Cognos, New Generation's NGS-IQ, and DB2 Web Query to name a few. All of these products have their pluses and minuses in terms of features, usability, quirks, and cost, and SSRS is no exception. Rather than do a complete comparison of these products, here are some potential compelling reasons to use SSRS.
•· First, if your organization already owns SQL Server 2005 or 2008 standard edition or higher, then it also owns SSRS. (The free SQL Server Express editions can also run Reporting Services; however, there are some limitations). •· Second, SSRS is a popular reporting platform and therefore has plenty of available technical resources in terms of information on the Web and personnel. Owning a stable and popular product has implications for developer job security as well as long-term cost of ownership. •· Third, the product is browser-based and loaded with features, including drill-downs, the ability to generate reports in multiple formats (PDF, Excel, TIF, etc.), and a full-featured report history, scheduling, and distribution module. •· Fourth, developers are provided with many features, including connectivity to many data sources, a report designer within the familiar Visual Studio IDE, pre-built controls, the ability to leverage .NET components (such as existing business logic), and a Web service-based API that will allow other applications (including iSeries applications) to interact with the reporting engine. •· Fifth, Reporting Services is designed to work with other Microsoft products, such as Sharepoint. •· Sixth, since this is a product by behemoth Microsoft, be assured that Microsoft has the resources to endow future versions with additional features. Configuring SSRS
Reporting Services, including configuration, is a huge topic, so I'll say at the outset I will not be able to do the topic justice. The SQL Server books online (see resources below) already cover how to install and configure SSRS. I'll give a brief overview here.
SSRS is usually initially configured during the install of SQL Server. However, if you skipped that step or need to change something, you can still use the Reporting Services Configuration Manager utility to do the configuration. Among other things, this utility is used to configure SMTP email for report distribution, the URL to the report server, and the database name that is used to store the information about the deployed reports.
SSRS can be configured in a variety of ways. Two possible options are shown below. Option A involves installing the SQL Server Database Engine itself and the SQL Server Reporting Services Engine on different computers. Separating the Report Server from the Database Engine is useful in a heavily used reporting environment where running the database engine and the report server may be too much for one server.
Figure 1: You can run the SQL Server Database Engine and Reporting Services on separate servers.
A second configuration, shown below, has the Database Engine and the Report Server installed on the same server.
Figure 2: Or you can run the SQL Server Database Engine and Reporting Services on the same server.
SSRS can query from the SQL Server database engine, but it can also query data engines that have an ODBC or OLE DB provider, such as Oracle, DB2, or MySQL.
With respect to writing reports against DB2 for i, there is an additional question that should be addressed: where should the reporting data be stored--on SQL Server or within DB2? Depending on the resources available, SSRS can query DB2 directly. Alternatively, it is a common practice to download and stage current DB2 data in SQL Server and then just let SSRS query SQL Server.
Tutorial #1: Building Sample Reports
I'll breeze through some quick examples of how to create a report using SQL Server 2008 Standard edition or higher and Business Intelligence Development Studio (BIDS). BIDS is actually a copy of Visual Studio that is installed with SQL Server, and although I will refer to BIDS through the remainder of the article, BIDS and Visual Studio are interchangeable. BIDS simply adds some "Business Intelligence" project templates that allow developers and administrators to work with special SQL Server features such as Reporting Services.
There are some prerequisites and assumptions for this tutorial:
•· The reader knows the basics of using Visual Studio/BIDS and has familiarity with SQL. •· The report data will be queried directly from DB2. •· An instance of SQL Server 2008 and SSRS is installed and configured. My SQL Server is installed on my local machine as the default instance. (Readers with named instances will have to adjust the example.) •· A development machine (can be separate from SQL Server) with BIDS is installed. •· Some V6R1 SQL examples are used, so some users may need to make appropriate substitutions. •· The Reporting Services machine will also need IBM's System i Access installed (formerly known as iSeries Access and Client Access), including the ODBC driver component. If you prefer, the optional OLE DB providers (IBMDA400 and IBMDASQL) can be installed as well, although I have not tested them for this application. •· Readers who have Visual Studio/BIDS 2005 or one of the Express products can do their best to follow along, although there will be some differences. •· For the data in this tutorial, I've created a DB2 for i schema named ADVWORKS08 (requires V5R3 or higher) that contains a few tables exported from the SQL Server AdventureWorks2008 sample database. AdventureWorks is a free sample database available to SQL Server users and is covered under the Microsoft Public License. You can download the converted sample data for this project.
Creating a Report Project
You should have a copy of Business Intelligence Development Studio in your SQL Server program folder. BIDS (aka Visual Studio) is the tool for building Reporting Services reports. Microsoft is trying hard to allow developers to use one development environment to do all their coding, business intelligence, user interface design, etc. If it is not installed on your development machine, BIDS is found under the Shared Features section of the SQL Server installation process.
To create a SSRS project, open BIDS and choose File > New Project. In the New Project window, choose Business Intelligence Projects in the left pane. In the right pane, the Business Intelligence templates will appear. Choose the Report Server Project Wizard template and assign the project a name and folder location.
You now have a project container that will ultimately house all of your data source and report definitions. As a guideline, I create one SSRS project for each set of related reports. If I need a set of reports for inventory management, a set for sales, and a set for purchasing, I create a separate BIDS project for each. It doesn't have to be done this way as it mainly depends on how you want to deploy your reports, who you want to have access to them, and how you want them grouped for presentation to the user community.
The first thing to add to your SSRS project is a data source. A data source is simply the "connector" that will allow SSRS to query a database, XML file, OLAP server, or whatever. The most popular data source connections are SQL Server, Oracle, ODBC, and OLE DB connectivity, although more options are available. A data source can be local to a report, or it can be defined as a Shared Data Source, which can be re-used by multiple reports. Typically, I create one shared data source to a DB2 ERP system that can be used by several SSRS reports.
Once you've established one or more data sources, it's time to create a report against the sources. Either you can generate a report from scratch by dragging and dropping various toolbox controls onto the report canvas, or you can have a little help by letting the Report Wizard assist in the report generation. There are several controls available to use on a report, including the familiar textbox, label, and image controls. Additionally, SSRS has some specialized reporting controls, including the list, table, matrix, and subreport controls. Incidentally, when using a table, list, or matrix control in SSRS 2008, you're actually using a new underlying control (aka "data region") called a tablix.
Creating a Shared Data Source
Let's start by defining a data source to DB2 for i. To do this, we'll need to add a new "shared data source" to the project (a shared data source can be used by more than one report in a project). Choose Add New Item from the Project menu. The Add New Item window appears:
Figure 3: Create a new data source to DB2 for i. (Click images to enlarge.)
Select the Data Source icon as the template and click Add.
The Shared Data Source Properties window appears.
Figure 4: Choose the data source properties.
Assign a name to the source (I called mine DB2i), select ODBC as the type, and fill in the ODBC connection string to your System i. You can simply modify the string I have below:
Dsn=AS400;dbq=,QTEMP,ADVWORKS08,QGPL;nam=1;cmt=0
If you're using SQL Server Express, you will not have options to connect to an ODBC or OLE DB data source; you can only connect to a local SQL Server! This is where you'll need to be creative in reporting DB2 data. You'll need to either download data from DB2 into SQL Server first or use a programming technique such as linked servers or a CLR table function to get data directly from DB2.
Next, click the Credentials option in the left pane. Specify a user name and password. It's a good idea to create a "reporting user" that has read-only access to data. If security is an issue, you'll want to use a user profile that has no explicit access to the data and then create DB2 stored procedures to retrieve the data on behalf of the user profile using adopted authority.
Figure 5: Change the credentials.
Click OK. The data source is ready to use.
Now we'll create two sample reports based on this connection: a tabular report and a matrix report. Tutorial #2: Creating a Tabular Report with Drill-Down Enabled
This example illustrates how to create a common tabular (i.e., table-based rows and columns) report design--including report breaks--and how to enable drill-down functionality that allows a user to show and hide detail data.
This objective of the report is to retrieve clothing product sales data from the DB2 data source and summarize it by product within product subcategory. Additionally, we'd like to be able to drill into a product to see the order detail underlying the total.
To add a report within BIDS, click Add New Item from the project menu.
Figure 6: Add a new item.
Choose Report Wizard from the available templates and assign your report a name. Notice the extension is .rdl, which stands for report definition layout (RDL) which is actually an XML file. This sample report's name is "AS400 Product Sales by Subcategory.rdl."
When prompted for a data source, choose DB2i (defined in the prior step) from the list. Click Next.
On the Design the Query screen, paste in the following query text that retrieves information from the sales and product tables:
SELECT soh.OrderDate AS Date, 'SO'||VarChar(soh.SalesOrderId) AS ORDER, pps.Name AS Subcat, pp.Name AS Product, SUM(sd.OrderQty) AS Qty, SUM(COALESCE((UnitPrice*((1.0)-UnitPriceDiscount)) FROM SalesPerson sp INNER JOIN SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID INNER JOIN SalesOrderDetail AS sd ON sd.SalesOrderID = soh.SalesOrderID INNER JOIN Product AS pp ON sd.ProductID = pp.ProductID INNER JOIN ProductSubcategory AS pps ON pp.ProductSubcategoryID = pps.ProductSubcategoryID INNER JOIN ProductCategory AS ppc ON ppc.ProductCategoryID = pps.ProductCategoryID GROUP BY ppc.Name, soh.OrderDate, soh.SalesOrderId, pps.Name, pp.Name, soh.SalesPersonID HAVING ppc.Name = 'Clothing' /* HAVING clause is optional */
Figure 7: You've begun to design your query.
Click Next.
On the Select the Report Type wizard step, choose Tabular report. Click Next.
On the Design the Table wizard step, all of the columns from your query appear. Distribute the "Available fields" (i.e., query columns) into the "Display fields" column on the right as follows:
Figure 8: Now, you're designing your table.
The "displayed fields" sections are as follows: •· Page--The page-level fields in a table will be displayed at the top of the table. By default, a page break will be generated whenever the values in the columns specified in this section change. Place the SUBCAT (subcategory) field here because you want to start a new page whenever the subcategory changes. To do this, click the SUBCAT field on the right and then click the Page button to move it to the Page section. •· Group--The group section controls how the detail fields are grouped in the table. For RPG and COBOL folks, a group is synonymous with a control break. Place the PRODUCT column in this section to introduce a product-level break on the report. •· Details--As the name implies, Detail fields are displayed for every row in the data source. The order of these fields is important as the wizard will place the fields on the report from left to right in the order specified. You can always change them after the fact in the report designer, but it's easier to let the wizard do it correctly the first time. Place the DATE, ORDER, QTY, LINETOTAL fields in this section.
Click Next.
On the Choose the Table Layout step, choose the Stepped Layout option and check the Include Subtotals box and the Enable Drilldown box. The stepped vs. blocked layout option is an aesthetic choice, while Include Subtotals will make the wizard do the chore of adding a subtotal line to the table. The Enable Drilldown option will allow the dynamic expansion and contraction of detail rows within a group.
Click Next.
On the Choose the Table Style step, choose a color scheme that appeals to you (or better, is close to your business logo colors, if possible). In this example, I chose Forest, which is mostly varied shades of green.
Click Next.
On this Completion step, the wizard will give you a summary of everything selected during the wizard's collection process. At this point, the wizard is done and you only need to click Finish. Optionally, you can click the checkbox to preview the report.
Formatting the Report
If you chose to preview your report, you can see that the wizard's initial generation is nice, but there are still quite a few rough edges to smooth out. Here's what the initial report looks like in preview mode:
Figure 9: Your initial report looks like this.
Notice that in the BIDS report designer, there are two tabs at the top: Design and Preview. The Design tab allows changes to be made to the report definition, and the Preview tab shows you what the report will look like. Please note that the Design tab must be selected in order to follow the following instructions. When in design mode, you'll see the table control, which should have four rows: Heading, Grouping, Detail and Total.
Initially, the report has the following characteristics: •· The page-level subcategory is shown just below the report title at the top of each page. •· Because drill-down is enabled, the report initially shows only the summary lines by Product (which we chose for the grouping of our table). However, clicking the plus symbol (+) next to each product will expand the report to show the underlying order detail rows. Broadway Systems in Grand Rapids, Michigan. He can be contacted at sqlsleuth@gmail.com.
Here's a sampling of some other articles by this author:
"More AS/400 Client/Server Programming with ADO and VBA" | |||||
| Read More >> | |||||
You must be logged in to view or make comments on this article.