Sparklines and Data Bars

Besides indicators, R2 adds sparklines and data bars to the arsenal of data visualization features. Sparklines are small graphics embedded in a context of words, numbers, and images. They help end users visualize simple trends. You could implement sparklines and simple data bars in the previous versions of Reporting Services but you had to go through many steps to strip down the chart or gauge regions. R2 makes the process much simpler. Let’s see what it takes to author a sparkline report with R2 that is similar to the Sparklines report included in my book source code. [View:https://prologika.com/CS/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/blog/6175.Sparklines-R2.rdl]

112009_1322_Sparklinesa1

This report helps the user visualize the performance of the Adventure Works sales persons. For each person, the report shows the employee’s trend based on the reseller sales made over four quarters and his performance ratio which is defined as the actual sales made by the employee divided by his sales quota. The report gets the data from the Adventure Works 2008 Analysis Services cube.

Configuring Sparklines

In Reporting Services, a sparkline is a scaled-down chart region. Configuring a sparkline takes a few simple steps.

1. Right-click on a tablix cell and click Insert ð Sparkline.

2. In the Select Sparkline Type dialog box that follows, select the Line sparkline type.

112009_1322_Sparklinesa2

 

3.  Double-click the sparkline region to open the Chart Data panel which is another new design feature in R2.

112009_1322_Sparklinesa3

4.  Click the plus sign in the Values pane and select the Reseller_Sales_Amount to configure the sparkline series.

5.  Click the plus sign in the Category Groups pane to add the Category_Quarter_of_Year field to configure the category groups.

That’s it! I’ve made a few more minor adjustments to finalize the sparkline, such as removing the chart border, setting the chart background for the “green bar” effect and increasing the line width.

Configuring Data Bars

To demonstrate data bars, I implemented the employee’s performance ratio using a data bar region. Similar to a sparkline, a data bar is a simplified chart.

  1. Right-click on a cell and click Insert ð Data Bar. I actually, configured the data bar outside tablix first by embedding it inside the rectangle and copying and pasting it inside the tablix cell. I did that to make the bar narrower so it doesn’t occupy the entire cell height.
  2. On the Select Data Bar Type, leave the default Bar type selected.

112009_1322_Sparklinesa4

3.  In the Chart Data panel, click the plus sign in the Value pane to add the PerformanceRatio field and set its aggregation function to Avg.

112009_1322_Sparklinesa5

That’s pretty much it to set up the initial data bar although I had to go through a few more steps to configure the bar labels and appearance.

Although not revolutionary features, Indicators, sparklines and data bars will save you time when you need to enhance your report with simple graphics. Sean Boon from the Reporting Services team has written an interesting blog post that demonstrates more sparkline features to implement a win-loss sparkline.

Indicators

Often, reports need to show images that represent discrete values, such as an image that shows a KPI status. Previously, you didn’t have another option but to use images that are dynamically changed based on the field value. Indicators, a new R2 feature, make this much simpler. In Reporting Services, an indicator is a simplified gauge region that lets you associate images with states. Although you can use indicators as stand-alone regions, you would typically nest them in a tablix, as the following report [View:https://prologika.com/CS/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/blog/8712.IndicatorDemo.rdl] demonstrates. The right-most column of this report shows the Gross Margin KPI status as an indicator.

111909_0245_Indicators1

Authoring this report takes a few simple steps:

  1. Add a dataset that requests the Status property of the Financial Gross Profit Margin KPI from the Adventure Works 2008 Analysis Services cube grouped by Year and Quarter.
  2. Use a tablix region to show the KPI status with Year and Quarter row groups.
  3. Right-click the cell in an empty column and click Insert ð Indicator.
  4. In the Select Indicator Type dialog that follows, select the indicator type. In this case, I chose the default directional type.

111909_0245_Indicators2

  1. Once the indicator region is added to tablix, right-click on it and click Indicator Properties. In the Indicator Properties dialog box, select the Value and States tab.
  2. Since in this case I display numeric values and not percentages, change the States Measurement Unit drop-down list to Numeric.

     

    By default, the default directional indicator lets you defined three states, as shown below. However, you can add or delete states and use custom images if needed. In my case, I defined a range from -1 to 0.-99 for underperformance, 0 – 0.99 as acceptable performance, and 1 as good performance.

     

    111909_0245_Indicators3

     

    Indicators are simple but very useful enhancement to Reporting Services. They lets you visualize discrete values on reports.

Pagination and Rendering Enhancements

R2 brings more control over pagination and rendering. A question that pops every now and then on the discussion list is how to name Excel worksheets for each instance of a row group. This scenario wasn’t supported before R2. In R2, there are addition page break options, as shown in the screenshot below. One of them is PageName.

111709_1350_Paginationa1

In this case, I set the PageName property of the ProductCategory row group to the Category field. Then, I set up the BreakLocation of the ProductCategory row group to break when the product category changes. The screenshot below shows what the exported to Excel report looks like. As you can see, each worksheet is now named. If the row group spills on the next page, a new worksheet is added with the same name.

111709_1350_Paginationa2

Another welcome enhancement is the ability to reset page numbering. Suppose you have a master report that includes a subreport and you want to reset the page numbering before the subreport is rendered. This is another commonly requested scenario which wasn’t supported before R2. In the screenshot below, I have a report package that includes two subreports. I want to reset the page number after the first subreport renders. Since the subreport region doesn’t have page break properties, I enclosed the first subreport in a rectangle and set the rectangle PageBreak ð BreakLocation to End and PageBreak ð ResetPageNumber to True. In the page footer, I added Globals!PageNumber of Globals!OverallTotalPages. Since you can reset the page number, the RS team has added also OverallPageNumber and OverallTotalPages built-in fields to the Globals collection. Given this setup, the report will increase the page number for each page in the Company Sales 2008 report. Once Company Sales 2008 has rendered, the server will reset the page number but it will show the overall number of pages.

111709_1350_Paginationa3

Notice that there is also a Disabled page break property. Suppose that when exported to other formats you want your report to break on each row group instance but when exported to Excel you want the entire report to export to a single worksheet. R2 has added two additional built-in fields to the Globals collection, RenderFormat.Name and RenderFormat.IsInteractive, that lets you support such scenarios. In this case, I had to define the following expression for the PageBreak ð Disabled property:

=Iif(Globals!RenderFormat.Name=“EXCEL”, True, False)

If the user exports the report to Excel, the expression turns on the Disabled property and no page breaks are generated. However, if the user exports the report to another format, the report breaks on each instance of the row group.

Finally, to round up the rendering enhancements, you have now the option to rotate text 270 degrees as shown in the report below. This can be easily achieved by setting the textbox WritingMode property to Rotate270. You probably would want to set also TextAlign to Center and VerticalAlign to Middle.

111709_1350_Paginationa4

Shared Datasets

When the Reporting Service team asked my opinion about shared datasets, a new feature in the forthcoming SQL Server Reporting Services R2, I was somewhat skeptical. I preferred them to focus on more important in my mind features, such as the ability to join datasets at report level. But the more I look at the way shared datasets got implemented, the more real-life scenarios I think may benefit from this enhancement.

Think of a shared dataset a hybrid between a shared data source and report execution. Similar to a shared data source, a shared dataset is a report dataset that can be managed independently and shared among reports. A shared dataset must use a shared data source. The shared datasets can be parameterized and reports that use it can pass parameters to it. Similar to report executions, a shared dataset can also be cached and refreshed on a schedule. In my opinion, there are two main scenarios where shared datasets can be useful:

  • Easier maintenance – Suppose you have a dataset that you can re-use across reports, such as a dataset that populates the parameter available values. You want all reports to reuse the dataset and pick up the dataset query changes automatically.
  • Improved report performance – You may have query that takes very long to execute. You want to execute the query in an unattended mode, such as outside working hours, perhaps for different set of parameters, and cache the dataset for a specific duration.

Creating Shared Datasets

Creating a shared dataset is easy. Both BIDS Report Designer and Report Builder 3.0 are capable of creating shared datasets. In Report Designer, you right-click on the new Shared Datasets folder in Solution Explorer, and click Add New Dataset. In Report Builder 3.0, you click the Create Dataset option on the main Create Report or Dataset popup screen.

You can easily convert a report-specific dataset to a shared dataset. Just right-click any dataset in Report Data Window and click Convert to Shared Dataset. Consequently, Report Designer will re-factor the dataset as a shared and it will add its definition to as an *.rsd file to the Shared Datasets folder in Solution Explorer. In the screenshot below, I have converted the EmployeeSalesDetails dataset in the Employee Sales Summary 2008 report to a shared dataset. Notice that the EmployeeSalesDetails dataset reference inside the report has a special icon.

111609_1413_SharedDatas1

As I mentioned in a previous post, the project properties dialog get enhanced to support new deployment settings that are specific to shared datasets. Specifically, the OverwriteDatasets setting specifies if the shared dataset definition will overwritten on deployment if it exists and the TargetDatasetFolder specified in which folder the dataset definitions will be deployed to.

Once the dataset is configured as shared, you can set up its properties, which include the dataset query, fields, and filters. Then, you can configure the dataset reference inside the report to pass parameters to the shared dataset or to have its own filters. This is similar to how to you configure a subreport. What if you want to use row-level security and you need to pass the user identify to the dataset query (User!UserID)? Just add a query parameter to the shared dataset, such as LoginID, and configure the dataset reference inside the report to pass User!UserID as a parameter to the shared dataset.

111609_1413_SharedDatas2

Managing Shared Datasets

Once the shared dataset is deployed, it can be managed just like any other published item using Report Manager or SharePoint. Common management tasks include setting the data source reference, caching, and security. The most interesting of these is caching. Just like report executions, you can cache a shared dataset. When a shared dataset is configured for caching, the report server will cache a dataset copy for each parameter combination. The same restrictions apply as with caching report executions. Specifically, the data source cannot use Windows Security or Prompt for Credentials authentication options. You can configure cache expiration options. For example, if data latency of 30 minutes is acceptable, you can configure the dataset cache to expire in 30 minutes.

111609_1413_SharedDatas3

Let’s say you a dataset query that takes very long to execute. You can set up a cache refresh plan to warm up the dataset cache on a set schedule. Cache refresh plans are also a new R2 feature. Previously, you pre-execute reports by creating a subscription using the NULL delivery provider. Moving to R2, you don’t need to use the NULL provider anymore and you can refresh shared datasets independently from reports. In the process of setting up a cache refresh plan, you need to specify an item-specific or shared schedule and default values for each parameter, just like you would do with snapshot caching or subscriptions.

111609_1413_SharedDatas4

As you can see, shared datasets can help you implement some interesting scenarios when you need to reduce the management effort or improve the report performance.

Not All Calculated Members Are Born Equal

An interesting issue popped up yesterday regarding the calculated member syntax. I had to multiply negative amounts in a financial cube for certain account categories that had negative amounts. The cube also had a Many:Many relationship between financial accounts and account groups which may be related to the issue. So, I had the following scope assignment:

CREATE
MEMBER
CURRENTCUBE.[Multiplier] /* Old Style*/


AS [Financial Account].[Financial Account Hierarchy].CurrentMember.Properties(“Multiplier”, TYPED),

VISIBLE = 0;

Scope

(

[Financial Account].[Financial Account Hierarchy].Members

);


this = [Financial Account].[Financial Account Hierarchy].CurrentMember * [Measures].[Multiplier];

End
Scope;

The Multiplier calculated member returns the Multiplier property of the current account member, which could be 1 or -1 (if the account has to be negated). Then, the scope assignment overwrites all measures for the current member. As simple as it is, the assignment didn’t work and the account amounts didn’t get converted although the Multiplier calculated member would return the correct number. I stripped out the entire cube script to only these two script definitions but I couldn’t make it to work. After some research, I came to this interesting discussion between Chris Webb and Read Jacobsen, which made me think that the issue probably has something to do with the solve order of the calculated member. As it turned out, the new style has a higher pass so the following fixed the issue:

CREATE
HIDDEN [Multiplier] = [Financial Account].[Financial Account Hierarchy].CurrentMember.Properties(“Multiplier”, TYPED); /* New Style*/

As Chris suggested, I tend to believe that it’s a bug especially given the fact that I’ve recently logged another nasty calculated member-related issue, where scope assignments would produce wrong results if the calculated members with the old style definitions are hidden.

Aggregates of Aggregates

Aggregates of aggregates is a new feature in SQL Server R2 Reporting Services. It can be better understood by the sample Aggregates.rdl report ([View:https://prologika.com/CS/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/blog/2474.aggregates.rdl]). It shows sales grouped by year and quarter. The Year group subtotals use the Sum aggregation function to sum the quarter sales. The report footer total averages the year subtotals.

111209_0151_Aggregateso1

Prior to R2, authoring such a report would require dividing the total sales by the number of quarters, which you can obtain by using the CountRows function. You cannot just use the Avg function in the footer as the Bad Sales measure shows:

=Avg(Fields!Internet_Sales_Amount.Value)

If you do so, the Avg function would operate on the detail rows (all quarter rows) and it would produce 2,258,360. R2 lets you nest aggregate functions. The Sales footer total uses the following expression:

=Avg(Sum(Fields!Internet_Sales_Amount.Value, “Year”))

In this case, the Average function operates over the aggregated year subtotals. Note that not all aggregate functions can be nested. For example, you cannot define an aggregate over the Aggregate, RunningValue and Previous functions.

Where is the SSAS 2008 Book?

Readers have been asking about a revised edition of my Applied Microsoft Analysis Services book for version 2008. First of all, I’d like to thank all my readers who bought and liked the book. This book has been a great success and it really exceeded my expectations in any way! It has great reviews on Amazon and it has sold over 10,000 copies to date, which is not a bad sales record for a technical book. I am still supporting my readers on the book discussion list and more than likely will continue doing so for foreseeable future. At the same time, I decided not to write a revision for SSAS 2008 or R2 for two main reasons:

  1. I just need a break from book writing. It took me several months to write the Applied Microsoft SQL Server 2008 Reporting Services and BI toolkit. I am not ready to start a new book and … face a divorce. Seriously, writing books is not easy and it takes a lot of time away from the family. And I just can’t write small.
  2. Version 2008 of Analysis Services has been an incremental and stabilization release. The theme was “don’t rock the boat” since the boat was rocked big time in SSAS 2005, which was essentially a re-write of Analysis Services from ground up. The major 2008 enhancements were in the area of performance and manageability. So, hardly enough material to write a revision from a feature standpoint although I definitely have plenty of real-life experience to contribute. So, Applied Microsoft Analysis Services 2005 is still not outdated and I hope you won’t go wrong reading it. Speaking of this book, since it looks like the demand for it waned in the past months (everyone jumped on the 2008 bandwagon, and this is what you should do) I’m not planning a new print. Instead, I lowered the price of the e-book version to $29.95, so the book is still available in e-book format if you decide to buy it.

Although I am not planning a new SSAS book anytime soon, I am committed to Analysis Services, which I like very much and use heavily in real-life projects. Looking forward, I am planning a new SSAS revision for SQL Server 11, which is next major release of SQL Server after R2. Meanwhile, I offer an Analysis Services 2008 training class if you are looking for instructor-led online training.

What Analysis Services 2008 books would I recommend?

Novice User

  1. Professional Microsoft SQL Server Analysis Services 2008 with MDX – written by members of the SSAS team
  2. Microsoft SQL Server 2008 Analysis Services Step by Step – a good introduction to Analysis Services
  3. Microsoft SQL Server 2008 MDX Step by Step – I’ve heard good things about this book from my co-workers who used it to learn MDX basics
  4. MCTS Self-Paced Training Kit (Exam 70-448): Microsoft SQL Server 2008 Business Intelligence Development and Maintenance – if you plan to take the 70-448 BI exam, we wrote this book for you. My contribution was the SSAS chapters.

Experienced User

  1. Expert Cube Development with Microsoft SQL Server 2008 Analysis Services – You won’t go wrong with the fellow MVPs: Marco Russo, Alberto Ferrari, and Chris Webb. I reviewed this book here.
  2. Microsoft SQL Server 2008 Analysis Services – if you really want to get under the hood, you won’t get disappointed by the folks who implemented Analysis Services
  3. MDX Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase – Bring your MDX knowledge to the next level.

Speaking of books and writing, I have an exciting announcement which I’ll make soon in another blog. Stay tuned…

BIDS 2008 and R2 Project Support

Continuing my intrepid journey in the SQL Server 2008 R2 land, I will discuss the new features of the recently-released CTP3. In this blog, I’ll talk about the first new feature that caught my attention – BIDS support for 2008 and R2 projects.

After hearing the outcry from many developers about project compatibility, in R2 Microsoft has decided to support both SQL Server 2008 and R2 report server projects. Similar to Analysis Services, Reporting Services now lets you target a server version in the project deployment settings. There is even a cool Detect Version link that would detect the SQL Server version based on the TagetServerURL setting.

111009_1959_BIDS2008and1

So, you can have a project that targets either edition. What happens when you use R2-specific features, such as the map control, but set the TargetServerVersion to SQL Server 2008?

The map, Map1, was removed from the report. SQL Server 2008 Reporting Services does not support map report items.

I hope this message got improved before R2 ships because BIDS doesn’t actually remove the map control from the report; you just get a build error.

You should know that as with previous versions, you can deploy older RDLs to the server so you don’t have to upgrade your report definitions to R2. For instance, you can deploy RDL 2005 and the server will render the report via the compatibility interfaces without upgrading it to R2 RDL. Notice also that now we have more deployment settings, such as TargetDatasetFolder and TargetReportFolder but that’s a subject for another blog.

SQL Server 2008 R2 November CTP (CTP3)

Microsoft announced release on SQL Server 2008 R2 November CTP (CTP3) with a build number of #1352.12. MSDN, TechNet Subscribers and TAP customers can download the SQL Server 2008 R2 November CTP today. Non-subscribers will be able to access the CTP on Nov. 11th. This should be a feature-complete build that includes all SQL Server R2 functionality.

Reporting Services

The new Reporting Services features include:

SharePoint Integration

SharePoint Integration has several new features that include the following:

  • Support for multiple SharePoint Zones.
  • Support for the SharePoint Universal Logging service.
  • A new data extension.
  • A query designer for SharePoint Lists as a data source.
  • Support for right-to-left text with Arabic and Hebrew in the SharePoint user interface.
  • In a report, the data extension supports include data from SharePoint lists for SharePoint Foundation 2010, SharePoint Server 2010, Windows SharePoint Services 3.0, and Office SharePoint Server 2007.
  • Report Parts.

Shared Datasets and Cache Refresh Plans

Shared datasets are a new type of report server item that can retrieve data from shared data sources that connect to external data sources.

Cache refresh plans let you cache reports or shared dataset query results on first use or from a schedule.

New Data Visualization Report Items

The November CTP introduces several new report items that depict data:

  • Sparklines and data bars are simple charts that convey a lot of information in a little space. These are often used in tables and matrices. Their impact comes from viewing many of them together and being able to quickly compare them, instead of viewing them singly.
  • Indicators are minimal gauges that convey the state of a single data values at glance.

Enhancements to Reporting Services Features

The November CTP provides the following enhancements to Reporting Services features that were released in earlier SQL Server 2008 R2 CTPs or SQL Server 2008:

  • Configuring a Map Layer—The Map wizard displays data from both the spatial data source and the analytical data source to help you choose the field on which to match.
  • Calculating Aggregates of Aggregates—You can create expressions that calculate an aggregate of an aggregate.
  • Enhanced Support for Expressions—New globals and a new property for report variables provide support for overall page numbering, naming pages, and information that is specified in the renderer.
  • Rotating Text 270 Degrees—Text boxes can now be rotated 270 degrees.
  • Report Pagination—Page breaks on tablix data regions (table, matrix, and list), groups, and rectangles give you better control of report pagination.
  • Naming Excel worksheet tabs—You can set properties on the report to name worksheet tab when you export the report to Excel.

Business Intelligence Development Studio Support for
SQL Server 2008 Reports and Report Server projects

Business Intelligence Development Studio supports working with both SQL Server 2008 and SQL Server 2008 R2 reports, and with Report Server projects in the SQL Server 2008 R2 version of Business Intelligence Development Studio.

New Web Service Endpoint

The report server includes a new management endpoint named ReportService2010.ReportingService2010. This endpoint merges the functionalities of both the ReportService2005.ReportingService2005 and ReportService2006.ReportingService2006 endpoints, and can support management operations of the report server in both native mode and SharePoint integrated mode. It also includes new features that are introduced in SQL Server 2008 R2, such as shared datasets and cache refresh.

Analysis Services

The Analysis Services new features are centered around Gemini and include:

PowerPivot for Excel

PowerPivot Field List Enhancements

Numerous enhancements have been added to the PowerPivot field list. These include the following:

  • Automatic detection of relationships when columns from unrelated tables are used in a PivotTable.
  • Support for searching fields by name.
  • Support for named sets.

PowerPivot Data Source Editing

Support is available for the following data source editing tasks:

  • Refreshing data for table, for all tables from a data source, or for all the tables in the PowerPivot window.
  • Editing data source information such as server name, database name, and credentials.
  • Editing table settings such as data source table or query used, columns imported, and filters.

Support for More Types of Data Sources

Targeted support has been introduced for importing data from the following sources:

  • Microsoft Access 2003, 2007, 2010
  • Microsoft SQL Server 2005, 2008, 2008 R2 Analysis Services
  • PowerPivot workbooks published to Analysis Services running in SharePoint Integrated Mode
  • Text files
  • Excel 97-2003, 2007, 2010
  • Microsoft SQL Azure
  • Oracle 9i, 10g, 11g
  • Teradata V2R6, V12
  • Sybase
  • Informix
  • IBM DB2 relational databases 8.1

Enhanced Data Analysis eXpressions (DAX)

DAX functionality has been significantly expanded in the following ways:

  • User interface enhancements include better propagation of errors and easier creation of DAX formulas.
  • Several functions have been added, including a set of functions that operate on textual data, as well as those that provide common Date and Time manipulations.
  • Several performance and functional capabilities have been introduced, including automatic recalculation.
  • Support for handling referential integrity violations between related tables has been introduced.
  • Automatic selection of data types for expressions is available.
  • For more information, see the topic “Data Analysis Expression Language Reference” in SQL Server 2008 R2 Books Online.

DAX is also documented in the online Help of the PowerPivot for Excel add-in. For more information, see the Help file that is installed with the add-in.

PowerPivot for SharePoint

Management Dashboard

A management dashboard for IT has been introduced. This dashboard provides visibility into PowerPivot use on a SharePoint farm that includes information about the following areas, as well as a rich PowerPivot workbook that can be used to build custom reports:

  • Published PowerPivot workbooks, including number of queries executed, number of users and size per workbook in the dashboard.
  • Hardware resource utilization for the PowerPivot service, including CPU and memory, is reported on a daily basis to the dashboard.
  • Data refresh activity is reported for all PowerPoint workbooks that are accessed through the same PowerPivot service application.

For more information about this feature, see the topic “PowerPivot Management Dashboard” in SQL Server 2008 R2 Books Online.

Enhanced Infrastructure

The following enhancements to infrastructure are available:

  • Claims-aware support to enable a user’s identity to flow between SharePoint components in a secure manner.
  • Parallelized refresh of PowerPivot data.
  • Performance optimizations.

The Aggregate Function

Reporting Services supports an Aggregate function to retrieve aggregated values directly from the data provider if the provider supports this feature. The Aggregate function is especially useful with retrieving data from Analysis Services because it may be difficult if not impossible to recreate aggregated values on the report, such as when the cube script overwrites the server aggregates.

It’s easy to use the Aggregate function. Just replace the default Sum() aggregate function with Aggregate() in the group subtotals. When you do this, the Analysis Services provider brings in additional rows that include the server aggregates which the report displays in the group subtotals.

Recently, I came across a report that would return the following exception after attempting to use Aggregate().

The Value expression for the text box ”<textbox.’ uses Aggregate with an invalid scope parameter.

I couldn’t find anything wrong with the way Aggregate() is used because it was in the right place on a crosstab report. Robert Bruckner from the Reporting Services team helped me understand the Aggregate() function limitations:

The Aggregate function can only be used if the current scope and all parent group scopes are all based on simple field references (e.g. =Fields!Accounts.Value), or constants. The Aggregate function also cannot be used in detail rows.

In my case, the report was using a parent-child Analysis Services hierarchy. To show the data recursively, the tablix Details group was set to group on =Fields!Account.UniqueName. This is an extended property that the SSAS provider supports and it’s not a simple reference. Changing the grouping to =Fields!Account.Value resolved the issue.