SSRS Reports Using Reports Wizards

1. Start a new Business Intelligence project in Visual Studio 2005. Then, from the menu, select Project > Add New Item. Select the Report Wizard option and call the file "FirstReportWZ.rdl". Click Add. Click Next on the Welcome screen and this will bring you to the Select the Data Source screen:

Select the New data source radio button and give the data source a name. Select Microsoft SQL Server as the type.


Tip: Use of the Shared data source option is actually a better option in most cases but, for the sake of this example, we will just create a new data source. We're going to set up a shared data source later in this article. 

Click the Edit button to bring up the Connection Properties dialog:

Enter or select a server name. The default is to log on using Windows Authentication. If you are using SQL Server Authentication, choose that setting, and enter the username and password. Finally, select a database, eg : ReportingDemo. Make sure you test the connection before you click OK.



Next up is the Design the Query screen. You can use the Query Builder by clicking the button at the top left, but in this case simply enter "Select * From Customer" into the Query string text box and click Next. On the Select the Report Type simply select the type you prefer (tabular or matrix) and hit Next.



This brings up the Design the Table screen:

Select State in the Available fields box and click the Group button. This will group the data in the result set by State. Select Next. On the Choose the Table Layout page, select the Stepped option and check the Enable Drilldown checkbox. On the next screen, choose a style for your report (I chose Ocean) and click Next.



This brings up the final screen, Completing the Wizard:

It shows a summary of your report options, and gives you an opportunity to rename the report and to preview it. Click Finish to end the wizard.



This is how your report will look in the standard Report Designer:

There are three tabs: Data, Layout and Preview. You should be on the Layout tab. Click on the Preview tab to view the report. Since this report doesn't take any arguments, it will run as soon as you click on the tab.

Use the +/- symbols next to the State abbreviations to expand and collapse the grouping of the report.

Creating the RDL Generator Visual Studio Project

1. On the File menu, point to New, and then click Project to open the New Project dialog box.
2. Click the Visual C# node in the Project types pane.
3. Click the Console Application icon.
4. In the Name box, enter a name for your project. Type the name RDLGenerator.
5. In the Location box, enter the path where you want to save your project, or click Browse to navigate to the folder.
6. Click OK. A collapsed view of your project appears in Solution Explorer.
   
   In Solution Explorer, expand the project node. A code file with the default name of Program.cs has been added to your project.

When you have finished creating the application template, replace the contents of the code file with the following:

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Xml;

namespace MyRDLGenerator
{
   class RdlGenerator
   {
      SqlConnection m_connection;
      string m_connectString;
      string m_commandText;
      ArrayList m_fields;

      public static void Main()
      {
         RdlGenerator FirstRdlGenerator = new RdlGenerator();
         myRdlGenerator.Run();
      }

      public void Run()
      {
         try
         {
            // Call methods to create the RDL
            this.OpenConnection();
            this.GenerateFieldsList();
            this.GenerateRdl();

            Console.WriteLine("RDL file generated successfully.");
         }

         catch (Exception exception)
         {
            Console.WriteLine("An error occurred: " + exception.Message);
         }

         finally
         {
            // Close the connection string
            m_connection.Close();
         }
      }

      public void OpenConnection()
      {
         // Create a connection object
   m_connection = new SqlConnection();
        
   // Create the connection string
   m_connectString = @"data source=localhost;initial catalog=DatabaseName;integrated security=SSPI";
   m_connection.ConnectionString = m_connectString;
        
   // Open the connection
   m_connection.Open();

      }

      public void GenerateFieldsList()
      {
         SqlCommand command;
   SqlDataReader reader;

   // Executing a query to retrieve a fields list for the report
   command = m_connection.CreateCommand();
   m_commandText ="SELECT Person.CountryRegion.Name AS CountryName, Person.StateProvince.Name AS StateProvince " +
      "FROM Person.StateProvince " +
      "INNER JOIN Person.CountryRegion ON Person.StateProvince.CountryRegionCode = Person.CountryRegion.CountryRegionCode " +
      "ORDER BY Person.CountryRegion.Name";
   command.CommandText = m_commandText;
  
   // Execute and create a reader for the current command
   reader = command.ExecuteReader(CommandBehavior.SchemaOnly);
  
   // For each field in the resultset, add the name to an array list
   m_fields = new ArrayList();
   for (int i = 0; i <= reader.FieldCount - 1; i++)
   {
      m_fields.Add(reader.GetName(i));
   }

      }

     public void GenerateRdl()
{
    // Create an XML document
    XmlDocument doc = new XmlDocument();
    string xmlData = "<Report " +
    "xmlns=\"link\">" +
        "</Report>";
    doc.Load(new StringReader(xmlData));

    // Report element
    XmlElement report = (XmlElement)doc.FirstChild;
    AddElement(report, "AutoRefresh", "0");
    AddElement(report, "ConsumeContainerWhitespace", "true");

    //DataSources element
    XmlElement dataSources = AddElement(report, "DataSources", null);
    //DataSource element
    XmlElement dataSource = AddElement(dataSources, "DataSource", null);
    XmlAttribute attr = dataSource.Attributes.Append(doc.CreateAttribute("Name"));
    attr.Value = "DataSource1";
    XmlElement connectionProperties = AddElement(dataSource, "ConnectionProperties", null);
    AddElement(connectionProperties, "DataProvider", "SQL");
    AddElement(connectionProperties, "ConnectString", m_connectString);
    AddElement(connectionProperties, "IntegratedSecurity", "true");
    //DataSets element
    XmlElement dataSets = AddElement(report, "DataSets", null);
    XmlElement dataSet = AddElement(dataSets, "DataSet", null);
    attr = dataSet.Attributes.Append(doc.CreateAttribute("Name"));
    attr.Value = "DataSet1";
    //Query element
    XmlElement query = AddElement(dataSet, "Query", null);
    AddElement(query, "DataSourceName", "DataSource1");
    AddElement(query, "CommandText", m_commandText);
    AddElement(query, "Timeout", "30");
    //Fields element
    XmlElement fields = AddElement(dataSet, "Fields", null);
    XmlElement field = AddElement(fields, "Field", null);
    attr = field.Attributes.Append(doc.CreateAttribute("Name"));
    attr.Value = "CountryName";
    AddElement(field, "DataField", "CountryName");
    field = AddElement(fields, "Field", null);
    attr = field.Attributes.Append(doc.CreateAttribute("Name"));
    attr.Value = "StateProvince";
    AddElement(field, "DataField", "StateProvince");

    //end of DataSources

    //ReportSections element
    XmlElement reportSections = AddElement(report, "ReportSections", null);
    XmlElement reportSection = AddElement(reportSections, "ReportSection", null);
    AddElement(reportSection, "Width", "6in");
    AddElement(reportSection, "Page", null);
    XmlElement body = AddElement(reportSection, "Body", null);
    AddElement(body, "Height", "1.5in");
    XmlElement reportItems = AddElement(body, "ReportItems", null);
    // Tablix element
    XmlElement tablix = AddElement(reportItems, "Tablix", null);
    attr = tablix.Attributes.Append(doc.CreateAttribute("Name"));
    attr.Value = "Tablix1";
    AddElement(tablix, "DataSetName", "DataSet1");
    AddElement(tablix, "Top", "0.5in");
    AddElement(tablix, "Left", "0.5in");
    AddElement(tablix, "Height", "0.5in");
    AddElement(tablix, "Width", "3in");
    XmlElement tablixBody = AddElement(tablix, "TablixBody", null);
    //TablixColumns element
    XmlElement tablixColumns = AddElement(tablixBody, "TablixColumns", null);
    XmlElement tablixColumn = AddElement(tablixColumns, "TablixColumn", null);
    AddElement(tablixColumn, "Width", "1.5in");
    tablixColumn = AddElement(tablixColumns, "TablixColumn", null);
    AddElement(tablixColumn, "Width", "1.5in");
    //TablixRows element
    XmlElement tablixRows = AddElement(tablixBody, "TablixRows", null);
    //TablixRow element (header row)
    XmlElement tablixRow = AddElement(tablixRows, "TablixRow", null);
    AddElement(tablixRow, "Height", "0.5in");
    XmlElement tablixCells = AddElement(tablixRow, "TablixCells", null);
    // TablixCell element (first cell)
    XmlElement tablixCell = AddElement(tablixCells, "TablixCell", null);
    XmlElement cellContents = AddElement(tablixCell, "CellContents", null);
    XmlElement textbox = AddElement(cellContents, "Textbox", null);
    attr = textbox.Attributes.Append(doc.CreateAttribute("Name"));
    attr.Value = "HeaderCountryName";
    AddElement(textbox, "KeepTogether", "true");
    XmlElement paragraphs = AddElement(textbox, "Paragraphs", null);
    XmlElement paragraph = AddElement(paragraphs, "Paragraph", null);
    XmlElement textRuns = AddElement(paragraph, "TextRuns", null);
    XmlElement textRun = AddElement(textRuns, "TextRun", null);
    AddElement(textRun, "Value", "CountryName");
    XmlElement style = AddElement(textRun, "Style", null);
    AddElement(style, "TextDecoration", "Underline");
    // TablixCell element (second cell)
    tablixCell = AddElement(tablixCells, "TablixCell", null);
    cellContents = AddElement(tablixCell, "CellContents", null);
    textbox = AddElement(cellContents, "Textbox", null);
    attr = textbox.Attributes.Append(doc.CreateAttribute("Name"));
    attr.Value = "HeaderStateProvince";
    AddElement(textbox, "KeepTogether", "true");
    paragraphs = AddElement(textbox, "Paragraphs", null);
    paragraph = AddElement(paragraphs, "Paragraph", null);
    textRuns = AddElement(paragraph, "TextRuns", null);
    textRun = AddElement(textRuns, "TextRun", null);
    AddElement(textRun, "Value", "StateProvince");
    style = AddElement(textRun, "Style", null);
    AddElement(style, "TextDecoration", "Underline");
    //TablixRow element (details row)
    tablixRow = AddElement(tablixRows, "TablixRow", null);
    AddElement(tablixRow, "Height", "0.5in");
    tablixCells = AddElement(tablixRow, "TablixCells", null);
    // TablixCell element (first cell)
    tablixCell = AddElement(tablixCells, "TablixCell", null);
    cellContents = AddElement(tablixCell, "CellContents", null);
    textbox = AddElement(cellContents, "Textbox", null);
    attr = textbox.Attributes.Append(doc.CreateAttribute("Name"));
    attr.Value = "CountryName";
    AddElement(textbox, "HideDuplicates", "DataSet1");
    AddElement(textbox, "KeepTogether", "true");
    paragraphs = AddElement(textbox, "Paragraphs", null);
    paragraph = AddElement(paragraphs, "Paragraph", null);
    textRuns = AddElement(paragraph, "TextRuns", null);
    textRun = AddElement(textRuns, "TextRun", null);
    AddElement(textRun, "Value", "=Fields!CountryName.Value");
    style = AddElement(textRun, "Style", null);
    // TablixCell element (second cell)
    tablixCell = AddElement(tablixCells, "TablixCell", null);
    cellContents = AddElement(tablixCell, "CellContents", null);
    textbox = AddElement(cellContents, "Textbox", null);
    attr = textbox.Attributes.Append(doc.CreateAttribute("Name"));
    attr.Value = "StateProvince";
    AddElement(textbox, "HideDuplicates", "DataSet1");
    AddElement(textbox, "KeepTogether", "true");
    paragraphs = AddElement(textbox, "Paragraphs", null);
    paragraph = AddElement(paragraphs, "Paragraph", null);
    textRuns = AddElement(paragraph, "TextRuns", null);
    textRun = AddElement(textRuns, "TextRun", null);
    AddElement(textRun, "Value", "=Fields!StateProvince.Value");
    style = AddElement(textRun, "Style", null);
    //End of second row

    //End of TablixBody

    //TablixColumnHierarchy element
    XmlElement tablixColumnHierarchy = AddElement(tablix, "TablixColumnHierarchy", null);
    XmlElement tablixMembers = AddElement(tablixColumnHierarchy, "TablixMembers", null);
    AddElement(tablixMembers, "TablixMember", null);
    AddElement(tablixMembers, "TablixMember", null);

    //TablixRowHierarchy element
    XmlElement tablixRowHierarchy = AddElement(tablix, "TablixRowHierarchy", null);
    tablixMembers = AddElement(tablixRowHierarchy, "TablixMembers", null);
    XmlElement tablixMember = AddElement(tablixMembers, "TablixMember", null);
    AddElement(tablixMember, "KeepWithGroup", "After");
    AddElement(tablixMember, "KeepTogether", "true");
    tablixMember = AddElement(tablixMembers, "TablixMember", null);
    AddElement(tablixMember, "DataElementName", "Detail_Collection");
    AddElement(tablixMember, "DataElementOutput", "Output");
    AddElement(tablixMember, "KeepTogether", "true");
    XmlElement group = AddElement(tablixMember, "Group", null);
    attr = group.Attributes.Append(doc.CreateAttribute("Name"));
    attr.Value = "Table1_Details_Group";
    AddElement(group, "DataElementName", "Detail");
    XmlElement tablixMembersNested = AddElement(tablixMember, "TablixMembers", null);
    AddElement(tablixMembersNested, "TablixMember", null);

    //End of Tablix, ReportItems, ReportSections

    //Save XML document to file
    doc.Save("Report1.rdl");

}

public XmlElement AddElement(XmlElement parent, string name, string value)
{
    XmlElement newelement = parent.OwnerDocument.CreateElement(name,
        "link");
    parent.AppendChild(newelement);
    if (value != null) newelement.InnerText = value;
    return newelement;
}

   }
}