Microsoft SQL Server 2012 Bible

Using Reporting Services Features to Visualize Your Data

So you have a basic report thanks to the wizard. Now you receive another few requirements. Your boss says, “This is great, but I'd like to know who my top clients are. Can you highlight clients who exceed $1,000,000 in sales?” You can accomplish this in several ways; two of them are covered next.

One way to achieve the goal would be add another field to the dataset, which would evaluate the annual sales and evaluate to true when they are more than $1,000,000. To create a calculated field on the dataset, perform the following steps:

1. Right-click the ResellerContact dataset.

2. Select Add Calculated Field.

3. In the last Field Name on the Fields tab, type PremiumClient.

4. Click the Fx button beside the Field Source box beside the Field Name.

5. In the Expression Dialog box, enter =IIF(Fields!AnnualSales.Value>1000000,”True”, Nothing) and click OK twice.

6. Drag the PremiumClient Field onto the report surface beside the AnnualSales field. Let go of the field when the ibar appears.

7. Click Preview to see the results.

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 field. To change the background color of the AnnualSales field to Yellow when the criteria are met, perform the following ...