SQL Server Compact Edition and reporting. Is SSRS an option?

Question

I need to create a simple, stand-alone application that will allow for users to input some data and then print a report. I would prefer to use SSRS as a reporting engine but I also want to keep my data store very simple.

I have another, sizable smart-client application that uses SQL Server Express edition as a user back-end. From experience, SQL Server Express is certainly an option, however, integrating a database creation and maintenance process is a bit of a nightmare, due to various end-user constraints.

SQL Server CE is much easier to use for my needs, if that is an option. Can I use SQL Server Compact Edition with SSRS?

Yes, I am aware that I will still need to install SQL Server Express if I want to use SSRS. I'm fine with that requirement. My primary concern is that I'd rather not have to automate the database creation process within SQL Server Express, which is what I have to do with the other smart-client application I mentioned.

Instead, I'd rather use a local SQL Server CE database file that I can bundle with my application and connect to from within SSRS. Can this be done?

If this isn't possible, is there some other, simple alternative (excluding the use of Crystal Reports) that would allow for simple reporting and inclusion of a database file with the application installer?

ErikEJ ErikEJ · Accepted Answer · 2013-06-27T08:15:08

You can use the standalone Reporting Services ReportViewer control with a DataSet from a SQL Server Compact database file, that runs completely locally and does not require any SQL Server installtion.

I do that in my SQL Server Compact Toolbox, with code similar to this (full source available at http://sqlcetoolbox.codeplex.com ) :

public partial class ReportGrid : UserControl
{
    public ReportGrid()
    {
        InitializeComponent();
    }

    public DataSet DataSet { get; set; }

    public string TableName { get; set; }

    private void ReportGrid_Load(object sender, EventArgs e)
    {
        if (DataSet != null)
        {
            DataSet.DataSetName = TableName;

            Stream rdlc = RdlcHelper.BuildRDLCStream(
                DataSet, TableName);

            reportView.LocalReport.LoadReportDefinition(rdlc);
            reportView.LocalReport.DataSources.Clear();
            reportView.LocalReport.DataSources.Add(
                new ReportDataSource(DataSet.DataSetName, DataSet.Tables[0]));
            reportView.RefreshReport();
        }
    }

}

SQL Server Compact Edition and reporting. Is SSRS an option?

1 Answers