Creating a role to one of several related items

I call this the Primary Address problem, because a classic example is when you have a Customer table and an Address table, and each customer can have many addresses (Primary, Billing, Shipping, etc.), but no more than one of any given type. If you have a FK constraint defined, the report model wizard will automatically…


I want to reference a parameter (twice!) in a formula filter condition

In this release of Report Builder there is no direct support for referencing parameters in the Formula dialog. They will show up if you edit a prompted filter condition as a formula, but there is no way to directly insert a parameter reference.   However, with a little trickery and relying on some behavior that is…


Getting grouping right in Report Builder

One thing Report Builder users need to be aware of when building a report is the difference between an entity group, which displays exactly one instance for each row in the underlying table, and a value group, which displays one instance for each distinct value of a particular field. The type of group they get…


SQL Server 2005 SP2 (CTP2) is now available

Huge new feature: integrate your RS 2005 report server with SharePoint 12! What’s new in SP2? Download here.


Enforcing timeouts on Report Builder queries

I’ve attached a sample C# project illustrating how to create a custom data processing extension (more info) that enforces a maximum timeout on any SQL queries submitted to it, including those generated from Report Builder. To try it out: Open and build the attached C# project in VS. Copy output assembly to the …\ReportServer\bin folder. Add…


Evolving your report model over time

Many factors combine to make report models highly likely to change and evolve over time. Sometimes the underlying schema changes. Sometimes new stuff is added. Sometimes you just want to improve how the schema is presented to users. Report models are designed to accommodate just these kinds of changes. One of my earlier posts mentions…


Creating a report model that can be used against multiple databases

Sometimes it is useful to create a report model that can be used against multiple databases that have the same structure, but reside on different servers and/or have different schema qualifiers. Uploading a second copy of a report model and pointing it at a different database is certainly easy enough, but you can run into…


Clickthrough reports are the key to integration

Clickthrough reports are the special kind of drillthrough reports created by Report Builder. They are “special” because they are linked to the parent report automatically, and are generated on the server from special template reports (or from a built-in template if you haven’t specified your own yet). The key value behind Clickthrough reports is that…


How to get RB to load a report created in VS Report Designer

Loading an arbitrary Report Designer report in Report Builder is not supported in SQL Server 2005. However, if you are trying to make it work for some reason, here are a few tips: – Don’t use page headers or footers – Don’t use rectangles or lists – Only use a single data region (table, matrix, or…


How to enable Report Builder for non-domain users

Report Builder uses a client technology called ClickOnce to download and launch the application files. This component runs outside of Internet Explorer, so it cannot leverage any credentials that may have been collected by IE to access the report server. It can only access and download the Report Builder application files if the user’s default…