As you saw in the previous section, there are three authentication options that Excel Services uses to authenticate to SSAS: Windows Authentication, SSS, and None.

The only case in which you would use Windows authentication is when SSAS accepts Windows authentication, and you want to let the identity of the workbook viewer delegate to the Analysis Services instance. This authentication method is known as Per-User identity and only Kerberos enables it. If you select Windows Authentication without implementing Kerberos, after the workbook is published to SharePoint, users will get the error shown in Figure 13-10 when viewing the workbook in the browser.

Figure 13.10. Figure 13-10

There are scenarios in which you want to have a single account act as a proxy for all your users when accessing the backend datasource. This account is referred to as an unattended service account, and it is widely used in the new service application infrastructure in SharePoint 2010.

In MOSS 2007, the single sign-on (SSO) feature somehow implements the concept of the unattended service account, but not in a practical way. Unfortunately, the SSO feature introduced some serious limitations with non-Windows identity providers and anonymous users, so it was replaced with the Secure Store Service (SSS) in SharePoint Server 2010. The SSS works with all types of authentication providers, and it's not limited to Windows identities. It provides access to external datasources under the security context of a predefined set of credentials stored in the Service Application's database.

In Excel Services, each workbook can have its own unattended service account or they all can share a global unattended service account. If the workbook connection's authentication type is set to SSS, you need to reference a target application ID that stores the unattended service account credentials required for authenticating to SQL Server Analysis Services. This account may or may not be used by other BI applications that need to talk to the same backend datasource. Whether to use a single or different unattended service accounts really boils down to your business requirements, but keep one thing in mind: the more accounts you create, the more administrative effort is required to manage them.

If the workbook connection's authentication type is set to None, the global unattended service account is used. This account, along with many other Excel Services settings, can be configured in the Excel Services service application. Failure to define this account will cause the error in Figure 13-11 to be thrown.

Figure 13.11. Figure 13-11

The process of creating the unattended service account is fairly straightforward. Before you start creating this account, you need to ensure that you are either the farm administrator or the service application administrator for the instance of the Secure Store Service.

In order to create this account, perform the following steps:

At this point, you should be able to see the new target application along with other target applications in the Manage Target Applications page, as shown in Figure 13-13.

Figure 13.13. Figure 13-13

After creating the target application, you should set credentials for it. To set credentials for the target application you've just created, follow these steps:

You are almost there! The last step is to introduce the new target application to Excel Services. To do so, follow these steps:

Almost every spreadsheet application currently on the market ships with a feature that allows sorting and summarizing large tables of data independent of the original data layout kept in the spreadsheet itself. This feature has different names in products such as Microsoft Excel, OpenOffice.org Calc, Quantrix, and Google Docs, but the concept remains the same in all these products.

In Microsoft Excel, this capability is called a PivotTable. Essentially, a PivotTable is a powerful data summarization and cross-tabulation object that allows you to do free-form layout of your business data. For instance, when you use Microsoft Excel for cube browsing, you can import the cube's data into Excel and represent it as PivotChart or PivotTable report, connected to the same cube.

The following steps will help you create a new Excel workbook containing an OLAP PivotTable report based on data in the Analysis Services cube.

With the Internet Sales PivotTable inserted, make the report complete by adding a PivotChart to the worksheet. This chart will give focus to the sales data and make it easy to understand.

To add a PivotChart based on the data in the Internet Sales PivotTable, you need to perform two easy steps as follows:

Two things need to be highlighted here. First, notice that when you apply filters to the PivotChart, a search box appears that allows you to look for a specific item in the hierarchy and find it, rather than having to browse all the way down or up in the hierarchy to find it. This is particularly important, because if a field contains lots of items, it's fairly difficult to find what you are looking for. Second, when you change the filters on the PivotTable, this affects the PivotTable and vice versa!

Figure 13.18. Figure 13-18

In an Excel PivotTable, you can filter a column or row label by using the Label Filter or Value Filter options. In the Internet Sales PivotTable example, you can select the PivotTable to report on backend data only when Source Currency is equal to US Dollar, as shown in Figure 13-18.

To filter Source Category on US Dollars only, follow these steps:

In Excel 2007, if you wanted to filter a PivotTable or PivotChart, you had to add it to the Report Filter section. Additionally, if you wanted to select multiple items in a filter, Excel would have shown you the tag "Multiple Items" without telling you which items you had selected. In Excel 2010, Visual Slicers are introduced to address issues of this kind. In nutshell, Slicers allow you to filter the data on PivotTables and PivotCharts with just a single click, which is much easier than before.

There are three characteristics about Slicers that make them a great navigation paradigm in Excel 2010. First, Slicers are rendered as buttons, making the filtering operations relatively easy. Second, you can select more than one item by holding the shift key and clicking on each item or by dragging mouse over a range. Third, the filters that produce no result from the backend datasource are grayed out, which is yet another good indicator when interacting with PivotTables and PivotCharts.

