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

http://www.c-sharpcorner.com/UploadFile/robo60/StandaloneRDLCReports11142007183516PM/StandaloneRDLCReports.aspx

Introduction

The information in this article was born out of a need to develop new standalone SSRS reports (to replace a stack of Crystal reports) where it was not possible for the report designer to see the data sources involved due to various account, security and other restrictions. Therefore I developed a process whereby I could set up dummy data sources (even on my local pc) to provide the fields for the report. Once the report was finished it could then be tested against the real data source.

In this article I demonstrate how to create a standalone .rdlc report and how to access it via C# code only. I also demonstrate how to set up the report fields for the report designer using a very simple technique based on stored procedures. Additionally I also demonstrate how to map a dataset consisting of two tables (ds.Tables[0] and ds.Tables[1] - a 3 & 4 column table respectively) to the standalone report. I also cover the setting up of a simple Test Harness C# project in Visual Studio 2005 to test your standalone .rdlc report.

This article is deliberately written with the beginner in mind, hence it is rather lengthy as I try to explain in detail what to do, so I apologise if you find it slow-going. It is also for those who have a particular need to create standalone .rdlc reports that are unbound at design time but have their data added at runtime. Many examples I have seem to assume that you will be running your report against the same data source that the report was originally designed against. For me this was not the case and I needed an alternative, hence the procedure outlined in this article. I hope what follows will be of some help to you.

Acknowledgements

I would like to thank Justin Saraceno whose article The ReportViewer control, a local SSRS 2005 report, and data from a Web Service got me going in the right direction. I use the same type of code approach that he uses. His article can be found on the http://www.c-sharpcorner.com web-site.

Requirements

My .rdlc reports had to be able to do the following:

Be 100% standalone and capable of being accessed in code only i.e. there should be no need to add the .rdlc file to your Visual Studio project.
Some of our current production report queries run Stored Procedures that have multiple SELECT statements in them. These SPROCS return a single dataset containing multiple tables. If ds is our dataset, then we get back ds.Tables[0] ds.Tables[1], ds.Tables[2] etc. These tables had to be able to be wired into the report in code only.

By the way, with the following technique you can also wire in Tables from different data sources as well. So if I had 2 datasets ds1 & ds2, then you can wire in ds1.Tables[0] and ds2.Tables[0]. The ds1 object might be a SQL Server query result. ds2 might be a mainframe query result etc. The report viewer control really doesnt care where these data tables are coming from they are all just tables as far as the viewer control is concerned.

Prerequisites

To use the following technique that I am about to describe you should make sure you meet the following prerequisites:

Visual Studio 2005 with SSRS on your local pc.
SQL Server 2005 Management Studio (with NorthwindCS or similar database) installed on your local pc.
Sufficient permissions within SQL Server 2005 Management Studio to CREATE and DROP Stored Procedures from the NorthwindCS (or similar) database.

We are using NorthwindCS here just for simplicity because it gets installed when you install SQL Server 2005 Management Studio. We just want somewhere to park our Stored Procedures that we are going to create soon. The Sprocs we create actually have nothing to do with the NorthwindCS database In a real Development environment you would probably want to store the Sprocs in a meaningful location.

Values for this example

Our standalone .rdlc file will be called MyTestReport.rdlc
It will reside here C:\Reports\MyTestReport.rdlc for standalone testing.
It will require 2 datasources to be added to it called Reportdata_Table0 consisting of 3 columns
(ID, DESCRIPTION and QUANTITY) and Reportdata_Table1 consisting of 4 columns
(CUSTOMER_ID, NAME, ADDRESS and POSTAL_CODE).
Our design version .rdlc file will be called TestReport.rdlc

The report friendly name will be "TESTREPORT" here is a summary of the datasource/dataset mapping.

Report Datasource name	Data coming from dataset	Table type
Reportdata_Table0	dsMyDataSet.Tables[0]	3 column table
Reportdata_Table1	dsMyDataSet.Tables[1]	4 column table

We will hard-code some test data into the test harness (by building a 2 tabled dataset in code) so that there is something to show on the report itself, but in your own application you can derive the data from anywhere (a query, or an .xml file, web-service call etc.)

The first step though is to create a simple test harness.

Create a simple TestHarness application

Our test harness will be where we design our .rdlc reports and test them also.

The basic approach will be to add a .rdlc file to our Visual Studio test harness project called TestReport.rdlc, merely so that we can get access to the designer, but when the report is finished it will be copied from the Visual Studio project location to the C:\Reports folder and then renamed to MyTestReport.rdlc to prove absolutely that we are dealing with a standalone file. The project has no knowledge of a file called MyTestReport.rdlc and this file is accessed in code only.

step 1 create a test harness

For this we want the following

A standard C# Windows application consisting of a single dialog.
One SSRS report viewer control (reportViewer1).
One Exit button (btnExit).
One View Report button (btnViewReport).
One combo drop down control (cmbReport).
A label for the combo (Report).

Here is what mine looks like

Anchor the buttons etc. to the Bottom and Right. Anchor the Report viewer to Top, Bottom, Left and Right.

The code behind the Exit button click event is:

private void btnExit_Click(object sender, EventArgs e)

{

this.Close();

}

Lets say our new report user friendly name will be "TESTREPORT". Therefore in the Form_Load event for this dialog put the following:

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

As you design more reports keep adding them to the combo in the Form_Load event so you can easily select the report you want to test.

I found I needed the following using directives:

using System.Data.SqlClient;

using System.Xml;

using System.Xml.Serialization;

using System.Data.OleDb;

using System.IO;

As I have been running SQL and Mainframe queries in my test harness. Depending on what you will be designing you may not need all of them. For this walk through you do not need any of them.

Add the following module variable dsMyDataSet this will be our ds.Tables[0] and ds.Tables[1] object I alluded to earlier:

public partial class Form1 : Form

{

//module scope dataset variable

DataSet dsMyDataSet = new DataSet();

Now in the click event for btnViewReport button add the following code:

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();

}

Now we need 1 function called GetMyDataTable(int i)) and here is what it looks like:

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 pecisely one-to-one for each table.

//return the relevant dataset table

return new Microsoft.Reporting.WinForms.ReportDataSource(sDataSourceName,
this.dsMyDataSet.Tables[i]);

}

Create a function that sets up the 2-tabled dataset using our module variable dsMyDataSet. Call this function from the Form_Load event so that dsMyDataSet is pre-populated with some data ready for display.

This is what I did: Note the column names, you will see them later when we do the Stored Procedures.

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;

}

Do not forget to call the above function from your Form_Load event, similar to:

private void Form1_Load(object sender, EventArgs e)

{

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

this.SetupDataSet();

At this point the project should build.

We have now got our Dataset that contains 2 tables, i.e. we have created dsMyDataSet.Tables[0] and dsMyDataSet.Tables[1], a 3 and 4 column table respectively with the column names as shown in the above code snippet and a couple of rows of data for each one.

If you look at the return statement in the above GetMyDataTable(int i) function you can see that our report will have to be designed to expect 2 data sources to be added to it called ReportdataTable0 and ReportdataTable1, and additionally these data sources have to have 3 and 4 columns defined within them respectively with identical names for the columns as we are using in the dataset dsMyDataSet.

Next step is to design the actual report, and the starting point for this is to get the report fields to show up in the Data Sources pane in Visual Studio so that you can drag and drop them onto the report designer surface.

REPORT DESIGN

Create the report input fields via Stored Procedures

This is where we make use of the NorthwindCS database on your locally installed instance of SQL Server 2005. Here we just want to use a couple of stored procedures to define the report input fields. There is no intention to use the stored procedures at runtime to provide data. At runtime the data will come from somewhere else (in this case a hard coded Dataset in the app.), but in your own application the dataset can be from anywhere.

Make sure your local instance of sql server 2005 is started. Check the icon in the system tray.

Create 2 Stored Procedures in Notepad ready to copy across to SQL Server 2005 that look like this (or type them directly into SQL Server 2005 query window after selecting NorthwindCS).

Sproc #1

USE NorthwindCS

/*------------------------------------------------------------------*

This stored procedure provides a starting point for the

design of a Test SSRS Report.

*-------------------------------------------------------------------*/

CREATE PROC up_SSRS_TestReport_InputFields0

SELECT DISTINCT

'1' AS ID,

'2' AS DESCRIPTION,

'3' AS QUANTITY

Sproc #2

USE NorthwindCS

/*------------------------------------------------------------------*

This stored procedure provides a starting point for the

design of a Test SSRS Report.

*-------------------------------------------------------------------*/

CREATE PROC up_SSRS_TestReport_InputFields1

SELECT DISTINCT

'1' AS CUSTOMER_ID,

'2' AS NAME,

'3' AS ADDRESS,

'4' AS POSTAL_CODE

Note that it is the column names that are important here, not the data. Note also that the column names match exactly to the column names that we added to our datatables earlier (dsMyDataSet).

Step 1

Go into SQL Server 2005, connect to the local server instance and browse to NorthwindCS and select New Query. Run each of the above SPROC code as a query, then refresh to see your new Stored Procedures in the NorthwindCS database.

Step 2

In Visual Studio 2005 with your Test Harness app. open make sure you can see the Data Sources pane. Single right-click in this pane and select Add New Data Source. This starts the Wizard.

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 own 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 its 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 datasource 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 2^nd 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 (1^st column)

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

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

From up_SSRS_TestReport_InputFields2 Sproc

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

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

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

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

We are almost there now

Remember that we need 2 report datasources 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 did 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 lets 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 have used fields from 2 data sources when we did the dragging and dropping earlier, SSRS has automatically added these 2 datasources 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. Its 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 (table1 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 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 Form1.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

}

//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 pecisely one-to-one for each table.

//return the relevant dataset table

return new Microsoft.Reporting.WinForms.ReportDataSourceDataSourceName,
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 1^st one runs a SQL query against a remote server and the 2^nd 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
     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 L3.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 = null;

myOleDbConnection = new OleDbConnection(strConn);

System.Data.OleDb.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]);

}

Code Master

Wednesday, 8 August 2012

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

No comments:

Post a Comment