|
Return to Newsletter Contents...
Viewing SQL Server Reporting Services Reports Without
Using Reporting Services
by: Charles Tournear, Sr. BI Consultant, CRCP, MCSE, MCT, MCDBA, MCSD
If you have a web application in which you only want to create a couple of data
reports, then you may not need or want to invest in setting up a SQL Server
Reporting Services server or a Crystal Reports Server to manage the reports.
Instead you can create your reports and run them in Visual Studio.
The first step is to add reports to your web application.
The file extension for a reporting services report is rdl.
When you add a report object to a web application in Visual Studio, it
creates a file with the extension rdlc.
The content of the files is the same.
Creating a report using rdlc files requires a full understanding of how to
create a reporting services report.
So if you have SQL Server Business Intelligence Development Studio you may want
to create a reporting services project and use the wizard capability provided by
the BIDS templates. Once you have
created an rdl file in a reporting services project you could then rename the
report file with the extension rdlc and import the file into your web
application.
This article isn’t going to go over how to create the reports but only how to
run them in a Visual Studio web or windows application.
After importing or creating the report (rdlc) files in the application, you’ll
need to create a form to display the report and add the report viewer control.
In Visual Studio 2008 the MicrosoftReportViewer control is under the
Reporting tab. In Visual Studio
2005, the ReportViewer control is under the data tab.
<html xmlns="http://www.w3.org/1999/xhtml"
>
<head runat="server">
<title>Untitled
Page</title>
</head>
<body>
<form
id="form1"
runat="server">
<div>
<rsweb:ReportViewer
ID="ReportViewer1"
runat="server"
Width="100%"
Font-Names="Verdana"
Font-Size="8pt"
Height="400px">
</rsweb:ReportViewer>
</div>
</form>
</body>
</html>
You could create independent pages for each report and bind the reportviewer
control to a datasource and a report file.
We’re going to look at having a single form with one reportviewer control
and change which report is displayed using code in the code behind page.
In the code behind page for the form that you put the report viewer control on
add a reference to Microsoft.Reporting.WinForms or Microsoft.Reporting.WebForms.
In a reporting services project the connection, command processing and creating
of a dataset are all handled for you by reporting services.
To view the report using the report viewer control without using
reporting services, we have to create the dataset ourselves.
If the report was created using a reporting services project, then the
name of the table in the dataset that you create must be the same as the name of
the dataset or datasets that were originally used to create the rdl file.
Imports
Microsoft.Reporting.WebForms
Partial
Public Class
_Default
Inherits System.Web.UI.Page
Dim rds As
Microsoft.Reporting.WebForms.ReportDataSource
Protected
Sub Page_Load(ByVal
sender As Object,
ByVal e As
System.EventArgs) Handles
Me.Load
Dim ds As
New DataSet
Dim conn As
New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=""C:\Program Files (x86)\Microsoft Office\Office12\1033\FPNWIND.MDB""")
Dim cmd As
New OleDb.OleDbCommand
cmd.CommandText = "SELECT
Categories.CategoryName, Products.ProductName, Products.UnitPrice,
Products.UnitsInStock, Suppliers.CompanyName, Suppliers.ContactName,
Suppliers.Phone FROM ((Products INNER JOIN Categories ON Products.CategoryID =
Categories.CategoryID) INNER JOIN Suppliers ON Products.SupplierID =
Suppliers.SupplierID)"
cmd.Connection = conn
Dim adptr As
New OleDb.OleDbDataAdapter(cmd)
adptr.Fill(ds, "DataSource1")
adptr.Dispose()
ReportViewer1.ProcessingMode = ProcessingMode.Local
Dim rep As
LocalReport = ReportViewer1.LocalReport
rep.ReportPath = "report1.rdlc"
rds = New ReportDataSource
rds.Name = "DataSource1"
rds.Value = ds.Tables("DataSource1")
rep.DataSources.Add(rds)
End Sub
End
Class
In the above code a connection object and command object are used in association
with a dataadapter to fill data into a dataset named ds. The reportviewer
controls localreport property has it’s reportpath property set the the report
file name. A new reportdatasource
object is then created and the name property is set to the the name of the
datatable and the value property is set to point to the dataset table.
This reportdatasource is then added to the datasources property of the
reportviewer’s localreport.
If the report used more than one dataset then the above process would need to be
done for each dataset in the report.
Using the above process you could then create a select case statement or a
function to assign the report and create the dataset for each report you wish to
display.
Go to Top |
Return to Newsletter Contents
|
