Export (0) Print
Expand All

Using an External Dataset with Reporting Services

SQL Server 2000

The DataSet object is central to supporting disconnected, distributed data scenarios with ADO.NET. The DataSet is a memory-resident representation of data that provides a consistent relational programming model regardless of the data source. It can be used with multiple different data sources, with XML data, or to manage data local to the application. The DataSet object represents a complete set of data including related tables, constraints, and relationships among the tables. Because of the DataSet object's versatility in storing and exposing data, your data may often be processed and transformed into a DataSet object before any reporting on that data occurs.

With Reporting Services data processing extensions, you can integrate into Reporting Services data processing any custom DataSet objects that are created by external applications. To accomplish this, you create a custom data processing extension in Reporting Services that acts as a bridge between your DataSet object and the report server. Most of the code for processing this DataSet object is contained in the DataReader class that you create.

The first step in exposing your DataSet object to the report server is to implement a provider specific method in your DataReader class that can populate a DataSet object. For example, if you want to simply load static data into a DataSet object, you could do so using the following provider-specific method in your DataReader class.

'Private members of the DataReader class
Private m_dataSet As System.Data.DataSet
Private m_currentRow As Integer

'Method to create a data set
Friend Sub CreateDataSet()
   ' Create a data set.
   Dim ds As New System.Data.DataSet("myDataSet")
   ' Create a data table. 
   Dim dt As New System.Data.DataTable("myTable")
   ' Create a data column and set various properties. 
   Dim dc As New System.Data.DataColumn()
   dc.DataType = System.Type.GetType("System.Decimal")
   dc.AllowDBNull = False
   dc.Caption = "Number"
   dc.ColumnName = "Number"
   dc.DefaultValue = 25
   ' Add the column to the table. 
   dt.Columns.Add(dc)
   ' Add 10 rows and set values. 
   Dim dr As System.Data.DataRow
   Dim i As Integer
   For i = 0 To 9
      dr = dt.NewRow()
      dr("Number") = i + 1
      ' Be sure to add the new row to the DataRowCollection. 
      dt.Rows.Add(dr)
   Next i
   
   ' Fill the data set.
   ds.Tables.Add(dt)
   
   ' Use a private variable to store the data set in your
   ' DataReader.
   m_dataSet = ds
   
   ' Set the current row to -1.
   m_currentRow = - 1
End Sub 'CreateDataSet

// Private members of the DataReader class
private System.Data.DataSet m_dataSet;
private int m_currentRow;

// Method to create a data set
internal void CreateDataSet()
{
   // Create a data set.
   System.Data.DataSet ds = new System.Data.DataSet("myDataSet");
   // Create a data table. 
   System.Data.DataTable dt = new System.Data.DataTable("myTable");
   // Create a data column and set various properties. 
   System.Data.DataColumn dc = new System.Data.DataColumn(); 
   dc.DataType = System.Type.GetType("System.Decimal"); 
   dc.AllowDBNull = false; 
   dc.Caption = "Number"; 
   dc.ColumnName = "Number"; 
   dc.DefaultValue = 25; 
   // Add the column to the table. 
   dt.Columns.Add(dc); 
   // Add 10 rows and set values. 
   System.Data.DataRow dr; 
   for(int i = 0; i < 10; i++)
   { 
      dr = dt.NewRow(); 
      dr["Number"] = i + 1; 
      // Be sure to add the new row to the DataRowCollection. 
      dt.Rows.Add(dr);
   }

   // Fill the data set.
   ds.Tables.Add(dt);

   // Use a private variable to store the data set in your
   // DataReader.
   m_dataSet = ds;

   // Set the current row to -1.
   m_currentRow = -1;
}

Once you create or retrieve your data set, you can use the DataSet object in your implementations of the Read, GetValue, GetName, GetOrdinal, GetFieldType, and FieldCount members of the DataReader class. The following code could be used to expose your DataSet object to Reporting Services.

public bool Read()
{
   _currentRow++;
   if (m_currentRow >= _dataSet.Tables[0].Rows.Count) 
   {
      return (false);
   } 
   else 
   {
      return (true);
   }
}

public int FieldCount
{
   // Return the count of the number of columns, which in
   // this case is the size of the column metadata
   // array.
   get { return m_dataSet.Tables[0].Columns.Count; }
}

public string GetName(int i)
{
   return m_dataSet.Tables[0].Columns[i].ColumnName;
}

public Type GetFieldType(int i)
{
   // Return the actual Type class for the data type.
   return m_dataSet.Tables[0].Columns[i].DataType;
}

public Object GetValue(int i)
{
   return m_dataSet.Tables[0].Rows[m_currentRow][i];
}

public int GetOrdinal(string name)
{
   // Look for the ordinal of the column with the same name and return it.
   // Returns -1 if not found.
   return m_dataSet.Tables[0].Columns[name].Ordinal;
}

public bool Read()
{
   _currentRow++;
   if (m_currentRow >= _dataSet.Tables[0].Rows.Count) 
   {
      return (false);
   } 
   else 
   {
      return (true);
   }
}

public int FieldCount
{
   // Return the count of the number of columns, which in
   // this case is the size of the column metadata
   // array.
   get { return m_dataSet.Tables[0].Columns.Count; }
}

public string GetName(int i)
{
   return m_dataSet.Tables[0].Columns[i].ColumnName;
}

public Type GetFieldType(int i)
{
   // Return the actual Type class for the data type.
   return m_dataSet.Tables[0].Columns[i].DataType;
}

public Object GetValue(int i)
{
   return m_dataSet.Tables[0].Rows[m_currentRow][i];
}

public int GetOrdinal(string name)
{
   // Look for the ordinal of the column with the same name and return it.
   // Returns -1 if not found.
   return m_dataSet.Tables[0].Columns[name].Ordinal;
}
See Also

Extending Reporting Services

Implementing a Data Processing Extension

Reporting Services Extension Library

Show:
© 2014 Microsoft