Report Builder / Model Constraints

1. RB doesn’t support master-plus-optional-detail reports in this release. The closest approximation is to display the master record with a count of the detail records (which may be zero). The user can then click through to see the details.

2.  It is not possible to specify multiple data sources for a single report model currently, but it is on the wishlist for a future release.

3. Report Model uses DSV (data source view) to map entities to relational tables.

Suppose you have your data in three different databases. Currently semantic models do not support directly DSVs with tables from multiple databases.What you can do is to create DSV off your biggest database (most database objects) and then extend this DSV with named queries that select from table from other databases.

Then you need to create logical pk/fk constraints on the named queries. Once you have this augmented DSV you can generate model on top of it.

The whole process (generating initial DSV, editing DSV and generating model) is
done in Model Designer.

4. The same data source for DSV in Analysis Services can be used in Report builder if the source for AS DSV is SQL Server. In the future when RB supports more data sources (Oracle is on the way, for example), you would be able to use the same DSV.

5. Report builder only allows to edit/run reports that use models as data sources - meaning their queries are based on semantic models.

6.  DSVs don’t support parameters – No parameterized queries, parameterized stored procedures or parameterized UDFs can be used in a DSV named query (unless you hardcode the parameter values in the named query itself).

7. Note: Report builder is not designed to be an OLAP adhoc slicing and dicing tool.

8. Officially, you can only generate a Report model via Report Manager / SSMS’s “Generate Model” option – You can’t edit it in BIDS afterwards.

Unofficially, the SMDL  is just an XML document, so you could manually touch it up inside a text editor. However, this technique is not supported by MS and you’d have to re-do your work any time you update the model.

9. In SQL Server 2005, you can build models from SQL Server 2000 and 2005 databases or from Analysis Services databases only. With SP2, you will be able to build models from Oracle if you are running version 9.2.0.3 or later. For information, see https://msdn2.microsoft.com/en-us/library/ms156272.aspx,  https://msdn2.microsoft.com/en-us/library/ms156296.aspx , and https://msdn2.microsoft.com/en-us/library/ms157367.aspx . Note that you cannot build report models from Access databases at this time.

10. In Visual Studio, you can use the report model-based query designer to create your reports using models. For more information, see https://msdn2.microsoft.com/en-us/library/ms345239.aspx. 

11. For row-level security, please see the following tutorial: https://msdn2.microsoft.com/en-us/library/ms365305.aspx.

Comments

• Anonymous
  April 07, 2007
  The comment has been removed

• Anonymous
  April 08, 2007
  Unfortunately there is no way to achieve this, i.e. there is no programmatic way to convert SQL queries to equivalent report model query. The last I know there were some limitations to semantic queries because of which it would not be possible to generate report model query from existing SQL queries.

• Anonymous
  February 03, 2010
  HELP!! I am concerned about updating the model in our organization, The reason that I say this is because I see that each attribute in the model is assigned a GUID, these GUIDs are bound to the reports that people in the organization have been creating and saved for later use. When I update the model those previously assigned GUIDs will be deleted and new GUIDs will be assigned to the attributes this means that the reports that have been saved will no longer work. Is there a different way that I should be updating the model? or should I be updating the view instead of the model.