SSRS Configure the Unattended Execution Account

One way to run scheduled reports is to use SQL server logins and store the credentials in the data source of the report. For every SQL server that needs to be accessed to read the report data the SQL account credentials must be maintained. The same credentials must be saved in every data source on the report server.

An alternative is to run SQL reports with an AD windows account. The AD account has to be granted the same rights to read report data, but without saving credentials. The Reporting Service Server stores the credentials once in the Unattended Execution Account of SSRS.

Configure the Unattended Execution Account

Start the Reporting Services Configuration manager

Connect to the Reporting services Server

In the left pane of the configuration manager, select ‘Execution Account’. Check ‘Specify an account’ and enter the AD account credentials.

Click on ‘Apply’. A dialog box will open to give the name for a key file with it’s own password.

Give the unattended account read rights in the SQL Server database

Add the AD account as a windows login to the SQL Server. Add the login as user to the database. Grant the DB user sufficient select and/or execute rights in the database to read the report data.

Use the unattended account in the Report Data Sources

The Connection string must use ‘Integrated Security=SSPI’

For Shared data sources on a report server. Browse to the Data Sources folder of the report server.

Select a Data Source and click on it , or open the dropdown and select ‘Manage’.

In the properties tab, in the connection string textbox add the ‘;Integrated Security=SSPI’ Select ‘Credentials are not required’.

click Apply.

Conclusion

Account administration is managed by AD admins. The SQL Admin only needs to define the AD as a user login on the SQL server level and grant access rights on the database level. No need to maintain passwords for every SQL server.

The Report server stores the unattended account once, hence it is no longer necessary to maintain SQL Login credentials and passwords for every Data Source deployed on the report server.

Sort date names order in a matrix report based on SSAS Data Source

 

I created a report based on a simple SSAS cube with a fact table containing a value per day per division. The report has a year parameter and needs to show the sum per month in a pivot table, with 12 months in the columns and a row for each division.

The report is easy to generate with the report wizard, but it results in columns where months are sorted by name: April, August, December, February, …

Problem

The Report designer sorts the columns alphabetically by the name of the month.

Solution

The months are supplied by the SSAS MDX in correct order, it is the report definition that sorts the resulting columns alphabetically.

The solution is quite easy: remove the sort order from the Month Column Group.

in the Group properties of the Month column group, go to the Sorting options and remove the sort on the Month:

So that no sorting is set:

The report shows the months in chronological order:

Add administrators to reports site after installation of Reporting Services

Add administrators to reports site after installation of Reporting Services

Problem

After installation and setup of the reporting services, when you browse to the reports site, you receive this error:

“User 'Domainname\Username' does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.”

You cannot even assign access because the Site settings link on the top right hand side is not available.

Solution

2 steps need to be performed:

• Add a windows user or group to the System Role Assignments and grant System Administrator rights.
• Add a role for this user or group at the root folder of the Report Server and grant

Walkthrough

• Log on locally to Windows Server running the Reporting Services with an account that is member of the local admin group.
• From the start button, run IE as administrator
• 
• In the UAC dialog, click Yes
• 
• In the IE address bar, browse to http://localhost\reports
• This there is no error message and the Site Settings link is available
• 
  Click on the Site Settings link, Click on the Security tab in the left hand pane
• 
• Click New Role Assignment in the toolbar above the list of groups and Users.
• In the New System Role Assignment page, add a Windows Group or username and assign a role, check System Administrator and System User. Click OK.
• 
• The new role is listed under Group or User.
• 
• Browse to the Reporting Services Home page, by clicking the Home link at the top of the page.
• Now you are in the Root folder of the Reports. In the toolbar, click Folder Settings.
• 
• In the Folder settings, there is only one tab, namely ‘Security’. Click on New Role Assignment.
• 
• In the New Role Assignment page, type a group or username, check Content Manager. Click OK to create.
• 
• The new role is assigned and listed in the list of Group or User.
• 

With these 2 actions we granted a windows group or user administrative rights on the report server. From now on you can browse to the reports site on any client PC, without the need to run as administrator.

To add users that can only browse reports

The following steps are needed:

• Grant access to the root folder of the reports site.
• Grant access to the Folder with the reports that the user needs to consult.

Dates in Excel files rendered from reports are displayed as plain numbers

Problem description

A report subscription is setup to send an email message on regular schedule. The report is included in the email as an attachment file rendered in excel format.

When MS-Windows users receive the email, they can open the excel file and dates will be shown in a normal date format:

When iPad users receive the email, they can open the excel file, but the dates are shown as numbers:

 

Workaround

Standard solution for MS-Windows users

In the report design, the date field, e.g. ‘ReportDate’ is displayed as a regular field

The textbox has a custom date format

The report is displayed with this custom format

And the export to excel will show formatted date cells when opened with MS-Windows

However on the iPad the date in column H will be shown as a number.

 

Workaround for iPad users

In the report design do not use a custom format property; instead use an expression with the Format function to represent the date:
=Format(Fields!ReportDate.Value, "dd/MM/yyyy")

The result from the report subscription email attachment looks like this on the iPad:

 

Reminder

With the workaround, the MS-Windows user will see the dates visualized correctly, but the cells are treated as plain text, not as dates. The cells are also left-aligned like any standard text.

SSRS group toggle visibility show initially as expanded, but shows plus sign instead of minus

SQL Server 2008 R2

Problem

A report with mulitple row groups is defined to toggle opened or closed. The requirement is to show the report with the groups initially shown as expanded. This works fine, but the toggle sign is shown counterintuitively as a plus (+) sign and not as the minus (-) sign.

The child groups and detail sections are setup to be shown with a toggle item that is referring to the parent group.

Typically a report with the detail expanded, is shown with (+) sign as toggle. From the (+) sign you would expect to expand the details, instead when clicking it, it will collapse.

Solution to initially show toggle item expanded with minus (-) sign

In hte example there are 3 group levels: country – region – zone, distinguished by the fields CountryName, RegionName, ZoneName. The basic toggle setup is as follows:

• In Visual Studio 2008 report design, select the tablix with the report groups
• In the row group pane, open the DetailsGroup rowgroup dropdown and select ‘Group properties…’
  
• In the Group Properties dialog window, in the left hand pane, select ‘Visibility’. In the right hand pane, the ‘Change display options are shown.
• Select the radio button option ‘Show’
• Check ‘Display can be toggled by this report item option’ and select the name of the parent group field, in this case ‘ZoneName’.
  
• Clcik OK to accept the change and close the dialog.
• The same is done for the rowgroup with the ZoneName. Open the Group properties dialog, set option to show, check and select the parent groupt report item ‘RegionName’.
• The same is done for the RegionName rowgroup, set to ‘CountryName’.

At this stage we have a report that opens expanded as shown in the introduction, with a (+) sign instead of (-)sign.

The next step is to set the InitialToggleState of each toggle report item to True.

• In the tablix, select the textbox with the toggle item ‘ZoneName’.
• In the Properties window of Visual Studio (NOT in the ‘Text Box properties… dialog window when one rightclicks on a textbox), scroll down to the InitialToggleState and set this property to True.
  
• Do the same for the textbox with the RegionName and CountryName.
• Save the report and preview it. Now it will show items expanded with a (-) sign.
  

Conclusion

On reports with several group levels, for which you want to toggle expand and show the report initially expanded but with (-) sign in front of the toggle report item, set the InitialToggleState property of the report item to True.

How to use a WCF service as SSRS 2008 R2 datasource

This article explains how to use WCF services that can return Lists of type T as datasource and dataset for a reporting services report. this is one of the many articles you can find on the web, who will each tell theri version of the story.

In another blog post i explain How to create a WCF service that can be consumed by SSIS or SSRS

Walkthrough

Create a Report Server project with BI Development studio 2008 R2. In the example it is called ConsumeWCF.

In the solution explorer rightclick on Shared Data Sources and Add New Data Source

• Name: dsrc_SysAComm
• Type: XML
• Connection string: http://<insert your hostname>/SysAService/SysAComm.svc
• 
• click OK

In the solution explorer rightclick on Shared Datasets and Add New DataSet

• Name: dset_Devicelist
• Data source: dsrc_SysAComm
• query Type: text
• Query:
  

  <Query>

  <Method Name="GetDeviceList" Namespace="http://tempuri.org/">

      <Parameters>

          <Parameter Name="LocationCode"><DefaultValue>A123</DefaultValue></Parameter>

      </Parameters>

  </Method>

  <SoapAction>http://tempuri.org/ISysAComm/GetDeviceList</SoapAction>

  </Query>

  
  
• To design and test the query, you can click the ‘Query Designer’ button. Note that the design feature is no more than a notepad-style text editor, but at least you can run the query to test for any syntax error.
  
  
• Save this shared dataset. It is now ready to be used as a dataset for a new blank report

SSRS Query syntax short explanation

Use the WCF Test Client to get some indication of the necessary key words. Personally I did not even try to use namespaces other than the standard tempuri.org. If you do not have the source code of the WCF service, then you can start WcfTestclient from the Developer command prompt for VS 2012. If you only have BI development studio 2008 and no VS2012, you can download a free version of SoapUI, which can be used like WcfTestclient

For starters have a look at the WCF Test client screenshot of the WCF service:

• <Query></Query> start and close the Query
  
• <Method> define the Name, Namespace (http://tempuri.org/ !mind the ending slash sign) and the parameters
  
  
  
  • <Method Name=”GetDeviceList” Namespace="http://tempuri.org/"> … parameters … </Method>
    
    
    
    • specify the name of the parameter
      
    • <Parameters><Parameter Name=”LocationCode”> …optional default value … </Parameter>
  
  
• <SoapAction> this single line is built with the Method Namespace, Interface service name and method name.
  
  
  
  • <SoapAction>http://tempuri.org/ISysAComm/GetDeviceList</SoapAction>

Create a WCF service to return List of type <T> and to be used by SSRS or SSIS

Problem

Data on system A can only be read by a proprietary interface. The data must be used by applications on system B equipped with SQL Server 2008 R2. The data will be read by SSIS packages to store in system B for further processing, and by SSRS for reporting.

Solution

To have a uniform access method, the data from system A will be passed along by a WCF service.

The solution will be made with Visual Studio 2012. Care must be taken that SSRS and SSIS can read the service data correctly. Therefore WCF service will use basic Http binding.

Other apps developed with VS 2012 can take full advantage of the List<Type T> returned by the WCF service. Odata has not been chosen since no viable solution to read Odata in SSRS has been found. Type T classes will be defined as MessageContracts in the service.

Walkthrough

• Create a new project WCF Service Application with Visual Studio
  • Under languages – select Visual C# - WCF – WCF Service Application
  • Call the project SysAService (the solution has the same name)
  •  
• Add a reference to the proprietary library with which to communiciate with System A.
  • this can be your own dll library too.
• Rename the Interface IService1 to ISysAComm and the service to SysAcomm. Rename the svc and the cs file too.
  
• · In the case of the proprietary library of System A , we had to change the Target framework in Properties of the application to .NET Framework 3.5
• · In Web config, verify that basicHttpBinding in the service endpoints definition
  

  <system.serviceModel>

  <services>

  <service name="SysAService.SysAComm" behaviorConfiguration="SysAService.SysACommBehavior">

  <!-- Service Endpoints -->

  <endpoint address="" binding="basicHttpBinding" contract="SysAService.ISysAComm">

  
  
• · In the Interface replace the default code by a method to retrieve a list of data from system A
  
  
  
  • the method is called GetDeviceList
    
  • the method returns a List<MyDevice>
    
  • the class MyDevice is also defined in the ISysAComm.cs file
    
    
    
    

    namespace SysAService

    {

    [ServiceContract]

    public interface ISysAComm

    {

    [OperationContract]

    List<MyDevice> GetDeviceList(string LocationCode);

    }

    [MessageContract(IsWrapped = true)]

    public class MyDevice

    {

    [DataMember]

    public string Abbreviation { get; set; }

    [DataMember]

    public string Designation { get; set; }

    [DataMember]

    public int Id { get; set; }

    public MyDevice()

    {

    }

    public MyDevice(string abbreviation, string designation, int id)

    {

    Abbreviation = abbreviation;

    Designation = designation;

    Id = id;

    }

    }

    }

  
  
• Now that we have the Interface to read and the class defined, edit the cpode in the service class SysAComm.svc
  
• Add a using statement to use with the soapexception
  
  
  
  • using System.Web.Services.Protocols;
  
  
• Add ServiceBehaviour before the class SysAComm
  
  
  
  

  [ServiceBehavior(AddressFilterMode = AddressFilterMode.Any)]

  public class SysAComm : ISysAComm

  
  

   

  
  
• Create the method GetDeviceList which will read the proprietary data and create a list of objects of type MyDevice.
  
• The complete list of SysAcomm.svc code
  
  
  
  

  using System;

  using System.Collections.Generic;

  using System.Linq;

  using System.Runtime.Serialization;

  using System.ServiceModel;

  using System.ServiceModel.Web;

  using System.Text;

  using System.Web.Services.Protocols;

  namespace SysAService

  {

      [ServiceBehavior(AddressFilterMode = AddressFilterMode.Any)]

      public class SysAComm : ISysAComm

      {

          public List<MyDevice> GetDeviceList(string LocationCode)

          {

              try

              {

                  SysAReader sys_a_reader = new SysAReader();   // the proprietary librra

                  List<SysADevice> sys_a_devices = sys_a_reader.GetDeviceList(LocationCode);

                  List<MyDevice> my_devices = null;

                  foreach (var sys_device in sys_a_devices)

                  {

                      if (my_devices == null)

                          my_devices=new List<MyDevice>();

                      my_devices.Add(new MyDevice(sys_device.Abbreviation, sys_device.Designation, sys_device.Id));

                  }

                  return my_devices;

              }

              catch (Exception ex)

              {

                  SoapException se = new SoapException(ex.Message, SoapException.ClientFaultCode, ex);

                  throw ex;

              }

          }

      }

  }

Debug the service

• Debug the service with the WCF Test Client : select the service SysAcom.svc in the solution explorer and hit F5.
  
• The WCF Test client starts:
  
  
• Doubleclick the GetDeviceList() method under ISysAComm, the Request and Response panes open.
  
  
• In the Request pane, the Parameter LocationCode has value (null), we can give a valid value to return a List of type MyDevice.
  
  
• When we click on the Invoke button, the method is called with the argument A123, click OK when the Security Warning dialog box opens.
  
• The Response pane is filled with data, in this case a list of 14 elements
  
  
• What we will need to call this service from a SSRS report or SSIS package can be found in the XML tab.
  
  
• Close the test client window.

Publish the WCF Service application to an IIS server

• In the visual studio project, rightclick on the SysAService project and select Publish… from the dropdown.
  
• The Publish Web wizard appears, select <new profile…> from the combobox.
  
  
• In the New profile dialog box enter a profile name, in the example we use the name of the IIS server
  
  
• Connection
  
  
  
  • Publish method: Web Deploy (make sure the web deployment agent is installed on the iis server
    
  • Server: TestWebServer (the test server is an intranet server in the Windows domain, it has no domain extensions)
    
  • Site name, the site will be published under the ‘root’ folder
    
  • User name and password: use an account with administrative rights on the iis server
    
  • Destination URL: http://TestWebserver/SysAService
    
  •  
    
  • Click on the ‘Publish’ button
    
  • The publish actions are executed and an I.E. windows is opened with the Destination URL.
  
  
• If you get HTTP error 500.19: most likely the application pool used is not for.NET 3.5
  
  
  
  • Start IIS Manager and connect to the TestWebServer
    
  • Open the branch under sites / root / SysAService
    
    
  • In the right hand side pane, click on Basic Settings… to open the Edit application dialog.
    
    
  • Click on the ‘Select…’ button to select an application pool with the correct .NET Framework. In our case we use .NET Framework 3.5, so we select a pool with .net Framework 2.0.
  
  
• When the app pool is adjusted, refresh the I.E. where you had the error. You should see the directory of the webservice
  
  
• When we click on the link SysAComm.svc the SysACom service page should display
  
  

  Conclusion

  
  

  The WCF service is now ready to be consumed by a Reporting services report or by an Integration Services package (both version 2008 R2). Read my blog about How to consume a WCF service in a source script in SSIS