Click here to Skip to main content
11,409,854 members (63,745 online)
Click here to Skip to main content

Getting starting with SQL Server Reporting services without using the Wizard

, 8 Apr 2006
Rate this:
Please Sign up or sign in to vote.
This article helps you create a report without use of the reporting wizard.
<!-- Article Starts - DO NOT ADD HTML/BODY START TAGS-->

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

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

Add new report

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

New report

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.

Data Designer

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

New Dataset

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.

Set command type

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

Add Table

Following is the output generated on adding tables.

output

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

Run

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

Toolbar

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

Table

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.

Table

Table

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.

Table Properties

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

Changing attributes 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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Soshan is a software engineer, currently working in variant projects. She does her programming in stuff like Assembler, C++, MFC and lots of web- and database stuff and now uses ASP.NET and C# extensively, too.

In her free time, she works in animation stuffs with extensive usage of Adobe Photoshop and CorelDraw. Driving racer cars is her current craze.

Whenever she gets bored, her interests goes on to networking and hacking-ethical. Has designed an ip-filter for firewall with a group of friends.

Comments and Discussions

 
GeneralThere is an easier and quicker way PinmemberPaulMcKenzie27-Jul-09 13:49 
QuestionWhat about using StoredProcedure with parameter PinmemberS.A.A.A.A17-Jun-07 7:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150414.5 | Last Updated 8 Apr 2006
Article Copyright 2006 by Soshan Fernandes
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid