Microsoft SQL Server Reporting with Visual Basic
The programmer is spoilt with the built-in Reporting facility of Microsoft Access: Visual Basic must use reporting software and tools from other systems.
There are four options for reporting in Visual Basic:
- Microsoft Access
- SQL Server Reporting
- Business Intelligence Development Studio (BIDS)
- Crystal Reports
The convenience of Microsoft Access Reporting
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.
- Visual Basic can call a Report directly, or it can call an Access Form that can then call the Report with the selection criteria.
The free Crystal Reports from SAP
The 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 set-up 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.
Crystal Reports 2016
The CR2016 is much as for the version from 20 years ago, but quite expensive. It does not know of an ACCDB type database – this is handled by the use ODBC. It does not have to be run in compatibility mode, as required by Crystal Reports 10.
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 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 Web page 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 Visual Basic code. This allows programmatic control of the viewing and printing of any report.
The Future of BIDs is uncertain
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.
BIDS has been replaced by SQL Server Data Tools - Business Intelligence.