Click here to Skip to main content
11,492,291 members (66,236 online)
Click here to Skip to main content

Creating Reports with SQL Reporting Service and Visual Studio .NET

, 9 May 2004 664K 4.7K 195
Rate this:
Please Sign up or sign in to vote.
Creating Reports with SQL Reporting Service and Visual Studio .NET

Introduction

The following article will give you a quick start on how to use the new reporting service of Microsoft inside your ASP.NET Application. It is relatively very easy to use reporting services, however it is a bit different from what we are used to in Crystal reports.

Background

Reporting service is basically a reporting server that uses SQL server as its backend database, all reports are deployed on the reporting server and from their you can access any reports you have access rights to. The basic idea is to have a single location where all reports are deployed, and provides a single point of access, this created a very flexible environment to deploy your reports over the enterprise. The idea is a very similar to Crystal Reports Enterprise Reporting.

Requirements:

You will need the following tools before installing the reporting service, those tools are needed for development of reports for deployment you will need exactly the same environment without Visual Studio .NET

  • SQL Server 2000 with SP3
  • IIS 5.0 or 6.0
  • Visual Studio .NET

Accessing Report Server Management Interface:

You can start by accessing your reporting service by going to http://localhost/reports this is where you can manage your reporting service. You can view reports and other information directly from this web interface, manage subscriptions, security, data sources and other. Mostly we won't be using it in this article except for viewing reports.

The Reporting Service Web Management provides browsing folders that contain reports, data source names that you have deployed. This tool provides viewing of reports, however for developing reports you must have Visual Studio .NET

The above figure shows the report server windows service, as you can see it must be running to be able to access, view and deploy reports from your development tool

As I write this article I heard from Microsoft that they have bought a tool that can provide creating reports directly from the reporting service web interface, I do not have any information when it will be released but hopefully soon.

Developing Your Own Reports

1. Creating Your First Report

First you create a new project, and select Report Project this will create a reporting service project. From here you will find two folders shared data sources, and reports. Shared data sources is one very interesting feature, this is where your data source for your reports. You can have more than 1 shared data source or even a single data source for every report, however it wouldn't be a good idea to repeat the same data source twice if you are using the same database.

2. Creating a Shared Data Source

Here just create a shared data source selecting your SQL server, Northwind database, we will be using basically the Northwind database to build a very simple report to list all our customers.

3. Selecting Data

Before selecting the data for your report, just click on new report and choose the wizard, it will take you step by step. First select the data source that you have just created, then select the table, choose any table you like, in this example I chose the customer table. Then select tabular, and then select all data fields into the detail list box. After you are done, go to the Data Tab of your report you will find table customer, with all fields select here you can alter the table or fields you want to select in your report, just as you are used to when creating a view in SQL Server.

4. Selecting Design

After you are done selecting the data go to, report designer select the layout tab in your report, as you can see in the left toolbox you can use any of the report control to enhance your report functionality and design. You can include charts, images, matrix, etc.. after you're done lets preview the report.

5. Previewing Report

One of the features I love about the reporting service, is the ability to preview your report before deployment, here you can view your report as if you are in the deployment environment.

6. Deploying Report on Report Service

The deployment part is tricky now, you do not just include a reporting customer control in your ASP.NET page and that's it, well you have to first deploy them on your reporting service Server. Ok now as we said all your reports are developed on Visual Studio .NET then they are deploying to a reporting server, either on your machine, intranet, or internet anywhere you want them as long you have access rights to that reporting server. To start deployment right click your application and select properties, the following window will appear. You will find the property "OverwriteDataSources" to be false, make it to true, then select the target folder, this can be anything you like. Then enter the location of your reporting server here it is localhost however it can be a domain, IP address or any location you want as long as reporting service is installed to it. After you are done press F5 or right click the project and select deploy, the minute this is done your reports are deployed on your reporting server.

7. Viewing Report from Report Service

As I said now your report is deployed on the reporting server you can access it directly by going to http://localhost/reports , select the folder you installed the report in then select your report. The report should appear like the one shown below:

8. Including ReportViewer Custom Control in your ASP.NET Application

Now the tricky part on how to include this report in your ASP.NET application, here you will need to use a custom control however, Microsoft does not provide a custom control like crystal report viewer custom control, in fact you will find it deployed in the samples directory of Reporting service. The custom control is located at

C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Applications\ReportViewer

You can just go and open that project and compile it and use the ReportViewer DLL in your ASP.NET application. This can be done by opening your toolbox, then click Add/remove and click browse and select the ReportViewer.DLL I included the source and the DLL in the source in case you cannot find it or you didn't install the sample applications of reporting service. Anyway after selecting the DLL you have to select the custom control from the list as shown below:

You will find the name of the Custom Control ReportViewer "Microsoft Sample Report Viewer Application"

When you are done, just include the custom control in your ASP.NET page and change the following properties.

  • First you have to select the report path and this should be something like :- My Reports/Report1 - exactly the sample folder you deployed your reports in.
  • Second you have to edit the ServerURL and here you enter your reporting service location http://localhost/reportserver/ this is the reporting server location, while /reports is the report server web management so take care not to get mixed up.

Once both are done, you can start viewing your report by accessing your ASP.NET web page.

9. Viewing your ASP.NET Application, including your Report

Now enter the location of your web application and choose the asp.net page that contains the custom control, and bingo here you find your report as shown below. See how easy

Conclusion

Now you can create as much reports as you want and use the custom control to view them by just changing their reportpath this can be done at runtime. If you need any extra help email me.

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

Software Development Manager / Architect.

Comments and Discussions

 
Questionabout user viewing report Pin
Member 1137643719-Jan-15 22:14
memberMember 1137643719-Jan-15 22:14 
QuestionUnable to publish Report parts through Report Builder Pin
Member 1096307225-Jul-14 1:12
memberMember 1096307225-Jul-14 1:12 
QuestionSSRS Parameter Customization Pin
Member 1035913224-Oct-13 21:42
memberMember 1035913224-Oct-13 21:42 
Questionnice Pin
spark4future19-Feb-13 0:50
memberspark4future19-Feb-13 0:50 
GeneralMy vote of 5 Pin
muhammad hafizi3-Dec-12 15:38
membermuhammad hafizi3-Dec-12 15:38 
GeneralMy vote of 5 Pin
yusra_764425-Nov-12 20:16
memberyusra_764425-Nov-12 20:16 
GeneralMy vote of 4 Pin
pradiprenushe26-Aug-12 22:28
memberpradiprenushe26-Aug-12 22:28 
QuestionRegarding Custom Template Pin
anup srivastava15-Jul-12 23:02
memberanup srivastava15-Jul-12 23:02 
GeneralMy vote of 5 Pin
aofeng310-Jul-12 21:20
memberaofeng310-Jul-12 21:20 
Questionmultiple page report Pin
ethan hawlke10-Jul-12 6:50
memberethan hawlke10-Jul-12 6:50 
Questionhi Pin
Gaurav Vohra7-Aug-11 21:22
memberGaurav Vohra7-Aug-11 21:22 
QuestionTrying to set report path in reportviewer properties Pin
mazer24229-Jul-11 15:54
membermazer24229-Jul-11 15:54 
GeneralHandy reporting software Pin
iamjtsjl5-Jan-11 6:10
memberiamjtsjl5-Jan-11 6:10 
QuestionParameter Challenges Pin
swninetails11-Mar-09 11:52
memberswninetails11-Mar-09 11:52 
I have a set of reports which I am integrating into my .net 2.0 application. I have figured out how to get the reportviewer working, how to set parameters and how to render the reports. I need to be able to condition some of the parameters based on a users permission access. I am using the reportviewer for the interface, this has the export, find, view report buttons, etc...

When the report first renders I can set parameters as I desire, but once the user changes visible parameters and clicks the View Report button my hidden parameters go back to their default value.

For instance I have three parameters on one report (Department,Assigned,Status). When the user chooses to execute the report, I want to programatically set the Department value and adjust the visible attribute to false, but allow the user to use the Assigned and Status as desired. This I can do visually right now - Confused | :confused: BUT when they click View Report, the Department parameter gets reset to the default value and not what I had it set to. I also can not seem to figure out how to trigger the click of the View Report button to allow me to reset the parameter value either at render or load or which ever event it should be.


Anyone have any suggestions? Is this an all or nothing, or can I set some parameters and allow users to set others and still use the reportviewer? I hope this makes sense... Sigh | :sigh:
GeneralSSRS Chart Control Pin
T. Ravindran12-Aug-08 6:15
memberT. Ravindran12-Aug-08 6:15 
GeneralAsk for username and password when calling the report Pin
sammak1687-Jan-08 20:57
membersammak1687-Jan-08 20:57 
QuestionHow can I gernerate PDF format report by using SQL Reporting Service dll Pin
Shinde Sachin16-Oct-07 0:08
memberShinde Sachin16-Oct-07 0:08 
GeneralWYSIWIG Pin
CourtneyB20-Jul-07 9:01
memberCourtneyB20-Jul-07 9:01 
GeneralMultiple parametes from a Multi select listbox to Reporting services Pin
md riyasath ali24-Feb-07 21:32
membermd riyasath ali24-Feb-07 21:32 
GeneralNothing was displayed on web page ! Pin
naimish_hit5-Jan-07 3:53
membernaimish_hit5-Jan-07 3:53 
GeneralHere's the answer to all parameter questions Pin
wOOk?es20-Dec-06 3:14
memberwOOk?es20-Dec-06 3:14 
QuestionPassing Parameter from ASP.NET Pin
Nick Hatzis10-Oct-06 22:17
memberNick Hatzis10-Oct-06 22:17 
AnswerRe: Passing Parameter from ASP.NET Pin
sunil_pune21-Nov-06 18:31
membersunil_pune21-Nov-06 18:31 
AnswerRe: Passing Parameter from ASP.NET Pin
sasikanth198226-Nov-06 23:31
membersasikanth198226-Nov-06 23:31 
QuestionManaging data (Urgent) Pin
PlescaSorin14-Aug-06 20:52
memberPlescaSorin14-Aug-06 20:52 
QuestionReport Viewer Pin
dreadjr7-Aug-06 12:05
memberdreadjr7-Aug-06 12:05 
QuestionProblem in Updating DataSource at ReportServer Pin
Muhammad Ghufran18-Jul-06 22:11
memberMuhammad Ghufran18-Jul-06 22:11 
GeneralProblem with Paramters Pin
Tushar Kothari17-Jul-06 1:12
memberTushar Kothari17-Jul-06 1:12 
AnswerRe: Problem with Paramters Pin
Muhammad Ghufran18-Jul-06 21:57
memberMuhammad Ghufran18-Jul-06 21:57 
General"Select All" Parameter HELP! Pin
TBReil25-May-06 9:07
memberTBReil25-May-06 9:07 
GeneralSQL Sudio.net 2003 with SQL Servier 2005 Reporting Services Pin
shamali18-May-06 10:27
membershamali18-May-06 10:27 
GeneralI dont have "Business and Intelligent Project" type.. Pin
ZarrinPour16-Mar-06 22:07
memberZarrinPour16-Mar-06 22:07 
AnswerRe: I dont have "Business and Intelligent Project" type.. Pin
Muhammad Ghufran30-Mar-06 19:42
memberMuhammad Ghufran30-Mar-06 19:42 
GeneralRe: I dont have "Business and Intelligent Project" type.. Pin
cwleung2217-Sep-06 21:27
membercwleung2217-Sep-06 21:27 
GeneralAccessing problem to Report Server from ASP.Net Pin
adb96hxo16-Mar-06 0:32
memberadb96hxo16-Mar-06 0:32 
GeneralReportviewer.dll Pin
dvm170222-Feb-06 15:50
memberdvm170222-Feb-06 15:50 
GeneralRe: Reportviewer.dll Pin
Muhammad Ghufran24-Feb-06 23:02
memberMuhammad Ghufran24-Feb-06 23:02 
GeneralRe: Reportviewer.dll Pin
dvm170226-Feb-06 11:47
memberdvm170226-Feb-06 11:47 
GeneralRe: Reportviewer.dll Pin
shamali18-May-06 11:46
membershamali18-May-06 11:46 
GeneralReport Refreshing Problem Pin
Muhammad Ghufran20-Feb-06 21:25
memberMuhammad Ghufran20-Feb-06 21:25 
GeneralRe: Report Refreshing Problem Pin
derekadk20-Feb-06 21:59
memberderekadk20-Feb-06 21:59 
GeneralRe: Report Refreshing Problem Pin
Muhammad Ghufran20-Feb-06 23:13
memberMuhammad Ghufran20-Feb-06 23:13 
GeneralNice "quickpath" article. Pin
wconnors16-Feb-06 17:55
memberwconnors16-Feb-06 17:55 
GeneralFailed to open a rowset. Pin
apurv iit13-Feb-06 6:00
memberapurv iit13-Feb-06 6:00 
GeneralRe: Paramter Problems Pin
Dipali Doke21-Dec-05 9:19
memberDipali Doke21-Dec-05 9:19 
GeneralRe: Paramter Problems Pin
NitinP28-Jan-06 1:53
memberNitinP28-Jan-06 1:53 
GeneralRe: Paramter Problems Pin
Dipali Doke20-Feb-06 1:38
memberDipali Doke20-Feb-06 1:38 
GeneralRe: Paramter Problems Pin
ra2036818-Oct-06 0:40
memberra2036818-Oct-06 0:40 
GeneralRe: Paramter Problems Pin
Muhammad Ghufran20-Feb-06 2:13
memberMuhammad Ghufran20-Feb-06 2:13 
GeneralRe: Paramter Problems Pin
wodeh29-Nov-06 23:53
memberwodeh29-Nov-06 23:53 

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
Web01 | 2.8.150520.1 | Last Updated 10 May 2004
Article Copyright 2004 by Akram Hussein
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid