What are you looking for ?
Home » » SSRS 2016 (2)

SSRS 2016 (2)

Posted by Wael.Medhat Posted on 2:37 pm with No comments


Report Builder


Report Builder is a stand-alone authoring environment for creating Reporting Services paginated reports outside of Visual Studio.

Report Builder provides the capability to design, execute and deploy SQL Server Reporting Services reports. The user interface has been streamlined to allow business users to become proficient at authoring reports very quickly.

In the previous lesson I demonstrate how to Installing Report Builder .
In this lesson, I will demonstrate how to:
  • Getting Started
  • Report Wizards
  • Building a Report from Scratch
  • Deploying Reports
To begin launch Report Builder from the Microsoft SQL Server 2016 Report Builder. By default the Getting Started dialog is displayed immediately after launching Report Builder:
The Getting Started dialog provides the following options:
  • New Report
  • New Dataset
  • Open
  • Recent

New Report

The New Report option allows you to choose a wizard or a blank report as your starting point. The wizards walk you through creating a report in a sequence of steps. The blank report allows you to handle all aspects of creating the report. For beginners the wizards are a great choice; experienced users may prefer the blank report.

New Dataset

A dataset defines the data that you want to render on your report.Alternatively you can define a dataset within a report (an embedded dataset) but it is a best practice to use shared datasets.

Open

The Open option displays the familiar file open dialog allowing you to locate and open a report. Report files have an extension of .RDL (report definition language).

Recent

The Recent option allows you to choose a report to open from a list of recently used reports.

Report Wizards

Create a table report with the Table or Matrix wizard. There are two modes: report design and shared dataset design. In report design mode, you specify data in the Report Data pane and the report layout on the design surface. In shared dataset design mode, you create dataset queries to share with others. In this tutorial, you will be using report design mode.
  1. In the left pane, verify that New Report is selected.
  2. In the right pane, select Table or Matrix Wizard.
  3. Specify a Data Connection in the Table Wizard
A data connection contains the information to connect to an external data source such as a SQL Server database. Usually, you get the connection information and the type of credentials to use from the data source owner. To specify a data connection, you can use a shared data source from the report server or create an embedded data source that is used only in this report.

Note: You can download the database I am using in this demonstration from this link SQL Server DB

Create an embedded data source

  • On the Choose a dataset page, select Create a dataset, and then click Next. The Choose a connection to a data source page opens.
  • Click New. The Data Source Properties dialog box opens.
  • In Name, type Demo a name for the data source.
  • In Select a connection type, verify that Microsoft SQL Server is selected.
  • In Connection string, type the following text, where <servername> is the name of an instance of SQL Server:
I will add period "." which represent the current server& from select or enter the database name, select your database & press Test Connection.
  • The message "Connection created successfully" appears.
  • Click OK.
  • You are back on the Choose a connection to a data source page, with your new data source selected.
  • Click Next.

Create a Query in the Table Wizard

  • On the Design a query page, the relational query designer is open. For this tutorial, you will use the text-based query designer.
  • Click Edit As Text. The text-based query designer displays a query pane and a results pane.
  • Paste the following Transact-SQL query into the blank
SELECT TOP (1000) Sales.Orders.orderid, CONVERT(VARCHAR(30), Sales.Orders.orderdate, 105) AS orderdate, CONVERT(VARCHAR(30), Sales.Orders.shippeddate, 105) AS shippeddate, Sales.Customers.custid,
Sales.Customers.contactname, Sales.OrderDetails.productid, Production.Products.productname, Sales.OrderDetails.unitprice, Sales.OrderDetails.qty, Sales.Orders.shipcity, Sales.Customers.country
FROM Sales.Orders INNER JOIN
Sales.Customers ON Sales.Orders.custid = Sales.Customers.custid INNER JOIN
Sales.OrderDetails ON Sales.Orders.orderid = Sales.OrderDetails.orderid INNER JOIN
Production.Products ON Sales.OrderDetails.productid = Production.Products.productid
  • On the query designer toolbar, click Run (!).
  • The query runs and displays the result set for the fields SalesDate, Subcategory, Product, Sales, and Quantity.
  • In the result set, the column headings are based on the names in the query. In the dataset, the column headings become the field names, and are saved in the report. After you complete the wizard, you can use the Report Data pane to view the collection of dataset fields.
  • Click Next.

Organize Data into Groups

  • On the Arrange fields page, drag productname to Values.
  • Drag qty to Values and place below Product.
  • qty is automatically aggregated by the Sum function, the default aggregate for numeric fields. The value is [Sum(Quantity)].
  • Select the arrow next to [Sum(Quantity)] to view the other aggregate functions available.
  • Drag orderdate to Row groups.
  • Drag country to Row groups and place below orderdate .
  • Click Next then Next Then  Finish to complete the wizard.  You have created the following report
  • Click the Run button on the ribbon to preview the report
Congratulation you have just created your first report.You can save the report to your local hard drive, a folder in the Report Manager (if SSRS is in native mode), or a SharePoint document library (if SSRS is running in SharePoint Integrated mode).
Share this article :
Labels:

Post a Comment

Flag Counter

Social Profile

 
Copyright x 2011. By Wael Medhat - All Rights Reserved