Hiding Subreports from the List of Available Report Documents in SharePoint

Does the SharePoint library which contains your SSRS reports look a bit like this?

The user is asked to look at a list of RDL files and guess which one is the top level report, and which are subreports which are only intended to be consumed inside a ‘container’ report or are used as the target of hyperlink actions inside another report.  But it’s easy to hide reports which are not intended for users to run individually by making use of SharePoint’s View features.  This example has screenshots from SharePoint 2010 but you can also do the same thing in MOSS 2007.

The goal is not to physically move the subreports, but to make them hidden from the list a user is presented with by categorising them in a managed way.  So, from the Library ribbon, click “Create Column”.

In the next dialog, fill in the following values:

• Column Name: "Report Type"
• Type of information: "Choice"
• Require that this column contains information: "Yes"
• Enforce unique values: "No"
• Choices: "Report", "Subreport"
• Display choices using: "Drop-Down Menu" or "Radio Buttons"
• Allow ‘Fill-in’ choices: "No"
• Default value: "Choice", "Report"
• Add to default view: Checked

You should end up with a page something like this:

After you create the column, you will be returned to the library. As long as you checked the “Add to default view” box when you created the column, you should now have an additional column headed “Report Type”, with no values for any of the reports.  So, edit the properties of each report and set the value for the new “Report Type” property to either “Report” or “Subreport”.

Once you have completed categorising your reports and subreports, create a new view by clicking the “Create View” button in the library ribbon.

Select “Standard view”, then create the view with the following settings:

• View name: "Reports only"
• Make this the default view: Checked
• View audience: "Create a public view"

You can add in any additional columns or sorts as you wish, then in the “Filter” section, enter the values you need:

After creating the view, you will be taken back to the report library but now you are only seeing the top level reports.

Kerberos in a Clustered and Load Balanced BI Deployment

As if it weren’t hard enough getting Kerberos delegation to work properly in your average business intelligence distributed architecture, there is another level of knowledge required when you’re building an environment that’s got lots of resilience built in.

This post covers the following scenarios:

• Clustered SQL Server
• Clustered Analysis Services
• Scale out and load balanced Reporting Services
• Reporting Services integrated with SharePoint

I’ve been building just such an architecture in a lab environment using SQL Server 2008R2 and SharePoint 2010 but the steps below should be applicable to previous versions as well.

The steps involved are:

• Install SQL and SSAS clusters and set up DNS
• Install SSRS for scale out deployment and set up network load balancer DNS
• Setting SPNs
• Trusting computers and service accounts for delegation
• Configuring Reporting Services
• Configuring SharePoint
• Configuring Reporting Services/SharePoint integration

SQL and SSAS Clusters

The good news is you really don’t need to do anything that you wouldn’t normally do when setting up a cluster for either of these two services.  Create the cluster, install the services on the nodes, set up MSDTC, then create a DNS name that points at the cluster address.

Make sure each node in the cluster uses the same service account. For example, create a domain account called svc-SQL and use it as the service account for SQL on every node of the SQL cluster.  This is required, because the SPN to be set up later can only use one account for the cluster as a whole.  Do the same for Analysis Services.

SSRS Cluster

Here again there’s nothing out of the ordinary; the order I’d do it in is:

• Provision the servers, configure the NLB to load balance across them, and create a DNS entry for the RS service on the NLB
• Install Reporting Services but don’t configure it
• Install the SharePoint binaries on each server and  join them to the farm (without making them a Web Front End)
• Run the RS configuration tool on the first server, create a new database in SharePoint mode, then complete the rest of the configuration (report server web service, etc.)
• Run the RS configuration on each other server, pointing them to the same Reports database.
• After configuring all the RS servers, add all the servers to the scale-out deployment using the appropriate tab on the configuration tool.  You only need to do this on one of the servers.

Again, you need to make sure the webservice is running under the same account on all servers.

Service Principle Names

First real challenge.  There are some key points to bear in mind:

• You create SPNs for the cluster name, not for the individual nodes in the cluster. Don’t create SPNs for the individual nodes as this seems to break Kerberos.
• You must specify the port number, even if you are using the default ports for SQL Server and Analysis Services.
• You must create SPNs using the fully qualified domain name of the servers, but creating them for the NetBIOS name seems to be optional – your mileage may vary on this.

The SPNs you will need are for:

• SQL cluster
• SSAS cluster
• RS cluster
• SharePoint Central Admin
• SharePoint web sites
• SharePoint applications such as PerformancePoint if you are using them

The commands will follow this template:

setspn –S MSSQLSvc/[SQL cluster].[domain]:1433 [service account]
setspn –S MSOLAPSvc.3/[SSAS cluster].[domain]:2383 [service account]
setspn –S HTTP/[RS NLB].[domain]:80 [service account]
setspn –S HTTP/[central admin server or NLB].[domain]:[port] [service account]
setspn –S HTTP/[web front end or NLB].[domain]:[port] [service account]

Let’s build some examples of that.  In this scenario, the SQL cluster is called SQL, the SSAS cluster is called AS, the SSRS load balancer is called RSNLB, the SharePoint Central Admin box is SP01, and the SharePoint web front end load balancer is called WFENLB. The domain is mycompany.local and all services are on their default port, other than Central Admin which is on port 8080.  You’d need to run the following commands.

setspn –S MSSQLSvc/SQL.mycompany.local:1433 svc-SQL
setspn –S MSOLAPSvc.3/AS.mycompany.local:2383 svc-AS
setspn –S HTTP/RSNLB.mycompany.local:80 svc-RS
setspn –S HTTP/SP01.mycompany.local:8090 svc-SPFarm
setspn –S HTTP/WFENLB.mycompany.com:80 svc-SPPortal

