Working with Report Model Query Designer

In Microsoft SQL Server 2008 Reporting Services, you can author reports based on report models by using Report Builder, Report Designer Preview, or Report Designer. Report Builder is a tool that is used to create ad hoc reports based on report models and uses a subset of the report definition language (RDL) available in Reporting Services. Report Designer is an advanced report authoring tool that uses the full functionality of RDL, enabling you to create complex reports from a variety of data sources, including relational and multidimensional databases, XML, and report models. Report Designer Preview is a tool that is used to create reports on the client computer. Report Designer Preview has all the functionality of Report Designer without requiring you to use Business Intelligence Development Studio. When building a report model query in Report Designer or Report Designer Preview, you use a tool available in Report Designer called Report Model Query Designer. 

With Report Model Query Designer, you can create queries based on models by dragging entities and fields to a design surface similar to the one in Report Builder, resulting in a query composed of report model metadata. When you finish building the report model metadata for your query, it is incorporated into the RDL of your report where you can organize and format the contents just like any other report. For example, you can create a report that contains conditional formatting, includes multiple datasets, and contains multiple data regions using different queries within a single report. When you render the report, the query is translated on the fly.

Before attempting to access the Query Designer, you must successfully create a report model and deploy it to a report server or SharePoint site. Query Designer can be accessed when the model is selected. When creating your data source, specify Report Server Model and then provide a valid connection string.

When using a report model located on a report server in native mode, use a connection string in the following format:

Server=http://<servername>/reportserver; datasource=/<modelpath>/<modelname>

For example:

Server=http://myreportservername/reportserver; datasource=/models/Adventure Works

When using a report model located on a report server in SharePoint integrated mode, you must specify the fully qualified URL to the model. Use a connection string in the following format:

Server=http://<servername>; datasource=http://<MySharePointSite>/<site>/<Document Library>/<MyFolder>/<modelname>

For example,

Server=http://myreportservername; datasource=http://TestSharePointSite/Site/Documents/Models/Adventure Works.smdl