The following steps will help you add Category and Subcategory Slicers to the Internet Sales PivotTable.

Another addition to Excel 2010 is the ability to add Sparklines to a set of data being reported on. A Sparkline is a powerful graphic that brings meaning and context to what it describes. Simply put, think of a Sparkline as a mini-chart without any fluff, such as tick marks, labels, axis lines, or a legend.

Excel 2010 ships with three distinct types of Sparklines:

If you have worked with and loved the conditional formatting data bars introduced in Excel 2007, then you will find Sparklines even easier to use.

The following steps will help you add Sparklines to the Internet Sales report to highlight trends in the Internet sales report and identify Line values with special formatting.

In Excel 2007, if you wanted to perform certain types of calculations against a PivotTable for rows and columns, you would have to do this either outside of the PivotTable in your own function or turn them into a cube formula.

In Excel 2010, Microsoft has introduced a new feature named Show Value, which allows you change the way you view values, by selecting from a list of predefined calculations, as shown in Figure 13-22.

Figure 13.22. Figure 13-22

The following steps will help you calculate and display regional Internet sales as a percentage of the country sales.

By looking at the PivotTable, you can quickly find out that in Canada for all year ends from 2002 to 2005, British Columbia has had the biggest percentage of sales among all provinces, as shown in Figure 13-23.

Figure 13.23. Figure 13-23

When working with OLAP PivotTables, there are scenarios where you want to work with the same logical group of items from the underlying data across multiple reports. For instance, most of the report layouts needed by Adventure Works need to show information about European countries. The problem is that this grouping doesn't exist in the cube, so you always end up applying the same filter to get reports for such countries over and over again.

A new feature in Excel 2010 that helps resolve issues of this kind is the ability to define Named Sets. This new feature allows you put common sets of items together and reuse them. This grouping can be done based on row items, column items, or your own MDX queries.

The following steps will help you add a Named Set to the Internet Sales PivotTable.

At this point, you have a reusable Named Set containing three European countries. The new Named Set is attached to the workbook and moves with it, so if anyone opens the workbook, they can reuse this set in their own PivotTable and focus on the rest of their analysis. Of course, the example used in this section was relatively simple, but the idea of reusability is pretty much the same, no matter how complex your Named Set becomes.

Although Named Sets may sound just like simple groupings of items, in reality they offer way more than that. You can do things with Named Sets that otherwise wouldn't be possible at all, such as combining items from multiple hierarchies.

In previous versions of Excel, you could create Named Sets with some limited functionalities, but there was no graphical user interface, and this could be only done through the Excel object model and by writing your own MDX queries. The Named Sets defined programmatically that way consisted only of items from a single hierarchy, and they could never be dynamic in nature.

In Excel 2010, you can make Named Sets based on your own custom MDX and use them in PivotTables to dynamically change their dimensionality. These types of Named Sets are called

Dynamic Sets. Dynamic Sets were first introduced in Analysis Services 2008, but unfortunately Excel 2007 couldn't fully benefit from them. For example, the Top 50 Selling Countries is one of the great examples in which Dynamic Sets can help a lot. Another example is when you want to see European countries when you're filtering on Source Currency by Euro and show North American countries when you're filtering by American dollar.

Like Dynamic Sets, Analysis Services has had writeback capability for a while, but it was not implemented in Excel out-of-the-box. In previous version of Excel, if you ever clicked on a cell in a PivotTable to edit its content, you would get an error message saying that PivotTables cannot be edited.

In Excel 2010, the ability to write back against a cube and change the underlying data is referred to as what-if analysis. This particular feature becomes extremely helpful when you use Excel and your existing data to plan something in future. In a nutshell, what-if analysis is all about clarifying what-if type questions you might have in your mind when looking at a PivotTable.

Figure 13.26. Figure 13-26

In order to use what-if analysis, you need to enable it from the PivotTable Tools

Once the what-if analysis is enabled, you can edit any values and enter the value of your choice. At the end, you can atomically calculate the selected change or have the cube calculate it for you and commit the information back to the cube, as shown in Figure 13-26.

A major consideration in deploying to Excel Services, and quite frankly the one you will want to plan carefully, is the file locations and connection document libraries that are considered trustworthy. The following steps walk you through defining these locations:

With the trusted locations properly configured, the next step is to publish the Internet Sales workbook to SharePoint and view it using Excel Services.

The following steps will help you publish the workbook:

Once a workbook is published to SharePoint, Excel Services performs loading and calculations included within the workbook on the server, which means that whatever logic is behind the workbook is no longer directly accessible to the end users.

Next, the Excel Web App tries to open the workbook in view mode. Unlike the previous version, in Excel Services 2010 if the workbook contains unsupported features, it is still rendered, but some of the functionalities may not work properly.

As a final remark, the Excel Web App works in IE, Firefox, and Safari. Figure 13-30 shows InternetSales.xlsx opened in Safari browser in iPhone 3G.

