Prologika Forums

Maintaining State in Reporting Services 2008

tlachev — Tue, 30 Jun 2009 13:10:21 GMT

Sometimes, more advanced reporting needs may require maintaining state using custom code. Recently I had to implement a rather involved report consisting of two sections: a detail section that used a recursive sum, Sum(Fields!SomeField.Value, , True), and a summary section with additional calculations which would reference some of the aggregated values in the detail section. Since Reporting Services is not Excel, you cannot reference arbitrary fields on a report. So, I had to cache the values from the detail section in a hashtable using custom code so the summary section could obtain these values when needed. The following embedded code gets the job done:

Friend Shared _hashtable As New System.Collections.Hashtable()

Function GetKey(ByVal id as Long, ByVal column as String) as String

return Report.User!UserID & id & column

End Function

Function AddValue(ByVal id as Long, ByVal column as String, ByVal value As Object) As Object

Dim key as String = GetKey(id, column)

If _hashtable.ContainsKey(key)=False Then

_hashtable.Add(key, value)

End If

Return value

End Function

Function GetValue(ByVal id as Long, ByVal column as String) As Object

Dim key as String = GetKey(id, column)

If _hashtable.ContainsKey(key) Then

Return _hashtable(key)

End If

Return Nothing

End Function

Protected Overrides Sub OnInit()

Dim keys As String() = New String(_hashtable.Keys.Count - 1) {}

_hashtable.Keys.CopyTo(keys, 0)

For Each key as String In keys

If key.StartsWith(Report.User!UserID) Then

_hashtable.Remove(key)

End if

Next key

End Sub

In my scenario, tablix cells in the detail section would call the AddValue function to cache the dataset field values. Then, cells in the summary section would call GetValue to obtain these cached values. This technique is not new and you've probably used it to solve similar challenges.

What's new is that moving to Reporting Services 2008 you may need to revisit your custom code and make changes. First, in prior version of Reporting Services, you probably declared the hashtable variable as an instance variable and that worked just fine. However, Reporting Services 2008 introduced a new on-demand processing engine where each page is processed separately. Consequently, you may find that the report "loses" values. Specifically, if the report fits on one page, everything will work as expected. But if the report spills to more pages, the hashtable collection will lose the values loaded on the first page. The solution is to declare the hashtable object as a static (Shared in Visual Basic) object so it survives paging.

Friend Shared _hashtable As New System.Collections.Hashtable()

But because a static object is shared by all users running the report, you need to make the hashtable key user-specific, such as by using the User!UserID variable which returns the user identity.

Function GetKey(ByVal id as Long, ByVal column as String) as String

return Report.User!UserID & id & column

End Function

Are we done? Well, there is a nasty gotcha that you need to be aware of. If the report takes parameters, you will surprised to find out that the custom code returns the cached values from the first report run and changing the parameters (report data) doesn't change the values in these cells that call the GetValue function. To make the things even more confusing, testing the report in the BIDS Report Designer or Report Builder 2.0 will work just fine. But you will get the above issue after you deploy and run the server report, such as when you request the report in Report Manager or SharePoint.

What's going on? As it turns out, the hashtable object survives report requests as a result of parameter changes. Consequently, the code that checks if the hashtable key exists will find the key when the report is reposted and it will not add the new value.

If _hashtable.ContainsKey(key)=False Then

_hashtable.Add(key, value)

End If

The solution is to clear the user-specific hashtable items each time the report is run. This takes place in the OnInit method. The OnInit method is a special method which gets executed before the report is processed. You can use this method to do initialization tasks or, in this case, clear state held in global variables.

Are we there yet? Almost. As the reader samsonfr pointed out in a blog comment, we'll need to make this code thread-safe because the chances are that multiple users may be running the report at the same time so concurrent threads may need to write and read to/from the static hashtable variable at the same time. As the Hashtable documentation explains "Hashtable is thread safe for use by multiple reader threads and a single writing thread. It is thread safe for multi-thread use when only one of the threads perform write (update) operations, which allows for lock-free reads provided that the writers are serialized to the Hashtable. To support multiple writers all operations on the Hashtable must be done through the wrapper returned by the Synchronized method, provided that there are no threads reading the Hashtable object. Enumerating through a collection is intrinsically not a thread safe procedure. Even when a collection is synchronized, other threads can still modify the collection, which causes the enumerator to throw an exception. To guarantee thread safety during enumeration, you can either lock the collection during the entire enumeration or catch the exceptions resulting from changes made by other threads."

Although the code adds and removes user-specific values only (the collection key uses User!UserID), Robert Bruckner from the SSRS dev team clarified

"Everytime you have code like this in a multi-threaded environment, there is potential for a race-condition:

if (!_hashtable.ContainsKey("abc")) _hashtable.Add(...)

In this case you have to take a lock to ensure ContainsKey and Add are run as an atomic piece of code."

Keeping this in mind, my proposal for making the code thread-safe code follows (concurrent experts, please comment if you find anything substantially wrong with my attempt to make a hashtable access thread-safe).

Friend Shared _hashtable As New System.Collections.Hashtable()

Dim _sht as System.Collections.Hashtable = System.Collections.Hashtable.Synchronized(_hashtable)

Function GetKey(ByVal id as Long, ByVal column as String) as String

return Report.User!UserID & id & column

End Function

Function AddValue(ByVal id as Long, ByVal column as String, ByVal value As Object) As Object

If id = -1 or id = -2 or id=-3 Then

Dim key as String = GetKey(id, column)

If _sht.ContainsKey(key)=False Then

_sht.Add(key, value)

End If

Return value

End Function

Function GetValue(ByVal id as Long, ByVal column as String) As Object

Dim key as String = GetKey(id, column)

If _sht.ContainsKey(key) Then

Return _sht(key)

End If

Return Nothing

End Function

Protected Overrides Sub OnInit()

SyncLock _hashtable.SyncRoot

Dim keys As String() = New String(_hashtable.Keys.Count - 1) {}

_hashtable.Keys.CopyTo(keys, 0)

For Each key as String In keys

If key.StartsWith(Report.User!UserID) Then

_hashtable.Remove(key)

End if

Next key

End SyncLock

End Sub

Suppressing Auto-generation of MDX Parameter Datasets

tlachev — Mon, 08 Jun 2009 22:13:49 GMT

There is an unfortunate issue with the MDX Query Designer in SSRS 2008 Report Designer and Report Builder 2.0 where changing the main dataset overwrites the parameter datasets. This is a rather annoying issue because often you need to make manual changes to the parameter datasets. However, when you make a change to the main dataset, the MDX Query Designer wipes out the manual changes and auto-generates the parameter databases from scratch. According to the feedback I got from Microsoft, the issue will be fixed in SQL Server 2008 R2.

Meanwhile, there is a simple workaround which requires manually changing the dataset definition in RDL. Basically, you need to add a SuppressAutoUpdate designer switch to the parameter dataset, as follows:

<Query>

<DataSourceName>Viking</DataSourceName>

<CommandText> …

<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>

<rd:Hidden>false</rd:Hidden>

</Query>

TechEd 2009 North America BIN304 Slides and Code Uploaded

tlachev — Fri, 22 May 2009 18:22:41 GMT

I've uploaded the slides and code from my Reporting Services 2008 Tips, Tricks, Now and Beyond breakout presentation delivered on May 13th 2009 at TechEd 2009 North America.

TechEd 2009 BI Power Hour Demos

tlachev — Thu, 21 May 2009 12:50:25 GMT

The TechEd 2009 BI Power Hour demos, which I blogged about before, are posted on the Microsoft BI Blog. Robert Bruckner has also posted the SSRS demo from the TechED USA 2008 Power Hour, which he renamed to Sea Battle.

Transmissions from TechEd USA 2009 (Day 4)

tlachev — Fri, 15 May 2009 05:08:00 GMT

I got the final evaluation results from my session. Out of some 15 Business Intelligence sessions, mine was the fourth most attended session with 169 people attending. Based on 35 evaluations submitted, it was ranked as the third most useful session with an average satisfaction score of 3.46 on the scale from 1 to 4. For someone who does presentations occasionally, I'm personally happy with the results. Thanks for all who attended and liked the session!

I took it easy today. I attended the Scott Ruble's Microsoft Office Excel 2007 Charting and Advanced Visualizations session in the morning. This was purely Excel-based session with no BI included. It demonstrated different ways to present information effectively in Excel, such as with conditional formatting, bar charts, sparklines, etc. Next, I was in the Learning Center until lunch.

In the afternoon, I decided to do some sightseeing and take a tour since I've never been to LA. I saw Marina Del Ray, Santa Monica, Venice Beach, Beverly Hills, Sunset Strip, Hollywood (and the famous sign of course), Mann's Chinese Theatre, and Farmer's Market. Coming from Atlanta and knowing the Atlanta traffic, I have to admit that the LA traffic is no better. In Atlanta, the traffic is bad during peak hours. In LA, it seems it's bad all the time. Movies and premieres make the situation even worse. There was a huge movie premiere at 7 PM in the Chinese Theater with celebrities arriving with limos. This jammed the entire area. There were at least two movies being shot in different parts of the cities. But the rest of tour was fun. LA is one of the few cities in the world where almost every building has a famous story behind it.

This concludes my TechEd USA 2009 chronicles. Tomorrow, I'll have time only for a breakfast and packing my luggage. I'm catching an early flight as it would take me four hours to fly to Atlanta. With three hours time difference, I'll be hopefully in Atlanta by 9 PM and at home by midnight.

Transmissions from TechEd USA 2009 (Day 3)

tlachev — Thu, 14 May 2009 04:51:00 GMT

Today was a long day. I started by attending the Richard Tkachuk's A First Look at Large-Scale Data Warehousing in Microsoft SQL Server Code Name "Madison". Those of you familiar with Analysis Services would probably recognize the presenter's name since Richard came from the Analysis Services team and maintains the www.sqlservernanalysisservices.com website. He recently moved to the Madison team. Madison is a new product and it's based on a product by DATAllegro which Microsoft acquired sometime ago. As the session name suggests, it's all about large scale databases, such as those exceeding 1 terabyte of data. Now, this is enormous amount of data that not many companies will ever amass. I've been fortunate (or unfortunate) that I never had to deal with such data volumes. If you do, then you may want to take a look at Madison. It's designed to maximize sequential querying of data by employing a shared-nothing architecture where each processor core is given dedicated resources, such as a table partition. A controller node orchestrates the query execution. For example, if a query spans several tables, the controller node parses the query to understand where the data is located. Then, it forwards the query to each computing node that handles the required resources. The computing nodes are clustered in a SQL Server 2008 fail-over cluster which runs on Windows Server 2008. The tool provides a management dashboard where the administrator can see the utilization of each computing node.

Next, I attended the Fifth Annual Power Hour session. As its name suggests, TechEd has been carrying out this session for the past five years. The session format was originally introduced by Bill Baker who's not longer with Microsoft. If you ever attended one of these sessions, you know the format. Product managers from all BI teams (Ofice, SharePoint, PerformancePoint, and SQL Server) show bizarre demos and throw t-shirt and toys to everything that moves (OK, sits). The Office team showed an Excel Services demo where an Excel spreadsheet ranked popular comics characters. Not to be outdone, the PerformancePoint team showed a pixel-based image on Mona Lisa. Not sure what PerformancePoint capabilities this demonstrated since I don't know PerformancePoint that well but it looked great.

The Reporting Services team showed a cool demo where the WinForms ReportViewer control would render a clickable map (the map control will debut in SQL Server 2008 R2) that re-assigns the number of Microsoft sales employees around the US states. For me, the coolest part of this demo was that there was no visible refresh when the map image is clicked although there was probably round tripping between the control and the server. Thierry D'Hers later on clued me in that there is some kind of buffering going on which I have to learn more about. This map control looks cool! Once I get my hands on it with some tweaking maybe I'll be able to configure it as a heat map that is not geospatial.

Finally, Donald Farmer showed another Gemini demo which helped learn more about Gemini. I realized that 20 mil+ rows were compressed to 200 MB Excel file. However, the level of compression really depends on the data loaded in Excel. Specifically, it depends on the redundant values in each column. I learned that the in-memory model that is constructed in Excel is implemented as in-process DLL whose code was derived from the Analysis Services code base. The speed of the in-memory model is phenomenal! 20 mil rows sorted within a second on the Donald's notebook (not even laptop, mind you). At this point Microsoft hasn't decided yet how Gemini will be licensed and priced.

As usual, after lunch I decided to hang around in the BI learning center and help with questions. Then, it was a show time for my presentation! I don't why but every TechEd I get one of these rooms that I feel intimidated just to look at them. How come Microsoft presenters who demo cooler stuff than mine, such as features in the next version, get smaller rooms and I get those monstrous rooms? It must be intentional; I have to ask the TechEd organizers. The room I got was next to the keynote hall and could easily accommodate 500-600 people, if not more. Two years ago, I actually had a record of 500+ people attending my session which was scheduled right after the keynote.

This year, the attendance was more modest. I don't have the final count yet, but I think about 150+ folks attended my session so there was plenty of room to scale up. I think the presentation well very well. The preliminary evaluation reports confirm this. I demoed report authoring, management, and delivery tips sprinkled with real-life examples. We had some good time and I think everyone enjoyed the show.

It's always good to know that your work is done. I look forward to enjoying the rest of TechED and LA.

Transmissions from TechEd USA 2009 (Day 2)

tlachev — Wed, 13 May 2009 05:31:00 GMT

I started the day by attending the Donald Farmer and Daniel Yu's session "Creating the Right Cubes for Microsoft Excel and Excel Services" hoping that I'll get a sneak preview of Excel 2010. Alas, it was all about refining the cube definition with display folders, perspectives, hierarchies, etc. so it appears more user-friendly in Excel. Later, I learned that Office 2010 (or whatever it will be called) is under strict NDA which explains the lack of demos. The most interesting thing about that session was that I finally understood why the SSAS team decided to scale down the cube wizard in SSAS 2008 to generate basic dimensions only. The reason was performance. You see, the BIDS 2005 cube wizard would oftentimes suggest non-optimal dimension hierarchies and the modeler wouldn't revise the design leading to bad performance.

Next, I attended the Thierry D'Hers Top Ten Reasons for Using Microsoft SQL Server 2008 Reporting Services. It's always good to watch RS-related sessions. Thierry listed the community support as one of the reasons for organizations to consider moving to SSRS. I agree with this and the great community support is applicable to all MS technologies. Speaking about BI only, a few years ago there wasn't a single book about Cognos for example. Granted, the last I look on Amazon there was one book. In comparison, Microsoft has a vibrant community of book authors, MVPs, trainers, etc. Almost every publisher has a book about SSRS 2008. This is of course good for the community and so good about authors as the competition is tough J

Thierry listed the Report Builder 2.0 as #1 reason to move to SSRS 2008, followed by data visualization, and tablix. Based on my real-life projects, I'd personally have listed them in the reverse order, with tablix being #1. This session officially announced that the map control will make to the Kilimanjaro release (now officially called SQL Server 2008 R2). Later on, Thierry was kind enough to show me a demo of the map pre-release bits. One of the data modes is using the SQL Server 2008 geospatial data types which would let you map any region in the world. Thierry showed a cool report showing the worldwide sales of SQL Server, where each country had a different color gradient based on the sales volume.

After lunch, I hang around the BI area in the learning center to answer questions and rub shoulders with Microsoft employees and peers. I was surprised to learn that there are only two Reporting Services-related sessions for the entire TechEd and one of them is mine! All of a sudden, I felt 2" taller. Later on, I felt adventurous to learn something completely new and attended a SQL Server 2008 Failover Clustering only to realize how much I don't know about it since I've never used it. BTW, there are great advancements in the SQL Server 2008 failover clustering, such as ability to upgrade or patch a cluster node without stopping it.

Transmissions from TechEd USA 2009 (Day 1)

tlachev — Tue, 12 May 2009 03:33:00 GMT

Day 1 of TechEd 2009 is almost over with the exception of the Community Influencers Party tonight. I heard that this year they expect 7,000 attendees. This is a huge scale-down from previous years. For instance, we had 16,000 attendees at TechEd USA 2007. Economy is hitting everything hard.

I thought the keynote was kind of lame. Judging by it, Microsoft has only three products: Windows 7 (officially announced to ship around holidays although Microsoft didn't say which holidays), Windows Server 2008 (the buzz is now the forthcoming R2 release), and Exchange Server 2010. Unlike previous TechEds, there wasn't a single announcement about other products. SQL Server KJ, Office 2010, Azure, dev tools? Nope, apparently not worth mentioning. Sure Mark Russinovich, whom I respect very much, did some cool Windows 7 demos but there were not enough to pique my interest. I understand that OS and Exchange Server are bedrock for every business and after the sad Vista saga, we have to show the world that now we'll do things right with Windows 7, but the BI soul in me was thirsty for more.

After lunch, I hang around the BI area of the Learning Center, where I answered questions and met with other peers, including Nick Barclay (MVP) whom I wanted to meet personally for a while. Then, I attended the excellent Donald Farmer and Kamal Hathi 's Microsoft Project Code Name "Gemini: Self Service Analysis and the Future of BI and I had the chance to see the Gemini, which I blogged briefly about before without knowing too much, for the first time in action and gain more in-depth knowledge.

The Gemini is an end-user oriented Excel add-in that will let the user acquire data from a variety of data sources, including SSRS reports (SSRS KJ will expose reports as data feeds) and SharePoint lists, and load them in an Excel spreadsheet. The tool crunches data very fast even on a modest computer (the demo showed a notebook computer working with millions of rows) thanks to its ability to compress column-level data. This works because a dataset column would typically contain redundant data.

Once data is loaded in Excel, the tool will attempt to automatically determine the relationships between datasets (loaded in separate spreadsheets) to create a hidden dimensional model consisting of fact and dimension in-memory tables. The user will be able to manually specify the dataset relationships by telling the tool which column will be used to join the datasets (very much like joining relational tables). Moreover, the user will be able to define calculated columns using Excel-style formulas. Finally, as the add-in builds behind the scenes an in-memory cube, the user will be able to slice and dice data in a Pivot table report. So, no Analysis Services is needed if all the user wants is manipulating data on the desktop.

Where things are getting more interesting is deploying the models on the server. To do so, the end user would deploy the Excel spreadsheet to the MOSS Report Library. Note that MOSS is required for server-side deployment. When other users request the spreadsheet, an Analysis Services redirector will understand that this is a Gemini model and service the requests from a server cube. At this point is not clear how exactly the server cube will be built and whether it could be managed in SSMS. Once the cube returns data, Excel Services will kick in to return data in HTML. A Reporting Services client can also connect to the server cube by its URL. This is no different than connection to a regular cube as Reporting Services will launch the familiar MDX query designer.

So, where is the IT in the new Gemini world? IT will use a cool MOSS dashboard to understand who's deployed what model and how the models are used, such as when the datasets were refreshed, what are the most popular models, what resources these models took on the server, etc.

What's my personal take on Gemini? It's not up to me to decide how useful it is since it's a business-oriented tool, such as Report Builder 2.0. Business users will have the final word. Based on my personal experience though, the data analytics problems that I need to solve with traditional Analysis Services cubes surpass the Gemini capabilities by far. So, don't throw your MDX knowledge out of the door yet. In my line of work, I can see Gemini being useful as a cube prototyping tool, especially in the early stages of requirement gathering where data can be typed in Excel and I can demonstrate to users what a cube can do for them. Of course, Microsoft plans for Gemini are much more ambitious than that. In the ideal world, all business users would upgrade to Office 2010 and create cool Gemini models to give IT folks a long-deserved break ;-). Or, so the fairytale goes….

To wrap up the day, I attended What's New in Microsoft SQL Data Services presentation by Rick Negrin to find out that SQL Data Services is nothing more than SQL Server running on Microsoft data centers. SQL Data Services will support two application connectivity modes: a "code near" mode where the application (typically a web application) is deployed to Azure and "code far", where the application will connect to SQL Server over Internet using the TDS protocol. Microsoft role is to provide scalability and failover. Not all SQL Server features will be available in version 1. For example, CLR will not make the cut.

A long and tiring day. I am off to the party now.

SQL Server 2008 Business Intelligence Development and Maintenance Toolkit Available

tlachev — Thu, 16 Apr 2009 01:46:58 GMT

Today was supposed to be the birthday of the SQL Server 2008 Business Intelligence Development and Maintenance Toolkit by Microsoft Press but it's not available with retailers, such as Amazon, yet. I guess a couple or so more days as the book is finding its way to resellers.

As with the 2005 version, I was privileged to work together with Erik Veerman and Dejan Sarka (all SQL Server MVPs) on the new revision. My part was the four Analysis Services chapters. Besides updating the book for SQL Server 2008, we re-worked the entire material to flow more logically and make this resource even more useful to help you prepare for the corresponding 70-448 exam.

I hope you'll find the toolkit useful and pass the exam to certify!

Overwriting Parameter Defaults

tlachev — Tue, 14 Apr 2009 01:02:00 GMT

Issue: An interesting question popped today on the discussion list about overwriting the default of a cascading parameter. In this particular case, the report has two parameters: Frequency and HireDate. The HireDate parameter is dependent on the Frequency parameter. Specifically, when the user changes the Frequency parameter, custom code changes the default of the HireDate parameter. Sounds easy, right? Well, not so fast.

This requirement calls for smashing the HireDate default. However, smashing values that the user selected (either explicitly or implicitly by not changing the default) is usually considered to be a bad thing. There exist uncommon cases such as this one where the author of the report can reasonably know the user would expect their previously selected value will be overridden. Unfortunately, Reporting Services doesn't not currently have a feature which would allow the report author to distinguish the common case from the uncommon case. As a result, the current design handles the common case. (Note: In SQL 2000, SSRS defaulted to smashing the user's selection with the default when an upstream parameter value changed. And, this resulted in a large volume of customer complaints.

Solution: So, what's the solution? One option you may want to explore, which the sample report demonstrates (attached), if the Valid Values list changes (as a result of the upstream parameter value changing) and it no longer contains the user's selection, SSRS is forced to re-evaluate the default. Careful use of the Valid Values list can in many cases simulate the desired behavior of always overriding the user's selection back to the default. This is I set the HireDate available values to the same expression, so its available value always changes.

(Please visit the site to view this media)

Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies, April 2009

tlachev — Mon, 13 Apr 2009 15:24:00 GMT

As I mentioned in a previous blog, one very important SSRS enhancement in the recently released SQL Server 2008 SP1 is Report Builder 2.0 ClickOnce. This lets you reconfigure the native and SharePoint integration modes to let the user use Report Builder 2.0 instead of Report Builder 1.0.

Note that in SharePoint integration mode, installing SP1 only is not enough. That's because there are actually two instances of RB2.0 ClickOnce. If you install SQL Server 2008 SP1, this will provide an update for native mode only. For SharePoint integration mode, you must refresh the Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies. This is necessary because the ClickOnce bits must reside on the SharePoint server. The April 2009 version of the add-in can be downloaded from here.

SSAS Import Wizard to the Rescue

tlachev — Thu, 02 Apr 2009 17:32:37 GMT

Sometimes, it's better just to reset. I came across an issue concerning processing a partition whose containing measure group joins a dimension via another reference dimension. Specifically, when I attempt to process the partition, the server would error out with:

The binding for the 'dimension key' column is not a ColumnBinding type

What made this issue interesting is that I was able to repro the issue each time I would process the partition but the Microsoft support engineer, who was assigned to this issue, hasn't been able to. After some digging we realized that the issue only happens in project mode, that is, when working with the project source in BIDS and deploying to the server.

How do we fix this horrible issue? We reverse-engineer the deployed cube using the SSAS Import Wizard to regenerate the source code.

In BIDS, click File->New->Project.
In the New Project dialog, select Import Analysis Services 2008 (or 2005 depending on the version you have) Database.
Specify the location of the deployed database. BIDS will reverse-engineer the database to its source code.

Magically, after this procedure the error went away although we are still working on finding out what caused the error with the original code.

Excel PivotTable Tabular Reports

tlachev — Tue, 31 Mar 2009 21:12:08 GMT

