It’s Done

/0 Comments/in /by

A quick update on my book Applied Microsoft SQL Server 2008 Reporting Services. I am happy to announce that the work on the manuscript is over and the manuscript RTM’d (released to manufacturing). The paper copy should be available on the reseller sites and brick and mortar stores around the publication date (August 15th). Expect the ebook version around that time as well. Of course, the good thing about the ebook version besides being searchable and portable is that it includes color images. As you would probably agree, color is a good thing when you are studying report design. Video demos are even better, of course, which brings me to the next topic.

While waiting, take a look at the book web page that just went live and check the available resources. Among other things, you’ll find two sample chapters (chapters 1 and 3), the book source code, and video demos, which I captured using the awesome TechSmith Camtasia. The videos are bonus material and are an experimental feature. Video demos are provided for a subset of the report authoring practices covered in the book. I am looking forward your feedback about this feature. Enjoy and I hope you don’t mind my thick as a brick accent J

Finally, the book web page includes a link to a discussion list, where you can make comments about the book and ask book-related questions. The new discussion list will replace my Reporting Services in Action discussion list, which will retire at the end of this month after four years of service. I hope you liked my participation and will consider “upgrading” to the new discussion list and book.

Automating Report Deployment

/0 Comments/in /by

Here is an awesome tip courtesy to the Craig Guyer’s I Command Thee blog. You probably know that Visual Studio supports project configurations. In BIDS 2008, you can automate report deployment with configurations. For example, the following command deploys the solution using the settings of the QA configuration.

C:\>devenv “C:\Books\RS2008\Code\ch03\Reports\Reports.sln” /deploy QA

This is especially useful when automating deployment to SharePoint because you need to change the report definitions to use absolute paths to external resources, such as shared data sources. BIDS deployment can handle this for you and save you writing custom code to automate deployment.

While we are on the SharePoint deployment topic with BIDS, one nasty bug was introduced late in the SQL Server 2008 cycle that prevents you deploying folders that contain a space, such as Data Sources, to SharePoint from BIDS. When you attempt to do this, you get:

Error rsInvalidItemName : The name of the item ‘Data%20Sources’ is not valid. The name must be less than 128 characters long. The name must not start with slash; other restrictions apply.

If the folder already exists, deployment is successful. Unfortunately, due to time constraints, this bug won’t get fixed in the RTM timeframe. As a workaround, don’t use spaces in the target folders, e.g.; DataSources instead of Data Sources.

SQL Server 2008 Available on August Price List

/0 Comments/in /by

Courtesy to Francois Ajenstat, Director of Product Management for Microsoft SQL Server, we know that SQL Server 2008 is on the August price list. What this probably means according to my interpretation is that the SQL Server 2008 RTM date is close. More than likely, SQL Server 2008 will be released in August.

Textbox on Steroids

/0 Comments/in /by

One of the new features in Reporting Services 2008 that debuted in the SQL Server 2008 RC0 build is the enhanced textbox report item. In previous releases, if you wanted to mix static and dynamic expression-based text, you either needed multiple textboxes or a Visual Basic expression to concatenate strings together. The first approach led to textbox “explosion”. The disadvantage of the second approach was that you couldn’t format string fragments inside the same textbox independently.

 

In Reporting Services 2008, the textbox report item has been redesigned to support multiple bands of text. The screenshot shows a report title of a sales order report. In the past, you w061908_1813_TextboxonSt1ould need two textboxes (or three if you wanted different formatting for the sales order number). You may be surprised to find that the entire title is implemented as a single textbox with two paragraphs. The second paragraph combines static text (Order #:) with dynamic text ([SalesOrderNumber]), which defines a placeholder for a dataset field value. Each fragment can have its own format settings. Thanks to these enhancements, you’ll find that by moving to Reporting Services 2008, you need fewer textboxes and you need to write less often expressions that concatenate text. Moreover, the new textbox lets you implement report solutions, such as mail merge, that were difficult or impossible to implement with previous releases.

Many report authors will rejoice learning that the textbox report item now supports a subset of HTML tags for formatting the text content. This is also known as rich formatting (not be confused with RTF which is not supported). You can import static HTML text or bind the textbox to a dataset field. For instance, if the dataset field includes HTML tags, such as <b>SO50750</b>, you can configure the textbox to interpret these tags and display the sales order number in bold.

SQL Server 2008 RC0 Product Samples Refreshed

/0 Comments/in /by

Microsoft has refreshed the product samples and AdventureWorks databases. I still have to figure out what’s the AdventureWorksAS2008 database that is not released yet.

Microsoft SQL Server 2008 Feature Pack RC0, June 2008

/0 Comments/in /by

Today, Microsoft released an updated feature pack for SQL Server 2008 RC0. Among other things, it includes:

  1. A web downloadable installer for Report Builder 2.0 which is not included anymore with the SQL Server 2008 setup
  2. An updated version of the RS add-in for SharePoint
  3. Analysis Services 10.0 OLE DB Provider

SQL Server 2008 Release Candidate 0 (RC0)

/0 Comments/in /by

Microsoft declared build #1442.32 to be the official SQL 2008 RC0 build and made it publicly available.

Key SSRS improvements in this build:

  1. Rich text formatting. You can now format the text inside a textbox with different styles.
  2. Report Wizard to let the user auto-generate the report definition.
  3. Fully functional MDX Query Designer.
  4. An updated build of the Report Builder 2.0 (aka Report Designer Preview) will be available via a web download. The URL link is not known at this point.

BI Salary Survey

/0 Comments/in /by

Not sure how much your BI expertise is worth? The 2008 TDWI Salary, Roles, and Responsibilities Report TDWI survey may help.

Intelligencia Query

/0 Comments/in /by

Chris Webb, a fellow MVP and MDX guru, who’s blog is a must-read for all SSAS junkies, was kind enough to let me take a look at the Intelligencia Query product he’s been working on for a while with Andrew Wiles. Chris announced the product back in April which is now selling as a commercial offering.

Now, lots of things have been said about the current state of the SSRS-SSAS integration, which is to say the least wanting. In a previous blog, I wrote “In my opinion, the biggest challenge the Microsoft BI initiative faces today is the inadequate support for Analysis Services.” Alas, we won’t witness an improved SSRS-SSAS integration in SQL Server 2008 as Microsoft decided to focus on other areas and priorities.

Rest assured though that Microsoft understands the importance of this integration scenario. I personally have voiced my concerns on a few occasions and have put this on the top of my wish list which I shared with the Reporting Services team. There are good things happening already which make me believe that SQL Server.NEXT (post-SQL Server 2008) will materialize this wish. What’s really needed is Excel-like support of SSAS in Reporting Services, plus calculated members which are already supported.

Then, the question is what to do meanwhile if you target Analysis Services (and you should). One approach is to take the “I’ll stick with Microsoft” approach. This may require you find workarounds for the static schema and other limitations, such as using the Microsoft OLE DB Provider for Analysis Services instead of the built-in SSAS provider. This is not as bad as it seems as little out-of-box thinking usually gets the job done. By the way, I wrote some 50 pages in my next book to cover integrating SSRS with SSAS in as much detail as possible.

The second approach is to look at third-party offerings, such as Radius Producer and Intelligenca Query. What I liked about Intelligenca Query is that it doesn’t require throwing the baby with the water. You can still keep the Microsoft Report Designer but use Intelligenca Query to replace the built-in SSAS provider. This works because just like the built-in provider, Intelligenca Query is implemented as a data extension. I tested Intelligenca Query with SQL Server 2008 and it worked without any issues with the BIDS Report Designer and Report Builder 2.0 (aka Report Designer Preview).

The good thing about Intelligenca Query is that it lets you plug in an arbitrary MDX query. Behind the scenes, it pulls out a little trick where it rewrites the report dataset by transposing columns to rows. Consequently, you have to use the matrix region to rotate the rows back to columns to recreate the original query results. Now, my ideal provider would support a dual mode where I can tell it not to rewrite anything but just to give me the results. Of course, if another dimension member is added, it won’t show up on the report columns (assuming a table with fixed columns) but I can see this being useful for notice users that create ad hoc reports or when the columns are fairly static.

051508_0237_Intelligenc1

Another thing I liked, of course, about Intelligenca Query is that it eliminates the nagging issue of server aggregates which are the aggregates for the dimension All members. With the built-in provider, you have to explicitly request them by using the Aggregate function but they won’t be retuned if you hand-code your query. Intelligenca Query simply brings them as additional rows. What if you don’t want them, such as when you need a group footer? No problem, just exclude them from the query and group on the rest of the rows.

Another cool thing is working with parent-child hierarchies. The MS built-in provider takes this strange design pattern where it dumps all members in a single column, thereby making it virtually impossible to create subtotals per level. With Intelligence Query this issue disappears:

051508_0237_Intelligenc2

I also liked the graphical query designer which is pretty much in par with the Microsoft graphical MDX Query Designer. On the downside, some outstanding work is required to handle parameters and synchronize the text query with the graphical designer.

If the SSAS built-in provider is driving you nuts, I encourage you to take a look and evaluate Intelligenca Query.

SSAS HTTP Connectivity and SharePoint

/0 Comments/in /by

Scenario

You won’t enable SSAS HTTP connectivity via a SharePoint site.

Issue

You followed the instructions in the Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows Server 2003 whitepaper but you get ” Failed to start monitoring directory changes” error when you try to access the SSAS server by its pump URL, such as http://www.adventure-works/olap/msmdpump.dll.

Solution

Ignore the step where you had to create an olap pool and assign the OLAP vroot to it. Instead, assign the vroot to the same IIS application pool as the one the SharePoint site belongs to, such as SharePoint -80. In addition, make sure you enable the web extension to the pump dll, as explained in the above article.

If you have created non-domain Windows accounts on the server for testing, make sure that the server and local Windows accounts (the one the user will use to log in to the user machine) are synchronized, that is, have the same name and password. For example, if I create a non-domain local account Bob on the server where SSAS is installed, Bob must log in to his machine as Bob and both accounts (user and server) must have the same password.