Microsoft Reports & ASP .NET website Using Dynamic DataSet
Report Object (.rdlc) file & ASP .NET Project using a Local DataSet as the ReportSource
This article describes how to use a Report object in an aspx webpage using a locally created DataSet (.xsd file type). The DataSet will be populated dynamically using a SqlDataReader to get the data from a query that uses a SELECT statement on a View created in a SQL Server Database (AdventureWorks). The data delivered is Vendor Information with detailed order information.
SSRS Project Background
This article covers creating an SSRS report on an ASP .Net web page using a ReportViewer. This report is similar to the .rdl file that I created for the composite report: SSRS Composite Report. The data set will be created manually and be independent from the report as opposed to an automatically generated DataSet using the Wizard that steps through creating the connection string, selection of data tables, and creation of the Fill and GetData methods. The data table inside of the .xsd file will be populated with data in a call to the AdventureWorks database in code.
SQL Server View
Using SSMS, I created this View in AdventureWorks using the grid. It returns the Vendor ID, Name, and Address and the Order Dates, Ship Dates, and Totals for the purchase orders.
SSRS ASP .Net ReportViewer Creation
Create an ASP website in Visual Studio. Add a New Item to the project of an WebForm. Open up the aspx file and from the Toolbox, add a ReportViewer to the new webform. Do not add a ReportSource or Report to it.
Creating the Dynamic Dataset
Add a new item of a DataSet to the project. Double click to open it up and then Right Click in the blank area to add a DataTable. This one is named dtVendor. Right click on the DataTable and add columns to it. Name them accordingly and make sure that they are the correct DataType. The data types for the amount columns should by System.Decimal. The data type for the Order and Ship Dates should be System.DateTime.
Adding the SSRS Report
Add a new item of a report to the project and double click the new .rdlc file to open it up. The Website DataSource window should have the DataSet that we added to the project. Open the Toolbox tab and drag and drop a List item on to the body of the report. Add a Rectangle object inside of the List object. Finally, drag and drop on of the fields inside of the Rectangle.
Coding the SqlDataReader and Dynamic DataSet
In a separate class file, I add a static function to return a SqlDataReader with the data from the View I created.
public static SqlDataReader GetVendorReport()
{
string scmd = "select VendorID,Name as VendorName, AddressLine1 as Address1, AddressLine2 as " +
" Address2, City, StateProv, PostalCode as ZipCode, OrderDate, ShipDate, subTotal, TotalDue, " +
" TaxAmt, Freight as FreightAmt, PurchaseOrderID, AccountNumber as AcctNumber from " +
" dbo.vw_VendorsPurchaseOrders";
string scon = @"Server=YOURSQLSERVERNAMEHERE;Database=AdventureWorks;Trusted_Connection=Yes;";
SqlConnection cn = new SqlConnection(scon);
SqlCommand cmd = new SqlCommand(scmd, cn);
cmd.CommandType = CommandType.Text;
try
{
cn.Open();
SqlDataReader sr = cmd.ExecuteReader();
return sr;
}
catch (Exception ex)
{
throw ex;
}
}
In the Code Behind file for the WebForm that has the reportviewer, I will add a DataSet called dsVendor and a function to populate the dataset table from the data reader in my static function in the data layer.
DataSet dsVendor;
public void PopulateReport()
{
dsVendor = new VendorDS();
SqlDataReader dr = SqlDataLayer.GetVendorReport();
dsVendor.Tables["dtVendor"].Load(dr);
}
Another function is used to set the dataset (dsVendor) table as the report data source and map the report path. Note that the call to ReportDataSource uses a slightly different name for the Named Dataset that the report is pointing to. When I dragged and dropped the first field from the local DataSet onto the report List object, it linked and renamed the dataset to a hybrid of both the DatasetName_DataTableName (VendorDS_dtVendor). This is the name of the data source that the Report is linked to even though it is the same DataSet. If you were to call the ReportSouce with just "VendorDS", you will get the following error when running the project:
A data source instance has not been supplied for the data source 'VendorDS_dtVendor'.
You can easily verify the DataSetName by clicking on the List object and looking for it in the Properties box.
The code for the binding function in the webform page code
protected void BindReport()
{
string reportPath;
try
{
reportPath = Server.MapPath("vendorsReport.rdlc");
Microsoft.Reporting.WebForms.ReportDataSource rds=new
Microsoft.Reporting.WebForms.ReportDataSource("VendorDS_dtVendor", dsVendor.Tables["dtVendor"]);
ReportViewer1.LocalReport.ReportPath = reportPath;
ReportViewer1.LocalReport.DataSources.Clear();
ReportViewer1.LocalReport.DataSources.Add(rds);
this.ReportViewer1.LocalReport.Refresh();
}
catch (Exception e)
{
throw e;
}
}
Finally, add the 2 functions to the Page_Load section.
protected void Page_Load(object sender, EventArgs e)
{
PopulateReport();
BindReport();
}
Report Formatting
Add the rest of the fields to the report and format the font size and textbox sizing. I added the Page Header and Footers to identify what the report was about and the date it was run as well as the Page Count - should someone want to Export it to Excel or PDF.
All of the address data is in a single textbox and I added Chr(13) & Chr(10) in the box to get line breaks between the addresses. I also added an IIF statement to prevent any blank lines due to a Null Address2 field.
Additional formatting for the Vendor field, I added some text to let the end user know that the value in the box was for.
Formatting the Date for the OrderDate and ShipDate fields. I don't want the time to show on the report.
Formatting for the dollar amount fields as the number is just a decimal with no $ and no commas and several digits after the ".".
Grouping the SSRS Report items
Grouping List Box that surrounds all of the data by right clicking and selecting Properties. Then click Edit Details Group and then on the General tab, select Fields!VendorID.Value from the Group On box under Expression.
The table requires a Group, too. Right Click on the table in the report on the Data row and select Edit Group. Select the expression Fields!OrderDate.Value to group on for the table.
When I run the project, the report comes up formatted as expected.
Additional SSRS WebPage Notes
The dynamic nature of populating the data in this way is helpful if you have multiple client databases and need to extract the same report for each database instance. It would be fairly easy to then build a connection string dynamically based on a parameter passed in the QueryString and then populate client data from the server into this DataSet.