Report Authoring with Report Builder

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

  1. MinalS

    MinalS New Member

    Joined:
    Jul 8, 2014
    Messages:
    138
    Likes Received:
    32
    Trophy Points:
    0
    Report Authoring

    The report writing needs to plan with other activities. The considerations are audience, report structure, look and feel of report, report visibility, delivery format, used on web or desktop, etc. All these considerations are connected with each other.

    Parameterized reports

    Filtering data is an important part for retrieval of data in a short time. The parameterized report depends on the report processing. The output of the report depends on parameters. They are useful in creation of linked reports, sub reports, or drill through reports.

    Two parameters are used in the report builder 2.0 as report parameters and query parameters. The query parameters are useful as data source for retrieval of data. In query parameter, a value must be specified by the user. Report parameters are useful in report processing with the data present when the filtering is done.

    Filtering data at source using query parameter

    Data Source creation
    1. Open the Report builder 2.0 and start the reporting services
    2. In the builder, click table or matrix wizard in the design area
    3. The new table or matrix window opens
    4. Click Next button. The Data Source properties window will appear. Select the data source from the databases
    5. Click Build button. The connection properties window opens
    6. Click Refresh button and select the server from where the data is to be retrieved
    7. Add the authentication data and click on the drop down and select or enter the database name. Click Test connection button
    8. Click Ok and the test results window and the connection properties window will close
    9. Click Edit button and modify the name to a new one
    Query design
    1. In the new table or matrix window, click next button. The design a query page window is shown below:

      [​IMG]
    2. Click on the table and add the checkmarks to the columns needed.
    Parameter design
    1. Click on the filter icon showing the add filter text above the applied filters pane
    2. Click on the default employee name from the name column and click on the city in the drop down menu
    3. Click on Like in the operator column and select is from the list
    4. Add a location and add a checkmark to the Parameter column
    5. Click Next button. The Arrange fields page of the New Table or Matrix wizard gets displayed.
    6. Click on the EmpID and add it to the values panel. Add the required variables to the Values panel
    7. Click Next. Choose the layout page gets displayed. Click next button
    8. In Choose a style window, accept the default value and click finish button
    9. Right click on the Dataset and select dataset properties from the list.
    10. Click on the Parameters field from the left pane. The Choose query parameter values get displayed. The Add and delete parameters are displayed
    11. Click Fields and it will display the columns that are in the report. User can add or delete columns
    12. Click Ok
    Viewing the report
    1. In the Home menu, click Run to view section
    2. Click Export for verifying the various formats.
    3. Click on Parameters in the Options section of the Run tab
    4. Click Office Button and save the report with a unique name
    5. Open the server by adding the URL in the IE browser
    Parameter removal from the created report

    The Parameters can be removed easily and effectively. The reports can be easily modified after they are designed by the user.

    The following steps are used for removing parameters
    1. Click on the Dataset in the report data
    2. Click on Parameters option. Choose query parameter values page is displayed
    3. Click in the Parameter name field on the page
    4. Click delete button. Click ok on the dataset properties page
    5. Expand the parameters folder in the report data and delete the parameter by right clicking and selecting the delete option
    6. Right click on the dataset and click query icon
    7. In the applied filters, delete the parameter for the filter through the delete button
    8. Click ok of the query designer
    Adding document map to the report

    Document map is navigational feature which allows user to navigate through the document and the hierarchies. When the user adds a document map, an extra side panel is added to the report.
    1. Right click on the row group and select Row group, group properties as shown

      [​IMG]
    2. In the General tab of the group properties, user can add and delete the group. User can choose to add a group using the drop down list or through the expression window.
    3. The Page breaks can be set up for the windows. It can also be added for the group.
    4. The sorting options are provided for the results. User can sort the fields in the dataset or an expression.
    5. The visibility of the group can be modified using Show or Hide, or hide based on an expression
    6. A variable can be added to the group. The values in the group scope can be calculated
    7. Click on the Advanced list item in the Group properties window
    8. Select the document map from the list
    9. Select the item and click ok
    10. Click Home and run the report
    Linked reports creation

    A linked report is a report server item used for accessing the existing report. The linked report is derived from the existing report. The report layout and the data source properties of the original report are inherited.

    When the user needs to add additional versions to an existing report, a linked report is created. The linked reports are based on the concept of parameterized reports. The linked reports are created when user needs to deploy the reports with different settings.

    The following steps are used for creating a linked report.
    1. Open the report manager in the system.
    2. Search for the folder containing the report user needs to link
    3. Open the options menu and click create linked report
    4. If user needs to select a different folder, click change location. Add the folder name required. Click ok
    5. The linked report is available to the user
    Creating a matrix report with the report model

    A matrix report is a data region containing a matrix data region. A matrix report consists of rows and columns.

    The steps for creating a matrix report are as explained below:

    Create a matrix report from new table or matrix wizard
    1. Click Start, Programs, Microsoft SQL Server Report builder
    2. From the left pane, select the new report option
    3. Click Table and matrix wizard from the right pane
    4. In the choose dataset page, click create a dataset
    5. Click Next. In the design query page, click edit as text
    Add Data and select the layout and style
    1. In the Arrange fields, select field from the available fields and add to the row groups
    2. Add all the required fields to the row groups and column groups
    3. Click Next. Select the page layout
    4. Select the Expand/collapse group option is selected
    5. In the choose a style page, select slate
    6. Click Finish. Click Run
    Adjacent column group

    User can nest the column and row groups in adjacent or parent child relationship.
    1. Click design and move to the design view
    2. Right click cell and point to Add group, click adjacent right
    3. In the Tablix group dialog box, select the appropriate field and click ok
    4. Right click on the cell in the new column group, click expression
    5. Add an expression to the expression box

      Code:
      Expression to:”=Sum(Fields!TotalCost.Value)”
      
    6. Click Run to preview report
    Merge matrix cells

    The number of cells in the corner area is dependent on the number of rows and column groups in the matrix.

    The following steps are used for merging the matrix cells
    1. Click Design and move to the design view
    2. Click matrix and view the row and column handles above the matrix
    3. Right click the cells and click merge cells
    4. Right click on the corner cell and click textbox properties
    5. Click Fill tab. Click on the fx button
    6. Click Run to preview the report
    Save the report

    The report can be saved on the computer or to the report server.

    For saving the report on the computer, follow the steps mentioned below:
    1. Click the report builder button. Click Save As
    2. Click Desktop, my documents, navigate to the folder where the report needs to be saved
    3. Replace the default application name
    4. Click Save and exit
    For saving the report on the report server, follow the steps mentioned below:
    1. Click the report builder button. Click Save As
    2. Click Recent Sites and servers.
    3. Select or add the name of the report server for saving the report
    4. Replace the default application name
    5. Click Save and exit
     
    Last edited by a moderator: Jan 21, 2017
    1 person likes this.

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