Reporting Services in SQL Server

Discussion in 'SQL Server' started by MinalS, May 12, 2016.

  1. The reporting services provides user with application programming interfaces and tools for the creation of reports. The reports can be easily authorized and published using these tools. User can create the environment on the current database and the web server infrastructure.

    User can render the reports on the web and on desktop formats. Reports can be in the form of tables, accessing data at specific time duration when user opens the report. There are different features of reports which can be used for defining them.

    List of different report definition features are as mentioned below:
    1. Matrix, tabular, free form and chart reports: User can create tabular, form based, matrix reports dependent on the data which is summarized by the user. The controls and the layout of the reports can be in free flow in the free flow layout.
    2. Graphic elements: The images or resources are easily referenced using the external data
    3. Aggregation: The data using the expressions can be easily summarized using aggregation. They contain min, max, sum, average, count and total.
    4. Parameterized reports: The parameters can be added to filter a query or a dataset.
    5. Data source support: The report contain data from multidimensional or relation data from the server.
    6. Navigation: The navigation option is provided by the document maps and the bookmarks.
    7. Presentation type: There are different presentation types like page and web oriented, desktop application formats. The formats can be HTML, MHTML, PDF, etc.
    Report Management features

    The different report management features which are used in reporting services are as mentioned below:
    1. Job management: It is used for analyzing and removing the processing reports.
    2. Report manager: The tool which is useful for managing the reports. The report server is useful for accessing it.
    3. Role based security: The security is useful in accessing the reports, resources and folders in the system. The security for the user access can be easily modified at any level.
    4. Linked reports: The existing reports can be easily modified by adding the set of properties, values of parameters, and security settings.
    5. Configuring server: The files containing the email, security, etc can be edited
    6. Scripted administration: The server administration tasks for the server can be easily created and executed
    7. Shared schedules: The data source connections are easily created and managed for the related reports.
    Report execution features

    The features needed for the report execution are as listed below:
    1. Monitoring performance: The SQL and ASP.NET server performance objects are used for monitoring the web service.
    2. Performance optimization: Parallel processing of queries is done for optimizing the server performance.
    3. Caching reports: The most required reports are cached and used
    4. Scheduling: The server agent supports the scheduled operations. User can schedule history, execution, caching.
    5. Execution time out settings: The time is assigned for the prevention of the report from resource overloading.
    Delivery features and accessing reports
    1. E- mail delivery: All the rendered reports are delivered to the mailbox.
    2. Navigating reports: User can add the navigation of pages, zooming effects, searching data in the reports
    3. File share delivery: All the rendered reports are delivered to the file share.
    4. Subscriptions: All the subscriptions are accessed and placed in a single location
    5. Standard subscriptions: The report delivery is automated using the standard subscriptions.
    6. Data driven subscriptions: The report distribution is possible through the data driven subscriptions. A list of recipient and instructions for the delivery are provided at run time.
    Architecture features
    1. Web and windows service: The server implements web and window service.
    2. Modular architecture: There are different components present in the reporting services. Some of the components are used for extensibility.
    3. Multiple rendering: The separation of data from the final rendering is performed using the loosely coupled phases.
    Report programming features
    1. Access URL: Through the parameterized URL strings, user can easily access the data. The report server namespace contains the address.
    2. SOAP API: The web service methods are used for accessing the reports
    3. Extensible delivery: Custom delivery extensions are used for routing the file shares, internal archive, or applications.
    4. Extensible rendering: The custom rendering extension is used for supporting the presentation in the application or web formats.
    5. Extensible data processing: Custom data processing extensions are supported to query and provide results.
    Report Life cycle in reporting services

    The lifecycle of a report is important in learning the involvement of different components in report creation. The report life cycle consists of management and delivery. All the phases of the report life cycle are explained below:
    1. Authoring phase: The authorizing tool is used by the designer for creating the report definition. The layout, query data and connection is provided in the definition.
    2. Management phase: Once the report definition is done, the report is published on the server. The server administers the managed reports. The managed reports is added in the server and helps it to be renamed, linked, scheduled and modified.
    3. Generated reports: The processing of reports does process data, combine the data with the layout, rendering data in the specific format. A generated report is produced after processing.
    4. Accessing reports: Once the report are generated, they can be accessed in different ways. The report is visible through email, website, or printer.

    Creation of reports



    The creation of report consists of report server project where the report definition file is added. Next, the report definition file is created, data source for the project is declared. All the steps which are involved in the report creation are explained below.

    1. Report server project creation

    1. In your system, click Start, All Programs, and Microsoft SQL Server 2012. Select SQL Server Data Tools option.
      [​IMG]
    2. From the File menu, click Project option. From the installed templates, click Business Intelligence Settings
    3. Select Report Server Project option, add an appropriate name to the project
    2. Defining connection data
    1. From the report pane, select New, Data Source option
    2. Add an appropriate name to the report. Select embedded connection option.
    3. From the type, select Microsoft SQL Server option.
    4. Add the following code in the connection string box
      Code:
      DataSource = localhost\SQLEXPRESS; initial catalog = demo;
      
    5. In the left pane, select Credentials and select Use Windows Authentication option. Click Ok.
      [​IMG]
    3. Dataset definition for table report

    1. 1) In the report data pane, select New, click Dataset. A dataset properties dialog box will open.
    2. 2) Add a name in the name textbox. Select the Text radio button in the query type option.
    3. 3) Add the following query in the query textbox.
      Code:
      	select * from orderdata
      
    4. Click on the Query Designer button. User can navigate to the graphical query designer by selecting Edit As Text.
    5. Click Ok and close the Dataset properties box.
      [​IMG]

    4. Addition of table to the report

    1. In the toolbox, select table. Navigate to the design view.
    2. In the report data pane, expand the dataset for viewing the fields
    3. Select and add all the required fields from the data pane and add it to the column in the table
    4. Once all the fields have been added, click on Preview tab for displaying the reports in the preview view.
     
    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