Getting starting with SQL Server Reporting services without using the Wizard

Introduction

In my previous article, we created a SQL server report using the wizard. However, the wizard has it's own limitations towards the customization of reports and stuff. Therefore, it would be awesome if the report be generated without use of wizards. Lets get started with the same.

Getting started

Once you have installed SQL server reporting services on your computer, open the Visual Studio IDE to create a new project via Files->New Project

Select Business Intelligence Project from the Project Types and Report Project from the Template. Remember the following points whenever you want to create a report.

1. Get yourself a good database designer: If you have a good data model, a single select statement will ease your problem for complex column selection. This inturn will provide faster throughput with cleaner SQL statements.

2. Create simpler SQL statement[use SPs for complex SQL statements]: If you remember, in the last tutorial, we created a query, followed by which the data were to be retrieved. Manage your joins appropriately or directly use a stored procedure for data retrieval rather than creation of complex SQL statements. And as you know the Stored proedure will be compiled code, so performance will thereby be enhanced.

3. Get a good hang over the display of data: The designer will help you with toolbars for creation of tables, textboxes, etc. Based on what output you desire, the look and feel should be properly matched. This includes creation of hidden columns using variant expression fields; plus using alternating color styles as we would use for datagrids in ASP.net.

 If all the above points are in place, there is nothing that can hamper your system[the roots are firm]. Lets get back to the creation process. Specify the name and location of the report in the textboxes provided and click OK.

In the Solution Explorer, 2 folders will be created by default. 1.Shared datasources 2. Reports

You can add a report to the project by selecting the Reports folder. Right click -> Add new report. You can also do so by Add->New Item

Using this, you will be prompted with an Add New Item dialog box, wherein you can specify either a Report or a Report Wizard.

On click of Open, you will be prompted with the Report Designer. The Report Designer, as discussed previously, contains 3 tabs, namely Data, Layout and Preview. The Data tab is used to provide the Select statement/ Stored procedures to be run in order to get in the data.

Add the New dataset on selection of dataset from the dropdown.

Select the dataset you want to use with command type is Text. You can select Command type as Stored procedure for using the SP. We'll discuss this later.

You can add a table from the database selected, by right-clicking on the pane and selecting Add Table from the list.

Following is the output generated on adding tables.

Run the SQL statement by clicking on the '!' button.

Next comes the designing of the page. Switch the tab to the layout and select the toolbar to add in a table.

By default, a table will be created with 3 columns along with the header,detail and footer rows.

Select the fields tab and drag the required fields into the required cell. On dragging fields to the details cell, the corresponding header will get filled with the relevant values.

Next comes the look and feel of the table. If we select the properties of the table or rather on click of F4 after selecting the table, we can select variant options.

Here below, I've made changes to the Font, Border styles, etc of the table.

This is the last step and your report is ready to be publised .

Click on the preview tab to view the look and feel of the report. In order to deploy and view the report on the web page, click on Ctr+F5 and you are done with the creation of a report without the use of a report wizard.

Please let me know incase of any problem during creation.