Direct Reports

Double Nickels on the Dime (Leaving the Reporting Services Team)

Brian Welcker — Thu, 13 Dec 2007 09:49:00 GMT

The rumors are true. I will be leaving the Reporting Service team after 7 years and SQL Server after 10 years. Here is the message that I sent to my team: It first dawned on me about 3 months ago that I had been in the same position for close to...(read more)

Transmissions from the Satellite Heart (What's up with Report Builder?)

Brian Welcker — Wed, 12 Dec 2007 00:33:58 GMT

Hopefully, you have gotten a chance to work with the latest CTP of SQL Server 2008 Reporting Services . But if you have, you may be a bit puzzled by the report design options in the CTP. Specifically: Report Builder looks just like it did in SSRS 2005...(read more)

Tribal Thunder (SharePoint White Papers Available)

Brian Welcker — Tue, 11 Dec 2007 10:29:00 GMT

After many months of working on getting them posted, I'm excited to let you know that two whitepapers on the integration between SQL Server Reporting Services and SharePoint are now available. SQL Server 2005 Integration with Microsoft SharePoint...(read more)

Everybody (Why Leave IIS?)

Brian Welcker — Tue, 04 Dec 2007 23:34:03 GMT

If you have gotten a chance to try an early build of SQL Server Reporting 2008 Reporting Services, you know that one of the changes in the product is the removal of the Internet Information Services (IIS) dependency. I have gotten some questions about...(read more)

Younger Than Yesterday (ReportViewer in VS 2008)

Brian Welcker — Mon, 26 Nov 2007 20:04:53 GMT

As most of you probably know by now, Visual Studio 2008 is now available. What you might not have noticed is a new version of the ReportViewer control and local /embedded report editing experience. Included with VS 2008 are the following new features...(read more)

Laser Guided Missiles (Report Localization through Parameters)

Brian Welcker — Wed, 11 Jul 2007 18:09:00 GMT

As localization is not a built-in feature for Reporting Services, people have tried a a variety of techniques for localizing reports. One technique is to use the LocID propertie to create a version of the RDL in each language. Another approach is to have...(read more)

Marquee Moon (Field References in Page Headers and Footers)

Brian Welcker — Wed, 20 Jun 2007 09:57:00 GMT

One of the questions that I get often is about putting database fields in their SSRS report page header and/or footer. Note that putting fields in the page header and footer is not the same as putting a data region in these sections. Because of the...(read more)

Personal Best (The BI Survey)

Brian Welcker — Sat, 16 Jun 2007 22:26:00 GMT

Some of you may be familiar with Nigel Pendse’s OLAP Report . This year, the survey is broadening to include more BI functionality and is being renamed "The BI Survey". If you have some time, take a moment and fill out the survey . We (and the other BI...(read more)

When I Was Born for the 7th Time (Thoughts from TechEd)

Brian Welcker — Sat, 16 Jun 2007 21:46:00 GMT

Last week I attended TechEd in Orlando, Florida . Here are my thoughts on the event. This year was TechEd's 15th anniversary. I realized on the way down that I was attending my 10th consecutive TechEd. When I started attending, TechEd was about 2,000...(read more)

Gone Glimmering (Fix for Visual Studio Hang)

Brian Welcker — Sat, 09 Jun 2007 00:48:26 GMT

I am very happy to announce that a fix has been released for the notorious "Visual Studio cannot shut down because a modal dialog is open" bug. I know that many of you are getting this daily when using the SQL Server 2005 Report Designer. You can read the KB article for the fix here. Currently, you will need to get the fix from Microsoft Customer Support but we are working to get this posted publicly.

Perfect Prescription (OneNote is Really Cool)

Brian Welcker — Thu, 07 Jun 2007 00:56:00 GMT

I think that you might be surprised how long it takes people within Microsoft to adopt new software. While there are some software that we are "strongly encouraged" to adopt <g>, what applications we use on a day to day basis is generally up to each one of us. I'm usually so busy trying out the latest features of my own product that I don't have time to try out the other stuff that is coming out from around the company.

Take OneNote, for example. Although it has been in Office for two versions, I never gave it a try. My original reluctance was basd on the mistaken assumption that it was primarily for tablet users. After that, I just never had the bandwidth to learn how to use it. So I stuck with Outlook, Word, or Notepad for taking notes. Since Office 2007 was released, several of my team members started calling me a luddite for not giving it a try so I took the dive. And you know what? It's really cool. I'm sure I'm not using half of the features but just the ability to not worry about where to save stuff makes taking notes a lot easier.

So, I'm on my way to TechEd writing the notes for my presentations in OneNote. Even this blog entry was written in OneNote then pasted into Live Writer (which is a very nice tool in itself). Now if domestic flights only had Internet connections...

Dreamy (Reporting Services at TechEd)

Brian Welcker — Mon, 04 Jun 2007 19:28:00 GMT

Unlike previous years, I decided not to attend the first day of TechEd. This means that I missed BobMu's keynote but you can read about the details from many of the TechEd bloggers.

If you read the press release from TechEd today, we announced the official name for the next release of SQL Server - SQL Server 2008 (so long, Katmai). You can also download the first CTP. While I would encourage you to try some of the new features in the CTP, you are going to be a little disappointed if you are looking for new Reporting Services features. We have scheduled our first set of new features for the next CTP - so stay tuned for a few more months. If you attend one of our TechEd sessions, you will get a preview of what is coming.

Our second big announcement from TechEd was that we have acquired the data visualization products of Dundas Software. Many of you have been using the enhanced chart for Reporting Services along with the gauge and map custom report items. We realized the importance of data visualization components for reporting so we purchased this codebase for inclusion in future versions of SSRS. We are working on the integration right now so I can't promise how much will make it into SQL Server 2008.

As for TechEd itself, I'm heading out tomorrow. Feel free to stop by the Business Intelligence part of the Learning Center and say hi. If you are looking for some Reporting Services related content, try these sessions:

BIN 305 - Applied Microsoft SQL Server 2005 Reporting Services
DAT 201 - The Next Release of Microsoft SQL Server: Overview
BIN 202 - Third Annual Business Intelligence Power Hour
BIN04-TLC - Reporting Services Rendering Tips and Tricks
BIN 306 - Microsoft SQL Server 2005 Reporting Services: Advanced Report Design
BIN 318 - Reporting Services Futures: Report Authoring for Information Workers
BIN05-TLC - Integrating Microsoft SQL Server 2005 Reporting Services and Microsoft Office SharePoint Server 2007
BIN15-TLC - Reporting Services Programmability
BIN 306R - Microsoft SQL Server 2005 Reporting Services: Advanced Report Design

Tago Mago (New Version of Radius)

Brian Welcker — Sun, 03 Jun 2007 08:12:59 GMT

I wanted to give some props to my friends at 90 Degree Software (Vancouver, Canada) on the latest release of their report authoring tool, Radius. Radius is a report design tool based on the Reporting Services Report Definition Langauge (RDL). If you are looking for something with more flexibility than Report Builder but a little friendlier than Report Designer, Radius is a good choice. With each release, they have added support for more of SSRS Report Definition Language and the ability to share pieces of RDL (queries, parameters, data regions, etc.) is really cool.

At the End of Paths Taken (Modifying Where Clause when All Parameter Values are Selected)

Brian Welcker — Sat, 19 May 2007 23:32:00 GMT

With the return of “Select All” in Service Pack 2, all multi-value parameters allow the user to pick all of the available values. But what if you want to remove the predicate from your WHERE clause instead of generating a long set of values in the IN predicate?

You can accomplish this is via an expression-based query and an internal parameter.

First, create a second parameter in your report. It should be have a new name, multi-valued, use the same valid values query and the default values set to the valid values query as well. In addition, mark the "internal" flag in the parameters dialog so it is not displayed to the user.

Next, change your main report query to an conditional expression that compares the number of selected values to the number of available values (which will always be selected in the new parameter). For example:

="SELECT Hour, Month, Year, Username, Sitename, Cookie FROM dbo.GlobalIISLog " & IIF(Parameters!ComputerName.Count = Parameters!AllComputerNames.Count,"", " WHERE (sComputername IN (@ComputerName))")

I have attached a sample report that demonstrates this technique.

Sky Blue Sky (MS BI Conference Notes)

Brian Welcker — Sat, 19 May 2007 19:53:49 GMT

Wow, what a show! I spent this week recovering from the first ever Microsoft Business Intelligence Conference. Conferences in downtown Seattle are always fun because I live in Seattle and don't have to cross the bridge to get to the Microsoft campus. I'd like to thank all of the folks that came up and told me how they were using SSRS and how useful the blog was to them. Here's a quick rundown of the show highlights for me.

Tuesday

The opening reception has really nice food (big prawns) and lots of people that I haven't seen in years.

Wednesday

During the first keynote, Jeff Raikes announces our acquisition of OfficeWriter from SoftArtisans. Well, technically, he announced that we acquired the company, which is not the case. We acquired the technology for a future version of SSRS and they will continue to sell the existing product. The details are correct in the press release.
Jeff also announces that the next version of SQL Server, code-named "Katmai", will ship next year. Yes, that is 2008, not 2010 or 2011.
I do a podcast where I talk a little about the conference and Katmai and the upcoming Seahawks season. The rest of the NFC west has improved but so have we. Should be a good season.
My session on Integration of Office SharePoint Server and Reporting Services (white paper here) went well except for the beginning of the demo. I was trying to show how you can configure an instance of SSRS to be in SharePoint Integrated mode. Unfortunately, I have multiple instances of SSRS on the demo machine and I was configuring the wrong one. I finally figured it out but it was a little scary for a few minutes.
Lots and lots of questions about RS Katmai at the exhibit hall reception. I know people are excited to get their hands on it and you will be able to in a couple of months.
The Panorama party at the Space Needle was a blast. From the observation deck, you can just barely make out my house (actually, the cell phone tower beside my house).

Thursday

Ted Kummert, my VP, talks in depth about Katmai in his keynote. Our product manager Francois does a demo of OfficeWriter.
I reprise my "executive dashboard" demo at the BI Power Hour. In my demo, I show a CustomReportItem that I built that allows for interactive KPIs within a grid. By clicking on a cell, you can mark it as good (O) or bad (X). By the end of the demo, the crowd realizes that it is actually a tic tac toe game. I'll post the code over the next couple of weeks.
The PerformancePoint team does a very nice "Deal or No Deal" game show and manage to get Bill Baker to put on a blonde wig. Patrick Husting posted some nice pictures from the session.
Cool attendee party at the Experience Music Project (aka the Jimi Hendrix museum).

Friday

Steve Ballmer does a nice job (as usual) on his keynote by articulating the importance of BI and how Microsoft's commitment to this space.

Watusi (SSRS Management Tools Changes for Katmai)

Brian Welcker — Tue, 24 Apr 2007 17:52:00 GMT

For Katmai we are considering the removal of namespace management (folders, reports, data sources, models) from the Reporting Services Add-in for SQL Server Management Studio (SSMS). In other words, we are considering removing the 'Home' folder under the Reporting Server node in SSMS.

Why the change?

Customer feedback and usage data indicates that Report Manager and/or SharePoint are the tools of choice for managing the Report Server namespace, rather than the SSMS add-in. The design constraints of SSMS mean that any new namespace functionality is significantly expensive to implement, specifically adding support for the namespace in SharePoint integrated mode. For Katmai we want to invest in SharePoint and Report Manager for namespace management and focus on SSMS as a server-level management tool.

This means that the namespace management functions that are not available in Report Manager (Model ClickThrough and Model Item Security) will be added to Report Manager (they are already in SharePoint). Job Management, configuring System properties, and administering Roles will be moved to SSMS. In addition, SSMS will be updated to work in SharePoint mode.

If you have feedback about these changes, please feel free to comment.

When Your Heartstrings Break (ReportViewer Controls SP1 Redistributable)

Brian Welcker — Wed, 28 Mar 2007 08:37:00 GMT

For those of you who are incorporating the ReportViewer control in your WinForms or ASP.NET applications, I'm happy to announce the availability of a new ReportViewer redistiributable package. This package contains all of the fixes to the controls that were in Visual Studio 2005 Service Pack 1 as well as a few that were discovered after SP1 was released.

There are two download locations:

Full Installation (for customers who don’t have the original package on their machine)
http://www.microsoft.com/downloads/details.aspx?FamilyId=E7D661BA-DC95-4EB3-8916-3E31340DDC2C&displaylang=en
Upgrade (for customer who have the RTM version of the package on their machine)
http://www.microsoft.com/downloads/details.aspx?FamilyId=35F23B3C-3B3F-4377-9AE1-26321F99FDF0&displaylang=en

Pocket Symphony (Enabling SSRS Feature in non-default Site Collections)

Brian Welcker — Thu, 22 Mar 2007 06:42:00 GMT

After installing the Reporting Services Add-in for Integration with SharePoint, you may find that the Reporting Services features are not visible in your SharePoint web sites. Why does this happen? Because when the add-in is installed, it is only activated for the sites in the default Site Collection.

Steps to resolve:

Bowse to the 'Home' or root URL of the site collection.
Click on Site Actions -->Site Settings --> Site Collection Features.
Choose the "Reporting Services Integration" feature, under the list of features for Site "Collection Features".
Press the "Activate" button.

Note: You will need to be owner of the site collection in order to view the "Site Collection features" link.

Candy Apple Grey (Installing SSRS and Windows Vista)

Brian Welcker — Tue, 20 Mar 2007 02:34:00 GMT

It's a bit tricky to get Reporting Services (including the report server that comes with SQL Express with Advanced Services) to work on Windows Vista. The key is to turn on the right set of IIS components in the "Turn Windows features on or off" tool in the Vista control panel. Here is the list of required IIS features you need to add for Reporting Services to work:

Web Management Tools

IIS 6 Management Compatibility
IIS 6 WMI Compatibility
IIS Metabase and IIS 6 configuration compatibility

World Wide Web Services

Application Development

.NET Extensibility
ASP.NET
ISAPI Extensions
ISAPI Filters

Common HTTP Features

Default Document
Directory Browsing
HTTP Redirection
Static Content

Security

Windows Authentication

Note that IIS7 in Vista Home Premium does not include Windows Authentication functionality so it will not work with SSRS.

You will get a compatibility message when you start to install SQL Server 2005. This is to let you know that you will need to install SQL Server 2005 Service Pack 2 after the installation finishes. You should also read Section 5.5 of the SQL Server 2005 Readme (Reporting Services Considerations for Windows Vista) for known issues and limitations.

We Were Dead Before the Ship Even Sank (SSRS Reports on SharePoint Lists)

Brian Welcker — Wed, 14 Mar 2007 06:11:00 GMT

Now that we have released the SSRS integration in MOSS 2007 / WSS in SQL Server 2005 Service Pack 2, people often ask whether they can build reports against data in SharePoint lists. Although this functionality is not built into SSRS, if you are willing to go pick up a 3rd party component, I have heard of several customers successfully using the Enesys RS data extension for SharePoint lists. If you want to go down the free software path, you might also check out this blog from Teun Duynstee. We will consider native support for lists in a future release.

Propeller (SP2 is Released!)

Brian Welcker — Mon, 19 Feb 2007 20:57:00 GMT

It's been a long time coming, but SQL Server 2005 Service Pack 2 is now available. See http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx.

Roots and Crowns (Too Many Data Tools?)

Brian Welcker — Sun, 18 Feb 2007 05:14:00 GMT

Sometimes I'll get a question like "Why would I use Reporting Services when I can use Excel?" or "Why does Microsoft have so many tools for building reports?" The underlying assumption behind these questsions is that a limited number of applications should be are able to work with data. IMHO, I think that working with data is something that should be included in every application, not just limited to "BI tools".

I look at interacting with data as a capability similar to formatting text or spell checking. No one asks "Why do I need to format text in PowerPoint when I have Word?" or "Who would need a spell checker in OneNote?". Applications can share a common set of capabilities but work in very different ways to achieve different results. Can you build a presentation in Word? Sure, but it's not optimal. Could you use PowerPoint to make a network diagram? Sure, but once again, it's not optimal. Anyone who has used Report Designer or Report Builder know that they feel different from Excel and make certain types of outputs easy to do (say, repeating groups of forms). At the same time, if someone feels comfortable with a working with a spreadsheet, they shouldn't feel compelled to use another tool just because they want to add data to it.

What should be common is how people work with data and what services they get around that data. Having every application work with data in a different way is like having every application with it's own way to edit text. In this area, we have a lot of work to do. If we can unify around a common set of experiences with data, I expect very soon that these features will show up everywhere.

Wincing the Night Away (Tips for SSRS in SharePoint Mode)

Brian Welcker — Mon, 05 Feb 2007 00:53:00 GMT

Here is some useful information about using SSRS in SharePoint Integrated mode (included with SP2):

Installing the SharePoint Add-In

If you plan to upgrade from a previous version of the add-in, you should uninstall the previous add-in then install the new one. After installing the new one, you will need to perform the RS configuration steps in SharePoint Central Administration again.
If you have trouble with the install, take a look at the log files. In addition to the MSI install log, the Add-In also creates another log file named RS_SP_<number>.log. Both logs are found in the "<Drive>:\Documents and Settings\<User_Name>\Local Settings\Temp" folder.
There is a known issue with the naming ReportServer.<culture>.resx files that are install as part of CTP3 in each SharePoint web sites' App_GlobalResources folder. After installing CTP3 this issue manifests itself as the following errors when you try to access any SharePoint web site:

Server Error in '/' Application.
Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed.

To solve this, check the following in IIS Manager for each SharePoint web site:

Explore into <Web site>\App_GlobalResources

Do you see any Reportserver.<culture>.resx files (where culture is a string like EN / FR/ CS etc.)?

If yes, then delete all Reportserver.<culture>.resx except for ReportServer.resx file.

Deploying Reports to Report Server that is in SharePoint Mode

Use the updated Report Designer that ships with SQL 2005 SP2 CTP.
Deployment paths need to change for deploying to a SharePoint Document Library with settings something like this:

TargetDataSourceFolder: http://<MyServer>/<mySharePointsite>/<my_DataSource_Folder>/
TargetReportFolder: http://<MyServer>/<mySharePointsite>/<my_Report_Folder>/
TargetServerURL: http://<MyServer>/<mySharePointsite>/

If you see a login screen when deploying reports, inspect the web.config fle of the SharePoint web site for the following entry:

<location path="_vti_bin/ReportServer/ReportServiceAuthentication.asmx">
    <system.web>
      <authorization>
        <allow users="*" />
      </authorization>
    </system.web>
</location>

Make sure there is only one such entry in the web.config file. Delete any duplicates. Next retry deploying. This is a known issue in the CTP release that we are fixing for RTM.

The Destroyed Room (Hyperion Essbase and Reporting Services White Paper)

Brian Welcker — Sat, 06 Jan 2007 00:30:00 GMT

One of the new Reporting Services features of SQL Server 2005 Service Pack 2 is integration with the Hyperion Essbase 9.3. Similar to building reports on SQL Server Analysis Services or SAP NetWeaver BI, this integration includes a new .NET data extension and a new query designer for Report Designer. If you are interested in how this works, you can take a look at this newly published white paper.

By the way, you also can download the final CTP for SP2 to give this feature (along with the new SharePoint intergation) a try.

Today! (Cleaning up Reporting Services SharePoint Add-In Setup Problems)

Brian Welcker — Thu, 09 Nov 2006 21:28:00 GMT

Some people have had problems during installation of the new Reporting Services Add-in for Microsoft SharePoint. If the add-in setup fails while to add the Reporting Services feature to the sites, some of your SharePoint sites will no longer work. You can tell if the custom action failed by looking for a file named RS_SP_#.log in your %temp% directory. If you have a log file there and your install failed, you will need to manually clean up your web.config files to get SharePoint up and running again.

First, you will need to locate the SharePoint virtual directories on your server. Typically these are located at c:\inetpub\wwwroot\wss\VirtualDirectories. This folder contains several folders with names which are numbers representing the ports used by your SharePoint sites. In each folder you will need to remove the following entries from the web.config file:

and

Once you remove these lines, save the file. You must do this for every web.config file under the above directory.

To get around the issue, you need to add the user who is running setup as one of the Site Collection administrators before you run the setup. You can do this though the Site Adminstration page.

This is a known issue with our CTP2 setup and has been addressed in CTP3.