Visual Basic Programmer

All about SQL Server

SQL Server Reporting with VB.Net

There are four options for reporting in VB.Net:

Microsoft Access reporting facility
SQL Server Reporting Services (SSRS)
Business Intelligence Development Studio (BIDS)
Crystal Reports

Microsoft Access

The use of Microsoft Access Reporting can be a short term solution. It will help speed the migration process – the SQL Server Reporting can be done later.

• Existing Reports can be used without change.
• New reports can be created using the Access Reporting Wizard.
• VB.Net can call a Report directly, or it can call an Access Form that can then call the Report with the selection criteria.

Crystal Reports

Crystal Reports used to come integrated with Visual Studio 2008 – now the free VS2010 version must be downloaded from SAP. Finding the downloads, files and fudges needed for the setup is not obvious.

When the initial problems are resolved:

• The features of Crystal Reports are incorporated well into the development environment of Visual Studio.
• Crystal Reports and the Viewer control work efficiently and well.
• Crystal Reports is a mature product and is still popular.

SQL Server Reporting Services

SQL Server Reporting Services is Microsoft's attempt at replacing Crystal Reports.

• Report viewer: Users can view Reports over the Web.
• Report Builder: There is a Front-End for ad hoc reporting. Knowledge of SQL and its complexities is not required.
• Report Designer: Allows the building of advanced Reports.

Business Intelligence Development Studio (BIDS)

BIDS is integrated with Visual Studio, allowing Visual Basic.Net to control the functionality.

• The BIDS Report Wizard is basic – it does not have the features of the Microsoft Access or Crystal Reports Wizards. That said, it is very easy to create sophisticated Reports.
• The BIDS Wizard only works with Visual Studio 2008. This means that the new features of Visual Studio 2010 (like controlling View State at the Page level) are not available.
• Documentation of the features of BIDS is sparse. There are few books or Web articles on the subject.

When a report has been created:

• The ReportViewer control can be used in VB.Net 2010, on a Form or a Webpage – see the BIDS Report Example, to preview and print a rdlc Report file. It can access any database source, not just SQL Server. The ReportViewer control has the option to export the result to Excel, PDF or Word.
• The Report, the SQL Select statement and the Data Source can all be changed in VB.Net code. This allows programmatic control of the viewing and printing of any report.

And the Future?

Microsoft's Reporting Strategies are hazy at this time. SQL Server 2011 has a new reporting tool called "Crescent", based on Silverlight. Crescent also requires SharePoint 2010, which makes it too complex for small companies.

It seems that Visual Studio 2010 will never get BIDS reporting, but the preview of Visual Studio 2012 has documentation on using the BIDs wizard. Microsoft SQL Server 2011 contains BIDS 2011.