Report Builder in SQL Server Reporting Services

Discussion in 'SQL Server' started by MinalS, May 26, 2015.

  1. MinalS

    MinalS New Member

    Joined:
    Jul 8, 2014
    Messages:
    138
    Likes Received:
    32
    Trophy Points:
    0
    The user interface of report builder

    The various parts of the report builder interface are described. User can access the report builder from Start, All Programs, and Microsoft SQL Server 2008 report builder. The report builder interface is as shown below:

    [​IMG]

    The design area consists of two icons as table or matrix and chart. They are associated with wizard which helps in performing various tasks. User can launch more than one instance of the report builder.

    The undo, redo and save controls are available. When user clicks on the save icon, the save as report window gets displayed. The .rdl extension is used.
    Click on the Office button and a drop down window is displayed as shown below:

    [​IMG]

    There are various tasks that can be performed in the report builder. They are creation of a new report, open an existing report, save a report, save reports with different name. The Save button saves the file at a default location and Save as invokes the similar window to save the report with a different name.

    The recent documents pane shows the recent reports created by the user. New helps user to create a new report. Click on Open and a new report window opens.

    When user clicks the option button, the report builder options window is displayed consisting of settings and resource as shown below:

    [​IMG]

    User can view, modify the report builder settings. By clicking on the resources button a new window enabling the interaction with Microsoft for SSRS activities, community is possible.

    The ribbon

    The main menu consists of Home, Insert and view menu items. It is the container for other toolbar items. The ribbon is the replacement for the classic menus.

    Home

    The Home menu containing items from left to right and is divided into sections is as shown below:

    [​IMG]

    The run toolbar item with the title views when clicked would execute the report open in the design view. The output will be the current date and time at the screen center. The font, paragraph, border and number toolbar sections are enabled if there is an editing to the reports.

    When user clicks the Insert menu item, the four sections as data regions, report items, subreports, and header and footer are displayed. The report can consist of one or more data regions.

    Data Regions

    The Data regions section consists of table, matrix and list. The graphical controls like gauge and chart are also used. The report builder is built as the dataset must be defined before any data regions are added to the body.

    Table

    The table is used for displaying the data from the database either all data or related to groups. It has a fixed number of columns which can be adjusted at the design time. The data can be grouped by a single field or multiple fields. User can create groups using the expression designer. Row groups are created. Static rows can be added for row headings. Aggregate for groups can be added by the user.

    Click on Insert, Table, insert table option. Click on the design surface and a table can be added to the design area. The table with handles for adjusting the dimensions is displayed. User can dragged to any other location on the design surface.

    The Dataset icon in the cell provides user with a drop down list. It consists of the fields in the dataset. User can select any field to occupy the cell when clicked and the header is added to the table.

    When the user right clicks on a cell, a drop down menu is available. The following items are available for the user.
    1. User can cut, copy, delete and paste the contents for the highlighted textboxes
    2. The textbox can be populated with the expression using the expression builder. The fx Expression is clicked to get the expression
    3. The Select is used for selecting the body of the textbox
    4. User can insert a new row or column. The rows can be added above or below of the clicked cell. The columns can be added before or after the clicked cell
    5. Rows and column group can be added
    The textbox properties window consists of various properties that can be arranged by the user. The properties are displayed on the left and a list for all the items have their own page is displayed. The Help button on any of the page takes to the definition of the properties.

    [​IMG]

    In the general page, the changes to the element in the Name, Value and sizing options page is available. The value is chosen from the column values from the dataset. The tooltip can be added for displaying the text.

    In the Number page, the number and date type formatting can be set for the cells containing the date and time.

    In the Alignment page, the vertical and horizontal alignments and textbox padding of the content from the edges of the cell can be done.

    The Font and border page are used for setting the font and page border.

    The Fill property adds a background color to the report and adds a graphic element. The element can be embedded, internal or from the database.

    The visibility of the textbox can be show, hide, show or hide based on an expression.
    The interactive sorting page allows user to define the sorting options on the textbox.

    Matrix

    The matrix provides functionality similar to cross – tab reports in MS Access. The matrix must contain one row and one column. The matrix is expanded in both the directions as horizontally and vertically for row groups.

    When the user clicks on the insert matrix and drops it on the design area of the report builder. When the boundary of the empty matrix is clicked, many features can be explored. The ColumnGroup, RowGroup and Data are the basic elements of the matrix.

    The row and columns group cells have their properties that can be displayed by right clicking on it. When the user right clicks on cell as Rows, the drop down menu is displayed.

    The Tablix for rows and columns have submenu item shown as rows. The columns can be applied with the similar properties.

    List

    The list is a data region for each row of data. It provides a single container for data that is used for generating the free form reports. There is no rigid structure as table for a data. User can place a list inside another list or column from the dataset.

    The designer interface is flexible and provides features by Tablix structure for displaying details and adding groups. When the list is added in the designer window, a row and a column is present. They can be extended by right clicking on the handles.

    Chart

    Charts combine the complete data in highly informative way. They are used for creating a graph from data for summarizing the important information. The Chart Wizard is useful for creating charts in report builder. There are different types of chart like bar charts, column charts, line charts, pie charts, area charts, etc.

    User can insert a chart by clicking Insert, Chart, Insert Chart. Next, click the body of the report. The Select Chart Type window appears.

    [​IMG]

    Accept the choice and click ok button. The chart template is added to the report. Double clicking inside the chart, the drop data fields appear on the sides of the chart. Drag and drop the appropriate fields on the chart.

    Repeat the similar process for all the fields. The design of the chart appears to the user. Execute the report from the ribbon. The chart will be displayed.

    Gauge

    The gauge is a data region in report builder. It consists of a single data region. It has a meter with range of values and indicator for representing the values or some configurable value. They can be used together with table and matrix elements.

    When the user adds a gauge to the report, it is positioned with the panel. The properties of it can be accessed when user clicks outside the boundary of the gauge. The data property must be associated with the dataset.
    1. Click on the report for displaying the report handles. Right click on the column and from the drop down menu, click Insert column, right.
    2. Click Insert, Gauge and click on the Data cell of the new column
    3. Select the gauge type to be displayed

      [​IMG]
    4. Accept the default that appears and click ok button.
    5. Double click the gauge and the configurable features are displayed
    6. Add the details for data representation in the second column of the table.
    7. Click Home, Run to process and display the report
    Report Data

    The report data is important in creating report from scratch without the wizards in default. When the user clicks the new drop down, the different menu items are displayed. They are as explained below:
    1. Data Source window is used to establish connection to the data source
    2. The Dataset displays the various properties present. They can be used for design a query, an SQL statement or import the SQL query
    3. Parameter helps user create parameter through the properties window.
    4. Image helps user select images from the system.
    Modifying the report

    The MS Access report is imported using the Visual Studio and hosted on the report server. The report will be modified to use the report items in the report builder.
    1. Start the report builder on your system
    2. Click on the Office button and from the drop down window, click open.
    3. Click Recent Sites and servers in the left pane. The URL for the report server is displayed
    4. Click on the open button by clicking the URL
    5. Click on the MS Access folder and click open button
    6. The report OrderInfo is highlighted and click open
    7. The dataset for the report is Dataset1 on the left of the report body is displayed.
    8. Highlight the ProductName and right click on it
    9. Click on the Placeholder properties and the page is displayed
    10. Modify the markup type to HTML and click on the fx symbol
    11. In the expression window, modify the expression
    12. Click ok for expression and placeholder properties window
    13. Click Run button in the Home menu
    14. Click View report button and the report is displayed
    Creating reports with charts and gauges

    The Microsoft excel spreadsheet is connected with some data and the report is created. User can add chart and gauge data regions to the report for displaying the data.
    1. Create a Microsoft Excel spreadsheet with data
      1. Create a simple spreadsheet with data using the MS Excel
      2. Open the empty MS Excel file and add some numbers to it
      3. Save the file by providing an appropriate name
    2. Create an ODBC DSN to access data
      1. Click Start, All Programs, Control Panel, Administrative Tools, Data Sources
      2. Change the tab to User DSN and click Add button
      3. Highlight the Microsoft Excel driver and click finish button
      4. The ODBC Microsoft Excel setup window is as shown below

        [​IMG]
      5. Add a name and description. Click select workbook button
      6. Select the file from the database name textbox. Click ok
    3. Create a data source using DSN in the report builder
      1. Open the report builder and click office button, select new from the drop down
      2. Click on the table or matrix to open the new table or matrix window
      3. Click new button to open the data source properties window
      4. Add a name to the data source and modify the default value
      5. Click on the select connection type and select ODBC
      6. Once ODBC is selected, click build button
      7. The select data source window with the File Data source page will be displayed
      8. Modify the machine data source, click gauges, ok
      9. Locate the excel file and click ok
      10. The DSN connection string is entered in the connection properties window
      11. Click Test connection window and check for the connection
      12. The connection string field in the Data source properties page is updated with the connection information
    4. Create a dataset based on the excel file data
      1. Click Next button in the New Table or Matrix window
      2. The design a query page is displayed. Add the query and click on the run button
    5. Design a report to display the data - Once user has the dataset for report, next is to display the data. The following wizard is used for data display.
      1. The Arrange fields page gets displayed. Click on the values in the available fields and add them to the Row groups and values fields
      2. Click Next button and select the layout for the page
      3. Select the page style and click finish button
      4. Execute by clicking Home, Run
    6. Create a chart to display the data - The chart is created on the basis of the available data. The chart template is used for chart creation.
      1. Click on the design button to move to the design view
      2. Click Insert, Chart, Insert Chart and click in the space
      3. The select chart type window is displayed. Accept the choice and click ok
      4. Double click in the chart and the drop data fields are present in the sides of the chart
      5. Drag the data fields to the chart.
      6. Run the report and the chart is displayed
    7. Add Gauge to display the data - The gauge adds the features for enhancing the appearance.
      1. Click on the report to display the handles. Right click columns and select Insert column, Right
      2. Click Insert, Gauge. Click Data cell of the new column
      3. Accept the default and click ok
      4. Double click the gauge for displaying the configurable features
      5. Add the data fields to the gauge
      6. Click Home, Run to execute the process and display the data
     
    Last edited by a moderator: Jan 21, 2017

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice