Creating Reports (.RDL Files) Manually - SQL Server Reporting Services

You can use Business Intelligence Development Studio or Report Designer to easily design your reports, but sometimes, especially when you migrate existing reports, you may find useful to develop or generate report definition files (RDL) directly.

RDL File Structure

RDL file is a XML file containing a report definition:

  • Report data - Connection string, query, fields etc. to obtain the data to display in the report
  • Calculations - Calculations and transformations on the report data (grouping, sorting, filtering, aggregations, scalar formulas etc.)
  • Report Layout - Specifies how to present the data and calculations in the resulting report

<Report> is the root element of an RDL document.

Minimal Valid RDL File (Blank Report)

Minimal Valid RDL file without the data and layout (blank report) that you can open in Report Designer must contain Report, ReportSections, ReportSection, Body, Page, Width and Height elements:

   <?xml version="1.0" encoding="utf-8"?>
   <Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" 
             xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition" 
             xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition">
     <ReportSections>
       <ReportSection>
        <Body>
          <Height>57.15mm</Height>
        </Body>
        <Page>
        </Page>
        <Width>152.4mm</Width>
       </ReportSection>
     </ReportSections>
   </Report>

Note. ReportSections and ReportSection elements are required for RDL 2010 specification (SQL Server 2008 R2)

Defining Report Data

To define the data for a report, you have to specify one or more data sources (connection information), one or more data sets (query and fields):

  <Report>
  ...
   <DataSources>
     <DataSource Name="DataSource1">
        <ConnectionProperties>
          <DataProvider>SQL</DataProvider>
          <ConnectString>Data Source=serverHost;Initial Catalog=databaseName</ConnectString>
          <IntegratedSecurity>true</IntegratedSecurity>
        </ConnectionProperties>
     </DataSource>
   </DataSources>
   <DataSets>
    <DataSet Name="DataSet1">
        <Query>
          <DataSourceName>DataSource1</DataSourceName>
          <CommandText>select * from cities</CommandText>
        </Query>
        <Fields>
          <Field Name="city">
            <DataField>city</DataField>
            <rd:TypeName>System.String</rd:TypeName>
          </Field>
          <Field Name="country">
            <DataField>country</DataField>
            <rd:TypeName>System.String</rd:TypeName>
          </Field>
          <Field Name="continent">
            <DataField>continent</DataField>
            <rd:TypeName>System.String</rd:TypeName>
          </Field>
        </Fields>
       </DataSet>
     </DataSets>
     ...
     <ReportSections>
        ...
     </ReportSections>
     ...
     </Report>

Migration Resources

Discussion

, January 17, 2013 6:47 am

the information provided above is good for basic understanding about the reporting…manually how to configure the report using this .rdl file which is in xml format.there are some special attribute or elements in the xml file need to deep understand……

You could leave a comment if you were logged in.