Here is a great tip thanks to Greg Galloway who pointed me to the Steve Novoselac's excellent blog. By default, Excel 2007 PivotTable stacks (hierachizes) the attributes on the report even if they are from the same dimension. For example, let's say the user wants to slice by the product hierarchy but want also some additional product attributes, such as the product color, size, etc. By default, Excel will produce the following layout:

Needless to say, this is hardly what the users want to see as they would prefer the product attributes to be displayed in a tabular format. Luckily, this is easy albeit not very intuitive.

Before you drop the first field on PivotTable, click the Design main menu on the ribbon (the one next to Options menu).

Expand the Report Layout drop-down menu and select Show in Tabular Form. Again, make sure that that PivotTable is empty before you do this.
If you want to remove subtotals, expand the Subtotals drop-down menu and select Do Not Show Subtotals.
Finally, to remove the +/- indicators, right-click on PviotTable and select PivotTable Options. On the Display tab, uncheck the Show Expand/Collapse Buttons.

Then, you can proceed to laying out the report as usual. Now you have a cool-looking tabular report.

Report Builder – Past, Now and Future

tlachev — Tue, 31 Mar 2009 13:21:00 GMT

The Report Builder technology made its debut in SQL Server 2005. It targeted business users who wanted to author their own (ad-hoc) reports but they were not necessarily technically savvy, meaning that no technical knowledge about database schemas or query syntax was assumed. The Report Builder technology includes a Report Builder Model and Report Builder Client. Similar to other ad-hoc reporting tools, such as Impromptu, the solution developer was required to implement a Report Builder model that is layered on top of the database and deploy it to the server. This model would receive the reports authored by the end users, which were described in an abstract language (SMDL), and automatically translate them to native queries for the supported data sources (SQL Server, Oracle, and Analysis Services).The initial vision, which didn't materialize, was to make the query generator open, so developers can plug in custom query generators for other data sources. The Report Builder Client is a ClickOnce desktop report designer which was capable of producing very simple template-based reports.

As Brian Welcker, a former Group Program Manager on the SSRS team, explained in his blog, the Report Builder technology itself enjoyed good acceptance but it wasn't perfect. To start with, many business users found the Report Builder Client too limiting. Targeting Analysis Services as a data source was problematic to say the least as essentially you had to "wrap" the cube with the Report Builder model layer while a direct access to the cube was preferred. As a result, SQL Server 2008 brought in Report Builder 2.0 which was targeted to supersede the Report Builder Client. As I explained, besides its name Report Builder 2.0 has very little to do with its predecessor as it's much closer to the BIDS Report Designer than Report Builder 1.0. It provides a full RDL support and shares the same design layout with the BIDS Report Designer but runs outside Visual Studio as a desktop application (ClickOnce-enabled in SQL Server 2008 Service Pack 1).

So, does this mean that the Report Builder technology itself is outdated moving to SQL Server 2008? Absolutely not. If you are looking for an ad-hoc solution that targets small to medium-size SQL Server and Oracle databases, you should definitely evaluate Report Builder. In fact, future releases are expected to bring exciting changes to the Report Builder model which I cannot discuss as they are under NDA. On the report authoring side, business users should evaluate both Report Builder 1.0 (still available in SQL Server 2008) and Report Builder 2.0. Based on my experience, even non-technical users, such as these who struggle with copy and paste, would prefer Report Builder 2.0, so I'd definitely encourage my users to switch to it.

That said, remember that Report Builder is not the only ad-hoc technology that Microsoft provides. As many of you know, I am a big fun of Analysis Services for OLAP and ad-hoc reporting. If you are willing to invest time to learn Analysis Services and MDX (and you should), another approach is to implement a cube instead of a proprietary Report Builder model. If the user needs traditional (banded) reports, they can use the Report Builder 2.0 MDX Query Designer to author such reports from the cube. Of course, one of the advantages of this approach is that Analysis Services enjoys broader client support so you are not limited to Report Builder 2.0 only as a reporting tool. For example, business users can use Excel, ProClarity, and third-party tools. So, choosing a Report Builder model or SSAS is an important decision that requires carefully evaluating your reporting requirements, implementation effort, and skill set.

What may not be so obvious is that if you decide to use a Report Builder model, you can have the best of both worlds: Report Builder model on the server that abstract the database and Report Builder 2.0 client that supports all report authoring features. Unlike the BIDS Report Designer, with Report Builder 2.0 the user doesn't have to (in fact the user can't) create a data source that points to the model. Instead, the user wold point to a server Report Builder model when defining the report data source:

In the Report Data pane, click New –> Data Source. Notice that the Data Source Properties dialog says "Use a shared connection or report model".
Browse to the Report Builder model on the server and click OK.

Once in the query designer, notice that it shows the same Report Builder metadata as in Report Builder 1.0. You have the same entity navigation that guides the user to the entities that are related to the selected entity. From here, authoring a report is a matter of dragging and dropping entities and fields. One thing that is not working in Report Builder 2.0 though, which is likely to be fixed in a future release, is filter prompts. Report Builder Client 1.0 would automatically populate the parameter available values if you select a filter as a prompt. Report Builder 2.0 would generate a report parameter but you have manually set up a dataset for the available values and bind it to the parameter.

Troubleshooting the SharePoint Add-in for Reporting Services Part 2

tlachev — Mon, 30 Mar 2009 21:03:00 GMT

Today, a co-worker declared a victory after struggling a few days to get Reporting Services 2008 SharePoint integration mode going on Windows Server 2008 (aka Longhorn). I helped of course J

It all started with access denied error when attempting to deploy reports to SharePoint. Navigating to the WSS Central Admin Operations page would reveal however that the Reporting Services section is missing. After n-installs of the RS add-in we realized that something is not right.
Take-home note: If the Reporting Services section is missing, the Reporting Services add-in failed to install irrespective of the fact that it didn't report any errors. Bummer No 1.
Wish No 1: The RS add-in should report errors to users ideally with recommendations.
I asked him to troubleshoot the RS add-in installation following the steps in this blog.

The most important line from the add-in log file was:

********** User does not have permissions to add feature to site collection ********

So, the add-in installed the Report Server Integration Feature but it couldn't activate it although my co-worker used the same account (his Windows account) to install the add-in and he had WSS Farms Administrators rights? Bummer No 2. Welcome to the SharePoint candy land where things are not what they appear to be.
Wish No 2: The farm administrator should have the right permissions to activate features or if he doesn't have them for whatever reasons, the add-in should report this beforehand.

Activate the feature manually:
1. Go to SharePoint 3.0 Central Administration.
2. Select Site Actions -> Site Settings
3. Under the Site Collection Administration section click Site Collection Features
4. Activate the Report Server Integration Feature in the list
Run the RS add-in again. This time, no error messages in the add-in log file.

Success!

Disclaimer: The issues described above might be Windows Server 2008-specific (we have a tighter security model now, right) or at least I don't recall having them on Windows Server 2003.

Measures on Rows

tlachev — Sat, 14 Mar 2009 01:26:00 GMT

Issue: You want an MDX query that returns measures on rows and calculated members on columns. You get The Measures hierarchy already appears in the Axis0 axis error. That's because calculated members added to measures are treated as measures and you cannot have the same dimension (Measures in this case) on different axes.

Solution: Let's point out that the above-mentioned error doesn't occur if you request dimensions on columns. For example, the following is a perfectly legit query:

select {[Date].[Calendar].[Calendar Year].&[2002],

[Date].[Calendar].[Calendar Year].&[2003]} on 0,

{[Measures].[Internet Sales Amount],

[Measures].[Reseller Sales Amount],

[Measures].[Sales Amount]

} on 1

from [Adventure Works]

In addition, you may be able to use the capabilities of the tool to rotate dimensions from columns to rows, such as using a crosstab report layout in Reporting Services. Interestingly, PivotTable in Excel 2007 lets you add measures on rows although behind the scenes Excel requests measures on columns but does an internal rotation to move the measure columns on rows.

However, sometimes you may need asymmetric columns that filter the results. This may force you to use calculated members on columns and measures on rows. Since you cannot put the calculated members on Measures, consider adding them to a dimension. The following query adds Column1 and Column2 calculated members to the Product.Category attribute. Notice that the first column filters the bikes sales for USA only.

with member [Product].[Category].Column1 as ([Product].[Category].[Bikes], [Date].[Calendar].[Calendar Year].&[2002], [Geography].[Country].&[United States])

member [Product].[Category].Column2 as ([Product].[Category].[Bikes], [Date].[Calendar].[Calendar Year].&[2003])

select {[Product].[Category].Column1, [Product].[Category].Column2 } on 0,

{[Measures].[Internet Sales Amount],

[Measures].[Reseller Sales Amount],

[Measures].[Sales Amount]

} on 1

from [Adventure Works]

Of course, you can use any tuple or aggregation function for the member definition. BTW, you can cheat SSRS to bypass the rule that insists that measures must be on columns and the entire validation and preparation goodness by manually entering the query in the report definition and defining the dataset fields and mappings.

Invisible Cubes

tlachev — Sat, 07 Mar 2009 15:54:37 GMT

I had a head-scratcher the other day. All of a sudden, SQL Server Management Studio stopped showing the cubes in the dropdown of the MDX Query tab although MDX queries and reports would execute just fine. The Reporting Services MDX Query Designer would complain with "No cubes found" error when I attempted to launch it from the dataset properties. This strange behavior coincided with installing SQL Server 2008 SP1 CTP, so I was quick to find a culprit. My suspicion was that the SSAS Windows authentication was failing was some reason although the SQL Server Profiler would show that the right Windows identity was connecting to the database.

To troubleshoot this further, I issued the following schema rowset query when connected to the SSAS database in SSMS:

select * from $system.mdschema_cubes

Interestingly, the query would return a list of the database dimensions (SSAS treats dimensions as cubes and prefix the dimension name with $) but it wouldn't return the cube name itself. Then, I had a Eureka moment. I went to the Cube Structure tab in the BIDS Cube Designer, and I realized that that somehow I managed to set the cube Visible property to False. Not sure why you would ever want to make the cube invisible and why this property is there to start with but there it was. Flipping it back to True and redeploying the cube fixed the mysterious No Cubes Found error.

Speaking at TechEd 2009 USA

tlachev — Mon, 02 Mar 2009 03:24:51 GMT

One of my TechEd 2009 session proposals got approved. The session is tentatively named Reporting Services 2008 Tips and Tricks, How-to, and Beyond. I am planning to cover solutions to common questions and challenges that span the three phases of the reporting lifecycle: authoring, management and delivery. I hope you can attend my session If you planning to attend TechEd 2009 USA. Shoot me a note if you want me to cover something in particular. See you in LA!

Hacking MDX Query Designer Parameters

tlachev — Thu, 26 Feb 2009 02:25:00 GMT

(Please visit the site to view this media)Continuing our intrepid journey in the land of SSRS-SSAS integration, after we've figured out how to retrieve the cube metadata, we are ready to tackle another unchartered territory: query parameters.

Issue: You need to parameterize the MDX query by a measure or a simple parameter. Take for example the following report (attached).

This report shows the Internet Sales Amount measure broken down by the Employees parent-child hierarchy. What's more interesting is the Show Descendants parameter. It lets you filter the report for a single employee (Show Descendants = False) or the descendants of the selected employee including the selected employee (Show Descendants = True). So, what's the issue? You can whip out such a report in no time if you source data from a relational database. However, the MDX Query Designer insists that the parameter is resolved to a single dimension member or a set of dimension members. Apparently, simple parameters or parameterizing by measures were considered uncommon scenarios. So, we need a sleek hack.

Solution: Luckily, there is a way to gain more control over the MDX query parameters but the design mode (the one that lets you drag and drop stuff) won't cut it. So, it may make sense to author the report query in design mode as much as you can before you jump into the cold waters of MDX query mode because there is no going back to drag and drop, sorry.

To start with, you need to define a query parameter for the ShowDescendants parameter. Switch to MDX mode and click the Query Parameters toolbar button (enabled only in MDX mode).
Add a new ShowDescendants "hanging" parameter that doesn't reference any dimension. Make sure that the query parameter name matches the report parameter name. Otherwise, the MDX Query Designer will treat the parameter as a new parameter and it won't associate it to the existing ShowDescendants report parameter. Note that the parameter name is case-sensitive.

Leave the Dimension and Hierarchy columns empty. Luckily, they are not required in MDX mode. Either type in a measure name, such as [Measures].[Sales Summary], or enter DEFAULT in the Default column. In the latter case, the default cube measure will be used when you test the query in the MDX Query Designer. See this blog by Chris Webb for ideas about how to speed up the default measure if needed. Click OK.
Back to the query, change it as follows:

SELECT NON EMPTY { [Measures].[Reseller Tax Amount] } ON COLUMNS, NON EMPTY

IIF(@ShowDescendants=False, StrToMember(@EmployeeEmployees), DESCENDANTS(StrToMember(@EmployeeEmployees)))

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME, LEVEL_NUMBER ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

These changes are specific to the report requirements. In our case, the IIF expression checks the value of the ShowDescendants parameter. If the user has selected the False option, the query brings only the selected employee on rows. Otherwise, we use the Descendants function to bring the descendants of the selected employee.

Looks hacky? Join me and make a wish on connect.microsoft.com to improve the SSRS-SSAS integration. Don't be surprised if you are not active that the MDX Query Designer will continue to sneak from one version to another unchanged.

Report Builder 2.0 ClickOnce

tlachev — Wed, 25 Feb 2009 19:38:00 GMT

One important change that Service Pack 1 for SQL Server 2008 (public CTP available here) will bring is ClickOnce deployment of Report Builder 2.0. By default, however, Report Manager or SharePoint (if you have SSRS installed in SharePoint integration mode) still point to Report Builder 1.0. Here is how to reconfigure SSRS to launch Report Builder 2.0 instead. The following procedure assumes SharePoint integration mode but the steps are similar if SSRS is running in native mode.

CAUTION Steps 1 and 2 below to copy folders are for the CTP bits of SQL Server 2008 SP1 only because the CTP build doesn't support RB 2.0 ClickOnce in SharePoint integration mode. SQL Server 2008 SP1 RTM will include an installer to make the appropriate folder changes automatically. Therefore, DO NOT manually copy the folder content with the final bits or undo your changes before you install SP1 RTM so the installer doesn't fail.

Back up the content of the C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\ISAPI\ReportBuilder folder. This step is not needed with native mode.
Copy all files from the C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\ReportBuilder folder to the C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\ISAPI\ReportBuilder folder. This step is not needed with native mode.
If you install SQL Server 2008 SP1 this will provide an update for native mode only. In order to get Report Builder ClickOnce for SharePoint integration mode working, you need to download the RB 2.0 ClickOnce update for the RS SharePoint add-in from the feature pack page. This is necessary because the ClickOnce bits must reside on the SharePoint server. As of the time of this writing, the latest add-in is April 2009 which you can download from here.
Open WSS Central Administration. Click the Application Management tab. Click the Set Server Defaults link found under the Reporting Services section. In native mode, open Report Manager and click the Site Settings top menu.

5. Enter the URL address to the ReportBuilder_2._0_0_0.application file. In SharePoint mode, assuming you followed steps 1-2, the URL address will be:
/_vti_bin/ReportBuilder/ReportBuilder_2_0_0_0.application

In native mode, it will be:

/ReportBuilder/ReportBuilder_2_0_0_0.application

At this point, SSRS is reconfigured to launch Report Builder 2.0 instead of Report Builder 1.0. From the end user perspective, the user would launch RB 2.0 just like they used to launch RB1.0, that is, click New->Report Builder Report in SharePoint or click the Report Builder button in Report Manager. The first time the user does this, the Report Builder 2.0 bits (almost 50 MB) will get downloaded and installed in the ClickOnce local application cache, which is a hidden directory under Local Settings for the current user. The ClickOnce application cache will not be cleared when the user clears the browser cache. Subsequent requests will use the local copy.

UPDATE Now that SQL Server 2008 SP1 is released, it includes a readme file with steps how to configure and troubleshoot Report Builder 2.0 ClickOnce in SharePoint integration mode.

Reporting Services Tracer

tlachev — Tue, 24 Feb 2009 02:35:40 GMT

To make a humble contribution to open source, I've just uploaded the Reporting Services Tracer (RsTracer) project to CodePlex. RsTracer helps you trace the URL and SOAP traffic to/from a report server. Given the sheer number of Web methods supported by Reporting Services, you might find yourself asking which web method should you choose for the management task at hand and how should you call it? At the same time, it is likely that Report Manager or Management Studio already supports some aspect of the management feature you want to implement.

Wouldn't it be nice to be able to peek under the hood and see what APIs Report Manager or a custom application calls? This is exactly what the RsTracer sample was designed to handle. RsTracer intercepts the server calls and outputs them to a trace listener, such as the Microsoft DebugView for Windows. RsTracer helps you see the APIs that a Reporting Services client invokes and what arguments it passes to each interface. It also intercepts the server response. Armed with this information, you can easily reproduce the same feature in your custom management application. You can use RsTracer with SSRS 2000, 2005, and 2008.

Enjoy!

Retrieving Cube Metadata in Reports

tlachev — Fri, 13 Feb 2009 21:24:00 GMT

An interesting question popped up on the discussion list the other day about how to retrieve a list of the cube measures so the report author can display them in a report parameter.

Solution 1: If you just need a list of measures but not the measure metadata, such as display folder, etc., you can use the following (not trivial, ok hacky) query, which I borrowed from my esteemed colleague MVP and MDX guru Chris Webb:

WITH SET MYSET AS HEAD([Date].[Date].[Date].MEMBERS, COUNT(MEASURES.ALLMEMBERS)-1)

MEMBER MEASURES.MeasureUniqueName AS MEASURES.ALLMEMBERS.ITEM(RANK([Date].[Date].CURRENTMEMBER, MYSET)-1).UNIQUENAME

MEMBER MEASURES.MeasureDisplayName AS MEASURES.ALLMEMBERS.ITEM(RANK([Date].[Date].CURRENTMEMBER, MYSET)-1).NAME

SELECT {MEASURES.MeasureUniqueName, MEASURES.MeasureDisplayName} ON 0,

MYSET ON 1

FROM [Adventure Works]

Solution 2: This approach is especially useful if you need the measure properties. In this case, you must use the OLE DB Provider for Analysis Services instead of the SSRS Analysis Services Provider (the one that gives you the nice MDX Designer). This approach uses the MDSCHEMA_MEASURES rowset, which is one of the built-in SSAS schema rowsets. Darren Gosbell provides a great overview of the SSAS rowsets here and Vidas Matelis gives more insights here. Thanks to the schema rowsets, getting a list of measures can't simpler.

SELECT * from $System.MDSCHEMA_MEASURES

The user will require Read Definition rights (on Role Properties General tab, check Read Definition) to obtain the metadata. If the user doesn't have this right, an empty dataset will be returned.

There are of course additional schema rowsets, such as MDSCHEMA_KPIS if you want to get a list of all KPIs or DBSCHEMA_TABLES in case you want to discover the cube dimensions and measure groups. BOL provides a full list here. Aren't SSAS schema rowsets cool?

Named Set Subtotals in Excel

tlachev — Tue, 27 Jan 2009 20:15:00 GMT

So many SSAS browsers, so little resemblance... And each browser has an independent view of which SSAS features it cares about and how it will go about implementing them. For example, Excel 2007 will let you use named sets on rows or columns but not as a filter. The cube browser and Report Builder let you use them in the filter area but not on columns and rows.

Issue: Business users have requested a subtotal for named set in Excel 2007. Say, you've defined the following named set in the cube to return the last 12 months from the current month.

CREATE DYNAMIC SET CURRENTCUBE.[Last 12 Months] AS

[Date].[Date Hierarchy].CurrentMember.Lag(11):[Date].[Date Hierarchy].CurrentMember

If you drop this named set on an Excel 2007 PivotTable report, you would notice that it doesn't give you a subtotal that returns the aggregated total of the measure used in conjunction with the set. However, the chances are that you would need a server subtotal, especially if you use scope assignments to derive the subtotal value. By contrast, the cube browser will show the subtotal if you use the named set as a filter and drag the corresponding dimension (in this case Date) to the rows or columns. This difference of course originates from the MDX query the client generates. In the latter case, the cube browser hiearchizes the dimension All member so the server returns this member, while a named set would simply returns the members of the set and nothing more.

Workaround: A simple workaround in Excel 2007 to get a subtotal is to add the dimension all member, such as:

CREATE DYNAMIC SET CURRENTCUBE.[Last 12 Months] AS

{[Date].[Date Hierarchy].CurrentMember.Lag(11):[Date].[Date Hierarchy].CurrentMember,
[Date].[Date Hierarchy].[All]}

Of course, if the named set uses another dimension, you need to add the All member of that dimension. For example, adding a subtotal to the Adventure Works Top 50 Customers will require the following change:

Create Dynamic Set CurrentCube.[Top 50 Customers]

As {TopCount

(

[Customer].[Customer].[Customer].Members,

50,

[Measures].[Internet Sales Amount]

), [Customer].[Customer].[All Customers]}

Wish List: Unify Microsoft-based browsers as much possible in terms of functionality. In this case, it will be nice, if Excel supports named sets as filters.

Business Intelligence is Fifth Hottest IT Skill

tlachev — Thu, 15 Jan 2009 19:21:15 GMT

ComputerWorld ranks BI as the fifth hottest IT skill.

Implementing Cube Settings

tlachev — Wed, 14 Jan 2009 03:05:00 GMT

I had to tackle an interesting requirement the other day. A cube had a number of KPIs defined but the threshold values that were used to define the "good", "bad", and "so-so" status ranges had to be defined as configurable values at any level of the organization dimension and KPIs. I settled on implementing a measure group that intersects the organization dimension and a new KPI dimension that lists the KPI names.

For example, as the screenshot shows, each KPI has two threshold values (Threshold Value1 and Threshold Value2) which can be defined at any level of the organization hierarchy. Note that the parent values read the setting values directly from the fact table instead of being derived by rolling up from the children.

The first implementation approach that comes to mind is to use the None AggregateFunction function for the measure group so you read the measure values associated with the member directly from the fact table. Some experimenting revealed the following "noble" truths about the None AggregateFunction:

BOL is wrong. I refer to the following statement:
(None AggregateFunction) No aggregation is performed, and all values for leaf and nonleaf members in a dimension are supplied directly from the fact table for the measure group that contains the measure. If no value can be read from the fact table for a member, the value for that member is set to null.

Specifically, When None is used, the server loads the fact data into the measure group leaves (just like with other aggregate function) but it doesn't roll up the values to the dimension leaves. See this Mosha's post to understand the difference between the dimension and measure group leaves.

The way it stands, the None function brings a questionable value. A narrow case exists where you may want to perform assignments or calculations in the measure group leaves as the above post demonstrates. Why you would want to do this instead performing them at the fact table level (or as named calculations) for obvious performance advantages is beyond me.
There is no way to bring the measure leaf values to the dimension leaves. Shoot me a note if you manage to come with some clever hack to do so.

Note: It would have been great if the None function behaves as described in the documentation. Even better, it could be nice if the server automatically brings up the fact value when the cube slice results in one fact record, i.e. the user has drilled down to measure group grain. This will make it possible to implement text-based measures, such to capture comments, or other text-based attributes, that don't justify moving them to a dimension. If you agree, vote for this enhancement on connect.microsoft.com.

So, how do we get around this predicament? One option is define a custom ~ operator for the Organization parent-child dimension so the server doesn't roll up the measure. However, the custom operator will be applied to all measures sliced by the Organization dimension which more than likely will be an issue. Of course, a trivial workaround is to duplicate the Organization dimension but this leads to other disadvantages. IMO, a better solution might be to use the DataMember property and a scope assignment:

SCOPE (

MeasureGroupMeasures("KPI Configuration"),

[Organization].[Organization Hierarchy].Members,

[KPI].[KPI].[KPI]

);

this = ([Organization].[Organization Hierarchy].CurrentMember.DataMember);

END SCOPE;

I scope at the KPI Configuration measure group where it intersects with the members of the Organization and KPI dimensions. The AggregationFunction property of the measures in the KPI Configuration measure group is now set to SUM. The tuple on the right side of the this assignment returns the measure value associated directly with the current data member. The new result is that this assignment overwrites the SUM function with the measure leaf value.