Bob's SQL Reporting Services Blog

Create short lists using the LookupSet function

Bob Meyers MSFT — Mon, 18 Jun 2012 19:19:00 GMT

Sometimes you have a short list of values that you really don't want to show on individual rows; you'd rather just concatenate them into a list, separated by commas, and put them all in a single row.

For example, suppose instead of listing the states in each U.S. Census region each on their own row, I'd like to see something more like this:

You can do this in a Reporting Services report in SQL 2008 R2 or later, using the built-in LookupSet and Join functions together, like this:

=Join(LookupSet(thisfield, otherField, returnField, datasetName), delimiter)

Note that the datasetName will be the same as the dataset to which the table is bound. So, for example, in the above case I did the following:

=Join(LookupSet(Fields!Region.Value, Fields!Region.Value, Fields!StateCode.Value, "StateRegions"), ", ")

If the values are not sorted the way you like them, or they include duplicates, you can use the following very similar functions by inserting them into your report's Code block:

=Code.JoinSorted(LookupSet(...), ...)

=Code.JoinDistinctSorted(LookupSet(...), ...)

The code for these functions is attached.

Hope this helps!

TechEd session: Tips and tricks for building rich reports

Bob Meyers MSFT — Mon, 18 Jun 2012 18:56:00 GMT

Here's a recording of my TechEd 2012 North America session last week in Orlando:

Tips and Tricks For Building Rich Reports

The entire session is based on Report Builder 3.0. All content is relevant to both SQL 2008 R2 and SQL 2012.

Topics covered:

Use a tablix to organize data how you want it
Use colors and graphics to emphasize data
Choose the right visual for what you're trying to communicate
Add clipboard data using an XML data source
Integrate related data using the Lookup function
Display short lists using the LookupSet function
Optimize output for Excel

Hope you enjoy!

When and why the primary entity changes when using a report model

Bob Meyers MSFT — Mon, 27 Feb 2012 18:34:00 GMT

Got a few questions lately on this subject, so I thought I’d post some additional info that may be helpful.

First, a few related links that may be partially helpful:

The philosophy behind Report Builder data navigation (blog post)
Exploring the Primary Entity and Its Related Data (MSDN)
Using that little checkbox in the Report Builder filter dialog (blog post)

Now for the full response.

As stated in the MSDN article linked above, the primary entity represents the “context” or “root” of the report or query. Those familiar with OLAP may like to think of it as the central “fact table” with a host of related “dimensions”. It is the set of detail data to which all grouping and total values are related. It is also the context in which the report filter is defined. In short, all data shown in the report is related somehow to the primary entity.

So, how does one choose the primary entity? Well, you don’t – at least, not directly. When you add the first field to your report, its containing entity becomes the primary entity. All other entities are then rearranged into a tree to show how they are related to the field you selected.

As you add more fields to your report, or change the report filter, the primary entity may occasionally shift. This only occurs in the following cases.

1. You add a field indicating you want to display a lower level of detail.

For example, suppose you started your report by adding a couple of Customer fields – Customer Name and Phone. These both belong to the Customer entity, so Customer becomes the primary entity for the report.

Next, you click on the related entity Orders, and add Sum of Sales Amount. Because this is a total, it does not indicate you want to display a lower level of detail, so the primary entity is unchanged. Also, because your report is primarily “about” Customers at this point, data about Orders with no related Customer is not displayed.

Suppose you then add Order Month from the Orders entity. Order Month is not a total, and a Customer can have many Orders with many different Order Months, so adding this field indicates you want to display a lower level of detail. To accommodate this, the primary entity shifts from Customer down to Orders.

As a result, the report now displays data about Orders and their related Customers, instead of Customers and their related Orders. This may seem like a subtle distinction, but it has some important effects. One is that Customers with no Orders are no longer included, as they were when the report was only about Customers. Conversely, data about Orders with no Customer are now included, though they were not previously. Another effect is that the report filter is now evaluated in the context of Orders instead of Customers. The impact of this will become clearer after the next point.

2. You add a filter suggesting the overall report is about a lower-level set of detail data.

In the example for #1, suppose that instead of displaying total sales for each Order Month, you wanted to filter the report to display only the sales for a particular Month or a particular Store. Typically, this means two things: (1) you want the totals to include only the Orders from that Month or Store, and (2) you only want to see the set of Customers that actually have Orders from that Month or Store (as opposed to a list of all Customers).

When you want both of these restrictions applied (and by default it is assumed that you do), this suggests the report is no longer “about” Customers, but rather about the lower-level set of detail data that relates the Customers you’re showing and the filter you’re trying to apply. In this case, the lower-level data that relates Customers and the filter you’re trying to create is Orders. Hence, the primary entity shifts from Customer down to Orders.

Note: To filter the totals without filtering the set of Customers displayed, you can apply a filter directly to the Sum of Sales Amount field, instead of applying the filter to the entire report. Conversely, to filter the set of Customers without filtering the total sales displayed, you can temporarily uncheck the little checkbox in the report filter dialog. See the related blog post linked above for more information.

3. (Less common) You add a group field to a report that contains only grand totals.

Suppose you clicked on the Orders entity first, and added Sum of Sales Amount. At this point, the primary entity is Orders, but if you run the report, all you see is a grand total of Sales Amount across all Orders. The grand total has not be broken down, or “grouped by” anything yet.

If you then add Customer Name (which is not a total) to the report, this will cause the total you added previously to be broken down or grouped by the new field. At this specific transition point, i.e. when you go from having all totals to at least one group field, the primary entity may shift up, in this case from Orders to Customer. The reason for this shift is to ensure you get the same result you would have gotten if you had added the same fields in the reverse order (e.g. Customer Name, then Sales Amount).

Other than this one case (#3), the primary entity shifts only incrementally in one direction – down – and only as needed based on the fields and filters you’ve selected. In most cases, this behavior provides the most stable experience and most intuitive results for your report. However, when repeatedly adding and removing many fields to/from a report, you may occasionally get into a situation where the primary entity has shifted too far down a particular path, which may make it harder to get a specific result. In that case, removing and re-adding your group fields should move things back into the desired state.

Add Excel-like "color scale" conditional formatting to your reports

Bob Meyers MSFT — Sat, 01 Aug 2009 03:09:00 GMT

I’ve been meaning to do this for a long time, and it looks like David Lean beat me to it earlier this year by posting an incredibly thorough four-part discussion of how to do conditional formatting in Reporting Services. I’ve played with his sample code a bit and put together a variation on his theme that meets some additional requirements, which I have found helpful.

The attached sample code is designed to provide the following functionality (the usual disclaimers apply):

· Convenient support for the standard color scales available in Excel 2007

· Support for arbitrary colors, including named colors (“MintCream”)

· Robust handling of null, error, and out-of-range values

· Robust handling of all integral and floating point numeric types

To use them in a report, do the following:

1. Open the Report Properties dialog (right-click on the space around the report body, choose Report Properties)

2. On the Code page, paste in the attached code

3. On the References page, add a reference, click Browse, locate and select System.Drawing.dll, usually in C:\Windows\Microsoft.NET\Framework\v2.0.50727 (this references allows the code to handle named colors)

The simplest and most common usage of these functions is to create a red-yellow-green “heat map” based on the value being displayed in the text box. This is done by setting the text box’s Fill color to an expression similar to the following:

=Code.ColorScaleRYG(Sum(Fields!SalesAmount.Value), 0, 100000)

The arguments 0 and 100000 are the min and max values for the color scale. In this case, the ColorScaleRYG function is used, which assigns red to the low value, green to the high value, and yellow to the value half-way in between.

The min and max values can also be calculated, like this:

=Code.ColorScaleRYG(Sum(Fields!SalesAmount.Value), Min(Fields!SalesAmount.Value, “Dataset1”), Max(Fields!SalesAmount.Value), “Dataset1”))

The argument “Dataset1” defines the scope in which the min or max value is calculated, which must be a parent scope of the current scope.

Note that these colors are not the pure “Red”, “Green”, and “Yellow” colors defined in HTML/.NET/SSRS, but rather the default values used in Excel which are intended to provide a reasonable background for black text. If you don’t like them, it’s easy to specify your own colors instead.

Here’s a full list of the functions provided:

‘ Convenience functions for standard 3-color scales

public function ColorScaleRYG(value, minValue, maxValue) as string

public function ColorScaleGYR(value, minValue, maxValue) as string

public function ColorScaleRYB(value, minValue, maxValue) as string

public function ColorScaleBYR(value, minValue, maxValue) as string

public function ColorScaleRWB(value, minValue, maxValue) as string

public function ColorScaleBWR(value, minValue, maxValue) as string

‘ Convenience functions for standard 2-color scales

public function ColorScaleRY(value, minValue, maxValue) as string

public function ColorScaleYR(value, minValue, maxValue) as string

public function ColorScaleGY(value, minValue, maxValue) as string

public function ColorScaleYG(value, minValue, maxValue) as string

‘ Arbitrary 3-color scale with interpolated midpoint value

public function ColorScale3(value as object, minValue as object, minColor as string, midColor as string, maxValue as object, maxColor as string) as string

‘ Arbitrary 3-color scale with explicit midpoint value

public function ColorScale3(value as object, minValue as object, minColor as string, midValue as object, midColor as string, maxValue as object, maxColor as string) as string

‘ Arbitrary 2-color scale with optional error color (the core function)

public function ColorScale(value as object, minValue as object, minColor as string, maxValue as object, maxColor as string, optional errorColor as string = "Transparent") as string

Enjoy!

Localizing a report model

Bob Meyers MSFT — Tue, 28 Apr 2009 02:15:00 GMT

Reports and report models can be localized at many levels. Following are some ideas around the current support in SQL Server 2008 for each type of localization.

- Localized metadata at report design time

o Example: Spanish report author sees “Cliente” instead of “Customer” in model explorer

o Report models do not support multiple languages for metadata names in a single model file

o It should not be difficult to build a custom solution to generate localized versions of the model file

§ Original .smdl file + XML file with localized entity/attribute/role names => localized .smdl file

§ Could use either XSLT or minimal code

o If implemented, reports would run against any localized version of the model because IDs are unchanged

o Metadata displayed in the report (such as column labels) would be in the language of the person who designed the report. Since they are merely text values copied in from the model explorer, they would not change at report run time.

- Localized metadata at report run time

o Example: Spanish report consumer sees “Cliente” instead of “Customer” in report column label

o SQL RS reports do not directly support localization of text labels in a report

o Some customers have implemented this using a custom “resource” assembly deployed on the server, and all labels in the report are replaced at report design time with expressions that retrieve the appropriate resource string from the custom assembly (sample)

o This is obviously cumbersome to set up at report design time, but it does work

- Localized data formatting at report run time

o Example: Spanish report consumer sees numeric and date values in the report data formatted as “1.234,56” and “27/04/2009” instead of "1,234.56" and "04/27/2009".

o This is supported by the default number formats available on the ribbon in Report Builder 2.0. In the dialog box, select the “Use regional formatting” checkbox.

- Localized data values at report run time

o Example: Spanish report consumer sees product category “Bicicletas” instead of “Bicycles” in report data

o This is typically done by storing localized values in the database as separate columns or as lookup tables based on user culture.

o Offhand I can’t think of a slick way to do this with report models. If you have some ideas, let me know.

Considerations for a large report model

Bob Meyers MSFT — Mon, 27 Apr 2009 23:58:00 GMT

Customer report models that vary in size from a few to a few hundred entities. Little Northwind with its 10 or so entities comes in around 200K, but we've seen models a hundred times that size (over 20 MB). One of the key drivers of model size is the constraint that you cannot build a query across multiple models, so the tendency is to pull more and more data into the "main" model.

Given that, I thought I’d share a few tips that may help if you want to build, deploy, and use a large report model.

1. Configure the report server allow upload of large files. By default ASP.NET limits the file upload size to 4 MB. You will need to modify this setting if your model exceeds the limit.

2. Consider removing the diagrams from the DSV. Typically the diagrams in a DSV account for ~20% of the uploaded model size. This information is only used by Model Designer, however, so removing it will not affect either the report server or a client like Report Builder. The DSV editor will not allow you to remove the default “<All Tables>” diagram, and if you do remove it, the editor will recreate it with a default layout the next time you open the file, so you will need to remove this diagram from the XML manually before publishing.

3. Define perspectives in the model. Perspectives allow users to self-select a convenient subset of the model at the time they design the report. This reduces download time (only the subset is retrieved by Report Builder) as well as clutter during the design experience. In extreme cases, you may want to require users to choose a perspective. Note that perspectives are not a security feature. Also note that it is possible, though not particularly easy, to change the perspective used in a query after the query has been created.

4. Use model item security. This reduces the subset of the model available to specific users and groups, which in turn reduces the download time and visual clutter for those users. Note that as of SQL 2008 report subscriptions are not supported when using model item security.

If you are using a large report model and have additional tips or questions, please contact me. We'd love to get your feedback.

SQL Server 2008 SP1 available now with important Report Builder 2.0 updates

Bob Meyers MSFT — Wed, 08 Apr 2009 08:28:00 GMT

SQL Server 2008 SP1 is now available, and includes some important updates related to Report Builder 2.0:

- RB 2.0 can now be deployed directly from the report server using ClickOnce. You can also configure whether RB 1.0 or RB 2.0 is launched from the Report Manager and SharePoint UI.

- An important fix to the Report Model Query Designer is included in SP1.

Download SQL Server 2008 SP1

Download Report Builder 2.0 (April 2009)

Help RB 2.0 users find and use shared data sources

Bob Meyers MSFT — Sat, 28 Mar 2009 07:26:00 GMT

Report Builder 2.0 makes it easy for business users to browse for and use centrally-defined and managed data sources. It does this by supporting direct connection to a report server as part of the design experience. It also maintains a list of recently-used data sources that show up at the top of the list each time the user needs to create a new connection in their report. One problem, though, is how to help RB 2.0 users find published shared data sources in the first place.

You can do two things as an administrator to make this easy:

1. Configure RB 2.0 on each client machine with a default report server. This can be done from within the app (see Options->Settings), or pre-emptively at install time by passing the REPORTSERVERURL command-line argument to the RB 2.0 installer (more info). Note that if you are using SMS, this can be done automatically as part of pushing the software out to the client.

2. Set default folder properties on the report server. RB 2.0 looks for two properties on the Report Server that tell it where to find shared data sources and models. If RB has a default report server, and these two properties are set on that server, any data sources or models that RB finds in those folders will automatically show up in the data sources list, in addition to any recently-used data sources. Use the attached scripts and the RS Script Utility to view or set these properties (instructions are in the script). Once the properties are defined, they can also be viewed and modified in SQL Server Management Studio, under Server Properties->Advanced->User Defined.

UPDATE: Adding missing attachment. :)

What is that extra column of 'AB.gAAAA=' values when I run a report model query?

Bob Meyers MSFT — Fri, 06 Mar 2009 20:36:00 GMT

Most people using Report Builder 2.0 with a report model soon ask the question, what is that extra column I get back with strange values like “AC8AAAA=” in it?

Answer: It's an entity key value. If you’re going to create a group on that entity in your report, you should use this field as the group expression. This is especially important if you’re going to create Clickthrough links, because those key values will be used to filter the target report, and filters on keys usually have better performance.

Call for feedback: What are you doing with report models?

Bob Meyers MSFT — Fri, 06 Mar 2009 20:22:00 GMT

Are you using a report model in your organization? The Reporting Services product team wants to hear from you!

What type of organization is using the model (dept/workgroup, small/mid business, enterprise)?
How long have you been using your model?
What is the backend database for your model?
How much data is behind your report model?
Who is responsible for developing/maintaining the model in your organization?
How much effort went into model design before you started using it?
How has your model changed over time?
How easy has it been to change without breaking reports?
How many people use the model to build reports?
What kind of people use the model to build reports (report developers, information workers, etc.)?
What kind of reports is the model most and least useful for?
What are the top three improvements you’d like to see related to report models?

