How to use Indicators and Data Bars in Report Builder 3

In the next few rows I will give a simple example of using two new elements in Microsoft’s Report Builder 3 – Indicators and Data Bars.

As it is still February and SQL Server 2008 R2 has not reached its release version, I am using the last CTP from November 2009. The database is AdventureWorksDW2008 (you can get it from here).

Let me first start with a screenshot of the final report:

clip_image001

Steps:

1. Start Report Builder 3 November CTP. Proceed with selecting a new report -> Table or Matrix Wizard

clip_image003

2. Continue with setting up the datasource. Just make sure to use AdventureWorksDW2008 or AdventureWorksDW.

3. After setting up the datasource, you will be redirected to the screen for adding new dataset. There you have two options – either using the graphical designer or using a simple text editor. You can use the following statement:

SELECT (de.LastName) + ‘, ‘ + (de.FirstName) as FullName,frs.SalesAmount

FROM FactResellerSales frs

JOIN DimEmployee de

ON de.EmployeeKey = frs.EmployeeKey

clip_image005

4. On the next screen we will define the row grouping and values to aggregate (grouping on FullName column, aggregations on SalesAmount):

clip_image007

5. Choose a layout and color scheme for your report and return to the design view of Report Builder with a raw version of the report. Then add two new columns on the right side of Sales Amount column – one for placing data bars, one for indicators:

clip_image009

6. Add a new data bar element by selecting it from the Data Visualizations ribbon and place it directly in the cell where the data bar to be displayed (in our case – just on the right of Sales Amount value column). When you place it there a small screen will appear where to select the type of data bar. I used a simple data bar.

clip_image011

7. Then you must choose the aggregation that will measure how “long” the data bar to be (by double clicking on the data bar cell a Chart Data window is opened and from the green plus sign you choose the aggregation field).

clip_image013

8. The same goes and with the indicator.

clip_image015

Well that was a simple demonstration on how to use some of the new features in MS SQL Server 2008 R2. Of course you can do a lot more with those elements, use different type of data bars, indicators, integrate labels, values, etc.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s