Business Intelligence with Microsoft SQL Server Reporting Services - Part 1 By Adnan Masood Programming Reporting Services Without further ado, I think it's about time to show you how to create our first report in SQL Server Reporting Services. Source code can be downloaded from 15seconds website; you'd need Northwind database to run the samples. This is a step by step introduction to create a report. Some of the physical steps are combined to avoid mundane tasks you are already familiar with. Step 1: Start a Business Intelligence Project in Visual Studio.NET You can either use Wizard or Report Project to create a report. We will be using Report Wizard here. Step 2: Create a data source connection string for Northwind database in SQL Server. Create a data source for report to retrieve data from Northwind database. The database shown in the example is local but you can use a remote connection for it as well. (sa empty password is not a good security practice, its for demo purposes only). Step 3: Design the query to display the data in report. Write or (design in the Query Builder) the SQL query to retrieve data from database into the report. I'm selecting all records from table "orders" for display in the report. Step 4: Select Report Type & Style This step is to chose the report type (tabular or Matrix) and table style for the report. Step 5: Select Deployment Location and Review the Report This step is to choose the deployment location i.e. the web server location (default is http://localhost/ReportServer) for report. Finally you can review all your selections and can go back and change them if needed. And voila, finally here is our rendered "Orders report". Within five wizard steps, a formatted data report is created which can be exported in a wide variety of formats, scheduled to be emailed or put on ftp and can be accessed over HTTP like any other webpage and supports built-in security. Figure: Orders Report in Report Designer Preview mode in VS.NET 2003 IDE. Report designer has three different views for a report; Data view, Layout view and Preview. Data view provides the database selection and query writing support for reports so if someone didn't want to use the wizard interface to create and publish reports, can use the data view to create datasets and define custom queries. Figure: Orders Report in Data view Similarly layout view provides the interface to set of tools for creating the elements on a report. Fields can be dragged and dropped from Fields toolbar in layout view and it will populate the table or matrix.will populate Figure: Report Designer Toolbox Figure: Orders Report in Layout View Expression editor is another useful tool provided with Report Manager. As an enhancement to this basic Orders report, I'm adding a header to it which will show the report generation time, name and number of pages in this report. This meta data is provided as global constants which are added into text boxes in the header pane. Other useful global variables for instance Execution time, Report Server URL etc can be seen in the screenshot below. Figure: Report Designer Expression Editor. However we've seen the report in Preview mode, its time to execute and view it in browser. After pressing F5 (or directly accessing the URI of report server), you'll see an animated gif saying report is being generated. The following report shows the time generated and report name along with pages as defined above using global variables. As you can see in the query string below, it defines the set of parameters from report. These parameters serve a useful purpose as you'll see further in this article. Figure: Order Report rendered in IE The drop down menu shows the export formats available for this report. As discussed earlier, Reporting services can export in HTML, Excel, War, PDF, TIFF, CSV and XML format as well you can define custom format specifier but will have to code wrapper for it. Report Manager Report Manager is the interface provided by Reporting server to manage reports. Microsoft is promoting web based management consoles as you can see ASP.NET 2.0 is also equipped with a website administration and management console. Using the Report Manager interface, depending on their access privileges, one can upload report files (RDL), set subscriptions to receive reports, create a new data source and add / modify user report access roles. As shown in figure above, OrdersReport is published by Report Designer in Visual Studio.NET for us. One can also manually upload an RDL file and it will be shown in Report Manager. Upon clicking the Orders Report report manager executes and show the output of report. It provides us four tabs with a report i.e. View, Properties, History and subscription. One can perform a wide variety of tasks with these reporting tabs. For instance you can setup a new role for an NT group of a single user. There are four roles in general; Browser, Content Manager, My Reports and Publisher as shown in the figure below. From the history tab, one can set the execution snap shots and further history settings. Probably the most important tab in report property is Data Source tag. It helps setting up the data source used by the report and impersonation settings as well. Connection type, string, credentials and further details required to execute a report are stored via this interface. RDL stands for Report Definition Language which is an XML based reporting definition initiative by Microsoft to share the different reporting formats. As defined by Microsoft Report Definition Language (RDL) is an XML-based schema for defining reports. The goal of RDL is to promote the interoperability of commercial reporting products by defining a common schema that allows interchange of report definitions. To encourage interoperability, RDL includes the notion of compliance levels that products may choose to support. http://www.microsoft.com/sql/reporting/techinfo/rdlspec.asp RDL Specs can be downloaded from the link above. One can manually upload the RDL file using the report manager's general tab and then by update link in the properties as shown in the figure below. Like code-behind files, the report manager in VS.NET also supports the editing of RDL file generated during the visual exercise performed above. In the figure below, you'd see the query, fields and data source selected in the XML file. Business Intelligence with Microsoft SQL Server Reporting Services - Part 2 By Adnan Masood Parameterized Reports Not all reports are static, most of the enterprise reports are data driven or parameterized. In the following section I'm adding two variables, startdate and enddate in the Orders Report. These arguments will be used to filter the data being queried from orders table. The parameters are regarded as query parameters and as you can see in the query window below, these parameters are passed in the SQL query string. Upon execution, the Report Designer preview asks for parameters values. When executing from a browser, it shows the parameters toolbar with default values in it. You can change the values in the parameters toolbar. Parameters can be set to null, defaulted through report manager and can also be data driven. Calling Stored Procedures from Reports If your enterprise reporting system policy dictates business logic to be encapsulated in stored procedures, reporting services are there to rescue you again. SQL server stored procedure provides an efficient way of processing compiled SQL statements and can be used by reporting services to build reports on top of their result set. In the following two examples, I'll be using stored procedures from northwind database and display their result in reports. The dataset creation process is similar to one specified above, however this time command type is selected as stored procedure. This first example is simpler; it uses the stored procedure "Ten Most Expensive Products" and use it as data source for its tabular data binding. The process of using stored procedure as data source can be seen below. Figure: Creating a data source from stored procedure via data tab in reporting services IDE for visual studio.NET This binding exposes collection of two fields (Ten Most Expensive Products, UnitPrice) which can be used as fields in the report table. After adding these fields in the recurring row, I've set the headings in header and we are all set to preview the report. Figure: Adding the fields in report table. Upon execution, the report will look like the following. Figure: Report preview which uses Ten Most Expensive Products as data source. Second example is a bit complex as it uses stored procedure as well as SQL to retrieve the parameters for combo box. The intent of this report is to provide sale by the category of products which includes beverages, produce, seafood, etc. Every product is associated with a category and the stored procedure "SalesByCategory" takes category as input and provide the sales breakdown of products in that category. However, to achieve this, we would need to display the list of categories in the report. Here is the step by step diagrammatic flow of how to achieve this goal. Figure: Creating a stored procedure based data set. First, as its name depicts the sales by category stored procedure provides the sales history on the basis of product category. To provide interactivity in the report, we need to acquire the data driven parameters i.e. list of categories from database. This can easily be achieved from report parameters window as shown in this figure below. The report is reading category name, order year and their corresponding values from database as parameters. Figure: Report Parameters being retrieved from database (step 1) Figure: Assigning default Report Parameters. (step 2) Figure: Assigning the corresponding value fields for report parameters. (step 3) Finally the report could be seen as follows. The Category name and order are drop down lists for selection and the report takes these parameters and pass them on the salesbycategory stored procedure for the required result set. Figure: Report preview; combo boxes for category details This whole procedure as demonstrated with GUI above can be represented in RDL as follows. <DataSources> <DataSource Name="Northwind"> <rd:DataSourceID>1a755f4d-006e-42ce-804b-852dc13c6840</rd:DataSourceID> <ConnectionProperties> <DataProvider>SQL</DataProvider> <ConnectString>initial catalog=Northwind</ConnectString> </ConnectionProperties> </DataSource> </DataSources> Listing: The RDL segment for data source. Notice the data source id as guid to uniquely identify the data source <DataSets> <DataSet Name="Northwind"> <Fields> <Field Name="ProductName"> <DataField>ProductName</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="TotalPurchase"> <DataField>TotalPurchase</DataField> <rd:TypeName>System.Decimal</rd:TypeName> </Field> </Fields> <Query> <DataSourceName>Northwind</DataSourceName> <CommandType>StoredProcedure</CommandType> <CommandText>SalesByCategory</CommandText> <QueryParameters> <QueryParameter Name="@CategoryName"> <Value>=Parameters!CategoryName.Value</Value> </QueryParameter> <QueryParameter Name="@OrdYear"> <Value>=Parameters!OrdYear.Value</Value> </QueryParameter> </QueryParameters> <rd:UseGenericDesigner>true</rd:UseGenericDesigner> </Query> </DataSet> Listing: The RDL segment for dataset. Query parameters are specified in this segment. <DataSet Name="Categories"> <Fields> <Field Name="CategoryID"> <DataField>CategoryID</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="CategoryName"> <DataField>CategoryName</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="Description"> <DataField>Description</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="Picture"> <DataField>Picture</DataField> <rd:TypeName>System.Byte</rd:TypeName> </Field> </Fields> <Query> <DataSourceName>Northwind</DataSourceName> <CommandText>SELECT * FROM CATEGORIES</CommandText> <rd:UseGenericDesigner>true</rd:UseGenericDesigner> </Query> </DataSet> </DataSets> Listing: The RDL segment for retrieving parameters. It includes the Command text and field definitions. <ReportParameters> <ReportParameter Name="CategoryName"> <DataType>String</DataType> <DefaultValue> <DataSetReference> <DataSetName>Categories</DataSetName> <ValueField>CategoryName</ValueField> </DataSetReference> </DefaultValue> <Prompt>CategoryName</Prompt> <ValidValues> <DataSetReference> <DataSetName>Categories</DataSetName> <ValueField>CategoryName</ValueField> <LabelField>CategoryName</LabelField> </DataSetReference> </ValidValues> </ReportParameter> <ReportParameter Name="OrdYear"> <DataType>String</DataType> <DefaultValue> <Values> <Value>1998</Value> </Values> </DefaultValue> <Prompt>OrdYear</Prompt> <ValidValues> <ParameterValues> <ParameterValue> <Value>1996</Value> </ParameterValue> <ParameterValue> <Value>1997</Value> </ParameterValue> <ParameterValue> <Value>1998</Value> </ParameterValue> </ParameterValues> </ValidValues> </ReportParameter> </ReportParameters> Listing: The RDL segment for fixed Parameters. It specifies the label, data value data set reference along with parameter (Ordyear) values and default values. Reporting services opens up a wide arena for you for data binding. The above examples demonstrate how can we use stored procedures in reports; however its your choice to use dynamic SQL, business objects (custom assemblies), stored procedures or any other custom data source for your report. The Report Viewer Control The report viewer control is an excellent intrinsic tool provided to facilitate report viewing in hosted client web or desktop application. This control makes URL based access a breeze and reporting integration to any existing .NET app a charm. This control comes with code both in VB.NET and C#. This control is available %System Root%\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Applications\ReportViewer\vb. You may want to build the solution to incorporate the assembly in your web / desktop application project. In rest of this section, I'll demonstrate how you can use this control to create a sample web application which supports reporting via URL access. The source code of this project is available as ParametricReporting.csproj in the source code zip file. First of all, in order to add this control in your web application, go to tools -> Add/Remove Toolbox Items. In the Customize Toolbar dialog box click the .NET Framework Components tab, browse to the location specified above and select ReportViewer.dll. Report Viewer component would be added to VS.NET toolbox and you can add it into your web form. This step is shown in the figure below. Figure: Adding the report viewer control in the web form. This is a simple web application which uses two calendar controls to select starting and ending date for orders report. The purpose of this example is to demonstrate how easily reports can be embedded in a web application. It also shows that reporting services functionality can easily be extended by traditional applications and their controls. Figure: Report viewer control added in web form. VS.NET IDE With couple of lines of code to pass the orders details parameters to the Report viewer control, the following report can be achieved. Figure: Report rendered by using report viewer control. With just modifying the rendering format to PDF, I can get the report in PDF within the report viewer control. This makes it a very handy choice for showing supporting reports and business intelligence associations within interactive web and desktop applications. Figure: Report rendered as PDF by using the URL access and report viewer control.