22 April 2011

SSRS: Wizarding a Report - First Magic Class

    One year back I started a set of posts on SSIS showing how to create a package with ‘SQL Server Import and Export Wizard’ using as source a SQL Server, respectively Oracle database, in a third post showing how to use the Data Flow Task. The “wizarding” thematic was based on the fact the respective posts were showing how to make use of the wizards built within SQL Server and Visual Studio in order to create a basic export package. Therefore the posts were targeting mainly beginners, my intention at that time was to use them as a starting point in showing various objects and techniques. I also intended to start a set of similar posts on SSRS, so here I am, in this first post showing how to use the Report Wizard to create a report in BIDS (SQL Server Business Intelligence Development Studio) using a SQL Server database.

    This short tutorial uses a query based on AdventureWorks2008 sample database and considers that your Reporting Services instance is installed on the same computer with the database. For simplicity, I focused on the minimal information required in order to built a simple report, following to cover some of themes in detail in other posts. The tutorial can be used together with the information provided in MSDN, see Creating a Report Using Report Wizard and Report Layout How-to Topics sections.

Step 1: Create the Query
    Before creating a report of any type or platform, it’s recommended to create and stabilize the query on which the report is based. For simplification let’s use a query based on Sales.vIndividualCustomer view:
-- Customer Addresses
SELECT SIC.Title
, SIC.FirstName

, SIC.LastName

, SIC.AddressLine1

, SIC.City

, SIC.PostalCode

, SIC.CountryRegionName

, SIC.PhoneNumber

, SIC.EmailAddress

FROM Sales.vIndividualCustomer SIC


Step 2: Create the Project
   Launch the BIDS from Windows Menu, create a new Project (File/New/Project) by using the “Report Server Project Wizard” Template, give the Project an appropriate Name and other related information.
SSRS Tutorial New Project
   This will open the Report Wizard, and unless you have chosen previously not to show this step, it will appear a tab in which are shown the step that will be performed:
- Select a data source from which to retrieve data
- Design a query to execute against the data source
- Choose the type of report you want to create
- Specify the basic layout of the report
- Specify the formatting for the report
- Select the report type, choose tabular

Step 3: Create/select the Data Source
    Creating a Data Source pointing to the local server isn’t complicated at all, all you have to do is to give your data source a meaningful name (e.g. Adventure Works) and then define connection’s properties by selecting the “Server name” and database’s name, in this case AdventureWorks2008.

SSRS Tutorial Connection Properties 
    Test the connection, just to be sure that everything works. In the end your data source might look like this:
SSRS Tutorial Select Data Source
    You can define your data source as shared by clicking the “Make this a shared data source” checkbox, allowing you thus to reuse the respective data source between several projects.

Step 4: Provide the Query
   Because the query for our report was created beforehand, is enough to copy it in “Query string” textbox. We could have used the “Query Builder” to built the query, though a tool like SSMS (SQL Server Management Studio) can be considered a better choice for query design. As pointed above, it’s recommended to built the query and stabilize its logic before starting the work on the actual report. SSRS Tutorial Design the Query
Step 5: Create the Report
   Creating a report supposes choosing a Report Type (Tabular vs. Matrix), the level at which the fields will be displayed (page, group or details), and the Table Style. For this tutorial choose a Tabular type and, as no grouping is needed, in “Design the Table” step choose all the Available fields and drag-and-drop them in “Details”, the bottommost list from “Displayed fields” section.
SSRS Tutorial Design the Table
    In Choose the Table Style go with the first option (e.g. Slate), though it’s up to you which one of the styles you prefer.

Step 6: Check the Report
    You can deploy the report if your report server is already configured, however in order to test the report you don’t have to go that far because you can test the report directly in BIDS. So you can go with the actual settings:
SSRS Tutorial Choose the Deployment Location
    In the last step provide a meaningful name for the Report (e.g. Customer Addresses) and here is the report in Design mode:
SSRS Tutorial Design
   By clicking on “Preview” tab you can see how the actual report will look like:
SSRS Tutorial Preview 
   Now I would recommend you to check what objects the wizard has created and what properties are used. Just play with the layout, observe the behavior and what the documentation says. There are plenty of tutorials on the web, so don't be afraid to search further.

No comments: