SSRS (SQL Server Reporting Services)

1   2   3   4   5   6   7   8   9   10   11   12   13   14   15   16   17   18

• SSRS Introduction


• Open the SQL Server Business Intelligence Studio (BIDS) 
• Data Source Creation
• Dataset Creation


• Report Server Project Wizard


• Report Server Project


• Report Model Project
• Data Source Views


• Types of SSRS Reports


• Parametrized Reports


• Single Valued Parameters


• Multivalued Parameters


• Cascading Parameters




•  Grouping and Drill-Down Reports


• Ad hoc Reports


• Sub Reports


• Drill-through or Linked Reports


• Snapshot Reports


• Cached Reports


• Deployment


• Report Subscriptions or Scheduling

1   2   3   4   5   6   7   8   9   10   11   12   13   14   15   16   17   18

SSRS: Report Subscriptions or Scheduling

Using subscriptions we can share the SSRS Report (Email or Windows File Share) to the end users. Below are the two types of subscriptions.

1. Standard Subscriptions
2. Data Driven Subscriptions

See Also:

Difference between Standard and Data Driven Subscriptions

SSRS: The SQL Agent service is not running

During Report Subscription create we may get the below error.

Error:
The SQL Agent service is not running. This operation requires the SQL Agent service. (rsSchedulerNotResponding) Get Online Help

Reason:
Report subscriptions are dependent on "SQL Server Agent". For each Subscription one job will create in the "SQL Server Agent". If the agent is not running or the user doesn't have proper permissions then the above error occurs.


If the SQL Server Agent is not running, follow the below steps to Start the Job Agent:

• Connect to server (where the SQL Server and SSRS installed). Go to the services

• Select "SQL Server Agent" and right click on it and select Start option.

SSRS: Standard Subscriptions

In Standard Subscriptions, we can pass the static values (We cannot change the value during "Run time/Pragmatically") only. Values are Parameters/Share File path/Recipient list/Mail Priority/Render type etc. During run-time it will take static values to deliver the report. We need to edit the subscription in case of any value which we need to changes.

Steps to Create Standard Subscriptions:

• Connect to the Report Manager and mouse over on the required report then open menu "down arrow" will enable, click on it. Select the "Manage" option.

• Manage window will open, then click on "Subscript" section. Once we are in Subscripts tab click on "New Subscriptions" (highlighted in the below screen).

• Once we click on "New Subscriptions", Subscriptions window will open. In this example i am going to choose "E-Mail" as deliver option. Enter recipient list, Report Output type, Priority and Comment details. After that click on "Select Schedule" button to schedule the report delivery.

• Choose the scheduling details and click on "OK" button.

• The report "BalanceDetails" in this example is having tow parameters, so we need to enter the parameter values and click on "OK" button.

• Now we can see the "Report Subscription" in the "Subscriptions section.

Not Able to See the Report Builder and Site Settings

If an user don't have appropriate permissions, that user cannot see the "Report Builder" and "Site Settings" options in the report manager URL.

Solution:

• Connect to the Report Manager URL as an admin, then click on "Site Settings"

• Then "Site Settings" will open. Go to the Security Tab, and click on "New Role Assignment" (Refer the below screenshot).

• Enter the Group/User Name and select the security level, then click on "OK" button.

• Then we can see the new role in the "Site Settings >> Security" tab.

SSRS User does not have required permissions

When we try to open "Report Server/Report Manager" URL (as an user), we may get the below error message.

Error Message:
Report Manager:
User 'admin-PC\admin' does not have required permissions. Verify that sufficient permissions have been granted and Windows User account Control (UAC) restriction have been addressed.

Report Server:
The permissions granted to user 'admin-PC\admin' are insufficient for performing this operation. (rsAccessDenied) Get Online Help.

Reason:
If the Administrator is not assigned the access permissions to the User/Group, then that specific User/Group cannot access the URL's.

Solution:
Click on the below link

SSRS Role Assignment OR Manage User Access

Not Able to See the Report Builder and Site Settings

SSRS Role Assignment OR Manage User Access

If we need to assign Role(User Access), we need to connect to the Report Manager URL as an admin.

Connecting to Report Manager as an Admin:
Case-1: 
If the Report Manager is not using windows authentication then use the admin credentials to connect to the Report manager

Case-2:
If the Report Manager is using windows authentication (not prompting user name and password). Login to the Server (Where the SSRS software is installed) as an administrator and connect to the Report Manager URL.

Case-3:
Report Manager is not prompting user name and password, but we are getting below error.

User 'admin-PC\admin' does not have required permissions. Verify that sufficient permissions have been granted and Windows User account Control (UAC) restriction have been addressed.

Follow the below steps:
Close the browser and open the browser as an administrator (follow the below steps)

• Right Click on the browser and select the option "Run as an Administrator".

• Once the browser is opened then enter the "Report Manager" URL and connect.

Role Assignment OR Manage User Access:

• Once we connected to the Report Manager, click on the "Folder Settings" (highlighted in the below image)

• Then click on "New Role Assignment".

• Once the "New Role Assignment" page is opened, enter the User/Group name and select the role which we need to assign.

• Click on "OK" button. We can see the newly add Role in the below screen.

• If we need to Change/Delete the current roles, select the required role (select the check box) and use the "Edit/Delete" options (Highlighted in the below screen.