Beginner's guide for creating standalone .rdlc reports with ssrs

private void btnExit_Click(object sender, EventArgs e)
{
    this.Close();
}

this.cmbReport.Items.Add("TESTREPORT");

using System.Data.SqlClient;
using System.Xml;
using System.Xml.Serialization;
using System.Data.OleDb;
using System.IO;

public partial class Form1 : Form
{
    // Module scope dataset variable
    DataSet dsMyDataSet = new DataSet();
}

private void btnViewReport_Click(object sender, EventArgs e)
{
    // Reset the report viewer
    this.reportViewer1.Reset();
    // Set processing to local mode
    this.reportViewer1.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Local;
    // Check which report to test
    if (this.cmbReport.Text.Trim() == "TESTREPORT")
    {
        // Load .rdlc file and add a datasource
        this.reportViewer1.LocalReport.ReportPath = @"C:\Reports\MyTestReport.rdlc";
        // Loop through each table in our dataset
        for (int i = 0; i < this.dsMyDataSet.Tables.Count; i++)
        {
            this.reportViewer1.LocalReport.DataSources.Add(this.GetMyDataTable(i));
        }
    }
    // Refresh viewer with above settings
    this.reportViewer1.RefreshReport();
}

private Microsoft.Reporting.WinForms.ReportDataSource GetMyDataTable(int i)
{
    // Form the datasource name - you need a naming convention for this to work
    string sDataSourceName = "Reportdata_Table" + i.ToString().Trim();
    // The line above will generate datasource names of "Reportdata_Table0" and
    // "Reportdata_Table1" for our 2 tabled dataset - we just need to ensure our .rdlc
    // report has been designed to receive 2 datasources with these same names, and that
    // the columns match up precisely one-to-one for each table.
    // Return the relevant dataset table
    return new Microsoft.Reporting.WinForms.ReportDataSource(sDataSourceName, this.dsMyDataSet.Tables[i]);
}

private void SetupDataSet()
{
    // Create 1st DataTable to hold some report data
    System.Data.DataTable myTable0 = new DataTable("myTable0");
    System.Data.DataColumn column;
    System.Data.DataRow row;
    // Create 3 columns for myTable0
    column = new DataColumn();
    column.DataType = System.Type.GetType("System.String");
    column.ColumnName = "ID";
    column.ReadOnly = true;
    column.Unique = true;
    myTable0.Columns.Add(column);
    column = new DataColumn();
    column.DataType = System.Type.GetType("System.String");
    column.ColumnName = "DESCRIPTION";
    column.ReadOnly = true;
    column.Unique = true;
    myTable0.Columns.Add(column);
    column = new DataColumn();
    column.DataType = System.Type.GetType("System.String");
    column.ColumnName = "QUANTITY";
    column.ReadOnly = true;
    column.Unique = true;
    myTable0.Columns.Add(column);
    // Add a row of data to myTable0
    row = myTable0.NewRow();
    row["ID"] = "1234567890";
    row["DESCRIPTION"] = "Rickenbacker Electric Guitar";
    row["QUANTITY"] = "5";
    myTable0.Rows.Add(row);
    // Add a second row of data to myTable0
    row = myTable0.NewRow();
    row["ID"] = "777745632";
    row["DESCRIPTION"] = "Gibson Electric Guitar";
    row["QUANTITY"] = "7";
    myTable0.Rows.Add(row);
    // Add myTable0 to global dataset
    this.dsMyDataSet.Tables.Add(myTable0); // dsMyDataSet.Tables[0] object;
    // Create 2nd DataTable to hold some report data
    System.Data.DataTable myTable1 = new DataTable("myTable1");
    // Create 4 columns for myTable1
    column = new DataColumn();
    column.DataType = System.Type.GetType("System.String");
    column.ColumnName = "CUSTOMER_ID";
    column.ReadOnly = true;
    column.Unique = true;
    myTable1.Columns.Add(column);
    column = new DataColumn();
    column.DataType = System.Type.GetType("System.String");
    column.ColumnName = "NAME";
    column.ReadOnly = true;
    column.Unique = true;
    myTable1.Columns.Add(column);
    column = new DataColumn();
    column.DataType = System.Type.GetType("System.String");
    column.ColumnName = "ADDRESS";
    column.ReadOnly = true;
    column.Unique = true;
    myTable1.Columns.Add(column);
    column = new DataColumn();
    column.DataType = System.Type.GetType("System.String");
    column.ColumnName = "POSTAL_CODE";
    column.ReadOnly = true;
    column.Unique = true;
    myTable1.Columns.Add(column);
    // Add a row of data to myTable1
    row = myTable1.NewRow();
    row["CUSTOMER_ID"] = "56790";
    row["NAME"] = "John Lennon";
    row["ADDRESS"] = "Strawberry Fields, Liverpool, England";
    row["POSTAL_CODE"] = "NWE232";
    myTable1.Rows.Add(row);
    // Add a second row of data to myTable1
    row = myTable1.NewRow();
    row["CUSTOMER_ID"] = "44982";
    row["NAME"] = "George Harrison";
    row["ADDRESS"] = "22 Penny Lane, Liverpool, England";
    row["POSTAL_CODE"] = "NWE231";
    myTable1.Rows.Add(row);
    // Add myTable1 to global dataset
    this.dsMyDataSet.Tables.Add(myTable1); // dsMyDataSet.Tables[1] object;
}

private void Form1_Load(object sender, EventArgs e)
{
    this.cmbReport.Items.Add("TESTREPORT");
    this.SetupDataSet();
}

USE NorthwindCS
GO
/*------------------------------------------------------------------*
This stored procedure provides a starting point for the
design of a Test SSRS Report.
*-------------------------------------------------------------------*/
CREATE PROC up_SSRS_TestReport_InputFields0
AS
SELECT DISTINCT
    '1' AS ID,
    '2' AS DESCRIPTION,
    '3' AS QUANTITY

USE NorthwindCS
GO
/*------------------------------------------------------------------*
This stored procedure provides a starting point for the
design of a Test SSRS Report.
*-------------------------------------------------------------------*/
CREATE PROC up_SSRS_TestReport_InputFields1
AS
SELECT DISTINCT
    '1' AS CUSTOMER_ID,
    '2' AS NAME,
    '3' AS ADDRESS,
    '4' AS POSTAL_CODE

Step 3. For the question "Where will the application get data from?" leave the default setting of Database and click Next.

Step 4. For the question "Which data connection should your application use to connect to the database?" click on New Connection.

If you installed SQL Server 2005 locally then your pc is the server. Type in your computer name into the Server name: field (this saves time there could be many servers on your network. So avoid browsing for them). I selected to use Windows authentication.

The section at the bottom of the Add Connection dialog should now be enabled, where you can select or enter a database name. Use the drop-down control to see the databases contained in your local instance of SQL Server 2005. Select NorthwindCS from the drop-down list.

Click on Test Connection to make sure it's working. Click OK to continue.

This is what my connection string looks like

Data Source=FNOA1281; Initial Catalog = NorthwindCS; Integrated Security=True

For the question "Save the Connection String to the Application Configuration File" just leave the default and click Next.

Step 5. Click Next. You will see a message briefly that says "Retrieving database information" followed by a dialog that allows you to Choose Your Database Objects. This list allows you to choose from Tables, Views, Stored Procedures, and Functions. In our case, we will be selecting the Stored Procedures we created earlier.

So expand the Stored Procedures section and put a tick against up_SSRS_TestReport_InputFields1 and up_SSRS_TestReport_InputFields2. This is a great feature of SSRS because you can lump related SPROCS together under a single category.

Step 6. In the DataSet name field at the bottom of the dialog change the default name that SSRS provides to something more meaningful like ReportdataTESTREPORT.

It should look like this

Click Finish to close the Wizard.

You should now have a data source category called ReportdataTESTREPORT and this is what it should look like in the Data Sources pane

From this information, SSRS will automatically form 2 Project data sources called.

Reportdata_TESTREPORT_up_SSRS_TestReport_InputFields1

and

Reportdata_TESTREPORT_up_SSRS_TestReport_InputFields2

i.e. the names get mangled together so they are unique. You will see them later in the Report Data Sources dialog.

Now we have our report fields courtesy of the NorthwindCS database and a couple of simple SPROCS. I knew that database would come in handy one day.

Add a new .rdlc file to your Visual Studio Project

Now we need to add a .rdlc report to the project so we can design our report.

In the Solution Explorer window single right-click the project name and select Add /New Item.From the templates displayed select Report and change the name at the bottom to TestReport.rdlc.

Click Add. It now appears in the Solution Explorer pane and a blank design page is presented to you.

Open the SSRS toolbox pane and drag and drop 2 table controls onto the report surface. These will be called table1 and table2 by default. Add an extra column to the 2nd table. We want a 3 and a 4-column table to match our 3 and 4-column data tables created earlier.

Put any text you like in the Header fields of each table control (something descriptive). Add any bordering to the tables according to individual preference.

Open the Data Sources pane again and expand the ReportdataTESTREPORT project data source node you created earlier. Expand the SPROC nodes as well.

Do the following drag and drops

From up_SSRS_TestReport_InputFields1 Sproc

Drag & drop: ID > table1/Detail (1st column)

Drag & drop: DESCRIPTION table1/Detail (2nd column)

Drag & drop: QUANTITY table1/Detail (3rd column)

From up_SSRS_TestReport_InputFields2 Sproc

Drag & drop: CUSTOMER_ID table2/Detail (1st column)

Drag & drop: NAME > table2/Detail (2nd column)

Drag & drop: ADDRESS table2/Detail (3rd column)

Drag & drop: POSTAL_CODE table2/Detail (4th column)

We are almost there now

Remember that we need 2 report data sources called Reportdata_Table0 and Reportdata_Table1. The next section explains how to do this

Setup the Report Data Sources with the names you want

As soon as you start dragging and dropping the fields as described above, SSRS will allocate the following DataSet names for each table in your report

Report Table Name	DataSetName property (as allocated by SSRS)
table1	Reportdata_TESTREPORT_up_SSRS_TestReport_InputFields1
table2	Reportdata_TESTREPORT_up_SSRS_TestReport_InputFields2

Check these values in the DataSetName property for each table on your report.

If we wanted to, at this point, we could use these DataSetName values (as they are shown in the table above). We just have to change our GetMyDataTable(int i) function to have the following line.

// create the Datasource name
sDataSourceName = "Reportdata_TESTREPORT_up_SSRS_TestReport_InputFields" + (i + 1).ToString().Trim();

But that is a bit of a mouthful. So let's make it something nicer.

Steps to renaming a report data source

• Make sure the report designer is open with your new report showing in the designer.

• From the menus select Report-->Data Sources. This opens the Report Data Sources dialog and it should look something like this

Note. The upper control is for Project data sources and the lower control is for Report data sources.

Because we used fields from 2 data sources when we did the dragging and dropping earlier, SSRS has automatically added these 2 data sources to the above dialog. To change the name of the Report data sources just click on the first one in the list and click rename. Type in ReportdataTable0. Do the same for the second one but call it ReportdataTable1.

Now it should look like

Click OK to dismiss the dialog, then Save your project so the new names stick. It's worth going back into this dialog to check that your new names have stuck. On some occasions when I have gone back in the names have still been set to their original values.

There is one final thing to do

Even though we have changed the name of the Report data sources to something that we want, this has only happened on the Visual Studio side and the report itself still thinks it is working with the original names that SSRS allocated. You can see this if you select one of your tables in the designer (Table say) and look at its DataSetName property.

Table1 property

So the final thing to do is to select the pulldown control in this property and select ReportdataTable0 for table1 and ReportdataTable1 for table2 in your report.

Table1 property

Table2 property

Now Visual Studio and your SSRS report agree on the name of the DataSetName.

The lesson here is that if you change the name of a Report data source you then have to visit every control in your report that makes use of that data source and check the DataSetName property.

Implement the standalone .rdlc file

Browse to where Visual Studio has your TestReport.rdlc file and copy it to the C:\Reports folder. Then rename it to MyTestReport.rdlc (you can leave the name the same really if you want, just make sure that whatever name you give it here, you use the same name when you access the file in code).

Run the application and select TESTREPORT from the combo pull-down, then click View Report.

You should see the final result similar to this

If you wanted to you could now go back into your project and delete all of the data sources from the Data Sources pane by deleting the corresponding .xsd file (Reportdata_TESTREPORT.xsd) from the Solution Explorer pane, and your MyTestReport.rdlc file will still work.

You could also delete the TestReport.rdlc design version of the file from the Solution Explorer pane if you wanted to and MyTestReport.rdlc will still work correctly i.e. we have created a 100% standalone report that can be wired up to any data source provided that data source supplies the components that the report expects (which in this case is one data source called Reportdata_Table0 consisting of a 3 columned table (ID, DESCRIPTION, QUANTITY) and a second data source called Reportdata_Table1 consisting of a 4 columned table (CUSTOMER_ID, NAME, ADDRESS, POSTAL_CODE)).

