Using Report Models as DataSources in OfficeWriter Designer

Problem

Prior to version 3.9.2, OfficeWriter designer did not support reports with queries based on Report Models. Publishing such a report from OfficeWriter Designer caused some fields to show gibberish or not show at all.

For more information about Report Models visit Microsoft web-site at http://technet.microsoft.com/en-us/library/cc678411.aspx.

Solution

From version 3.9.2 and above, OfficeWriter supports Report Model DataSources. Reports created using Visual Studio (2005 and 2008) or ReportBuilder version 2.0, that use a report model as a datasource are now parsed correctly by the OfficeWriter designer.

Note: Support for newer versions of Reporting Services (2008, 2008 R2) and Report Builder (3.0) have been added in later version of OfficeWriter. For more information, refer to the change log in the OfficeWriter Docs.

Reports may contain both regular SQL queries and semantic queries (Report Model-based queries). This support is seamless to the user with no change to the toolbar. However, a feature was added to improve the user experience.

In Report Models, all fields are mapped to entities (instead of tables). We added a feature that allows the user to know to which entity a certain field belongs. For example, if the user encounters a field First Name, it will have a way of knowing whether this is an employee or a customer first name. When a report model is the datasource, its fields will be displayed as a submenu of their entities (rather than the usual flat list of fields), as in the image below:

Figure 1.

Below is the same query, but in the unmapped view:

Figure 2.

The RDL alone does not contain sufficient information to map the fields to entities. Therefore, when a Report Model-based query is selected from the Select Query drop down, we attempt to retrieve the model (as an XML file) from the server. If the address to the model on the server is embedded in the RDL, we try to retrieve it immediately. If we are not able to get the model, or if there is no address embedded in the RDL, the following window will open:

Figure 3.

This dialog offers the following options:

  • Continue will continue without retrieving the model. This will not affect the functionality of the report, the only effect is that the fields will not be mapped to entities, but will be displayed in a single list (as in Figure 2).
  • Retry will attempt to retrieve the model from the server specified in the RDL if the address exists.
  • Browse… will open a window (as in Figure 4 below) that will allow the user to browse to a server and choose a model from a list of available models on the server.

Figure 4.

Note that each model has a unique ID which is not visible to the user. Two models, even if they are created based on the same database with the same entities and fields, will have different IDs. Therefore, mapping the fields of a report created with one model against another model will fail. However, each model preserves its ID when deployed to different servers, so fields can be mapped using a model from another server if it was deployed from the same source model.

Once the model is retrieved, the fields are mapped to entities under the Insert Fields drop-down list (as in Figure 1). Formulas created in Visual Studio or ReportBuider are not related to any entity and will show as regular fields at the bottom of the Insert Field drop-down.

This change applies to the client-side OfficeWriter designer. The server-side components of OfficeWriter require no special modification to work with Report Models. However, we recommend always using matching versions of the designer and the server-side installation of OfficeWriter.

Related posts: