ptg
331
Report Models and the Model Designer
18
Expressions should have a familiar ring to them. Expressions in a report model take one or
more source fields, and manipulate them with functions, operators, and constants to
derive a calculated value. These expressions are just like expressions in Report Designer.
They are based on VB.NET, and automatically provide access to
System.Math
and
System.Convert
. Functions from the
Microsoft.VisualBasic
namespace and references to
other assemblies or custom code can be used, too.
A good example of what could be an expression is the combination of
FirstName
and
LastName
to create a new field called
Name
.
Folders
Folders allow you to group collections of entities. Folders can also contain other folders
and perspectives. Folders allow you to add hierarchical information to the model. Folders
can also be used to group items together regardless of hierarchical relationships. For
example, because Report Builder users can navigate folders in a manner similar to
Windows Explorer, you can move infrequently used items into a folder to hide them from
the users.
Perspectives
Perspectives help to give us a narrower view or a view of a subset of the model.

Creating a Data Source
Now that you have an empty project, the next thing to do is connect it to a data source.
Remember the data source has to be SQL Server (or abstracted through linked servers or
UDM).
Unlike Report Designer, no other data sources are accepted directly:
1. Open Solution Explorer and right-click the Data Sources folder. Click Add New Data
Source. This launches the New Data Source Wizard.
2. Skip the Welcome page by clicking Next.
3. On the next page, select Create a Data Source Based on an Existing or New
Connection, and then click New.
4. Enter
localhost
as the name of the server to connect to.
5. Select Windows Authentication.
6. From the Select or Enter a Database Name list, select Adventure Works.
7. Click Test Connection; if you get an OK dialog box, click OK. If not, try to resolve
the error using the debug information given, and then try again.
8. Click OK. At this point, you should have a screen that looks similar to Figure 18.2
(Data Source Wizard).
9. Click Next.
10. At this point, you could change the name of the data source. If it is not already, call
the new data source
Adventure Works
.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
333
Report Models and the Model Designer
18

ptg
334
CHAPTER 18 Ad Hoc Reporting
FIGURE 18.3
Selecting the tables and views.
FIGURE 18.4
Name Matching screen of the Data Source View Wizard.
After the wizard is complete, double-click the
Adventure Works DSV.dsv
file in Solution
Explorer. This opens a document showing you the relationships that the wizard has just
inferred (see Figure 18.5).
To get the document to fit on one page, you might have to click the View menu, point to
Zoom, and then click To Fit.
Creating a Report Model
Finally, you can create the actual model. To create the model, complete the following
steps:
1. Open Solution Explorer and right-click the Report Models folder. Click Add New
Report Model. This launches the New Report Model Wizard.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
335
Report Models and the Model Designer
18
FIGURE 18.5
Data source view.
2. Click Next on the Welcome screen.
3. The next screen is the screen to select data source views. At this point, only the
Adventure Works DSV should show up. Select this, and then click Next.

18
7. Click Finish. Figure 18.9 shows the completed model.
Modifying Items in the Model
The way entities show up in the Model Designer reflects how they will show up in Report
Builder. To make things more meaningful for the end user, many of the properties or enti-
ties can be customized. Working with the model involves some of the same basic concepts
as working with Report Designer. The properties of any object can be modified from the
Properties window.
Things you can customize include sorting, instance selection, and inheritance. What this
means is that attributes can be set to come from other entities or that users can see a
filtered list of instances of an entity. Entities can also predefine formatting of their attrib-
utes. For example, attributes that reflect currency can be formatted as currency depending
on the user’s localized settings. The order in which things appear in the entity browser can
also be customized. By default, entities appear sorted in alphabetic order, but you can
move certain entities to the top if users are going to report off them frequently.
To modify items in the model, it is just a matter of right-clicking in the tree view or list
view. The Report Model menu in the Model Designer also gives the same menu options.
To delete any item, just navigate to the item, right-click it, and choose Delete from the
shortcut menu.
To add an entity, folder, or perspective, do the following:
1. Navigate to the top of the tree view.
FIGURE 18.9
Completed database model.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
338
CHAPTER 18 Ad Hoc Reporting
2. From the Report Model menu, select New Entity, New Perspective, or New Folder.
From this point, each of these items has its own caveats.

in the Report Server. Because the information in the data source view is incorporated into
the semantic model, the data source view does not get published. If the information in
the data source view, or for that matter, anything in the semantic model, needs to be
updated, just publish an updated model.
After the model is published, it can be secured using Reporting Services role-based security.
To use the model in Report Builder, a user must have access to it.
When you are ready to deploy the semantic model, the deployment steps closely resemble
the deployment steps in Report Designer:
1. Right-click the project file in Solution Explorer, and select Properties.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
339
Report Models and the Model Designer
18
2. Review the target folder’s properties. There is one target folder for the data sources,
and there is another target folder for the semantic models.
3. Verify that the Overwrite Data Sources option is on the intended setting. This option
is similar in name and function to the one in Report Designer.
4. Click OK.
To deploy the model and data source, right-click the project file in Solution Explorer and
select Deploy from the shortcut menu. To deploy just the model file, right-click the
semantic model and click Deploy.
Creating Models from Report Designer
A second way to create a model is from Report Manager. Report Manager can take any SQL
Server or Analysis Services data source and generate a model from it. You can complete the
following steps to generate a model from Report Manager:
1. Go to the Report Manager web page. By default, it is located at
http://localhost/Reports.
2. Click the New Data Source button.

340
CHAPTER 18 Ad Hoc Reporting
FIGURE 18.10
Starting Report Builder from the Report Manager web interface.
Report Builder uses the models stored on the Report Server. Report Builder calls these
models. Through Report Builder, the end user knows nothing of the actual data source
used by the model. End users will most likely be referring to the model as the data source,
as shown in Figure 18.11. Keep in mind that because the models are secured by the Report
Server, users will not be able to access models to use as data sources for their report if the
appropriate permissions have not been set.
Report Builder has predefined templates available for matrix, table, and chart report
layouts. Note that lists and rectangles are not supported. To use a template, the user just
has to click a template in the left pane. Figure 18.12 shows the Report Builder interface.
After selecting a template, the end user simply has to drag and drop fields from the Report
Data Explorer onto the data region provided. Report Data Explorer is nothing more than
the entity explorer. Attributes and expressions make up the fields that are usable from
within the entity explorer.
Some basic features are also available through Report Builder. Formatting can be done to
any text box on the report layout. Data can be filtered, sorted, and grouped. A full range of
export formats, such as PDF, TIFF, Excel, HTML, XML, CSV, and TIFF are also available to
end users.
Report Builder saves reports to the Report Server. After being published, the reports can be
managed like any other reports.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
341
Report Models and the Model Designer
18
FIGURE 18.11

Column Fields.
4. After you drag Name over to the layout view, the entity explorer should have
switched to entities that have a role relating to sales territory. From this list, select
Sales Order Headers.
5. From the Attribute Fields menu, drag Order Year, which is located under Order Date,
over next to the Sales Territory name. The mouse cursor should turn blue to signify
that the table is ready to add a column.
NOTE
The Model Builder automatically creates expressions based on dates. These expres-
sions are usually date parts.
Certain entities and attributes also might have a
#of <Entity>
expression. The rules
chosen during the wizard are the driving factor behind these. The Model Builder also
chooses whether to include a distinct count of values based on database statistics.
6. Drag Order Quarter (also located under Order Date) over next to Order Year.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
343
Report Models and the Model Designer
18
7. Drag Total Sub Total over next to Order Quarter. After doing this, notice three total
lines that appeared. One of the nice features of Report Builder is that it automati-
cally groups and sums data for you.
8. Enter
Total Product Sales by Quarter by Territory
in the text box above the
table where it says Click to Add Title.
Figure 18.13 shows what your report should look like.

1. Choose Save As from the File menu.
2. In the root folder of the Report Server, enter the filename
Sales by Territory—
Table
, and then click OK.
Matrix/Pivot Table Report
Now, let’s go on to make the same report using a pivot table or matrix. The concepts are
the same; just the data region is different.
1. If you have already closed Report Builder, reopen it.
2. Select the Matrix Report style from the Report Layout section on the right pane.
3. From the entity explorer, select Sales Territory, and drag the Name attribute over to
the matrix where it says Drag and Drop Row Groups.
4. From the entity explorer, select Sales Order Headers, and drag the Order Year under
Order Date to the matrix layout where it says Drag and Drop Column Groups.
5. Similarly, select Order Quarter and drag it over to the matrix columns groups. Place
it under Order Year. The mouse cursor should turn blue when it is ready to add a
column group.
6. Select Total Sub Total and drag it to the Detail section of the matrix where it says
Drag and Drop Totals.
7. Add a title to the report; call it
Sales by Territory
.
8. To make things easier to see, let’s use some basic formatting to separate the quarters
from the years. Right-click the inner 0, and select Format from the shortcut menu.
9. Go to the Fill tab and select Gold as the color. Then go to the Alignment tab and
choose Center for the horizontal alignment. Do the same for the Adjoining Total
text box. This should make all quarters-related information headers gold.
10. Do the same thing to the upper 0 and the adjoining total, except select Green as the
color. Select Center for the horizontal alignment. On the Border tab, click the
Outline button to put an outline border around the year.

ptg
346
CHAPTER 18 Ad Hoc Reporting
FIGURE 18.15
Finalized report design.
Report Builder 2.0 Features
Let’s take a look at some of the features of Report Builder 2.0. A number of the features
listed here are not part of Report Builder 1.0. Report Builder 2.0 is intended to be a full-
featured report designer more akin to BIDS. (A lot of the differences between Report
Builder 1.0 and Report Builder 2.0 can be attributed to that.)
. Multiple data sources: Report Builder 2.0 can use native SSRS data sources.
Therefore, just as with reports you build in BIDS, you can use any OLE DB or ODBC
data source. You can also use multidimensional data sources like Analysis Services or
Essbase. You can also use XML data sources.
. Report data regions: You can use Report Builder 2.0 to create tabular, matrix, and
free-form reports. You can also use charts, gauges, and all the features of the new
SSRS Tablix feature.
. Ad hoc reporting: You can create and save reports and choose to save them directly
to the Report Server or to your local desktop
. Link to subreports: With Report Builder, you can link to subreports and use report
parameters just as you can with BIDS. This allows a certain level of interactivity. If
you use a report model, you can use infinite clickthrough.
. Rendering formats: Report Builder 2.0 allows all rendering extensions used by SSRS.
These include HTML, PDF, CSV, XML, TIFF, Word, and Excel.
. Custom report items: Custom report items can be used in Report Builder 2.0. Of
course, this requires a certain amount of configuration.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
347

CHAPTER 18 Ad Hoc Reporting
FIGURE 18.17
Report Builder 2.0 Default Target Server screen.
shared data sources. Enter
http://localhost/ReportServer
(see Figure 18.17). Click
Next.
7. Click Install.
8. Click Finish when install completes.
When the install is complete, you can launch Report Builder 2.0 by clicking the Report
Builder 2.0 shortcut in the Start menu. The program group is called Microsoft SQL Server
2008 Report Builder 2.0.
Design Surface
The design surface of Report Builder 2.0 is where most of the work of report authoring
takes place. Similar to other report designers, this is where you add data regions, modify
groupings, edit expressions, change visibility, and format reports.
Items from the data pane or from the Ribbon can be dragged onto the design surface. You
can right-click anywhere on the design surface to edit report properties, and you can right-
click any item to pull up its properties.
Page Size
One of the biggest issues faced by new users is reconciling page size with the printable
page size. The page size on the design surface is based on the total available page size.
However, the printing page size subtracts the margins from the total page size. You can use
the Ruler from the Ribbons to get an idea about how large your reports are. Click the View
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
349
Report Builder 2.0
18

The View tab, shown in Figure 18.20, contains items to help design reports. From this tab,
you can turn off and on dialog boxes (for example, the Report Data, Grouping, Ruler, and
the Properties dialog boxes).
From the Run tab, shown in Figure 18.21, you can get to crucial tools, such as the Print
Layout, Page Setup, and Print functions. You can also use the Run tab and click the Export
button to export into different rendering formats, such as XML, CSV, TIFF, PDF, MHTML,
Excel, and Word. Clicking the Design button will send you back into design view.
Other Dialog Boxes
The data pane is there to manage data sources and data sets. From the data pane, you can
also drag fields from common fields, or from the data sets.
The Properties dialog box enables you to modify properties for individual report items.
You have to enable the dialog box from the View tab in the Ribbon. After you enable it,
when you click any report item, modifiable properties will display. You can sometimes
modify the properties just by editing text, or selecting a valid value from a drop-down.
Other times, a custom dialog box may pop up.
The grouping pane in Report Builder 2.0 is similar to the grouping pane in BIDS. It
contains two main groups: Row Groups and Column Groups. Groups can be created by
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.