Bob's SQL Reporting Services Blog

Sorting the values in parameter dropdowns in Report Builder

In SQL 2005 the semantic queries generated by Report Builder do not support sorting. This is not a problem in the report itself, because all sorting is defined and implemented in the report definition instead of the query. However, for parameter dropdowns there is currently no solution: if the query doesn't sort the data, there's no way in RDL to define the sort you want. As a result, the values in your parameter dropdowns will sometimes be scrambled.

If you are interested, the attached C# project contains an updated version of the custom data processing extension I shared in a previous post for enforcing query timeouts, This version adds the ability to recognize lookup queries generated by Report Builder (e.g. those generated for parameter dropdowns), and append an appropriate ORDER BY clause to get the data back in the right order. Since the order of the result data is not defined in this release, it should not break anything, and since it uses a very strict Regex to recognize the lookup queries, it should not affect performance of any other queries. This implementation will sort dropdown items in exactly the same order as they appear in the Filter dialog in Report Builder, whether they are attribute values or entity instances (including the use of an entity's SortAttributes if defined).

To try out the sample, download the attached ZIP file and follow the same instructions as before, Please note that, as I mentioned before, this approach exercises an unsupported feature, so if you run into problems and ask MS Support for help, they will tell you to go jump in a lake. :)

Filtering on totals in Report Builder

Since releasing Report Builder almost two years ago, we've found that many users run into difficulty at some point with filtering on totals. A classic example of this is, "show me a list of customers with more than $5000 in Accessory sales." "Accessory sales" is a typical slice-and-dice filter, because it just eliminates detail or measure values (sales). It means, "include only the sales for products in the Accessory category." "More than $5000", on the other hand, is not a typical slice-and-dice filter. It means, "include only the customers for which the sales total is more than $5000." In other words, the filter does not eliminate detail or measure values (sales), it eliminates groups instances (customers) based on a detail or measure total.

Filtering on totals can be very handy at times. It is possible in an Excel PivotTable using the "Value Filters" context menu on the row or column labels (try it -- it's fun). It eliminates items from the row or column axis based on the value totals at the outermost level. Filtering on totals is also possible at any level in Report Designer by adding group filters, although this is not always recommended, since it may mean requesting much more data in your query than your report really needs to display.

Filtering on totals is also possible in Report Builder, but (a) there is only limited support for it, (b) it takes some training to ensure you build the right filter, and (c) in the case of RB-over-AS, the performance of the resulting queries will probably be unacceptable. Why is filtering on totals so hard right now? Because in this release RB was primarily designed and optimized for relational/operational reporting. It has a number of innovative and powerful features that are especially valuable in this space (and unavailable in typical OLAP reporting, e.g. Excel). The reverse is also true: some features typical in OLAP reporting have only limited or no support in this release of RB. Filtering on totals is one of those features. There is still a large area of overlap, though (i.e., the extremely common "slice-and-dice" scenario), which is why we offer the option of RB-over-AS. However, users should realize that in this release there will be some challenges/limitations. Specifically, filtering on totals when using RB over AS will rarely result in acceptable performance.

That said, let me go through a couple of examples to help explain what is possible, and what to watch out for.

Example 1: A botched report
The report we want is, "show me a list of customers with more than $5000 in Accessory sales" (same as above). Conceptually this is fairly easy to think about, so many users will assume it must be easy to build. So they'll just follow their nose and build a report that has all the parts they think it needs, then hit "Run" and expect the right answer, like this:

1. Create a new table report using the AdventureWorks sample report model
2. Add Customer->Customer Name to the report
3. Add Customer->Sales Orders->Sales->Sum Line Total to the report
4. Open the Filter dialog
5. Add Customer->Sales Orders->Sales->Product->Product Category to the filter, and set it to "Accessories"
6. Add Sale->Line Total to the filter, and set it to "greater than 5000".
7. Close the filter dialog and run the report

Looks like it should do the job, right? Nope. This report actually returns no data. What went wrong?

Well, there are two problems. The first is that step 5 caused RB to change the primary entity of the report from Customer to Sale, based on the fact that the little checkbox at the bottom of the filter dialog was checked. Changing the primary entity ensures the filter condition will filter all the values added to our report, not just toss out some of the rows. This would be perfect if all we were doing was slice-and-dice filtering. However, we are trying to filter on a total at the same time. This change in the primary entity means that the context of the report filter is now Sale instead of Customer, so the condition added in step 6 means "include only individual sales where Line Total is greater than 5000" (notice that this change is reflected in the text at the top of the filter dialog, and later in the filter description on the surface of the report). As it turns out, there are no individual sales of a product in the Accessory category where the line total is greater than 5000, so we get no results.

The second problem is that in step 6, we selected Line Total instead of Sum Line Total. This actually doesn't make a difference in this case because of the effect of the first problem, but it was nevertheless an issue because we weren't thinking about the fact that we wanted to filter on an aggregate of Line Total in some other context (Customer), not the individual Line Total value attached to a single Sale. We shouldn't have expected to specify both filter conditions in the same place.

Example 2: Getting it right
This report is possible in RB using the approach described below, and has reasonable performance over a relational database (on AdventureWorks on my laptop, this report returns in <2 seconds). Be warned, however, that for a number of reasons, running a report that uses this approach over AS will have performance ranging from disappointing to tragic.

To do the same report as Example 1 correctly, we need to avoid making Sales the primary entity, because that would keep us from being able to specify a filter at the Customer level. We can accomplish this by applying the slice-and-dice part of our filter directly to the displayed field instead of to the overall report. We can then reuse the filtered field to create a report-level filter condition in the context we want (Customer).

1. Create a new table report based on the AdventureWorks sample report model.
2. Add Customer->Name to the report
3. Add Customer->Sales Orders->Sales->Sum Line Total to the report
4. Right-click on the Sum Line Total field in the table, and choose Edit Formula (the Formula dialog opens)
5. Double-click on the field reference in the formula to expand it
6. Click on the link to add a filter on Sales
7. Add Sale->Product->Product Category to the filter, and set it to "Accessories"
8. Click OK to close the Filter dialog.
9. Check the box at the bottom of the Formula dialog to create a custom field using this formula, click OK, and name the field "Accessory Sales".
11. Open the main Filter dialog
12. Add the new "Accessory Sales" field to the filter, and set it to "greater than 5000"
13. Close the Filter dialog and run the report

Result: A quick 1-page report showing customers with more than $5000 in accessory sales.

So, that's great! Does that mean RB supports any kind of filtering on totals? No. You can filter on totals as long as (a) all the totals are in a single context, and (b) that context is the primary entity of the report.

If you wanted to filter on totals in more than one context, such as "show me customers grouped by territory, but only show customers with > $10000 in sales and territories with > $1M in sales acoss all customers", it wouldn't work, because you need a filter on the sales total for each customer and the sales total for each territory. Note that this isn't possible in Excel either, since it only allows filtering on totals for the outermost group on an axis.

If you wanted to filter on a total in a context other than the primary entity, such as "show me customer sales grouped by territory, but only show territories with > $1M in sales", that wouldn't work either, because you need to show values for each customer (which means Customer needs to be the primary entity), but filter on totals for each territory (which would not be the primary entity). This is possible in Excel, but note that if you add an outer group like Territory in Excel, you actually can't do the report I described above, because it won't let you filter on a total for an inner group.

Another, more common example is when you want to filter on a total at the lowest level of detail in the report, but that doesn't happen to be an entity context, such as "show me monthly sales, but only months with > $1M in sales." This would work if Month were an entity, but chances are it isn't in your report model, and if not, you can't make it the primary entity, so you won't be able to filter on a total in that context (in this case, the primary entity would probably be Sales Order, where Order Date->Month is defined). This is possible in Excel, because it doesn't distinguish between entity groups and value groups.

So, what kind of filters can you do in RB that you often can't do in OLAP reporting? Actually, a lot. RB supports an arbitrary number of filter conditions, "OR" groups, aggregate filter groups (e.g. "customers with any orders where Year=2004"), row filters that don't filter the displayed values (e.g. "show customers and their total sales, but only those with > $1000 sales in the last month), and independently filtered fields (e.g. the "Accessory Sales" custom field we created in Example 2), including those with filters on multiple levels (e.g. "total sales with Product->Category = "Accessories" and Sales Order->Order Year = 2004"). All of these features help deliver a rich and powerful relational/operational reporting experience, which is what this release of RB was primarily designed to do.

Rest assured, however, that improving support for filtering on totals is still a high priority for the product team.

Requiring Report Builder users to choose a perspective

Some customers have report models that are large enough they do not expect any user to ever request the entire model, and would actually prefer to disallow that option, and require them to choose a perspective instead. There is an undocumented and unsupported feature that provides this behavior in the current release. It is used by Analysis Services when generating a report model over a cube.

The simplest approach is to use the SOAP API's to add a custom property to the report model with name = "MustUsePerspective" and value = "true".

If you wish, you can add a similar custom property to the report model within Model Designer, set the property data type to Boolean, set the namespace to "http://schemas.microsoft.com/sqlserver/2004/11/semanticquerydesign", and whenever you publish the model the corresponding server property will be added automatically.

Launching Report Builder from the command line

A number of people have asked if it is possible to launch RB from the command line. While it is in fact possible, it is an unsupported feature in SQL Server 2005. That said, here's how to do it. You just need to specify what report server to connect to, like this:

     ReportBuilder.exe /s=http://mybox/reportserver

Other supported URL parameters are also recognized when passed on the command line, like this:

     ReportBuilder.exe /s=http://localhost/reportserver "/My Favorite Report"

     ReportBuilder.exe /s=http://localhost/reportserver "/model=/Models/Adventure Works"

Again, this feature is not supported in this release. If you run into problems and call MS product support, they will tell you to go jump in a lake. :)

Using that little checkbox in the Report Builder filter dialog

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 detect the 1:* relationship and create an OptionalMany role from Customer -> Address (and an OptionalOne role coming back). However, this still doesn't make it easy to create a report showing Customers and their Primary Address information. What you really want is a separate 1:1 relationship for each type of address, so you can pull in address information without making Address the primary entity of your report.

The best way to do this I know of is like so:

1. Add calculated fields in the DSV to the Customer table for each type of address, called "xxxAddressType". Each calculation should be a constant (e.g. 'PRI', or whatever the values in your AddressType field are).
2. Define a unique constraint on the Address table by opening the DSV in code view (XML). Find the primary key constraint for the Address table, insert a copy immediately after it, set msdata:PrimaryKey="false" on the new one and add xs:field elements for the CustomerID and AddressType fields. You have to do this in code view because you can't define non-PK unique constraints in the DSV editor.
3. Create relationships in the DSV (using the editor again) from Customer to Address that join CustomerID=CustomerID and xxxAddressType=AddressType.
4. Create roles in the report model from the Customer entity to the Address entity. Bind the roles to the new relationships you just defined in step 3.

[UPDATE] For reference, here's an example of defining a unique constraint (step 2). In the DSV, find the primary key constaint on the same table, which should look something like this:

<xs:unique name="Address_Constraint1" msdata:ConstraintName="Constraint1" msdata:PrimaryKey=true">
   <xs:selector xpath=".//Address" />
   <xs:field xpath="AddressID" />
</xs:unique>

Then make a copy, and modify it like this:

<xs:unique name="Address_Constraint2" msdata:ConstraintName="Constraint2" msdata:PrimaryKey="false">
    <xs:selector xpath=".//Address" />
    <xs:field xpath="CustomerID" />
    <xs:field xpath="AddressType" />
</xs:unique>

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 arguably a bug J, you can get one (or even two!) parameter references into the formula:

1.       Add a field to your filter

2.       Click the field name and choose Prompt

2.5.  Do this if you want two references: Click the operator (usually “equals”) and choose From…To

3.       Click the field name and choose Edit as Formula

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 depends on the field(s) they used to create the group initially. Problems can arise if the user intends to create an entity group with many fields, but instead creates a series of value groups (one for each field). This may make it impossible to get the sorting they want, and can introduce unwanted subtotals levels as well.

For example, a user might drag in the Last Name field as the first step in creating an Employee report. However, if the DiscourageGrouping property for this field in the report model is "false", dragging in this field creates a value group on Last Name. This means that the group cannot be used to display data about individual employees; it can only show totals for all employees with a given last name. So, other fields that are not totals (e.g. First Name) must be added to a new group. If they also do not discourage grouping, a value group will be created for them as well. And so on...

The safest way for the user to get an entity group when they really want one is to drag in the entity itself from the entity list, instead of dragging in a field first. This will create an entity group displaying the DefaultDetailAttributes or IdentifyingAttributes for that entity. This is also a convenient shortcut for adding the fields they almost certainly want to include anyway. As the developer of the report model, you can help users who are unaware of this trick by setting the DiscourageGrouping property in the report model on any field for which users are unlikely to want a value group. They can still get one in their report by using a custom field that simply references it, but the default behavior will give them an entity group instead.

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:

1. Open and build the attached C# project in VS.
2. Copy output assembly to the ...\ReportServer\bin folder.
3. Add the config entries indicated in the NewConfigEntries.txt file (included in the project).
4. Use Report Manager to edit the properties for the data source your report model is bound to, and change the connection type to "Microsoft SQL Server (timeout)".
5. Run a report against the report model that has a long-running query.
6. Notice that the database query and report processing are cancelled after the timeout specified in the config file. This occurs regardless of the setting in the report (which RB always sets to 0=infinite).

Because mapping the SQL semantic query engine to something other than the built-in SQL data extension is technically not supported, the proverbial disclaimer applies: If you try this at home and it works, great; if something bad happens and you ask MS product support to fix it for you, they will tell you to go jump in a lake.

That said, hopefully some will find this helpful.

UPDATE: The new config entries identified in the attached file are based on a recent internal SP2 build. If you aren't running SP2 (not likely, since it hasn't been released yet), ignore the SemanticQuery entry and just copy your existing one, changing the name attribute to match the name of the new custom data extension.

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 several changes you can make to your models without breaking reports, as well as one that will break reports. Generally speaking, though, I believe you can change anything except the following in a report model, and existing reports will still run:

Entities: ID and Inheritance properties

Attributes:.ID, DataType, and IsAggregate properties, and entity membership

Roles: ID and Cardinality properties, entity membership, related role and its cardinality and entity membership.

Note that if you change attribute expressions, entity/attribute/role bindings, or the definitions of tables and columns in the DSV (e.g. by modifying a Named Query), you may get different results, but the reports will still run.

Obviously, you can organize and re-organize things in and out of folders as often as you want, and all your existing reports will still load and run just fine.

Also, if you want to "deprecate" a field, you can use the Hidden property to exclude it from the design-time experience in Report Builder. This will not affect existing reports.

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 issues if the second database has different schema qualifiers. The problem is that, by default, the Data Source View wizard creates a DSV that includes schema qualifiers in the bindings. If those qualifiers are not necessary (i.e. the bindings can be fully resolved if a default database is specified in your data source connection string), you can make your report model work for the second database by opening the .dsv file in a text editor and use Find/Replace to simply remove all the schema qualifiers.

Test it out against the old and new databases, and you should be good to go.

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 they add tremendous interactivity and depth to every single report you create, without an ounce of effort by the report author. Another, often overlooked advantage is the opportunity to greatly enhance the drillthrough experience across all your reports, by designing rich, custom Clickthrough report templates to be used instead of the built-in ones.

The restrictions on Clickthrough report templates are few. Most importantly, they do not need to be designed in Report Builder (although it's usually a good idea to start there since RB can do some of the basic wiring for you). Instead, you are free to use the full power of RDL in the Visual Studio Report Designer to build rich, powerful detail and summary reports. The exciting part is that your investment here will be leveraged so widely by report authors and consumers. Every Report Builder report will contain Clickthrough links that lead users to these reports to display key information about the data they are looking at.

One final point -- we lamentably failed to include support for external hyperlinks in the initial release of Report Builder. However, this restriction does not apply to Clickthrough reports designed in Report Designer, so building custom Clickthrough templates is a great way to link the Report Builder experience to external applications (e.g. click here to edit this Customer record in MyCRMApp, or click here to process this Invoice, etc.).

Okay, one final final point -- when designing a single-instance Clickthrough report, make sure you use a data region (e.g. List) even though the report will only display values for a single record. Using top-level textboxes without a data region can make the report slower.

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 chart)

- Always bind the data region to a report model query

- Make sure the data region groups (including the Details group, which must always be present) *exactly* matches the groups in the query, both in number and sequence (left-to-right for table, rows then columns for matrix, category then series for chart).

If you can get the report loaded, RB will add the appropriate Clickthrough links when you save it back out.

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 credentials are sufficient. There are three ways to satisfy this constraint:

1. Client and server have a trust relationship (e.g. same domain), the user is logged into the client using a domain account with permission to access RB files on the report server, and Windows authentication is enabled on the ReportBuilder folder in IIS.

2. User has stored credentials on the client for the report server, using either the "Remember my password" checkbox in Internet Explorer or the Stored User Names and Passwords control panel, those credentials are Windows credentials, and Basic authentication is enabled on the ReportBuilder folder in IIS. This option is recommended only if you are using SSL, and only if the requirement that users store their credentials is acceptable. Note that if they do not, the application download will simply fail -- they will not be prompted to supply any credentials.

3. Anonymous access is enabled on the ReportBuilder folder in IIS. This may make your server vulnerable to denial-of-service attacks if it is not operating in a trusted environment.

Note that the authentication settings on the ReportBuilder folder in IIS are usually inherited from the report server virtual root.