Send me your responses and I’ll forward them to the product team. We build these technologies for you, so your feedback is critical! Thanks in advance for your help!

How to build a Clickthrough link in Report Builder 2.0

Bob Meyers MSFT — Thu, 05 Mar 2009 18:45:00 GMT

As I mentioned in a previous post, due to resource constraints in this release, Report Builder 2.0 is not a strict superset of RB 2005/1.0. One of the features missing from RB 2.0 is automatic creation of Clickthrough links. You can still get them, of course, by creating the basics of your report in RB 1.0, then saving and reopening in RB 2.0. It is also possible to build them manually.

Here's how:

1. Determine what the target entity should be. This is the model entity the Clickthrough link should drill to. For example, if the value being displayed is # Sales Orders, you'll want to drill to the Sales Orders entity.

2. Get the target entity ID. (This is the worst part) Unfortunately, this value is not accessible from within RB 2.0. You can see it in the property grid in Model Designer, or you can get someone who has the RS Script Utility to use the attached script to get a list of all the entity IDs in the model. Keep the list around for reference.

3. Determine whether to show a single-instance or multiple-instance report. If the path from the value’s context to the target entity involves at least one “one-to-many” relationship, then the drill action can return multiple instances of the target entity, so you’ll need to show a multiple-instance report. Remember that the path from a report subtotal to the target entity is always one-to-many. Only use a single-instance report if there are no one-to-many relationships along the path. For example, if the value being displayed is # Sales Orders per Sales Territory, the value’s context is Sales Territory, and the path from that context to the target entity is Sales Territory -> Sales Person -> Sales Orders. Both of those relationships are one-to-many, so this Clickthrough action should use a multiple-instance report.

4. Create a "Go to report" action. Open the property dialog for the Text Box, Placeholder, Chart Series, or other report element that has Action properties, and go to the Action property page. Select the “Go to report” option and use the following settings:

Setting	Value
Report Path	=DataSources!MyDataSource.DataSourceReference
Parameters	Value
rs:EntityID	(Target entity ID)
rs:DrillType	“Detail” or “List” for single- or multiple-instance, respectively
rs:Command	“Drillthrough”
DrillthroughSourceQuery	=Datasets!MyDataset.RewrittenCommandText
DrillthroughContext	=CreateDrillthroughContext()

A few tips:

· Clickthrough links will not work correctly in a “details” scope, i.e. a group with no grouping expression. If you want to use them in a scope that is currently a details scope, you’ll have to change it to a non-details scope by adding one or more grouping expressions that uniquely identify each row of data.

· If the value you are displaying is an expression that involves multiple field references, set the target entity as the entity at the end of the longest common path between them.

· Read up on how creating customized Clickthrough reports can help maximize the value of every Clickthrough link you create.

A better way to model inheritance

Bob Meyers MSFT — Thu, 05 Mar 2009 18:00:00 GMT

I've been playing around with a report model we use internally here in the SQL Server product group. At a conceptual level, the data being modeled makes heavy use of inheritance (EntityA "is a" EntityB), but in working with the model and with Report Builder, I'm finding some significant advantages to using the Role.ExpandInline property instead of the Entity.Inheritance property. As I described in an earlier post, both are options for denormalizing or "flattening" the underlying schema.

How to do it

Using the Role.ExpandInline property to model inheritance is just as easy as, if not easier than, using the Entity.Inheritance property. Remember that when a relationship is defined in the DSV between the parent entity and the child or derived entity, a pair of roles are generated in the model initially and bound to that relationship.

When using Entity.Inheritance to model inheritance, you need to:

Delete the generated roles
Set the Inheritance property on the child entity to point to the parent entity
Set the Inheritance binding property to the relationship defined in the DSV

When using Role.ExpandInline to model inheritance, all you need to do is:

On the child->parent role, set ExpandInline=true.
On the parent->child role, rename the role "As <child entity name>".

Also, make sure the child->parent role has Cardinality=One, and the parent->child role has Cardinality=OptionalOne. These should be true regardless of whether you choose to expand the role inline.

Advantages of using Role.ExpandInline

Beyond the initial convenience in expressing an inheritance relationship using Role.ExpandInline, there are several more substantial advantages I see to modeling the concept this way:

1. You can choose where the ancestor entities' fields appear in the field list of the current entity.

When using Entity.Inheritance, all ancestor fields are automatically inserted at the top of the field list of the child entity. The main problem with this is that the auto-generated Count field for the derived entity is no longer at the top of the list where users expect it to be. Instead, they will see the Count field for the most distant ancestor first, with the Count fields for the intervening entities and finally the current entity scattered further down the field list.

When using Role.ExpandInline, you can move the role that represents the inheritance to at any position in the field list of the current entity (even a sub-folder if that makes sense), and the ancestor entities’ fields will be inserted there.

Example: Suppose we have a Person entity whose field list starts with #Persons (typical). Suppose we also have an Employee entity that inherits from Person. Ideally, the Employee field list will be displayed as #Employees, followed by Person fields, and finally other Employee fields. When using Entity.Inheritance, this is not supported. When using Role.ExpandInline, this is easy – simply move the Employee->Person role immediately below the #Employees attribute in the Employee entity field list, and the Person fields will be “expanded inline” there.

2. You have fine-grained control over which of the parent entity's fields are visible in the current entity.

When there is more than one derived entity, it is common for many fields on the parent entity to be relevant to only some of the child entities. If Entity.Inheritiance is used, there is no way to prevent all parent entity fields from being shown all the time. This can be confusing in the cases where they are irrelevant.

When using Role.ExpandInline, you can use the HiddenFields collection on the role that represents the inheritance to control exactly which fields from the parent entity will be visible in the child entity.

Example: Suppose the Person entity mentioned above has three derived entities: Employee, CustomerContact, and VendorContact. It also contains (among other things) a #Persons field and a LastContacted field. The former is relevant to all Persons, but only when they are being treated as Persons. When treated as a derived entity, the derived entity Count field should be used instead. Also, suppose the LastContacted field is present on Person because it is common to CustomerContact and VendorContact, but it is not relevant to Employee. Ideally, the #Persons and LastContacted fields would be omitted in the Employee field list, while only the #Persons field would be omitted from the CustomerContact and VendorContact field lists. When using Entity.Inheritance, this is not supported. When using Role.ExpandInline, this is easy – just use the Role.HiddenField collection in each child entity to define exactly which parent entity fields are hidden in that context.

3. You have fine-grained control over which non-direct ancestor entities related by inheritance (“uncles”) are accessible from the current entity.

When using Entity.Inheritance, you can use the Entity.DisjointInheritance property to control whether the special “As <entity-name>” pseudo-roles are displayed for treating instances of the current entity as instances of a non-direct ancestor entity related by inheritance. However, this is an all-or-nothing option. If DisjointInheritance is false, all non-direct ancestor entities related by inheritance are displayed in all child entity contexts; if true, none of them are displayed in any child entity context.

When using Role.ExpandInline, you can use the Role.HiddenFields collections to define exactly which inheritance roles from the parent entity are hidden in for each child entity context.

Example: Suppose that by company policy, Employees were allowed to be customers, but not vendors. On the other hand, it was perfectly fine for non-employee vendors to also be customers. Because of these constraints, it would make sense to display an “As CustomerContact” role in the Employee context, but not “As VendorContact”. In contast, we would want to display an “As CustomerContact” role in the VendorContact context. When using Entity.Inheritance, this is not supported. When using Role.ExpandInline, this is easy – just use the Role.HiddenFields collections in each child entity to define exactly which inheritance roles from the parent entity make sense in that context.

4. You can prevent the fields of all descendant entities from being added to the field list for the current entity.

When using Entity.Inheritance, the fields from all descendant entities are always inserted at the bottom of the field list of the current entity. Even with only a small number of derived entities, the field list can quickly become quite long and confusing, since there is no clear indicator which fields are associated with which derived entity. The list may even contain multiple fields with the same name (but different meanings), which would be especially confusing.

