Skip to content

Creating an online list of users in a Database using Report Wizard

February 8, 2012

Today, I had added and reworked on a piece of useful code to generate reports using the Report Wizard and RDLC (Client Report Definition). They are easy once you get the hang of them.

I had reused a dataset called session.xsd within the App_Code folder which you can generate as part of the Report Wizard process and use the data columns within that DataSet to display your data onto the report. ALso I have used the term DefinedDataType but that is the defined class type using NHiberate that populates your IList containing and matching those columns in the session.xsd file.

Also note that the data is populated using the method GenerateData(), you ccan check for null data using e.g. row[“EndDateTime”] = DBNull.Value;, this is a requirement if null data is present in the SQL database as NHibernate will throw an exception for not handling the null value.

As you can see that the bulk of GenerateData() is defining a DataSet object and populating the dataobject fromn sessionsCollection using the Add method of DataSet like so: –  sessionData.Columns.Add(column, typeof(object));

Lastly in the method RenderReport(), the majority of the code is defining the local report and defining the document settings as a long string with the last pieces of code writing out the Response stream as shown below: –

Response.Clear();
Response.ContentType = mimeType;
Response.AddHeader(“content-disposition”, “attachment; filename=OnlineReport.” + fileNameExtension);
Response.BinaryWrite(renderedBytes);
Response.End();

You can easily use the Report Wizard to defined a new data set type to map to the .xsd file and to allow the presentation to be generated for you in the .rdlc file using the Add New item in Visual Studio, I found this easier to do then fully customising the .rdlc file if you are missing a data column fromthe database to be displayed out.

Outside of this, you can defined two data types, PDF and Excel Spreadsheets which I have used QueryString to select the appropriate document type to be generated locally.

See the full code below: –

using System;

using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using Microsoft.Reporting.WebForms;

public partial class reports_OnlineReport : System.Web.UI.Page
{

protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString[“format”] != null)
{
string format = Request.QueryString[“format”].ToString();

switch (format)
{
case “pdf”:
RenderReport();
break;
case “excel”:
RenderReportAsExcel();
break;
default:
break;
}
}
}

private DataTable GenerateData(IList<DefinedDataType> sessionsCollection)
{

// Give the collection a name (sessionCollection) so that we can reference it in our report designer

DataTable sessionData = new DataTable();

DataSet portalSessionsDataSet = new DataSet(“Sessions”);
sessionData = portalSessionsDataSet.Tables.Add(“Sessions”);

sessionData.Columns.Add(“Username”, typeof(string));
sessionData.Columns.Add(“StartDateTime”, typeof(DateTime));
sessionData.Columns.Add(“EndDateTime”, typeof(DateTime));
sessionData.Columns.Add(“SessionLength”, typeof(int));
sessionData.Columns.Add(“BytesUploaded”, typeof(int));
sessionData.Columns.Add(“BytesDownloaded”, typeof(int));

foreach (DefinedDataType portalSessions in sessionsCollection)
{
DataRow row = sessionData.NewRow();
row[“Username”] = portalSessions.Username;
row[“StartDateTime”] = portalSessions.StartDateTime;
if (portalSessions.EndDateTime != null)
{
row[“EndDateTime”] = portalSessions.EndDateTime;
}
else
{
row[“EndDateTime”] = DBNull.Value;
}
row[“SessionLength”] = portalSessions.SessionLength;
row[“BytesUploaded”] = portalSessions.BytesUploaded;
row[“BytesDownloaded”] = portalSessions.BytesDownloaded;

sessionData.Rows.Add(row);
}

return sessionData;
}

private void RenderReport()
{

ReportViewer localReport = new ReportViewer();
localReport.LocalReport.ReportPath = Server.MapPath(“~/OnlineReport.rdlc”);

// Get the user’s session data from the database.

IList<DefinedDataType> sessionsCollection = new List<DefinedDataType>();

sessionsCollection = DefinedDataType.GetOnlineSessions();

DataTable sessionData = GenerateData(sessionsCollection);

ReportDataSource reportDataSource = new ReportDataSource(“session”, sessionData);

localReport.LocalReport.DataSources.Add(reportDataSource);
localReport.LocalReport.Refresh();

string reportType = “PDF”;
string mimeType;
string encoding;
string fileNameExtension;

//The DeviceInfo settings should be changed based on the reportType
//http://msdn2.microsoft.com/en-us/library/ms155397.aspx
string deviceInfo =
“<DeviceInfo>” +
” <OutputFormat>PDF</OutputFormat>” +
” <PageWidth>8.5in</PageWidth>” +
” <PageHeight>11in</PageHeight>” +
” <MarginTop>0.5in</MarginTop>” +
” <MarginLeft>1in</MarginLeft>” +
” <MarginRight>1in</MarginRight>” +
” <MarginBottom>0.5in</MarginBottom>” +
“</DeviceInfo>”;

Warning[] warnings;
string[] streams;
byte[] renderedBytes;

//Render the report
renderedBytes = localReport.LocalReport.Render(
reportType,
deviceInfo,
out mimeType,
out encoding,
out fileNameExtension,
out streams,
out warnings);

//Clear the response stream and write the bytes to the outputstream
//Set content-disposition to “attachment” so that user is prompted to take an action
//on the file (open or save)
Response.Clear();
Response.ContentType = mimeType;
Response.AddHeader(“content-disposition”, “attachment; filename=OnlineReport.” + fileNameExtension);
Response.BinaryWrite(renderedBytes);
Response.End();

}

private void RenderReportAsExcel()
{

LocalReport localReport = new LocalReport();
localReport.ReportPath = Server.MapPath(“~/OnlineReport.rdlc”);

// Get the user’s session data from the database.

IList<DefinedDataType> sessionsCollection = new List<DefinedDataType>();

sessionsCollection = DefinedDataType.GetOnlineSessions();

DataTable sessionData = GenerateData(sessionsCollection);

ReportDataSource reportDataSource = new ReportDataSource(“session”, sessionData);
localReport.DataSources.Add(reportDataSource);
string reportType = “Excel”;
string mimeType;
string encoding;
string fileNameExtension;

Warning[] warnings;
string[] streamids;
byte[] renderedBytes;

//Render the report
renderedBytes = localReport.Render(
reportType,
null,
out mimeType,
out encoding,
out fileNameExtension,
out streamids,
out warnings);

//Clear the response stream and write the bytes to the outputstream
//Set content-disposition to “attachment” so that user is prompted to take an action
//on the file (open or save)
Response.Clear();
Response.ContentType = mimeType;
Response.AddHeader(“content-disposition”, “attachment; filename=OnlineReport.” + fileNameExtension);
Response.BinaryWrite(renderedBytes);
Response.End();

}

}

Advertisements

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: