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…
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…
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…
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…
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:…
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…
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…
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…
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…
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.…