When using Role.ExpandInline (on the role from the child entity to the parent entity), the reverse role from the parent entity to the child entity is just a role by default, so none of the fields for the child entity appear in the parent entity context. For clarity, you can rename the reverse role “As <entity-name>”, similar to the pseudo-roles metioned earlier.

Example: Continuing the examples above, the Person->Employee role should be renamed “As Employee”, the Person->CustomerContact role should be renamed “As CustomerContact”, and the Person->VendorContact role should be renamed “As VendorContact”.

5. You can inherit from more than one entity.

When using Entity.Inheritance, you can specify at most one parent entity related by inheritance. This is often not an important limitation, but occasionally the data really demands that an entity inherit from more than one parent entity, because it just makes a lot more sense to present it to the user that way.

When using Role.ExpandInline, there is (obviously) no constraint on how many roles can be expanded. Inherit from as many entities as you want.

Example: Suppose the Employee entity inherits from both Person and ProjectResource. When using Entity.Inheritance, this is not supported. When using Role.ExpandInline, this is easy – just expand both roles.

6. You can change your mind later about which relationships to model as inheritance.

When using Entity.Inheritance, the queries generated against your model contain no explicit navigation between entities related by inheritance. This means that adding or removing inheritance later can break existing queries. Now, technically, adding inheritance later will not break queries if you keep around the old role that the inheritance replaced (hidden of course to avoid confusion). But there is no workaround for the opposite situation – if you want to remove inheritance later, you can certainly add a new role to represent the relationship, but if you keep around the inheritance as well to avoid breaking existing queries, there is no way to hide it, and the resulting behavior will be very confusing to users.

When using Role.ExpandInline, you can change your mind at any time. The queries will be the same whether you choose to expand the role or not, so modeling a relationship as inheritance (or not) will have no impact on either new or existing queries.

Disadvantages of using Role.ExpandInline

At this point, I can only think of one disadvantage to using Role.ExpandInline to model inheritance.

1. Users will not have the “Is A” operator available in the filter dialog.

When using Entity.Inheritance, a special “Is A” filter operator is available in the filter dialog when filtering on entities that participate in inheritance relationships. This operator allows you to test whether a particular instance of one entity maps to an instance of another entity related somehow by inheritance. Direct ancestors are not included in the list of options, as it is assume answer is always “true” in those cases. However, descendents and non-direct ancestors (uncles, cousins, etc.) are listed.

When using Role.ExpandInline, this operator is not available. Instead, the user would need to add the related entity and check if it is “empty” (or null).

Example: Suppose the user wants to create a filter condition that tests whether a particular Person is also a VendorContact. When using Entity.Inheritance, the user could use the “Is A” filter condition operator to do so. When using Role.ExpandInline, the “Is A” operator would not be displayed. The user would have to drag in the “As Vendor Contact” role instead, and check if it is empty (or null).

Conclusion

As you can see, there seems to be a clear winner here. So much so, that we are considering deprecating the current Entity.Inheritance construct in a future release, and creating a new construct that explicitly models inheritance similar to the ExpandInline approach. When/if we do this, we would presumably also remove the one current disadvantage associated with it by adding explicit support for it in the filter dialog.

Report Builder 2.0: Whither report models?

Bob Meyers MSFT — Thu, 05 Mar 2009 17:46:00 GMT

Just a quick post to answer the inevitable question when those familiar with Report Builder 2005/1.0 start playing with Report Builder 2.0:

Whither report models?

The short answer is that they are still available as a data source type, and the report model query designer is included as well, so you can create datasets against them just like any other data source. The part that's missing, obviously, is the ability to build the report layout and the query at the same time by dragging data from the model directly on to the layout surface, as in RB 1.0 (we called this "integrated query design"). The reason for this limitation in RB 2.0 is simply limited resources. There was so much demand for a more full-featured standalone report authoring application, including full layout capabilities and support for many data source types, that we did not have time to recreate the same "integrated query design" experience in a completely overhauled and much more (internally) complex layout editor. Because we were unable in the SQL 2008 timeframe to make RB 2.0 a complete superset of the RB 2005/1.0 experience, we chose to re-ship RB 1.0 in that release (with some minor updates) for those who preferred an RB 1.0-like solution for now.

Rest assured, though, that we are hard at work on closing the gap as soon as possible. We are also working on a number of exciting and valuable enhancements to report models and report model queries that will enable new scenarios while making the query design experience even easier.

Sorting the values in parameter dropdowns in Report Builder

Bob Meyers MSFT — Thu, 11 Oct 2007 11:44:00 GMT

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

Bob Meyers MSFT — Fri, 28 Sep 2007 17:06:00 GMT

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

Bob Meyers MSFT — Tue, 27 Feb 2007 22:28:00 GMT

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

Bob Meyers MSFT — Fri, 23 Feb 2007 03:04:00 GMT

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

Bob Meyers MSFT — Mon, 15 Jan 2007 21:19:00 GMT

The Filter dialog in Report Builder has a little checkbox near the bottom that many users have asked about. The checkbox label says, "When adding a new condition, apply to all data in my report."

The effect of selecting this checkbox is that when subsequent fields are added to your filter, and those fields have a to-many relationship from the primary entity, an attempt will be made to adjust the query so those conditions filter not only the rows in the report, but the totals as well. This is the default behavior, and mimics the familiar "slice-and-dice" approach to filtering.

If the checkbox is not selected, no attempt will be made to ensure the conditions you add affect your totals. They will only affect the set of rows displayed in your report. (Technically, they will only affect the set of rows from the primary entity that contribute to the rows in your report. If you are not displaying individual rows from the primary entity, e.g. you are grouping on a field instead, a new filter condition may still affect your totals even though the checkbox is not selected.)

For example, if I add Customer Name and Total Sales to a new report, then open the Filter dialog and add a filter condition specifying Customer.Orders.Order Year = 2006, two things could happen:

If the checkbox is selected, the primary entity will change from Customer to Order, and the filter will now be an Order filter. This will affect the set of rows displayed (only Customers with Orders in 2006 will be displayed), and the Total Sales field (only sales from Orders in 2006 will be included).

If the checkbox is not selected, the primary entity will remain Customer, and an aggregate filter group ("Any Orders with...") will be created instead. This will affect the set of rows displayed (only Customers with any Orders in 2006 will be displayed), but it will not affect the Total Sales field (it will continue to show their all-time sales). This allows you to isolate the row filter from the filter used for totals.

Creating a role to one of several related items

Bob Meyers MSFT — Thu, 21 Dec 2006 03:43:00 GMT

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:

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).
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.
Create relationships in the DSV (using the editor again) from Customer to Address that join CustomerID=CustomerID and xxxAddressType=AddressType.
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

Bob Meyers MSFT — Thu, 21 Dec 2006 03:32:00 GMT

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

This will give you a formula with one or two references to the parameter. You can edit the rest of the formula to get what you want -- just don't delete the ones you have, because you won't be able to reinsert them. Hopefully two is enough, because I can’t think of any way to get three.

Getting grouping right in Report Builder

Bob Meyers MSFT — Thu, 21 Dec 2006 02:55:00 GMT

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

Bob Meyers MSFT — Fri, 10 Nov 2006 21:46:00 GMT

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

Bob Meyers MSFT — Tue, 31 Oct 2006 22:12:00 GMT

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 the config entries indicated in the NewConfigEntries.txt file (included in the project).
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)".
Run a report against the report model that has a long-running query.
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.

UPDATE 5/8/2009: Updated attachment to fix issue in SQL 2005 SP3 and SQL 2008 (need to implement IDbConnectionWrapper). I updated the policy config entry to use the default install location for SQL 2008, and upgraded the project file to VS 2008. Thanks to hawdong for pointing out the issue.

Evolving your report model over time

Bob Meyers MSFT — Thu, 28 Sep 2006 21:30:00 GMT

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

Bob Meyers MSFT — Thu, 28 Sep 2006 21:20:00 GMT

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.