Appendix I

Here is the full listing of my Form 1.cs file just in case you get lost

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Xml;
using System.Xml.Serialization;
using System.Data.OleDb;
using System.IO;
namespace TestHarness
{
    public partial class Form1 : Form
    {
        // Module scope dataset variable
        DataSet dsMyDataSet = new DataSet();
        public Form1()
        {
            InitializeComponent();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            this.cmbReport.Items.Add("TESTREPORT");
            this.SetupDataSet();
            this.reportViewer1.RefreshReport();
        }
        private void btnExit_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        private void btnViewreport_Click(object sender, EventArgs e)
        {
            // Reset the report viewer
            this.reportViewer1.Reset();
            // Set processing to local mode
            this.reportViewer1.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Local;
            // Check which report to test
            if (this.cmbReport.Text.Trim() == "TESTREPORT")
            {
                // Load .rdlc file and add a datasource
                this.reportViewer1.LocalReport.ReportPath = @"\Reports\MyTestReport.rdlc";
                // Loop through each table in our dataset
                for (int i = 0; i < this.dsMyDataSet.Tables.Count; i++)
                {
                    this.reportViewer1.LocalReport.DataSources.Add(this.GetMyDataTable(i));
                }
            }
            // Refresh viewer with above settings
            this.reportViewer1.RefreshReport();
        }
        private Microsoft.Reporting.WinForms.ReportDataSource GetMyDataTable(int i)
        {
            // Form the datasource name - you need a naming convention for this to work
            string sDataSourceName = "Reportdata_Table" + i.ToString().Trim();
            // The line above will generate datasource names of "Reportdata_Table0" and
            // "Reportdata_Table1" for our 2 tabled dataset - we just need to ensure our .rdlc
            // report has been designed to receive 2 datasources with these same names and that
            // the columns match up precisely one-to-one for each table.
            // Return the relevant dataset table
            return new Microsoft.Reporting.WinForms.ReportDataSource(sDataSourceName, this.dsMyDataSet.Tables[i]);
        }
        private void SetupDataSet()
        {
            // Create 1st DataTable to hold some report data
            System.Data.DataTable myTable0 = new DataTable("myTable0");
            System.Data.DataColumn column;
            System.Data.DataRow row;
            // Create 3 columns
            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "ID";
            column.ReadOnly = true;
            column.Unique = true;
            // Add the Column to the DataColumnCollection.
            myTable0.Columns.Add(column);
            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "DESCRIPTION";
            column.ReadOnly = true;
            column.Unique = true;
            // Add the Column to the DataColumnCollection.
            myTable0.Columns.Add(column);
            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "QUANTITY";
            column.ReadOnly = true;
            column.Unique = true;
            // Add the Column to the DataColumnCollection.
            myTable0.Columns.Add(column);
            // Add a row of data
            row = myTable0.NewRow();
            row["ID"] = "1234567890";
            row["DESCRIPTION"] = "Rickenbacker Electric Guitar";
            row["QUANTITY"] = "5";
            // Add the row of data to the table
            myTable0.Rows.Add(row);
            // And a second row
            row = myTable0.NewRow();
            row["ID"] = "777745632";
            row["DESCRIPTION"] = "Gibson Electric Guitar";
            row["QUANTITY"] = "7";
            // Add the row of data to the table
            myTable0.Rows.Add(row);
            // Add myTable0 to global dataset
            this.dsMyDataSet.Tables.Add(myTable0); // dsMyDataSet.Tables[0] object;
            // Create 2nd DataTable to hold some report data
            System.Data.DataTable myTable1 = new DataTable("myTable1");
            // Create 4 columns
            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "CUSTOMER_ID";
            column.ReadOnly = true;
            column.Unique = true;
            // Add the Column to the DataColumnCollection.
            myTable1.Columns.Add(column);
            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "NAME";
            column.ReadOnly = true;
            column.Unique = true;
            // Add the Column to the DataColumnCollection.
            myTable1.Columns.Add(column);
            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "ADDRESS";
            column.ReadOnly = true;
            column.Unique = true;
            // Add the Column to the DataColumnCollection.
            myTable1.Columns.Add(column);
            column = new DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "POSTAL_CODE";
            column.ReadOnly = true;
            column.Unique = true;
            // Add the Column to the DataColumnCollection.
            myTable1.Columns.Add(column);
            // Add a row of data
            row = myTable1.NewRow();
            row["CUSTOMER_ID"] = "56790";
            row["NAME"] = "John Lennon";
            row["ADDRESS"] = "Strawberry Fields , Liverpool , England";
            row["POSTAL_CODE"] = "NWE232";
            // Add the row of data to the table
            myTable1.Rows.Add(row);
            // Add a row of data
            row = myTable1.NewRow();
            row["CUSTOMER_ID"] = "44982";
            row["NAME"] = "George Harrison";
            row["ADDRESS"] = "22 Penny Lane , Liverpool , England";
            row["POSTAL_CODE"] = "NWE231";
            // Add the row of data to the table
            myTable1.Rows.Add(row);
            // Add myTable1 to global dataset
            this.dsMyDataSet.Tables.Add(myTable1); // dsMyDataSet.Tables[1] object;
        }
    }
}

Appendix II

Here are two more examples of GetMyData() type functions that I have used recently: the 1st one runs a SQL query against a remote server and the 2nd one runs a mainframe DB2 query.

private Microsoft.Reporting.WinForms.ReportDataSource GetMyDataEAR005()
{
    // TEST Reporting server
    string strConn = "server=EDSQL022\\SQL07;database=MgtReporting;Integrated Security=SSPI;";
    string sQuery = "SELECT DISTINCT AL1.ReportName AS Report_Name, " +
                    "reportMnemonic AS Report_Mnemonic, AL1.ReportType AS Report_Type, " +
                    "AL1.Active, AL2.ReportGroup AS Report_Group, AL4.ADRole AS AD_Role, " +
                    "L3.BizArea AS Business_Area " +
                    "FROM dbo.vwFullReportList AS AL1 " +
                    "LEFT OUTER JOIN dbo.vwReportGroupMap AS AL2 ON AL1.ReportID = AL2.ReportID AND AL1.ReportType = AL2.ReportType " +
                    "LEFT OUTER JOIN dbo.vwReportBizMap AS AL3 ON AL3.ReportID = AL1.ReportID AND AL3.ReportType = AL1.ReportType " +
                    "LEFT OUTER JOIN dbo.vwReportRoleMap AS AL4 ON AL4.ReportID = AL1.ReportID AND AL4.ReportType = AL1.ReportType " +
                    "ORDER BY Report_Mnemonic, AD_Role";
    DataSet ds = new DataSet("ReportDataSet");
    SqlConnection cn = new SqlConnection(strConn);
    cn.Open();
    SqlDataAdapter da = new SqlDataAdapter(sQuery, cn);
    da.Fill(ds);
    cn.Close();
    return new Microsoft.Reporting.WinForms.ReportDataSource("ReportList_EAR005", ds.Tables[0]);
}
private Microsoft.Reporting.WinForms.ReportDataSource GetMyDataCWCR004()
{
    string strConn = "Provider=DB2OLEDB;Data Source=DBT1;Persist Security Info=True;Password=xxxxxxx;User ID=my_id;Initial Catalog=DBT1;Network Transport Library=TCPIP;Network Address=fbrukdev.network;Network Port=5025;Package Collection=HIS2004;Default Schema=MIS";
    string sQuery = "SELECT DISTINCT AL1.ESA_CODE, AL1.ESA_DESCRIPTION, AL2.FUTURE_MTH_NUM, " +
                    "CASE WHEN AL1.FUTURE_MTH_NUM = 0 THEN 'Past' ELSE 'Future' END AS TIMESPAN, " +
                    "AL1.YR_MONTH, AL1.TOTAL_CNT, AL1.EXTRACT_DATE " +
                    "FROM MIS.CWC_JSKR_FLOW AL1, MIS.CWC_JSKR_FLOW AL2 " +
                    "WHERE AL1.ESA_CODE = AL2.ESA_CODE AND (1 = 1)";
    DataSet ds = new DataSet("ReportDataSet");
    OleDbConnection myOleDbConnection = new OleDbConnection(strConn);
    OleDbDataAdapter da = new OleDbDataAdapter();
    OleDbCommand myCommand = new OleDbCommand(sQuery, myOleDbConnection);
    myCommand.CommandTimeout = 600;
    da.SelectCommand = myCommand;
    myOleDbConnection.Open();
    da.Fill(ds);
    myOleDbConnection.Close();
    return new Microsoft.Reporting.WinForms.ReportDataSource("ReportData_CWCR004", ds.Tables[0]);
}