Figure 13.30. Figure 13-30

One of the drawbacks of publishing an Excel workbook to Excel Services 2007 is that the published Excel workbook is not editable. This means that users cannot mock around with the data contained in the workbook and then save the changes back to the Excel workbook. Although, the Open In Excel and Open Snapshot In Excel options allow users to take the workbook offline and take further actions, most users prefer to be able to do online editing.

In Excel Services 2010, you can edit a workbook using Excel Web App. Edit mode offers only a subset of the Excel 2010 functionalities, but there is a fairly impressive set of operations that you can perform when editing the workbook online.

Edit mode also supports joint sessions, where more than one user can edit the document at the same time. This is done through a separate session for each user in Excel Web App. The Excel Web App keeps alerting everyone about the changes that have been made to the workbook by others.

Figure 13-31 shows an editable version of the Internet Sales workbook and a simple calculation at =$G$3.

Figure 13.31. Figure 13-31

Another way to render and manipulate a published Excel workbook is through the Excel Web Access web part, also known as EWA. Figure 13-32 shows the entire Excel workbook displayed in a single EWA.

Figure 13.32. Figure 13-32

Unlike its predecessor, EWA has no dependency on client-side scripts or any ActiveX control to function properly. You can navigate through the sheets within the published workbook just as you would when using the Excel client. In addition to the entire workbook, a section of the workbook can be hosted and rendered in a EWA web part. This is done based on named parameters within the workbook, which should be created when authoring it.

Figure 13.33. Figure 13-33

In Excel Services 2007, if you wanted to allow interaction with the workbook through the EWA, you had to list parameters on the left side in the Parameter pane. In the EWA web part's properties, you can control how much users can interact with the hosted workbook and for what features, as shown in Figure 13-33.

One thing needs to be highlighted here. Interactivity in EWA web parts is not the same as editing in Excel Web App. In the Excel Web App, the changes users make to the workbook are written back to the original workbook. When interacting with EWA, users can see changes in calculations and visualization objects, but the original workbook remains intact.

You can expose your Excel model in the Web Access web part and turn on the interactivity feature so that users can interact with the workbook without a need for listing parameters on the left side in the Parameter pane. Also, you have the option to specify whether changes made to the workbook need to be committed back or, in the case of a model, not committed back to the workbook.

Last, but certainly not least, EWA supports the Web Part Connection framework and can send data to or receive data from other web parts to create really powerful dashboards and mash-up scenarios.

One of the challenging configuration steps in the previous version was to properly set up the authentication to the backend datasource. The problem was that the authentication option couldn't be defined per datasource; instead, PerformancePoint would use the application pool identity of the SharePoint Web application. From the security practice perspective, this was not recommended because users potentially could use PerformancePoint to access to the data that they shouldn't have access to at all, such as SharePoint content databases.

PerformancePoint Services 2010 implements per datasource authentication and has some new features that control how authentication to the datasource itself is configured. One of these new features is the unattended service account for PerformancePoint.

The unattended service account concept in PerformancePoint services 2010 is very similar to Excel Services' unattended account with two differences. As explained earlier in this chapter, in Excel Services you create the target application in the Secure Store Service application and then reference its Application ID in the Excel Services service application's settings. Thus, both the username and password are stored in the Secure Store Services application.

In PerformancePoint Services, however, you create the unattended account directly in the PerformancePoint Services application settings. In this case, the password is stored in Secure Store Service and the actual username is stored in the PerformancePoint Services database. If you look at a PerformancePoint target application, you will find that it only contains the password field and not the username field.

An unattended account can be created using the following steps:

As with any other BI solution, the first thing that you will want to do is to go after data. In order to create the datasource used for this dashboard, follow these steps:

At this point, you have successfully created the dashboard's main datasource and it's been uploaded already to the Data Connections document library by the Dashboard Designer.

As you saw in Figure 13-36, there are three different authentication types available for the datasource you are building.

The unattended service account option has been discussed already, and by now you should know what it does, but the other two options deserve more attention:

Recall from the previous section that your goal for building the Internet Sales dashboard is to compare Internet sales information with an established goal, and then measure and monitor the success of the online business per country.

But what is success anyway? How is it implemented in a dashboard? Success (or the goal) in a certain area of the business is defined by someone in your organization who knows the business inside and out. In PerformancePoint, a primary metric used to implement and measure this success is something referred to as a key performance indicator (KPI). Once a KPI is defined and implemented, it can be used to monitor the organization's progress in a specific area, such as gross profit margin per product category earned from Internet sales.

In order to create a new KPI to track gross profit margin for Internet sales, you need to follow these steps:

At this point, your new KPI should look like Figure 13-42. Notice on the Details pane that you have all available information about the KPI, such as related datasources.

Figure 13.42. Figure 13-42

With the dashboard's datasource and KPI complete, you have all the elements that you need to build the scorecard. This scorecard will contain the Gross Profit Margin KPI, and shows all sales across all years and is broken down by product category.

In this section, you create a report that connects to the scorecard you created in the previous section and display Internet sales for all years grouped by product category.

Now that you have gone through all the steps of building different pieces of the dashboard, it's time to put all the pieces together. In this section, you will create a dashboard that displays the scorecard and the report and connect them. This connection will allow filtering of the report (the pie chart) using the currently selected category from the scorecard.

The dashboard that you just published is nothing more than a Web Part page, two web parts, and a web part connection, which were all set up automatically as part of the dashboard-publishing process.

These connections are not exclusive to PerformancePoint web parts. Using the web part connection, you can take your dashboard design to the next level by adding more web parts to the page representing more complex analytical scenarios. You can examine the content of the dashboard by switching the page to edit mode, as shown in Figure 13-51.

Figure 13.51. Figure 13-51

There are many more functionalities available on the chart itself. Let's suppose that, for the purpose of trend analysis, you need to change the type of the chart. To do so, right-click underneath the chart's legend, and from the context menu select Report Type

If you right-click on the analytic chart itself, you'll see that there are plenty of helpful built-in functionalities at your fingertips, as shown in Figure 13-53.

Figure 13.52. Figure 13-52

Figure 13.53. Figure 13-53

There are three options in this menu that need to be highlighted here:

Last, but certainly not least, if you ever decide to show a dashboard element in a completely new page to have more real estate, from the web part that hosts the element, modify the properties, and select Open in New Window, as shown in Figure 13-55. You can also reset the view to the element's original state.

Figure 13.55. Figure 13-55

All the Reporting Services operations a user may perform in SharePoint application pages are routed, in the form of a request, to the Report Server for further processing. Depending on the authentication type of the connected mode integration, the request may or may not include the user's identity token in the header.

