SQLUNINTERRUPTED

I am just a medium, SQL Server the Goal

Category Archives: Reporting Services

How To Determine User permissions on a Report Item

Leave a comment Posted by on April 11, 2012

Recently while delivering a SQL Server Reporting Services workshop, a customer presented to me a very interesting question.

In their environment, they have multiple roles defined in SSRS, and when I say multiple, I am talking about more than 300 roles defined in SQL Reporting Services (customer has a work force of over 200K). My initial reaction was one of awe, as this is probably the first time I have seen anyone using this many roles and have that many users defined in SQL Reporting Services.

Their requirements were two fold

  1. Given a Report Item name and the User name, they need to find the permissions this user has on a report item.
  2. If the user is part of multiple roles, they also need to figure the permissions associated with each role.

The first part of the problem is real simple. In the Report Server Database we have 4 tables, writing a query one which provides the role membership of a user on the report items.

  • dbo.Roles
  • dbo.Catalog
  • dbo.Policies
  • dbo.PolicyUserRole
   1: Select C.Name,R.RoleName, U.UserName from 

   2: dbo.Catalog C 

   3: inner join dbo.PolicyUserRole PR on C.PolicyID = PR.PolicyID

   4: inner join dbo.Roles R on R.RoleID = PR.RoleID

   5: inner join dbo.Users U on PR.UserID = U.UserID 

   6: where U.UserName = '<reportusername>' and C.Name = ‘<report Item Name>’

The above query returns the effective permissions on the report Item for a given user.

The second part of the problem is a bit complex. In the Report Server Database, we do not store the explicit permissions for the Roles. What we instead do, is store it as a Bit Mask in the dbo.Roles table.

The TaskMask column in the dbo.Roles tables maps to the effective permissions for each role. There is no simple way to find what these bit mask values represent, so I created two static tables in the Report Server database or any other database (preferable) and then use these two tables in the function I created.

The Two Tables I created were 

   1: create table SystemRoleTemp (ID int IDENTITY(1,1), permission varchar(200))

   2:         insert into SystemRoleTemp(permission) values 

   3:          ('Manage Roles')

   4:         ,('Manage Report Server Security')

   5:         ,('View Report Server Properties')

   6:         ,('Manage Report Server Properties')

   7:         ,('View Shared Schedules')

   8:         ,('Manage Shared Schedules')

   9:         ,('Generate Events')

  10:         ,('Manage Jobs')

  11:         ,('Execute Report Definitions')

   1: create table UsersRolesTemp (ID int IDENTITY(1,1), permission varchar(200))

   2:         insert into UsersRolesTemp(permission) values 

   3:         ('Set Security for Individual Items')

   4:         ,('Create Linked Reports')

   5:         ,('View Reports')

   6:         ,('Manage Reports')

   7:         ,('View Resources')

   8:         ,('Manage Resources')

   9:         ,('View Folders')

  10:         ,('Manage folders')

  11:         ,('Manage Report history')

  12:         ,('Manage Individual Subscriptions')

  13:         ,('Manage All Subscription')

  14:         ,('View Data Sources')

  15:         ,('Manage Data Sources')

  16:         ,('View Models')

  17:         ,('Manage Models')

  18:         ,('Consume Reports')

These two tables map the permissions for the System Roles and Item Specific roles.

Next I created a UDF to return the permissions associated with each role.

The UDF check for the corresponding bits of the BitMask and appends the permissions in a string, the function then returns the string to the caller.

   1: Alter function dbo.EffectivePermissionsRoles (@TaskMask nvarchar(20), @RoleFlag tinyint)

   2: Returns varchar(500)

   3: As

   4: Begin

   5:     Declare @effectivepermissions varchar(600) ='Role Permissions ==> '

   6:     declare @taskmaskindex int

   7:     set @taskmaskindex = LEN(@TaskMask)

   8:     declare @count int = 1

   9:     declare @temp varchar(50)

  10:     

  11:     ---- For System Roles

  12:         

  13:         If (@RoleFlag = 1)

  14:         begin

  15:             While @count < @taskmaskindex

  16:                 begin

  17:                     if(SUBSTRING(@TaskMask, @count, 1) ='1')

  18:                     begin

  19:                         Select @temp = permission + ','  from dbo.SystemRoleTemp where ID = @count

  20:                     end

  21:                     Set @effectivepermissions = @effectivepermissions + ISNULL(@temp,'')

  22:                     Set @temp = ''

  23:                     Set @count = @count+1

  24:                 end

  25:         end

  26:         

  27:         --- For Item Specific Roles

  28:         If (@RoleFlag = 0)

  29:         begin

  30:                 While @count < @taskmaskindex

  31:                 begin

  32:                     if(SUBSTRING(@TaskMask, @count, 1) = '1')

  33:                     begin

  34:                         Select @temp = permission  + ',' from UsersRolesTemp where ID = @count

  35:                     end

  36:                     Set @effectivepermissions = @effectivepermissions + ISNULL(@temp,'')

  37:                     Set @temp = ''

  38:                     Set @count = @count+1

  39:                 end

  40:         end

  41:     return @effectivepermissions      

  42: end

  43: GO

Finally, we can put all of it together in a Stored Procedure or in a SQL Query as follows, to return the effective permissions for the users on a particular report Item.

   1: Select C.Name,R.RoleName, U.UserName, dbo.EffectivePermissionsRoles(R.TaskMask, R.RoleFlags)  As RolePermissions 

   2: from 

   3: dbo.Catalog C 

   4: inner join dbo.PolicyUserRole PR on C.PolicyID = PR.PolicyID

   5: inner join dbo.Roles R on R.RoleID = PR.RoleID

   6: inner join dbo.Users U on PR.UserID = U.UserID 

   7: where U.UserName = '<User Name>'  and C.Name = '<ReportItemName>'

Advertisement
Reporting Services, SQL Server , , ,

Working with Data Alerts in SQL Reporting Services 2012 – Part1

2 Comments Posted by on April 2, 2012

With SQL Server 2012 Reporting Services, we introduced a new feature called Data Alerts. Data Alert is a data driven alerting mechanism, where in you can get automated responses if the data associated with a particular report changes. These alerts are send by email, with the user having control on the frequency of these emails and the rules (conditions) on which these alerts are fired.

Requirements

Data Alerts work only with reports which are deployed in a SharePoint library. This means if your Reporting Services is configured for Native Mode, you cannot configure Data Alerts.  SSRS 2012, unlike earlier versions of SSRS does not allow changing an existing Native Mode installation to SharePoint Integrated mode using the Reporting Services configuration manager. You need to install a new instance of reporting services in SharePoint mode to use it.

How Data Alerts work

Data Alerts work flow can be divided into two sections, configuration and processing of alerts. During the configuration phase, the user needs to be first run the report. Data Alerts can only be configured if the Report has data. Data Alerts can be configured for any report (containing any item type) as long as it has some data. In order to configure Data Alerts, the report data source must use Stored Credentials or no credentials (would use the SSRS execution account).

Power View reports cannot be configured for Data Alerts.

image

Once the Data Alert has been configured, it is put in the Alerting database. This database is automatically created when we install SSRS in SharePoint integrated mode. By default the name of the database is ReportingServices<GUID>_Alerting. This also creates a SQL Agent job, to automatically processes the data alert based on the schedule defined, while creating the alert.

When the SQL Agent Job starts at its schedule, it starts the processing of the data Alert. During processing, the Report is run and the data feed for the report is refreshed. Then the rules defined for the alert are applied to the data feed. It a data value matches the rules applied in the alert, it is included in the results for the Alert. An email messages is composed with all the data value which match the rules and sent to the recipients (specified during alert creation).

The email messages being sent, typically contains the following

  1. From email address, is the email address configured in your SSRS Email Settings.
  2. To addresses are the emails provided during Alert creation.
  3. The Email body consists of the following, but is not limited to
    • Data Values in the result set.
    • A link to the report on which the alert is built.
    • The name of the person who configured the alert.
    • The parameters and values used in the report during the time of alert creation.

image

In another post, I will talk about installing data alerts and using the data alert designer.

Reporting Services, SQL Server , ,

Creating a Report Subscription using .Net

Leave a comment Posted by on March 20, 2012

In order to create a report subscription, we can use any of the following Reporting Services endpoint.

  • ReportService2005 -  If Reporting Services (SSRS 2008 or earlier) is running in Native Mode
  • ReportService2006 – If Reporting Services (SSRS 2008 or earlier) is running in SharePoint Integration Mode.
  • ReportService2010 – For Reporting Services 2008 R2.

Each of these endpoint, expose a Method for the creation of a Report Subscription. In this example, I am creating a Report Subscription for windows File Share. Same techniques could be used for creating a email based subscription.

Steps to create a Report subscription

Declare an object of the ReportService2005 (or any of the above mentioned endpoints) and bind the object to the Report Server.

   1: ReportingService2005 rs = new ReportingService2005();

   2: //            rs.GetExtensionSettings("

   3:             rs.Url = "http://localhost/reportserver/ReportService2005.asmx";

   4:             rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

 

Provide the path for the report, for which the subscription needs to be made and define a schedule for the Subscription. In this example, I created a Subscription schedule to run at 5PM every Thursday.

   1:         string report =  "/PowerManagementSolution/MachineTypeInformation";

   2:             string desc = "Created using .Net";

   3:             string eventType = "TimedSubscription";

   4:             string scheduleXml =

   5:                @"<ScheduleDefinition>" +

   6:                 "   <StartDateTime>2012-02-16 T17:00:00" +

   7:                 "   </StartDateTime>" +

   8:                 "   <WeeklyRecurrence>" +

   9:                 "      <WeeksInterval>1</WeeksInterval>" +

  10:                 "      <DaysOfWeek>" +

  11:                 "         <Thursday>True</Thursday>" +

  12:                 "      </DaysOfWeek>" +

  13:                 "   </WeeklyRecurrence>" +

  14:                 "</ScheduleDefinition>";

Next, I defined the parameters for the Subscription. For example, the Format, the location where the rendered report needs to be created, using a ParameterValue collection.

   1:     ParameterValue[] extensionParams = new ParameterValue[7];

   2: // file name for Report rendering.

   3:             extensionParams[0] = new ParameterValue();

   4:             extensionParams[0].Name = "FILENAME";

   5:             extensionParams[0].Value = "Sales Order Detail";

   6:  

   7:             extensionParams[1] = new ParameterValue();

   8:             extensionParams[1].Name = "FILEEXTN";

   9:             extensionParams[1].Value = "True";

  10:  

  11: // File Share and format for Rendering Report

  12:             extensionParams[2] = new ParameterValue();

  13:             extensionParams[2].Name = "PATH";

  14:             extensionParams[2].Value = @"\\myfileshare\MyReports";

  15:             

  16:             extensionParams[3] = new ParameterValue();

  17:             extensionParams[3].Name = "RENDER_FORMAT";

  18:             extensionParams[3].Value = "EXCEL";

  19:  

  20: // The login account, which has permissions to write to the file share.

  21:             extensionParams[4] = new ParameterValue();

  22:             extensionParams[4].Name = "USERNAME";

  23:             extensionParams[4].Value = @"<NetworkLogin>";

  24:  

  25:             extensionParams[5] = new ParameterValue();

  26:             extensionParams[5].Name = "PASSWORD";

  27:             extensionParams[5].Value = "<NetworkPassowrd>";

  28:  

  29: // Write Mode for file creation.

  30:             extensionParams[6] = new ParameterValue();

  31:             extensionParams[6].Name = "WRITEMODE";

  32:             extensionParams[6].Value = "None";

once the parameters and the Report Values have been specified, we need to call the CreateSubscription method for creating the subscription.

   1: /// Setting the Report Server Subscription Details. This is a FileShare based Subscription.

   2:  

   3:             string matchData = Convert.ToString(scheduleXml);

   4:             ExtensionSettings extSettings = new ExtensionSettings();

   5:             extSettings.ParameterValues = extensionParams;

   6:             extSettings.Extension = "Report Server FileShare";

   7:  

   8:             try

   9:             {

  10:                 // call the Create Subscription method in the Reporting Services endpoint.

  11:                 rs.CreateSubscription(report, extSettings, desc,

  12:                     eventType, matchData, null);

  13:                 MessageBox.Show("Susbcription Created");

  14:                 Application.Exit();

  15:             }

  16:  

  17:             catch (SoapException E)

  18:             {

  19:                 Console.WriteLine(MessageBox.Show(E.Message));

  20:             }

This code would create the Subscription on the mentioned Report Server instance.

Reporting Services, SQL Server

Render and Email a Reporting Services Report using .Net

2 Comments Posted by on March 19, 2012

In an earlier post, I had described a customer scenario, where they would want to execute and render a report from a .Net application, and then send the rendered report as an email attachment.

In the previous post, I had talked about how to generate a Tree View of the Report Server Items. In this post, I would be talking mainly about the how to execute a report and render it in a required format. And then send the Report as an email attachment.

The Post, assumes the following

  1. The report which needs to be executed is selected on the Tree View and the report path is passed to the function.
  2. The Report runs with default parameters.
  3. The SMTP mail server is preconfigured.

In order to execute a Report, we need to use the ReportExecutionService() Class, defined in the ReportExecution2005 endpoint for Reporting Services.

I created a new class ExecuteAndRender for this functionality.

The ExecuteAndRender Class, defines and object for the ReportExecutionService() class, in order to execute the methods exposed by Reporting Services.

   1: public ExecuteAndRender()

   2:         {

   3:              rs = new ReportExecutionService();

   4:              rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

   5:              rs.Url = "http://localhost/reportserver/ReportExecution2005.asmx";

   6:         }

Next I created a function RenderReport(string ReportName), this function performance the following tasks

  1. Define the Report Rendering and execution settings.
  2. Calls the ReportExecutionService.LoadReport() to load the report definition.
  3. Defines the report parameters. In this case, since all default values would be used, we do not explicitly define the parameters.
  4. Calls the ReportExecutionService.Render() method to render the report in the desired format.
  5. One the report have been rendered, we create a PDF file for the report.
  6. Then call the AttachAndSendEmail() to send the email. 
   1: public void RenderReport (string ReportName)

   2:         {

   3:             byte[] result = null;

   4:             //string reportPath = "/ReportFolder2";

   5:             string format = "PDF";

   6:             string historyID = null;

   7:             string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";

   8:  

   9:             //DataSourceCredentials[] credentials = null;

  10:             //string showHideToggle = null;

  11:             string encoding;

  12:             string mimeType;

  13:             string extension;

  14:             Warning[] warnings = null;

  15:             

  16:             //ParameterValue[] reportHistoryParameters = null;

  17:             string[] streamIDs = null;

  18:  

  19:             ExecutionInfo execInfo = new ExecutionInfo();

  20:             ExecutionHeader execHeader = new ExecutionHeader();

  21:  

  22:             rs.ExecutionHeaderValue = execHeader;

  23:             execInfo = rs.LoadReport(ReportName, historyID);

  24:  

  25:             //ReportParameter[] param = execInfo.Parameters;

  26:  

  27:             // Prepare report parameter. Currently the report executes with Default paramerts.

  28:             // Here we retrieve the 

  29:             // But if required the Report Paramaeters can be added.

  30:             

  31:             ParameterValue[] parameters = new ParameterValue[execInfo.Parameters.Length];

  32:  

  33:             rs.SetExecutionParameters(parameters,"en-us");

  34:             String SessionId = rs.ExecutionHeaderValue.ExecutionID;

  35:  

  36:             try

  37:             {

  38:                 result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

  39:                 execInfo = rs.GetExecutionInfo();

  40:             }

  41:             catch (SoapException e)

  42:             {

  43:                 throw e;

  44:             }

  45:             // Write the contents of the report to an PDF file.

  46:             try

  47:             {

  48:                 FileStream stream = File.Create("report.pdf",result.Length);

  49:                 stream.Write(result, 0, result.Length);

  50:                 stream.Close();

  51:                 ///Now Send the email

  52:                 AttachAndSendEmail("report.pdf");

  53:             }

  54:             catch (Exception e)

  55:             {

  56:                 throw e;

  57:             }

  58:         }

Next, we have the AttachAndSendEmail() function to connect to the SMPT server and send the email. This function achieves the following tasks

  1. Create an SMTP client to connect to the SMTP server.
  2. Create the From and To Addresses for the email.
  3. Creates the MailMessage with the attachment.
  4. And Sends the email. 
   1: public void AttachAndSendEmail(string ReportFileName)

   2:         {

   3:  

   4:             SmtpClient client = new SmtpClient("Address of the SMTP Server", 25);

   5:             client.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;

   6:  

   7:             MailAddress from = new MailAddress("<from Address here>","<DisplayNamehere>",

   8:                             System.Text.Encoding.UTF8);

   9:  

  10:             MailAddress to = new MailAddress("<To Address here>","<DisplayNamehere>",

  11:                 System.Text.Encoding.UTF8);

  12:  

  13:  

  14:             MailMessage msg = new MailMessage(from,to);

  15:             msg.Subject = "Report Attached " + DateTime.Now.ToString();

  16:             msg.Body = "Report From Report Server";

  17:             msg.BodyEncoding = System.Text.Encoding.UTF8;

  18:             msg.Attachments.Add(new Attachment(ReportFileName));

  19:  

  20:             try

  21:             {

  22:                 client.Send(msg);

  23:             }

  24:             catch (System.Net.Mail.SmtpException e)

  25:             { throw e; }

  26:         }

 

This code sends the messages in an synchronous manner. In case if required, SMTPClient.SendAsync method can also be used.

Reporting Services, SQL Server , , ,

What’ New SQL Server 2012: Reporting Services Office based Renderers

Leave a comment Posted by on March 18, 2012

With SQL Server 2012, Reporting Services, both Excel and Word rendering now creates a Office 2007-2010 compatible files. These files are also compatible with Excel/Word 2003.

Excel based files have the extension .xlsx, while the word based files have .docx extensions.

In addition, there are several other enhancements with the excel rendering

  1. Max row limitation of excel 2003 (65K) rows is no longer applicable. Now the max number of rows per worksheet can be over 1 million.
  2. Maximum columns per worksheet is 16,384.
  3. Number of colors allowed in a worksheet is approximately 16 million (24-bit color).
  4. ZIP compression provides smaller files sizes.

With word rendering, the .docx files are much smaller than the previously created .doc files.

Reporting Services, SQL Server , , , , ,

← Older posts