SQL Server 2008 February CTP Is Out

February CTP is out. It brings the much anticipated integration with Visual Studio 2008 and the new BIDS Report Designer.

Download the February CTP here:

Updated Books Online for February CTP are available here:

What’s new in Reporting Services February CTP (compared to the previous November 2007 CTP):

  • Support for Microsoft Word Rendering
    This allows users to render reports as Word documents that are compatible with Microsoft Word 2000 and greater.
  • Data Visualization Enhancements
    Data Visualization Enhancements provides significantly improved support for Chart and adds support for Gauge controls directly within reports.
  • SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies
    The SQL Server 2008 Reporting Services Add-in for SharePoint Technologies allows you to take advantage of SQL Server 2008 report rendering, processing and management capabilities in SharePoint Integrated mode.  This version includes Data-driven subscriptions.
  • Report Design Enhancements in Business Intelligence Development Studio (BIDS)
    BIDS overall has been migrated from a Visual Studio 2005 to Visual Studio 2008 platform.  In addition, the report design tools found in BIDS were significantly updated for Reporting Services.  Updates include:
    – the more powerful Tablix based Tables, Lists, Matrices
    – the Report Data window for easier interaction with data fields
    – the integration of data visualization enhancements (Chart, Gauge)
    – new dialogs throughout the design experience
  • Throughput Enhancements
    Further improvements in report rendering throughput, the number of reports rendered in a given amount of time for several types of workloads.

Last Non Empty Affairs

Enterprise Edition of Analysis Services 2005 and 2008 supports semi-additive functions, such as LastNonEmpty, LastChild, and so on, to facilitate working with semi-additive measures, such as account balances and inventory snapshots. For example, the screenshot below shows LastNonEmtpy in action. To demonstrate this, I added a Last Sales measure to the Internet Sales measure group in the Adventure Works cube and set its AggregationFunction property to LastNonEmpty. Of course, this doesn’t make much sense because sales amounts are fully additive but it’s OK for demo purposes.


As expected, the quarter and year totals show the last non empty sales. The customer totals are correct too because semi-additive measures sum across any dimension except Time. But what about the grand total of $14.98? Shouldn’t we get the sum of the quarter (or year) subtotals?

To answer this question, we need to understand how LastNonEmpty operates. LastNonEmtpy works across time and not across other dimensions. Because the last customer recorded sales are in June 2008, LastNonEmpty gives you Q2 total of $14.98 which becomes the YTD total as well. So, does it make sense to sum the totals vertically? The philosophical answer is “it depends”. If this was inventory balances, the question is why we have missing balances in the last day. In other words, to get the vertical sum right we need to record balances for each product for each period. One may say that in this case LastNonEmpty is correct because it makes no assumptions about what happened to the “missing” values for products, customers, etc. It could be that we didn’t record them or it could be that there we discontinued that their ending balance is indeed not applicable.

But, there could be cases when we need LastNonEmpty to sum vertically instead of getting the last values. For example, the cube may have test scores and you may need to get the last score, such as to calculate the average student or school score. To address this requirement, our first impulse could be to use a scope assignment that overwrites the (Root(Customer), Root(Date)) tuple to server SUM aggregate function (the one you specify in the AggregateFunction property). But, as it stands, SSAS doesn’t support plugging in server aggregate functions in scope assignments.

So, we will try the reverse approach:

  1. Add a new fully-additive measure Sales with SUM aggregate function.
  2. Overwrite the Sum with LastNonEmpty at the customer level.
  3. Hide Last Sales and use Sales instead.




[Customer].[Customer].[Customer].Members, /*or Leaves(Customer)*/





this = [Measures].[Last Sales]; /*assign Last Sales to Sales to make it semi-additive at customer level*/

End Scope;

Note that [Customer].[Customer].[Customer]. Members exclude the Customer All member. Since the server uses the All member to aggregate up, aggregating at a higher level, such by product will work fine because All member will contain the SUM of the last sales.

It will be great if a future SSAS release supports:

  1. Using server aggregate functions in scope assignments
  2. Give the modeler an option to control the behavior of the semi-additive measures.