If you browse to the Reporting Services Integration page (Central Administration

Figure 13.62. Figure 13-62

In this page you can select either of the following options:

Although the new claims-based authentication model in SharePoint 2010 allows you to plug multiple authentication providers into a single web application, there are still cases where you need to extend the web application and use multiple zones.

For example, let's suppose that Adventure Works requires some users to authenticate to the company's intranet sites using smart cards while others still enter their credentials to logon to the sites. The business requirements dictate that reports must work for both types of users regardless of their authentication method. To do so, the IT department has extended the intranet web application, created a new zone, and has set up the certificate mapping for that web application in IIS 7.0.

In the previous versions of Reporting Services, if you wanted to display SSRS reports to those users who authenticated themselves by using their smart cards, reports must have been published to the new zone configured for smart card; otherwise, reports would error out saying that SSRS reports don't support multi-zone scenarios. This was an issue because such reports were pretty useless in other zones.

Thankfully, Microsoft has addressed this issue in Reporting Services 2008 R2 by introducing multi-zone support in the connected mode. You can use the alternate access mapping functionality in SharePoint and set up access to Report Server items from one or more SharePoint zones (default, Internet, intranet, extranet, or custom). You will see an example of a report rendered in two different zones later on. Keep reading!

So far, you have learned that the multi-zone scenario is fully supported in SSRS 2008 R2 when it's configured in connected mode. Suppose that Adventure Works would like to allow access to the catalog of products report without forcing Internet users to log in.

Even though SharePoint supports anonymous access to a site and maps anonymous users to the Limited Access permission level, there is an issue in displaying SSRS reports to anonymous users in connected mode. Unfortunately, Reporting Services still requires a valid security context and doesn't support anonymous access to reports right out of the box. The issue is that Anonymous users do not represent a true security context in SharePoint; therefore, when they try to access reports, SSRS won't be able to authorize their access to Report Server.

Report Server never allows its service account (configured in the Reporting Service Configuration Manager) and all its administrative privileges to be delegated when connecting to a resource on the network. So, if you are reporting against a datasource that does not require authentication or when you use a SQL account in your datasource, the question is how is the connection between Report Server and the datasource established? Under what security context? Remember, Report Server must use a valid Windows security context to access resources such as an XML file or a SQL instance that supports SQL authentication.

In the Reporting Services world, this liaison account is referred to as execution account and it's mainly used in the following two scenarios:

For example, when you create a report that has a link to an external image stored in a remote server, in the preview mode your credentials as a developer will be used to display the image. However, when the report is deployed to production and viewed on a SharePoint site, Report Server uses its execution account to retrieve the image. If the execution account is not specified, the image is retrieved using no credentials (i.e., anonymous access). Obviously, if neither of these two accounts has sufficient rights to access the image, it won't show up in the report. This is very important to remember for deploying reports to SharePoint, because images used in your report may or may not be in the same site collection that the current report viewer has permission to access.

To set up an execution account you need to specify it in the Execution Account page in the Reporting Services Configuration tool, as shown in Figure 13-63.

Figure 13.63. Figure 13-63

The execution account is encrypted and stored in the RSReportServer.config file.

If you have installed Reporting Services using a scale-out topology, you must run the configuration tool on each report server and use the same set of credentials for the execution account.

As mentioned before, you will use BIDS 2008 R2 to create the reports in this chapter. However, there are also two ways you can build your reports in BIDS: manually or by using the Report Wizard. In this section, you will use the manual process.

To author your first report, follow these steps:

The query also takes two parameters, named @StartDate and @EndDate, to limit the calculation of the sales amount to a period of time specified by the parameters.

SELECT
   PC.Name AS Category, PS.Name AS Subcategory,
   DATEPART(yy, SOH.OrderDate) AS Year,
   'Q' + DATENAME(qq, SOH.OrderDate) AS Qtr,
   SUM(DET.UnitPrice * DET.OrderQty) AS Sales
FROM Production.ProductSubcategory PS INNER JOIN
   Sales.SalesOrderHeader SOH INNER JOIN
      Sales.SalesOrderDetail DET ON SOH.SalesOrderID = DET.SalesOrderID INNER JOIN
      Production.Product P ON DET.ProductID = P.ProductID
      ON PS.ProductSubcategoryID = P.ProductSubcategoryID INNER JOIN
   Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID
WHERE (SOH.OrderDate BETWEEN (@StartDate) AND (@EndDate))
GROUP BY DATEPART(yy, SOH.OrderDate), PC.Name, PS.Name,
   'Q' + DATENAME(qq, SOH.OrderDate), PS.ProductSubcategoryID

You can examine the query result by clicking the button that has the exclamation mark on it and then entering a sample start date and end date such as 1/1/2003 and 12/31/2004. The returned result will appear in the grid below the query section, as shown in Figure 13-67. Once you are done, click OK to close the Query Designer.

Figure 13.67. Figure 13-67

Step 14: Click OK again to close Dataset Properties dialog.

At this point, you should have a dataset with the following fields: Category, Subcategory, Year, Qtr, and Sales. The next logical step is to actually build the report display as outlined here:

You are almost done with the initial formatting and clean up, but you still have to enable the drill-down, so the report allows users to look deeper in any area they choose. The goal is to show categories and years only when the report is first run and then allow users to see the subcategories and quarters by using the tree-style +/- controls that appear next to each category or year.

That's everything you need to do to build a very basic report that shows the Adventure Works sales by quarter and product category. Finally, preview the report; it should appear like the one shown in Figure 13-70.

Figure 13.70. Figure 13-70

If you have been developing or designing reports for any amount of time, you probably know that no report is complete without some kind of visualization. Essentially, reports are there to allow end users to make fast business decisions, so if you can represent your report in such a way that they can intercept its data immediately and get the key points, your report would be of great value to them.

With SQL Server Reporting Services 2008, Microsoft introduced a useful data visualizations, named gauge. The gauge allows report developers to visually display aggregated data, and it's commonly used in digital dashboards. In SQL Server Reporting Services 2008 R2, more data visualizations are introduced. Sparklines, data bars, and indicators are additions to the SQL Server Reporting Services family, representing the same basic chart characteristics of values, categories, and series, but without any fluff such as axis lines, labels, or a legend.

In the example in this section, you have a chance to work with a Sparkline chart, while continuing work from where you left off with the sales by quarter and product category report created in the previous section.

For a Sparkline chart, you need a value field like Sales and a group like Quarter for which to record the trend. To add this to your report, follow these steps:

Although you used a matrix in your report, you are really using a tablix data region under the covers. The tablix (table + matrix) data region was first introduced in Reporting Services 2008, and it offers the flexibility of the table combined with the crosstab reporting features of the matrix.

As you can see in your report, Product Category and Product Subcategory are sharing two different columns, and there is a considerable amount of horizontal spacing wasted in the first column. You can reduce this spacing and make both groups share the same column by using a new feature in tablix called stepped columns. If you have been doing crosstab reports, you probably know that this wasn't an easy thing to implement with the old matrix. For more information on stepped columns, refer to the official documentation at www.devhorizon.com/go/21.

Another feature in tablix that can help you improve your crosstab reports is something known as side-by-side crosstab sections. Your report is currently broken down by year at the top, but what if you wanted to have the same grouping (Product Category, Product subcategory) by territory side by side with the year section? What if you want to allow users to drill down into categories and subcategories and see the year breakdown and territory breakdown at the same time?

If you wanted to do this report in SSRS 2005, you had to do it using multiple matrices, but in SSRS 2008 and 2008 R2, you can use a tablix and its native support for side-by-side crosstab sections. All you need to do is to include the territory data in your return result set and add it as a parent column grouping in the same matrix you just used in your report. As matter of fact, you can have an unlimited number of side-by-side crosstab groups (correlated or uncorrelated) on rows and columns of a tablix data region.

The tablix feature of Reporting Services makes using asymmetric layouts in your report super easy.

Now that you have prepared your report, you are ready to deploy it to SharePoint 2010. In BIDS, the terms publish and deploy are interchangeable. They both refer to a process that makes the report available in SharePoint for online viewing. Although the publishing process may seem simple at first glance, there is more to it than just moving the content from your local drive to a SharePoint site.

What happens during publishing that makes it a special process? First, BIDS validates the report before it is added to the destination libraries in SharePoint and if there are any problems you will be notified. As you may know, you can always go to a document library and upload documents yourself, but in this particular case, you should avoid direct uploads, because the validation check never occurs. This means that you never know if your reporting files are valid until either you manually access them or a background process such as snapshots, subscriptions, or a caching process references them.

Second, during the publishing process any shared datasource in the report project will be converted to an .rsds file name extension (originally, the file extension was .rds). Both .rds and .rsds files have the same content, but they come in different schemas. What's important to note is that it's only the .rsds file extension that is recognizable by SharePoint, and this is defined in the file extension mapping file (Docicon.xml) located at Drive:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14TemplateXML using the following entry:

<Mapping Key="rsds" Value="datasource.gif" OpenControl="SharePoint.OpenRsdsFiles"/>

The process of converting the .rds file extension to .rsds involves a web service call to the CreateDataSource() web method located at the ReportService2006.asmx endpoint, which makes the actual conversion. Note that you can perform the conversion programmatically by calling this web service and passing in an .rds file.

Finally, there is one more thing that the publishing process does for you. If you happen to publish a report that already exists in the destination document library, the report will be checked out, updated as a new version, and then checked back in for you. This is important because it illustrates that reports are treated like any other document content type for the purposes of versioning, permissions, and retention.

Publishing your report to SharePoint is relatively simple. Right-click the solution name and click Properties to open the Property dialog window, as shown in Figure 13-74.

Figure 13.74. Figure 13-74

Below, you will find all the properties and a brief explanation to help you understand what they are for and what you need to type in them to deploy your report to SharePoint.

Set the value of the TargetDatasetFolder, TargetReportFolder, and TargetReportPartFolder properties to the fully qualified URL of the Documents document library in the BI Center (see the "Configuring the BI Center" section). Next, set TargetDataSourceFolder to the fully qualified URL of the Data Connections document library in the BI Center (see the "Configuring the BI Center" section). Finally, set the TargetServerURL property to the fully qualified URL of the BI Center and the TargetServerVersion property to SQL Server 2008 R2.

With the deployment properties completely configured, you are ready to deploy the report with all its items to SharePoint. Note that you need Full Control or Contribute permission in the site on which you are deploying your reports; otherwise, you will get the Reporting Services login when you attempt to build and deploy the reports. To deploy this report, all you have to do is right-click the solution and click Deploy.

At this point, you can browse to the document library and click the name of the report to render it in the browser (via RSViewerPage.aspx), as shown in Figure 13-75.

Figure 13.75. Figure 13-75

In case you didn't notice, there is a Cancel link on the page every time your report is run. This means that report processing is now completely asynchronous, and you have the option to cancel it while it's in progress.

By definition, report parts are individual items that make up an SSRS Report. They can be anything in a report from a parameter to a data region, such as a matrix. The idea is like splitting an ASPX page into smaller user controls, so they can be shared across multiple pages.

The good news is that these components can now be saved individually — without the rest of the report page. More precisely, however, report developers from either BIDS or Report Builder 3.0 publish report parts to a destination folder, and then other report developers or information workers can reuse the published parts and put together their own reports without having to build everything from the ground up.

Creating report parts in BIDS is for more experienced report developers, who will create these components and perhaps use source control, whereas Report Builder is for less experienced users, who should use the published report parts to build their own reports.

The report that you built in this section has only three items that can be published as report parts. To make these parts available on the SharePoint site, follow these steps:

Once the report parts are published to a site, they can be found and reused by information workers using Report Builder. To see a list of available report parts in Report Builder, browse to the View tab and select Report Part Gallery. You can search for a specific part by typing its name in the search box provided on the top of the pane.

In addition to the RSViewerPage.aspx, there is another way of displaying your reports in SharePoint, through the use of the standalone Report Viewer web part. Adding a Report Viewer Web Part to a page is as easy as dragging and dropping it into a Web Part zone and then setting some simple properties.

To host your report in a Report Viewer web part, follow these steps:

Figure 13-79 shows a rendered report in a Web Part page.

Figure 13.79. Figure 13-79

Now that you have a good understanding of the Report Viewer web part's capabilities, you should be aware of a few limitations with this web part.

First, you cannot have multiple reports in your site that point to different instances of Report Server. This is due to the fact that Reporting Services integration with SharePoint is implemented and configured at the farm level, and the Report Viewer web part and other integration operations simply follow the same model at each site collection level. Second, you cannot group multiple reports into a single instance of a Report Viewer web part. Third, you cannot open a saved report as an attachment to a list item. The Report Viewer web part can only respond to reports that are stored in a document library or are passed in via a connectable web part. Last, but certainly not least, the Report Viewer web part class is sealed and as such is not available for developers to leverage. If you need your own custom Report Viewer web part, you need to code it from scratch or put a wrapper around the Microsoft Report Viewer control.

One of the great features of SharePoint is the Web Part Connection framework, which allows web parts to accept connections from other web parts. In a nutshell, a connection is an association between two web parts that enables them to share data.

As demonstrated throughout this chapter, building Dashboard pages where different types of web parts exist on the same page, each showing different content and data, is an important part of the BI capabilities of SharePoint 2010. In real-world scenarios, these web parts often communicate with each other and are very interactive.

In the previous section, it was mentioned that one of the limitations of the Report Viewer web part is its one-to-one association with a report definition file. A standalone Report Viewer web part is useful when visitors to a page are likely to be interested in a particular report. However, in dashboard-style scenarios, a standalone web part is less likely to be what you really want. You need a web part that's more interactive.

Thankfully, the Report Viewer web part acts as a subscriber in web part connections by implementing the required interfaces, as shown in Figure 13-80. This means that you can make an instance of the Report Viewer web part to communicate with, and get its parameters or even the report definition from another web part on the same page or across pages.

Figure 13.80. Figure 13-80

If you recall from an earlier discussion, one of the key factors to support the "BI for everyone" vision is to allow users access to the most up-to-date data for their day-to-day analysis. The problem is that, in many organizations, direct access to the backend datasources historically has been limited to a number of administrators and a few service accounts. That's mainly because directly accessing raw data without going through the business logic and security layers is not a best practice and can put organizational assets at much higher risk.

Starting with SQL Server 2008 R2, SSRS report data can be rendered as an Atom feed that follows WCF data services conventions. This means that you can get high-quality and refreshable data sourced from pretty much anywhere a report can get data from; whether that data is represented in a tablix, chart, or other form, really doesn't matter!

Once you have found a report with backend data that you are interested in analyzing, you can pull it into your PowerPivot workbook by clicking the new orange Export to Data Feed button on the Report toolbar, as shown in Figure 13-81.

Figure 13.81. Figure 13-81

This will generate an .atomsvc file output and ask you if you want to open it locally.

If you already have an Excel workbook open, you will be prompted to either select an open workbook to add the data feed to, or create a new workbook for the feed.

Next, the Excel client is launched and it goes straight into the PowerPivot tab where the Table Import Wizard pops up.

If you click the Next button, the Table Import Wizard will show you a list of data regions in the report that you can import into your Gemini model and specify table names for. Optionally, you can preview data and select which columns from the data feed to add to your model, as shown in Figure 13-82.

Figure 13.82. Figure 13-82

Now you should be able to consume the data feed and use the Tablix1 data region as a datasource in your PowerPivot workbook.

At the time of writing, SQL Server Reporting Services 2008 R2 is still in November CTP. In this version, the Export To Data Feed option only works for Tablix and Chart and not for Map. This will most likely change when the product is released to manufacturing (RTM).

The Open with Report Builder option in SSRS 2008 R2 has received two major bug fixes. First, if you click the Actions menu and choose Open with Report Builder, Report Builder 3.0 now launches by default if it's installed on the server. This action points to the following URL to instruct Report Builder which report to open:

http://adventureworks.devhorizon.com/_vti_bin/ReportBuilder/ReportBuilder_3_0_0_0
.application?ReportPath=http://adventureworks.devhorizon.com/BICenter/Documents/
SalesByQtrAndProductCat.rdl

Prior to the SSRS 2008 R2 release, Report Builder 1.0 would be launched and it was almost impossible to make SharePoint open the Report Builder 2.0 instead.

A second issue that's been addressed in this release is the opening by Report Builder of published drill-through reports. Previously, this would result in an error by Report Builder, because Report Builder was trying to resolve the action locally. However, in Report Builder 3.0, the action is forwarded to SharePoint for further processing and no action is taken locally.

Several operations that are discussed in this section require that you hard code the credentials in your report's datasource. That's because such operations do not represent a valid windows security context, and they can't access the backend datasources by themselves.

To store credentials in your datasource, browse to the Data Connections library where you published the datasource and just click on it. You will be taken directly to a page like the one shown in Figure 13-84. In this page, there are multiple options, but the one you will want to configure is the third one from the top, where it says Stored Credentials.

Figure 13.84. Figure 13-84

When you specify the stored credentials, there are two options you can select that determine how the stored credentials are authenticated.

In the real world, most of the reports you develop have one or more parameters, so before diving into evaluating other processing options, it makes sense to look at managing report parameters first.

Unlike when you run parameters' reports on-demand, end users won't get a chance to specify parameter values for reports delivered to them behind the scenes. As you saw, you can manage the default values configured for the report parameters when authoring reports in BIDS or Report Builder. You can also manage report parameters after they are published to SharePoint without having to go through the publishing process again.

To manage the parameters of your report, follow these steps:

In this page, you can override the default value for the selected parameter as well as specify how the parameter value should be provided to the report. Available options are:

Reporting Services provides a powerful and easy-to-use caching mechanism that helps you keep a balance between having up-to-date data in your reports and having faster access to the reports.

Of course, caching comes at a cost and can be destructive if used in inappropriate ways. So, before you jump right into the hassle of setting up your report for caching, you need to have a plan. The most important step is to figure out how your design can best utilize caching and what risks you need to be aware of.

When you configure a report for caching, the first time it is requested everything is identical to the on-demand report execution. In fact, the first user who hits the report turns the report into a cached instance and pays the price for everyone else who requests the same instance later on. A cached instance is tied to a combination of parameter values. For example, if you have a parameterized report that has two parameters A and B, then a cached instance of this report with parameter values of A1 and B1 is different from another cached instance that has A2 and B2 as parameter values.

Once the report is turned into a cached instance, it is stored in the Report Server temporary database as an intermediate format image until the cache is invalidated. At this point, if any user requests that report with the same combination of parameter values, the report server retrieves the image from the Report Server temporary database and translates it into a rendering format.

As you may notice, for a report that uses several parameters, there can be multiple cache instances in memory. So, this is something that you may want to consider right up front.

Another thing to consider in your cache planning is the cache refresh plan. The key question you should ask yourself in this step is: How frequently must the cache be invalidated? The answer to this surprisingly simple question reveals a lot about the schedule you need to associate with your cache refresh plan (see "Managing Cache Refresh Plans" later). Remember that, in a transactional database, underlying data may change often; keeping an in-memory representation of data for a long time can lead to inaccurate results and, obviously, wrong decisions.

You don't want to get demoted for just caching a report, right?

Now that you have a plan in place, the final piece of puzzle is the most obvious one, caching the report by following these steps:

The way that you cached your report in the previous section is good, but you could use more control over how the report should be cached. In SSRS 2008 R2, Microsoft introduced cache refresh plans to address this issue.

To create a cache refresh plan, follow these steps:

With the two cache plans properly configured, now you have your report with the following caching policies:

As mentioned previously, caching your report is a great way to give end users a reasonable balance between having current data in the report and having them access reports faster than typical on-demand report execution.

Reporting Services also offers report snapshots that can be used as an alternative approach to caching. In concept, report snapshots and caching are used for a single purpose: delivering reports faster, while lowering on-demand execution costs. Report snapshots can be used for the following two purposes:

Functionality-wise, report snapshots differ from a cached instance in several ways. The first, obvious, difference is that, in caching, you have full control over how often a cached instance should be invalidated (using an expiration schedule or cache refresh plan), but you certainly cannot control when the new cached instance kicks in. This is because cache refreshing depends on when the first request is received after a cached instance expires.

The report caching process lacks the ability to produce a persistent copy of the report from a specific point in time. Report snapshot can be run at a specified time regardless of user requests and can be placed into history without overwriting previous snapshots. Remember that when report execution is persisted, end users will have the ability to compare the report instances at various points in time. This is a very important feature and often a business requirement.

The following steps will walk you through creating a snapshot of your report:

With the snapshot properly configured, on the specified dates an image of the report with the specified parameters is created and stored in the report history. You can see the snapshots taken by selecting View Report History from the same ECB menu.

Figure 13-91 shows the snapshot gallery for your report. As you can see, you can manually create snapshots too, by clicking on the New Snapshot button.

Figure 13.91. Figure 13-91

The schedule you defined in Step 7 is a bit different from the schedule you defined for cache refresh plans (see "Managing Cache Refresh Plans"). This schedule is for data processing, and it's independent from the report processing. The second difference between snapshots and caching is that in report caching you cache the data and report layout together. However, in snapshots, it's the data that can be retrieved in advance and stored as a snapshot, and when the report is actually viewed, everything is put together and returned to the end user. This makes snapshots a more lightweight report-processing option compared to caching.

The third difference is that rendering information is not tied to and stored with the snapshot. Instead, the final viewing format is adjusted based on what is appropriate for a user or an application requesting it. This functionality makes snapshots a much more portable solution. The fourth difference is that report snapshots offer less flexibility than report caching.

Snapshots are like pictures and lack interactivity to an extent. However, a cached report allows users to interact with the reports at the same level as on-demand report execution. For example, snapshots are always taken using the default parameter values (if applicable), and there is no way to change them afterward. This limitation forces you to create a different snapshot if you need to change the report parameters. Recall that, by using cache refresh plans, you can target multiple cached instances of the same report to different sets of parameters.

Figure 13-92 illustrates a snapshot report. Notice how the parameter input pane is disabled.

Figure 13.92. Figure 13-92