Posts

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.

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.

Heat Maps as Reports

Continuing my intrepid journey through the new Reporting Services R2 enhancements, in this blog I’ll demonstrate some of the cool map features. As you’ve probably heard, R2 brings a brand new map control that lets you visualize spatial data on your reports. Since mapping is the one of the major enhancements in R2, there will be plenty of resources to cover it in details. For example, Robert Bruckner has written a great blog to get you started with mapping. The SSRS R2 forum adds more resources.

But what if you don’t need to visualize geospatial data, such as restaurants in the Seattle area? You shouldn’t bother with the map, right? Not so fast. What’s interesting is that the map supports the two spatial data types in SQL Server: geography and geometry. The latter lets you visualize everything that can be plotted on the planar coordinate system. That’s pretty powerful when you get to think about it. If you have the item coordinates, you can map pretty much everything. A few weeks ago, for example, I saw a Microsoft sample map report that plotted floor layout. Today, I’ll show you a report that I whipped out during the weekend that demonstrates how to visualize a heat map.

083009_2119_HeatMapsasR1

The Heat Map Report

A heat map is a graphical representation of data where the values taken by a variable in a two-dimensional map are represented as colors. The most basic variation of a heat map is a tree map that lets you visualize data trends presented as nested rectangles. In our case, the sample report shows the Adventure Works reseller sales. As shown in the screenshot, the larger the rectangle is the more sales were contributed by that reseller. The same can be observed by analyzing the rectangle color which ranges from green (most sales) to red (less sales). The greener the color is, the more sales that reseller has. I chose to use the sales as a color variable although I could have used another measure, such as number of employees, profit, etc. You can download the source code from here.

Limitations

I’ll be quick to point out the following limitations of this reporting solution:

  1. As it stands, producing the report requires a two-step approach. First, you need to prepare the dataset and save it in a SQL Server table. Second, you run the report from the saved data. Unfortunately, the ReportViewer control doesn’t support RDL R2 which means it doesn’t support maps, so you cannot bind the dataset and run the report in one shot. While we are waiting for Microsoft to update ReportViewer, you can use a server-side custom data extension that exposes the ADO.NET dataset with the spatial data as a report dataset. This approach will et you bind the ADO.NET dataset to a server report. Chapter 18 in my book source code includes such an extension.
  2. The performance of the algorithm that I use to calculate the coordinates of the nested rectangles degrades after a few hundred rows in the dataset. If you need to map more results, you may need to revisit the algorithm to see if you can optimize it.

Understanding Rectangle Geometry

SQL Server describes polygons in Well-Known Text (WKT) standard sponsored by Open Geospatial Consortium. For instance, a polygon is described as five (surprise) points on the coordinate system, as shown below. The fifth point is the same as the first point.

083009_2119_HeatMapsasR2

You can convert WKT to the SQL Server geometry data type by using the STPolyFromText method, such as:

geometry::STPolyFromText(‘POLYGON ((0 0, 0 125.6331, 115.0095 125.6331, 115.0095 0, 0 0))’, 0)

Calculating the Coordinates

To calculate the nested rectangle coordinates, I used the excellent Squarified Treemaps algorithm by Jonathan Hodgson. Jonathan explains in details how the algorithm works. My humble contribution added the following changes:

  1. I decoupled the algorithm from his Silverlight solution to a C# console application.
  2. Instead of loading the sample dataset from a XML file, I load it from the Analysis Services Adventure Works cube. Specifically, the MDX query returns the top fifty resellers order by Reseller Sales.
  3. I added Console.WriteLine statements to output insert queries which you can execute to populate the Test table.

CREATE TABLE [dbo].[Test](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [Name] [varchar](50) NULL,

    [Shape] [geometry] NOT NULL,

    [Size] [decimal](18, 5) NULL,

    [Area] AS ([Shape].[STArea]()),

CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED

(

    [ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

insert into test(Shape, Size, Name) values (geometry::STPolyFromText(‘POLYGON ((0 0, 0 125.6331, 115.0095 125.6331, 115.0095 0, 0 0))’, 0), 877107.2, ‘Brakes and Gears’)

insert into test(Shape, Size, Name) values (geometry::STPolyFromText(‘POLYGON ((0 125.6331, 0 247.9348, 115.0095 247.9348, 115.0095 125.6331, 0 125.6331))’, 0), 853849.2, ‘Excellent Riding Supplies’)

Once you execute the INSERT statements to populate the Test table, you can use the SQL Server spatial data visualizer to view the results:

083009_2119_HeatMapsasR3

Authoring the Report

Once you have the data, authoring the report is easy.

  1. Create a new report in Report Builder 3.0 Add a dataset that uses the following query:
    SELECT * FROM Test
  2. Click the Map icon to start the Map wizard.
  3. On the Choose a Source for Spatial Data step, choose the SQL Server Spatial Query option and click Next.
  4. On the Choose the Dataset step, select the Dataset from step 1.
  5. On the Choose Spatial Type, the map wizard should identify the Shape column as a geometry data type. At this point, the pre-release Report Builder 3.0 shows an empty map because it doesn’t recognize the coordinate system as Planar. Don’t worry, we’ll fix this later.
  6. On the Choose Map Visualization, select the Color Analytical Map option.
  7. On the Choose the Analytical Dataset step, select the same dataset because it has a Size column (reseller sales) that we will use for the color visualization.
  8. On the Choose Color Theme step, choose =Sum(Fields!Size.Value) for a field to visualize and Red-Green as a color rule because the resellers with less sales will be coded red. Click Finish to exit the wizard.
  9. In design mode, select the map and change the Viewport CoordinateSystem property to Planar.

083009_2119_HeatMapsasR4

Once you’ve changed the coordinate system, the polygons should magically appear. I’ve made a few more changes but you should be able to understand them by examining the map properties.

During my Reporting Services Tips and Tricks TechEd 2009 presentation, one of my most important tips was to encourage you to write more reports and less custom code. I showed a cool SharePointb-ased dashboard that we built by assembling report views. Following this line of thought, consider report maps instead of writing custom code when you need to visualize spatial data of any kind.

SQL Server 2008 R2 August Community Technology Preview Available

Microsoft released SQL Server 2008 R2 August Community Technology Preview which includes the Report Builder 3.0 R2 August CTP redistributable.

Reports as Data Feeds

Reporting Services SQL Server 2008 R2 features reports as Atom data feeds. This is accomplished with a new Atom Data Feed extension, as shown on this screenshot.

081409_0200_ReportsasDa1

This extension produces an atom service document (an *.atomsvc file). It lists at least one data feed for each data region in a report. In fact, some regions, such as tablix with column groups (cross-tab report layout) may generate multiple feeds.

At this point, you probably imagine subscribing to reports and receiving feeds in Outlook when report data is changed. Unfortunately, you won’t get very far doing so. As it turns out, atom data feeds are different from Atom feeds that most applications, such as IE and Outlooks understand directly. So even if you extract the Url out of the service document and display the .atom rendering of a report in IE, the browser won’t show useful information because it doesn’t understand that the data rows in the data feed are the important part.

So, how are report data feeds useful then? At this point, the design goal for reports as data feeds was to support Gemini – the new Excel 2010 extension that lets end users build in-memory cubes. Gemini can acquire data from variety of data sources, including data feeds and SSRS reports. Here is what it takes to subscribe to a report feed.

081409_0200_ReportsasDa2

  1. In the Gemini add-in, click the From Data Feed button.
  2. On the Connect to Data Feed step of the Table Import Wizard, browse to the data feed file you exported from the report. In this case, I am pointing to the atomsvc file exported from Company Sales 2008 report. On this step, you can set up advanced options, such as timeout, authentication, etc, and test the feed.

    081409_0200_ReportsasDa3

  3. On the Select Tables and Views step, select the regions on the report whose feeds you want to subscribe to. Since Company Sales 2008 has only one region, only one data feed is exposed. You can click the Preview button to preview the feed data. In the preview window, you can select which columns to import and set up a row filter (doesn’t seem to be working in the August CTP).

    081409_0200_ReportsasDa4Reporting Services, Analysis Services, Microsoft Office, SQL Server 2008 R2

  4. On the Import Summary step, preview the feed options and click Finish to import the data.

This imports the feed data into the Excel spreadsheet. From there, you can use Gemini to build the in-memory model and analyze the report data.

Reporting Services Lookup Functions

One new SSRS SQL Server 2008 R2 feature that caught my attention is lookup functions. When reading the Reporting Services discussion list, I come frequently across questions that ask if it is possible to look up a value from a dataset other than the dataset the region is bound to. Excluding getting the first or last value (First and Last functions) or aggregate functions that work on the entire dataset, the only solution was to cache the dataset in a report code-behind code and use custom code to perform the navigation.

R2 will change this by introducing Lookup, LookupSet, and Multilookup functions. The lookup functions are best understood with an example. Take a look at this report (attached) which is based on the Sales Order Detail 2008 report that ships with the Adventure Works sample reports.

081309_0142_ReportingSe1

I added a couple of textboxes to the report. The order details section includes a Sales Person field that displays the sales person’s name. What’s interesting is that the sales person name is retrieved from the SalesOrder dataset that feeds the order header section. Just to address the question that I can almost hear you asking, no, R2 will not support joining datasets at a report level. Instead, I use the Lookup function which performs on-the-fly search, as follows:

=Lookup(Fields!SalesOrderID.Value, Fields!SalesOrderID.Value, Fields!SalesPerson.Value, “SalesOrder”)

I pass the field of the SalesOrderDetail dataset (Fields!SalesOrderID.Value) on which the lookup will be based. I pass the field of the SalesOrder dataset that will be matched. As a third argument, I pass the field (Fields!SalesPerson.Value) of the SalesOrder dataset that I need. Finally, I pass the name of the dataset (“SalesOrder”) that will be searched. The Lookup function is executed for each row in the source dataset.

In the order header section, I display the third item total from the SalesOrderDetails dataset by using the LookupSet function, as follows:

=Code.GetValueByRowNumber(LookupSet(Fields!SalesOrderID.Value, Fields!SalesOrderID.Value, Fields!LineTotal.Value, “SalesOrderDetail”), 2)

You call LookupSet in the same way you call Lookup. The difference is that Lookupset returns a variant array of all values that match. I decided to make the demo more interesting and let you specify the row index needed by adding a simple function to the report code.

Function GetValueByRowNumber(ByVal items As Object(), ByVal pos as Integer) As Object

If items Is Nothing Then

Return Nothing

End if

 

If pos > items.Length -1 Or pos<0

Return Nothing

End If

 

Return items(pos)

End Function

 

The expression passes the variant array returned by LookupSet and the row index to the GetValueByRowNumber. The GetValueByRowNumber returns the field at that row.

The Multilookup function, which I don’t demonstrate, accepts an array of source values to match and returns an array of the matched values that have 1:1 relationship to the source values, such as:

=MultiLookup(Split(Fields!CategoryList.Value,”,”) , Fields!CategoryID.Value, Fields!CategoryName.Value,”Category”))

[View:/CS/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/blog/7532.Sales Order Detail 2008.rdl]

The Dazzling R2 Report Manager

In this and future blogs I’ll cover the most important new features in the forthcoming release of Reporting Services which will be included in SQL Server 2008 R2 (code-named Kilimanjaro or KJ for short). Let’s start with the most obvious new feature: the all new Report Manager UI.

SharePoint-like UI

The moment you open Report Manager, you will notice that significant work was done to make the UI more intuitive, a la SharePoint.

081209_0051_TheDazzling1

For example, similar to SharePoint, a drop-down context menu pops up when you hover on a report item that lets you quickly access relevant tasks.

Simplified UI

The management pages also received facelift. Microsoft has reduced the number of buttons and changed the tab styling so you are sure which tab is selected.

081209_0051_TheDazzling2

Although seemingly minor, I am sure you’d love the new look and feel. For example, I like the fact that I don’t have run a report or switch to Details mode just to get to the report properties.

Report Builder 3.0 ClickOnce

Another welcome enhancement is that opening a report in Report Builder, launches Report Builder 3.0 (more on it in another post) by default. Report Builder 1.0 is still installed but it’s not the default reporting client. You see the writing on the wall. Report Builder 1.0 days are counted and you better embrace the future.

AJAX Report Viewer

Another major enhancement is the debut of an AJAX-enabled ReportViewer control (developers will get the Visual Studio counterpart in Beta 2 of Visual Studio 2010). The control eliminates visible page reposts when interactive actions, such as drilldown, are initiated. The page fades out but it stays in place and a spinny lets the user know that an interactive action is taking place.

081209_0051_TheDazzling3

All in all, end users and administrators will enjoy the new Report Manager. You won’t have to switch to SharePoint integration mode just to get a SharePoint-like UI.

New Reporting Services Features in SQL Server 2008 R2 CTP2

It turned out that for time being the CTP2 of SQL Server 2008 R2 which I announced yesterday is available for TAP participants only. It will be publicly available on Wednesday. Meanwhile, Robert Bruckner listed the new Reporting Services features which got implemented in CTP2. The biggy is the new map region that lets you visualize geospatial and geometry data.

In my first book I had a sample that demonstrated how to export a report as a data feed. Well, in R2 this will be natively supported. Reports as data feeds, anyone? Visual Studio 2010 Beta 2 will include an AJAX Report Viewer. I would hope for a Silverlight version but one step at a time.

Again, post your KJ-related question to the Reporting Services forum.

SQL Server 2008 R2 CTP Available

Microsoft just pushed CTP2 of SQL Server 2008 R2 build #1092.20 (code-named Kilimanjaro).