Microsoft® SQL Server® 2012 Bible

1245

Chapter 55: Authoring Reports in Reporting Services

55

The previous method gives another literal value to answer the question “who are my best

customers?” If space on the report is tight or your user prefers to see visualizations in

color to highlight the data, you can achieve the same goal by conditionally formatting the

AnnualSales fi eld. To change the background color of the AnnualSales fi eld to Yellow

when the criteria are met, perform the following steps:

1. Right-click the text box for AnnualSales (the data, not the label).


2. Select Text Box Properties.


3. Select the Fill tab.


4. Click the Expression box Fx beside the drop-down menu for Fill color.


5. Type =IIF(Fields!AnnualSales.Value>1000000,"Yellow", Nothing).


6. Click OK twice.


7. Click on the preview tab to preview the report.

Creating a Matrix Report

Released with SSRS 2008, the matrix data region gives the utmost fl exibility in confi g-

uring the data in a report and easily achieves what used to take custom expressions to

accomplish. The matrix template enables reports to aggregate detail rows both horizontally

and vertically and to drill both horizontally and vertically. Matrix reports often contain

row and column groups, which are expandable in the report and enable data to be aggre-

gated horizontally or vertically. To create a matrix report, proceed through the same steps

outlined in the section “Authoring a Report from Scratch,” with the exception of step 16.

Instead of leaving the report type as Tabular, select Matrix.

Working with Cubes

SQL Server Analysis Services cubes are objects that contain measures aggregated by one

or more dimensions. For instance a cube might contain sales data aggregated by date and

region. You have just been introduced to matrix reports, which work beautifully with

data from Analysis Services. You will now create a matrix report with the data from the

Adventureworks cube inside the Adventure Works DW Analysis Services database. You cre-

ate a report that details the Internet Sales by Geographical region and Product Category

data. To create the report, perform the following:

1. In Solution Explorer, right-click Reports, and select Add ➪ New Item.


2. In the Add New Item dialog box, select Report from templates, and type in
   InternetSales for the report name. Click Add.


3. From the Report Data pane, right-click data sources, and select Add Data Source.


4. In the Data Source Properties Dialog box, type AWCube.


5. For Type, click the drop-down, and select Microsoft SQL Server Analysis Services.

c55.indd 1245c55.indd 1245 7/31/2012 10:28:06 AM7/31/2012 10:28:06 AM

http://www.it-ebooks.info