Introduction
SQL Server Express is based on SQL Server and supports most of the database engine features. If we design and create a database with SQL Express, we can easily migrate this database to any edition of SQL Server but we will not able to use a database with the Express Edition that was created in a higher version of SQL Server.
SQL Server Express with advanced services provide the following features in addition to SQL Server Express.
- Reporting Services
- Full-Text search
- BI Development studio
- SQL Server Management studio
Features Supported by SSRS with Express advanced services
SQL Server Express with advanced services provide the following Reporting Services features:
- Supported rending formats: HTML, Acrobat and Excel.
- Report server management and viewing the reports are supported using the Report Manager.
- Report configuration can be done using the Reporting Services Configuration tool.
- Command line utilities like Rsconfig.exe, Rs.exe and Rskeymgmt.exe are available in SQL Server Express.
- We can use Windows authentication and predefined roles to map an existing group and user accounts to a named collection of operations.
- On-demand report processing is supported. When the user opens the report, all the process, like report initialization, query processing and data are merged into the report layout and rendering the report into a presentation format is done on-demand.
Feature not supported by SSRS with Express advance service
The following features are not supported by reporting services with SQL Server Express (with advanced services).
- Remote data sources are not supported. Report data sources must be SQL Server database that run locally in SQL Server Express.
- Ad-hoc reporting using semantic models and Report Builder is not supported.
- The rendering formats TIFF, XML and CSV are not supported.
- Analysis services, XML, SAP, SSIS, Oracle, OLEDB and ODBC data sources are not supported.
- Report caching, scheduled report processing, snapshots are not supported.
- Report subscriptions, data driven subscription and delivery are not supported.
- Report history is not supported.
- Shared data set is not supported.
- Infinite clicking through feature is not supported with this version.
- Model base report is not supported.
- Custom report items are not supported.
- Custom authentication and role-based assignment is not available with this version. We can map an existing Windows domain user and group accounts to predefined role definitions.
- API extensible platform data processing, rendering and security are not supported.
- Long-running report processing management is not supported.
- SharePoint integrated mode is not supported.
- Scale-out deployment is not supported.
- Power view is not supported with this version.
- Reports will run only with a SQL Server data source Express edition.
If we are using the User Instance feature (RANU, run as normal user) in SQL Express, the data source will not be available over the remote connections. Remote users who access the report will get the following error when the report data source has been configured for the User Instances feature.
An error has occurred during report processing. Cannot create a connection to data source “<<data source name>>'. For more information about this error, navigate to the report server on the local server machine, or enable remote errors."
Report servers always maintain the log file for the errors. A log file also contains the entry for the preceding error.
Cannot create a connection to data source “<datasourcename>”. ---> System.Data.SqlClient.SqlException: User does not have permission to perform this action.
Summary
SQL Server Reporting Services with SQL Express advanced services is a good choice as a reporting tool with no license cost. Every tool has certain limitations and SSRS also has some limitations. In this article, I have explain what the limitations of SSRS are when using Express advanced services.