SSRS Subscription emails are not being delivered (SharePoint Integration Mode)

Leave a comment

For the past few days we have had issues with subscriptions not being emailed to our end users. I had not experienced this issue before so I googled and found some posts on Dean Kalanquin’s blog that while helpful did not resolve the issue. They did, however, lead me in the right direction shich is why I posted them below.

Monitoring and Troubleshooting Subscriptions

Troubleshooting Subscriptions: Part II, Using the Reporting Trace Log File

Following the first blog I checked the SQL Agent job and all appeared to be fine according to the job’s history. Next, I looked in to the Events table and the Notifications table. To test the event I grabbed the execution code from the job properties, executed the code then queried the Events table repeatedly until I could see the entry. Crude, yes but I was in a pinch. 🙂

exec [SharePoint_ReportServer].dbo.AddEvent @EventType=’TimedSubscription’, @EventData=’ed209f73-953a-4fe8-96b6-3569bd2151aa’

SELECT * FROM Events

Following the execution I found that records were indeed being placed into both the Events table and the Notifications table as well. This proved that the event processing service was functioning correctly.

My next step was found in Dean’s second blog that lead me to the Trace Log file.

$:\Program Files\Microsoft SQL Server\MSRS10_50.MNPSRS11\Reporting Services\LogFiles\

There I found the error that was returned . .

Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database.

This is when the lightbulb above my head lit up. 🙂  I had run across this issue before and it was permissions related. Below is my checklist that will quide you through fixing this issue without blindly applying sysadmin rights to you Report Server service account. 😉

  • Log in to your SQL Reporting Server server and view the configuration options under SQL Server Configuration manager. Once opened click on the SQL Services node and you will see the login used to run the service.
  • Navigate to the login in SQL Server, this under Security>Logins and add sysadmin permissions in Server Roles.
  • Run the subscription via SharePoint, Native Report Server or manually via execution code or SQL Agent job. If the subscription is sent, then the problem is permissions related. If not, hit the Back button and try another blog. 😀
  • Run the following code that grant execute permissions to several objects in the master and msdb databases. Simply Find/Replace [yourlogin] with your login.

GRANT EXECUTE ON msdb.dbo.sp_help_category TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_add_category TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_add_job TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_delete_job  TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_help_job TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule  TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO [yourlogin]

GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO [yourlogin]

GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO [yourlogin]

GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO [yourlogin]

  • Add the public server role to the MSDB and Master databases under Login properties>User Mappings
  • In addition to the above you will need to GRANT SELECT permissions on ..

msdb.dbo.sysjobs
msdb.dbo.syscategories

Now all that is left is to remove the sysadmin server role and test your subscriptions again. All should be well, if not . . . well I can’t help you then. 😉

 

 

SharePoint & SSRS – Removing the “Open with Report Builder” selection from the Action Menu

2 Comments

After some reading I found that this menu option is not controlled by SharePoint permissions. Below are three workarounds, two of which I have tested (1&2).

 

“Open With Report Builder” option


 

 

 

 

 

 
1.)    Setup up version control that requires approval prior to modification. This approach would not remove the “Open with Report Builder” option, nor would it disallow a user from saving changes to a report. This approach simply does not allow the modification of a report without approval. A report that has been modified cannot be viewed by end users and will stay in a queue (in the form of a view) until approved or rejected by someone with the proper permissions. This approach has the most maintenance overhead as someone will need to purge the libraries every now and then.

 

2.)    Make configuration changes to the ConfigInfo table in the ReportServer. This approach requires configuration changes in Report Server to the ConfigInfo table.

 

SELECT *

FROM dbo.ConfigurationInfo

WHERE name = ‘EnableReportDesignClientDownload’

 

 

BEGIN TRANSACTION

 

UPDATE dbo.ConfigurationInfo

SET value = ‘True’

WHERE name = ‘EnableReportDesignClientDownload’

 

ROLLBACK TRANSACTION

 

Setting EnableReportDesignClientDownload to “False” removes the “Open with Report Builder” option from the Action Menu while still allowing developers to modify existing reports and create new reports (if they have previously downloaded the client). Where issues may arise is when a new user would like to develop reports, then the option will need to be switched on momentarily to allow the client to be downloaded.

 

3.)    Setting security on the directory where the installation file is located. This would not allow anyone without permission to have access to the client. This approach does not, however, remove the “Open with Report Builder” action item. The end user will simply receive a permissions error in SharePoint.

 

Of the above options I am in favor of #2. This option would provide a cleaner UI/UX in that it removes the option completely. Add to that easier maintenance in that a simple script is applied to make changes. The only drawback I can see is messing around in the configuration tables.

Custom Report Viewer Permission Level

Leave a comment

An issue that we have run into a few times with SSRS running in SharePoint Integration mode is that we would like for the end users to have access to the Report Services features without having god status or Full Control permissions in the library. To accomplish this a new permission level needs to be created specific to Reporting Services.

There are two ways to go about doing this at the permissions level an I am not a SharePoint guru so take this with a grain of salt. The first option is to create a permission level that only addresses the report viewing permissions. In this case you will need to couple this level with an additional standard SharePoint permission level for additional permissions. (Ex. Add Report Viewer and Design permissions, so the user can not only have Report Viewer permissions but also have Design permissions in the library as well. The second option is to combine two roles into one which is the approach I took. To accomplish this you will need to create the Report Viewer permission level and into it also add the features of another level. This is better illustrated in the below walkthrough.

On the Site Actions menu , click Site Settings.

1. On the Site Settings page, in the Users and Permissions section, click Advanced permissions.

2. If the Settings menu is not available, select Manage Permissions of Parent from the Actions menu. Otherwise, skip to step 4.

Note: The Settings menu is not available on the Permissions page if your site is inheriting permissions from its parent site.

3. On the Permissions page, on the Settings menu, click Permission Levels.

Click permission levels

4. On the Permission Levels page, click Add a Permission Level.

Add permission level

5. On the Add a Permission Level page, in the Name and Description section, type a name and optionally a description.

Add name description and permissions levels

In my case, I added the Report Viewer permissions as well as the View Only permission level permissions. To grab another roles permissions to copy them in to the new permission level simply click the permission level from the Permission Level page and copy the permissions to your new permission level.

*To add report subscription features simply check “Create Alerts” and “Manage Alerts” from within the permissions selection page.

SSRS – Repeating Header Row

Leave a comment

Took me awhile to crack this one . .

In Report Builder ensure that you have Advanced Mode selected in the Row/Column Group section.

select Advanced Options

Once selected you should have several static row and column groups. Select the Header Row group in the Row Groups section. If

you are unsure which static group you are selecting look into the report pane. Every time a group is selected its corresponding cell is

highlighted as well. Once the header group is found, look at the properties and set them as follows; Fixed Data = True, RepeatOnNewPage=True.

Make the proper selections


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Now you’re all set.

*If you cannot see the properties of the static group head to the view Tab at the top of the application and ensure the properties check box is checked.

SSRS Tablix with alternating background color

Leave a comment

This one is pretty simple and can be handled in an expression.

Expression

=iif(RowNumber(Nothing) Mod 2, “White”, “Gainsboro”)

According to the expression first color (White) will be the background color of the first line in the dataset. The second color (Gainsboro or light gray) will be the alternating background color.

To apply this right-click the dataset cell for which you would like the alternating background color applied to, then click

“Textbox Properties”.

Image

Next, select Fill from the right sidebar and click the “fx” next to the fill color.

All that remains is to paste the expression into the textbox and you’re done. The only pain in this process is that you’ll need to select each individual cell in each dataset in order to change its background color. Report Builder does not support the selection multiple textbox properties within a dataset.

SSRS Multiple Datasets Error

Leave a comment

Received this error while trying to populate a textbox value from a dataset.

“The value expression for the text box ‘textbox1’ refers directly to the filed ‘field’ without specifying a dataset aggregate. When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions whick specify a dataset scope.”

This error occurs when there are multiple datasets within the report. The solution is to add scope to your textbox expression.

Example:

=(Fields!Title.Value)

to

=(Fields!Title.Value, “DataSet“)

easy peesy 😉

 

SSRS Multi-Value Select drop down

Leave a comment

I recently discovered an alternative way to implement the multi-value select drop down control. My primary solution was to create a parameter followed by creating the same parameter of the same data type in my SQL stored procedure code. My alternative is below . . .

 

Create a parameter the same way you would for a stored procedure via right click paramters>Add parameters

Add Parameter

 

 

 

 

Once you have applied the needed properties required of the parameter right-click your dataset and select properties.

Datasource Properties

 

 

 

 

 

From within the Dataset’s properties select Filters from the left pane. Click the Add button and select the expression or column you would like the parameter to apply to followed by selecting the “In” clause as your operator. Lastly, type the name of your parameter into the Value textbox within square brackets prefixed with the @ character [@parameter].

Older Entries

%d bloggers like this: