SQL Server Reporting Services (SSRS) is part of Microsoft SQL Server services since version 2000. If you are an ordinary user with a basic knowledge of SQL, this article will help you in creating your first SSRS report.
自2000版以來,SQL Server Reporting Services(SSRS)是Microsoft SQL Server服務的一部分。如果您是具有SQL基本知識的普通用戶,則本文將幫助您創建第一個SSRS報告。
Before you begin using SSRS and creating reports you must have:
sql server安裝實驗報告。 在開始使用SSRS和創建報告之前,您必須具有:
The first thing to do is to create a report server project because we will need this later for saving report definition files and any other files that you need for creating SSRS reports.
要做的第一件事是創建報告服務器項目,因為稍后我們將需要它來保存報告定義文件以及創建SSRS報告所需的任何其他文件。
創建文明村情況報告?
Setting up a connection
建立連接
創造服務, In order to retrieve data from a database or from some other resource, you will need to define the data source. In the following section, you will see how you can define the data source. We will use AdventureWorks2012 database as an example.
為了從數據庫或其他資源中檢索數據,您將需要定義數據源。 在下一節中,您將看到如何定義數據源。 我們將以AdventureWorks2012數據庫為例。
程序創建服務、Note: If the database is not on the local computer, replace localhost with name of your database server instance.
注意:如果數據庫不在本地計算機上,則將localhost替換為數據庫服務器實例的名稱。
使用服務?Define a T-SQL query for report data
定義報告數據的T-SQL查詢
In this section you will learn how to specify a dataset. You will need some basic SQL knowledge in order to create a query and define what information you will need from your database. The query you will see is just an example, and for your own purposes and database, you will have to change that step.
在本節中,您將學習如何指定數據集 。 您將需要一些基本SQL知識才能創建查詢并定義數據庫中需要的信息。 您將看到的查詢只是一個示例,對于您自己的目的和數據庫,您必須更改該步驟。
?
SELECT?? soh.OrderDate AS [Date],?? soh.SalesOrderNumber AS [Order],?? pps.Name AS Subcat, pp.Name as Product,????SUM(sd.OrderQty) AS Qty,??SUM(sd.LineTotal) AS LineTotal??
FROM Sales.SalesPerson sp?? INNER JOIN Sales.SalesOrderHeader AS soh?? ON sp.BusinessEntityID = soh.SalesPersonID??INNER JOIN Sales.SalesOrderDetail AS sd?? ON sd.SalesOrderID = soh.SalesOrderID??INNER JOIN Production.Product AS pp?? ON sd.ProductID = pp.ProductID??INNER JOIN Production.ProductSubcategory AS pps?? ON pp.ProductSubcategoryID = pps.ProductSubcategoryID??INNER JOIN Production.ProductCategory AS ppc?? ON ppc.ProductCategoryID = pps.ProductCategoryID??
GROUP BY ppc.Name, soh.OrderDate, soh.SalesOrderNumber, pps.Name, pp.Name,?? soh.SalesPersonID??
HAVING ppc.Name = 'Clothing'
Defining a Dataset for the Table Report (Reporting Services)為表格報表定義數據集(Reporting Services)報告格式。
Note: soh, pps, sd, pp, ppc are just shorter names for tables that we have in AdventureWorks2012 database.
注意: soh,pps,sd,pp,ppc只是AdventureWorks2012數據庫中表的簡稱。
After finishing with previous technical details, we can start with more interesting things, like designing your first SSRS report. This part is easy because instead of writing code, you can drag-and-drop graphic icons into the report format. This section will show you how to add Table and Fields to your report.
在完成了先前的技術細節之后,我們可以從更有趣的事情開始,例如設計您的第一個SSRS報告。 這部分很容易,因為您無需編寫代碼,而是可以將圖形圖標拖放到報告格式中。 本節將向您展示如何將表和字段添加到報告中。
Preview report
預覽報告
If you want to preview your report to see how it all looks, to correct errors, to correct issues or to verify design and data connection, click tab Preview.
如果要預覽報告以查看其外觀,更正錯誤,更正問題或驗證設計和數據連接,請單擊“ 預覽”選項卡。
Tips and tricks
技巧和竅門
An easier way to add a table is to right-click on the design surface, click Insert and then click Table.
添加表的一種更簡單的方法是右鍵單擊設計圖面,單擊“ 插入” ,然后單擊“ 表”。
Format currency and date
格式化貨幣和日期
If you want to format Date field to show only the date, follow the steps below:
如果要格式化日期字段以僅顯示日期,請按照以下步驟操作:
If in your table you have a field that holds information about currency, but you only see ordinary numbers, you can format that field to display a number as currency:
如果表中有一個字段,其中包含有關貨幣的信息,但您只看到普通數字,則可以格式化該字段以將數字顯示為貨幣:
Changing column width and text style
更改列寬和文本樣式
You also have an option to change text style (font, size etc.), and to change column width. Column width you can change by simply dragging the columns to the desired size.
您還可以選擇更改文本樣式(字體,大小等)并更改列寬 。 只需將列拖動到所需的大小,即可更改列寬。
Text style you can change by clicking the Format menu. In Format menu find Font, then click whatever you need (bold, italic, etc.)
您可以通過單擊格式菜單來更改文本樣式。 在“ 格式”菜單中,找到“ 字體” ,然后單擊所需的任何內容(粗體,斜體等)。
If you want to make data set in your SSRS report do the following:
如果要在SSRS報告中設置數據,請執行以下操作:
Tips and tricks
技巧和竅門
You can do the same by right-clicking on the surface and clicking View, and then Grouping.
您可以通過右鍵單擊表面并單擊“ 視圖” ,然后單擊“ 分組”來執行相同的操作。
Total is the sum of numeric, non-null data in the data region, and if you want to add totals for a group, you can do that by clicking Add Total for the group in the Grouping pane, and if you want to add totals for an individual cell just click Add Total for the cell.
總計是數據區域中數字非空數據的總和,如果要添加組的總計,則可以通過在“分組”窗格中單擊該組的“ 添加總計”來完成,如果要添加總計對于單個單元格,只需單擊該單元格的“ 添加總計 ”即可。
Add a daily total and grand total
添加每日總計和總計
Tips and tricks
技巧和竅門
After you add total, you can change the default function Sum. There is a list of different function you can use (avg, count, etc.).
添加總計后,可以更改默認功能總和。 您可以使用不同功能的列表(平均,計數等)。
Finally, when you finished with creating your first SSRS report, you may want to publish the report:
最后,完成創建第一個SSRS報告后,您可能要發布該報告:
Note: A problem will occurred if you don’t have permissions on the report server or if you have been using SSDT with administrator privileges.
注意:如果您對報表服務器沒有權限,或者您一直在使用具有管理員權限的SSDT,則會出現問題。
Most common problems
最常見的問題
The most common problem in publishing the report to the report server is configuring the target server URL.
將報表發布到報表服務器中最常見的問題是配置目標服務器URL。
The first step that you need to do in configuration is to launch the SQL Server Reporting Service Configuration Manager and connect to the Reporting Service.
在配置中需要做的第一步是啟動SQL Server Reporting Service 配置管理器并連接到Reporting Service 。
After that in section Web Service URL you can find URL to the report server.
之后,在“ Web服務URL”部分中,您可以找到報表服務器的URL。
In section Report Manager URL you can see URL for viewing and managing reports.
在“ 報告管理器URL”部分中,您可以查看用于查看和管理報告的URL。
In the Project menu open the Test report Properties.
在項目菜單中,打開測試報告屬性 。
Note: Prefix “Test report” is name of your report.
注意 :前綴“測試報告”是報告的名稱。
Final step in configure target server URL is to fill the Web Service URL in the TargerServerURL property with correct URL that you find in Report Manager URL.
配置目標服務器URL的最后一步是使用在報表管理器URL中找到的正確URL填充TargerServerURL屬性中的Web服務URL 。
Congratulation, you have successfully created your first SSRS report!
恭喜,您已經成功創建了第一個SSRS報告!
Reporting Services (SSRS)
Reporting Services Concepts (SSRS)
Reporting Services Backward Compatibility
Cause and Resolution of Reporting Services Errors
報告服務(SSRS)
報告服務概念(SSRS)
報表服務向后兼容性
Reporting Services錯誤的原因和解決方案
翻譯自: https://www.sqlshack.com/create-sql-server-reporting-services-ssrs-report/
版权声明:本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间:8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态