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:
1. Start Report Builder 3 November CTP. Proceed with selecting a new report -> Table or Matrix Wizard
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
4. On the next screen we will define the row grouping and values to aggregate (grouping on FullName column, aggregations on SalesAmount):
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:
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.
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).
8. The same goes and with the indicator.
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.