In this session we'll show you how to change a Sparkline series fill color when value(s) are either positive or negative. This session demonstrates how to work with Sparkline’s using SQL Server Report Builder 3.0 for authoring and designing the report.
What is a Sparkline?
Sparklines and data bars are small, simple charts that convey a lot of information in a little space, often inline with text. Sparklines and data bars are often used in tables and matrices. Their impact comes from viewing many of them together and being able to quickly compare them one above the other, rather than viewing them singly. They make it easy to see the outliers, the rows that are not performing like the others. Although they are small, each sparkline often represents multiple data points, often over time.
What we'll cover in this session:
- Create a Data Source
- Create a Dataset
- Develop custom query using T-SQL
- Add a Matrix to Report
- Add Sparkline to Report
- Create expression to dynamic change series fill color
- Test Report
This article assumes you have the following:
- Installed SQL Server 2012 or 2008 R2, with Reporting Services.
- Downloaded AdventureWorks 2012 sample database.
- Experience with SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS). Note: For this session we'll be using SSDT.
- Experience with attaching to an exisitng database using SQL Server Management Studio.
- Experience with T-SQL scripting language.
- Experience with Reporting Builder 3.0 or Reporting Services using Business Intelligence Development Studio (BIDS).
Note: A download of the demo is available at the end of this session.
Let's get started!
Step 1 - Create Data Source
1) Open Report Builder 3.0 from Start >> All Programs >> Microsoft SQL Server 2008 R2 Report Builder 3.0 >> Report Builder 3.0.
Note: If this is the first time you're using Report Buidler 3.0, you will have to set the path to your Report Server or SharePoint site.
Configure Report Builder
I. From the upper left corner of Report Builder, click the Red Ribbon and then the Options from the lower left corner.
II. Select the Settings option from the left pane.
III. Enter your default Report Server location and set the other property values as desired. For example: http://localhost/ReportServer Click OK to save your settings and close the dialog box.
2) From the Report Data pane, click New and select Data Source
the data source dialog will open
Note: You may choose an existing shared data source, if you have previously created one. For this example, we will be using an embedded connection.
2) Provide a name for your data source. We will use the default value provided in this example.
3) Select "Use a connection embedded in my report" option
4) Select your Connection Type from the drop down. For this example we are using Microsoft SQL Sever.
5) Click the Build button from the right of the "Connection string:" textbox. You will see the Connection Properties dialog below.
6) Enter the same values in the property fields, found in the image above.
- Test the connection when you are done.
- If test is successfull, click OK.
- Click OK to close the Connection Properties window.
- Click OK to close the Data Source Properties window.
- You should now see your data source name on the left Report Data pane.
Note: the value in Server Name property is a period "."
Step 2 - Create Dataset
1) From within the Report Data pane, right click the Datasets folder and select Add Dataset...
Note: the Dataset Properties window will render.
2) Change the Name textbox to 'AnnualGrossProfit'.
3) Select the Use a dataset embedded in my report option.
4) Select 'DataSource1' for the Data Source dropdown.
5) Copy and paste the following SQL to the Query textbox:
SELECT Year(soh.OrderDate) as "Sales Date",
(Sum(sod.UnitPrice)- Sum(p.StandardCost)) as "Gross Profit",
ps.Name as "Product Subcatergory Name"
FROM Sales.SalesOrderHeader soh inner join
Sales.SalesOrderDetail sod on soh.SalesOrderID = sod.SalesOrderID inner join
Production.Product p on sod.ProductID = p.ProductID inner join
Production.ProductSubcategory ps on ps.ProductSubcategoryID = p.ProductSubcategoryID
GROUP BY Year(soh.OrderDate), ps.Name
ORDER BY Year(soh.OrderDate), ps.Name
Your Dataset Properties window should look similar.
6) Click OK to close the Dataser Properties window. The newly created Dataset should show under the Datasets folder.
Step 3 - Add Matrix
1) From the top ribbon, select the Insert tab.
2) Within the Data Regions section, select Matrix >> Matrix Wizard.
Note: this will render the New Table or Matrix window.
3) Select the Choose an existing dataset in this report or a shared dataset option, and select AnnualGrossProfit.
4) Click Next.
5) Drag the fileds as presented in the image below:
Sales_Date to Column Groups
Product_Subcategory_Name to Row Groups
Gross_Profit to Values
6) Click Next.
7) We'll keep the default for Choose Layout page. Click Next.
8) We'll keep the default for Choose Style page. Click Finish. You see the following after clicking Finish.
Step 4 - Add Sparkline
1) Click in the Total column once. Above this field, click the Grey column bar, so the whole column is selected, like so.
2) Right click the grey column bar and select Insert Column >> Left
3) For the column name, enter "Trend".
4) Place your cursor in the cell below Trend. Righ click the cell and select Insert >> Sparkline
5) Choose the defaul Sparkline Type "Column" and click OK.
6) Click on the Sparkline and the Chart Data pane opens. In the Values area click the Add Fields (+) sign and select Gross_Profit.
7) For the Category Group, add the Sales_Date field and place your cursor outside the Chart Data pane.
Step 5 - Create custom Expression
1) Click on any of the Series column bars in the Sparkline. You should see somthing similar.
Note: Notice the white circles indicating the Series have been selected.
2) Right click on the select Series and select Series Properties...
Note: the Series Properties window renders.
3) Click the Fill tab for the left pane.
4) Under the Pick Color area, click the Expression Fx button.
5) Copy and Paste the following code in the Expression window.
=IIF(Fields!Gross_Profit.Value<0, "Maroon", "OliveDrab")
6) Click OK to close the Expression window.
7) Click OK to close Series Properties window.
8) Right click on the select Series and select Horizontal Axis Properties...
9) Under the Axis range and interval check Align axes in: and make sure your Tablix is selected in the dropdown box.
Note: Align Axes - When you check this option, the values in your sparklines and data bars will align across the different cells in the table or matrix, even if there are missing values in the data they are based on.
10) Click OK to close the Horizontal Axis Properties window.
11) Copy the Sparkline and paste a copy to the cell right below. You should see something similar.
Step 6 - Test Report
1) Click the Run button from the Ribbon. You should see the following results.
You have completed creating a custom expression to handle dynamic Fill Colors on Sparkline series values.
Download: Sparkline Demo Report
Reference: Sparklines and Data Bars (Report Builder 3.0)