Something to bear in mind: if you have created an alternative URL for your SharePoint site (such as http://intranet), you need to create the SPN for that name instead of the WFE load balancer name.

Trusting for Delegation

On the DC, ensure that each of the following are trusted for Kerberos delegation:

• SharePoint Central Admin server
• SharePoint web servers
• Reporting Services servers
• SharePoint Central Admin web application pool account
• SharePoint web site application pool account
• Reporting Services web app service account

You can’t trust accounts until an SPN has been set up – the ‘Delegation’ tab doesn’t even appear in their account’s properties – so you have to complete this step after the SPNs have been created.

You might find you need to trust the SQL and SSAS boxes and service accounts.  You also might find you need to trust the virtual network names for their clusters.

Configure Reporting Services

On each of the SSRS servers, locate the file rsreportserver.config and edit it:

• Find the element <AuthenticationTypes> and ensure it contains the element <RSWindowsNegotiate/>.  Remove the element <RSWindowsNTLM/> if it exists.
• Find the element <UrlRoot> and insert the web address of the SharePoint site (in the example above the correct value would be http://WFENLB or http://intranet).
• Find the element <Hostname> and add the name of the SSRS cluster (in the example above the correct value would be RSNLB).  The <Hostname> element may not exist, so you will need to create it – it goes inside the <Service> element, the same as the <UrlRoot> element.  You would create a line such as <Hostname>RSNLB</Hostname>.

You must repeat for every SSRS server otherwise only some of the servers will actually work and your users will get ‘intermittent’ errors.

SharePoint

A trip to Central Admin should show that the RS servers have already been added to the farm.  Make sure the Central Admin and all other site collections are set to use Windows Authentication in all their zones which you intend to use SSRS in.

SharePoint and Reporting Services Integration

SharePoint 2010 automatically installs the SSRS add in as part of the pre-requisites.  For MOSS 2007, download the latest version of the add in and ensure your entire SP farm is service-packed.

Integration is then ready to be configured from Central Admin. In SP 2010 the config panel is at General Application Settings –> Reporting Services Integration.  The values you need are:

• Report Server Web Service URL: the address of the SSRS web service as accessed through the network load balancer, for example http://RSNLB/reportserver
• Authentication Mode: Windows Authentication
• Credentials: The username and password of a domain account which is in the local Administrators group on the SSRS servers.  If there isn’t a single account that’s in this group on every one of your SSRS boxes, use an account which is in the Administrators group for the first SSSR box in the deployment.  You need to specify the username in the format [domain]\[user]

After the first server is integrated, you are given the chance to add each of the other SSRS servers in the scale-out deployment to the integration.  You must complete this for each server you have in the deployment.

Finally, you can click ‘Set server defaults’ to change any of the the properties of the integration you want to alter.

Points to Bear in Mind

Not working?  Here’s what I check in my setup:

• Kerberos needs TCP and UDP ports 88 open in order to work.  Is your firewall blocking that port?  Try disabling the Windows firewall to check.
• Are the clients using Internet Explorer 6 or above?  Is the browser seeing that the site is in the local intranet zone? Does the zone allow integrated Windows authentication?
• Go back over the SPNs.  One mistake in your setspn command will have broken Kerberos completely.
• Check all the accounts and computers are trusted.
• Try navigating direct to the SSRS web service (i.e. not by opening a report in SharePoint’s interface).  If you can run a report through http://rsnlb/reportserver but get an error message when doing it through SharePoint, the probable cause is with a SharePoint SPN or delegation permission not with Reporting Services.

A Better Date Dimension – Modelling in Analysis Services

Hopefully you are continuing from part one of this post.

If you have run the script to create and populate your date, calendar and bridge tables you can go ahead and add the tables to your Analysis Services project’s data source view.  The tables all have foreign keys so just add them and the DSV will sort out the relationships.

Next, create two dimensions – one based on the date dimension and one based on the calendar dimension.  Set the Type of the calendar dimension to ‘Time’ and the ErrorConfiguration to ‘Default’.  On the calendar dimension create these attributes with the associated properties:

• Calendar Key
  Name: ‘Date’
  Type: Calendar –> Date
  NameColumn: ActualDate
• Calendar Name
  DefaultMember: [Calendar].[Calendar Name].&[1]
  IsAggregatable: False
  KeyColumns: CalendarOrder
• Date Name
  Type: Calendar –> Date
• Year
  Type: Calendar –> Years
  NameColumn: YearName
• Quarter
  Type: Calendar –> Quarters
  KeyColumns –> Quarter and Year
  NameColumn: QuarterName
• Month

Type: Calendar –> Months
KeyColumns –> Month, Quarter and Year
NameColumn: MonthName

You might well want to use some of the other columns in the calendar table such as month of year.  My date dimensions are usually very rich so fill your boots and add whatever you think will help your end users.

Next, create a hierarchy of Calendar Name –> Year –> Quarter –> Month –> Date and set up the attribute relationships.

 

 

At this point you should be able to process the dimension.  You can then browse it to get something like this:

As you can see, the definition of what constitutes ‘2008’ differs based on the calendar you expand.

Bridging the Many-to-Many

Create a new measure group in your cube using the bridge table as its source.  A count measure will be added automatically.  This is required but useless so set its Visible property to False.

On the Dimension Usage tab, make sure the new measure group is connected to the Date and Calendar dimensions (the relationships in the DSV will do this properly when you add the dimensions to this tab).

Any measure groups that need to join to the calendar dimension must have a regular join to the date dimension and a many-to-many join to the calendar dimension through the bridging table measure group.

You can now process and test the cube.

Here is an example with a very simple sales measure group, showing two calendars being browsed:

The TOTAL and all the total for each months for each calendar is the same – 75.  This is because the calendar name attribute is set to non-aggregatable.  But the months belong in different quarters.  For example, April is in Q2 in the Gregorian calendar, but it is the first month of Q1 in a fiscal calendar which starts April 1.

You can also use the calendar name as a parameter in a Reporting Services report based on the cube – just make sure the parameter is set to allow single values only.

What if you also create reports from the relational database?  You now have to join to two extra tables every time you want to include a date, don’t you?  Well, no, you don’t, and I will explain why in my next post.