SQL Server Reporting Services create and subscribe to reports, reportingservices

SQL Server Reporting Services create and subscribe to reports, reportingservices

If the data in the database is updated in real time, and the report we use for research and evaluation is based on such real-time data, then we can use the SQL Server Reporting Services (SSRS) service. In a simple application scenario, we need to observe the sales data KPIs of the last day, week, or month and compare them with the previous period to obtain the corresponding tables or figures, sales data that happens in real time will all enter the database. The advantage of doing so is that, for such functional requirements, only reports with time-varying input parameters can be automated using SSRS, avoiding repetitive work. The following briefly summarizes the SSRS creation and subscription process experience.

Install and configure SSRS
When we install ms SQL Server, the important Feature SSRS is also installed. In this case, you only need to search for the program "Reporting Services Configuration Manager (RSCM)" in the Start menu to open and perform basic Configuration. The main configuration parameters must be consistent with the previous installation and configuration of SQL Server, such as the user name and authentication method used.

After the configuration is complete, open the Web Service URL or Report Manager URL to access the corresponding page. If a similar error occursThe user "XXX" does not have the required permissions. Verify that you have granted sufficient permissions and have fixed the Windows User Account Control (UAC) restriction., You need to first open the browser as an administrator, and then copy the URL to enter. Then, you can go to the permission page on the Report Manger page to add various permissions for the user.

Install and configure Report Builder
The key purpose of using SSRS is to create a Report, but the Report Manager does not provide such a function. In this case, other tools are required. Report Builder is such a tool that can help you create and edit reports.

If you have a Report Builder link on the Report Manager page, click it. If Report Builder is installed, it will be automatically opened. If it is not installed, it will be downloaded and installed. If there is no Report Builder, We need to download the Report Builder installation first. During the installation process, if you want to enter the connected Report Server, you can copy the Web Service URL in the preceding RSCM to the Report Server Web URL. If a problem occurs during the installation process, you can leave the Report Server blank and wait for the installation to start and use.

Create report
To create a Report, you must first have a data source. Therefore, you can create a data source on the Report Manager page. Click "New Data Source" and enter the name and connection string (for example, Data Source =. \ SQLEXPRESS; Initial Catalog = dbname; Integrated Security = true ). You can save the connection after the test is successful.

After you open Report Builder, you need to connect to the Report Server first if no Report Server is connected. Step 2 is Add Dataset (if you use embedded data for reference ). Click Query Designer to select the data to be used, which can be Table or Stored Procedure. You can also use SQL statements directly. In this way, the dataset is added.

After preparing the data, you can create a table. At this time, the operation is actually similar to the general word, excel and other software, you can set tables, graphics, etc. to display, as long as the beautiful use. After adding the file, click Run or F5 to Run the preview. If you are not satisfied, continue the adjustment. Note that if an error "Failed to preview report" is prompted. "Under the details button, it states" The permissions granted to user 'domain \ username' are insufficient for parameter Ming this operation. (rsAccessDenied), you need to start Report Builder as an administrator. If you are satisfied, save the report and give it a name. At this time, if you go back to the Report Manager page, you will find that the Report is created successfully.

Subscription report
We may want to view the latest reports every day, but we are too lazy to view them. Then we can use email subscription. Steps are as follows:

If it is prompted that the subscription is not supported, it indicates that the SSRS version we installed does not support this function. You can find the corresponding Microsoft document to check whether the installed version is not supported. Https://msdn.microsoft.com/en-us/library/cc645993.aspx#Reporting