• Article

How to: Create a Sparkline in a Table (Report Builder 3.0 Video)

Applies to: Report Builder 3.0

Author: Maggie Sparkman, Microsoft Corporation

Length: 00:05:36

Size: 11 Mb

Type: WMV file

Watch this video

Video Summary

This video demonstrates creating a table in Report Builder and adding a sparkline chart to it. Sparklines are small, simple charts that convey a lot of information in a little space.

The video uses data from a hard-coded query located in this transcript, so you can follow along. Go directly to the hard-coded query.

Video Transcript

Video
Time-Stamp

Audio

00:05

Introduction

Hello, my name is Maggie Sparkman. I’m a technical writer for Microsoft SQL Server Reporting Services.

In this video, I’m going to demonstrate creating a sparkline chart in a table. Sparklines are little charts that pack a lot of information into a small space. In this case they’ll be used for comparing these products in my table.

In this video here are the steps I’m going to run through:

  1. Start Report Builder 3.0 from SharePoint 2010

  2. Run the Table Wizard to create a table

  3. Format dates and currency

  4. Add column to the table

  5. Add a sparkline to the column

  6. Align the sparklines with each other

The main thing I want to point out is that you, too, can create this report. This transcript contains the hard-coded query that you need to copy. For more information about what you will need in order to complete the report, see Prerequisites for Tutorials (Report Builder 3.0).

00:59

Create a report and run the ‘Table or Matrix Wizard’

OK, here we are in SharePoint Server 2010.

  1. Click Documents.

  2. Click New Document, and then click Report Builder Report.

    We see this because we have the Reporting Services 2008 R2 add-in for SharePoint installed. That launches Report Builder 3.0.

  3. We want a New Report. We run the Table or Matrix Wizard.

  4. On the Choose a dataset page, we are going to Create a dataset.

  5. On the Choose a connection to a data source page, we can use any connection to a SQL Server 2008 R2 database because we are going to use the hard-coded query.

  6. Test the connection.

  7. On the Design a query page, click Edit as Text.

  8. Go to the hard-coded query, press Ctrl + A to select it all, Ctrl + C to copy it, and Ctrl + V to paste it.

    Go to the hard-coded query in this topic.

  9. Click Run.

    There’s my data, the same data that you will have if you choose to follow along.

  10. On the Arrange fields page, add SalesDate to Column groups, Product to Row groups, and Sales to Values.

  11. On the Choose the layout page, Show subtotals and grand totals is checked.

  12. On the Choose a style page, click Slate.

  13. Click Finish.

02:17

Save the report

So here it is.

  • I save it to the same server that I ran Report Builder 3.0 from.

02:36

Format the numbers

I am going to fix up the formatting.

  1. Select the [SalesDate] placeholder.

  2. Click Sample Values.

  3. In the Number box, click the Date format.

  4. Select the upper-left [Sum(Sales)], hold down Shift, and select the lower-right [Sum(Sales)] to select all four cells.

  5. Select Currency.

  6. Click Decrease decimal two times so the numbers have no decimals.

02:54

Insert a sparkline

I am going to insert the sparkline in the table.

  1. Select the Total column.

  2. Right-click, click Insert column, and then click Left

    This is where I am going to insert the sparkline.

  3. Right-click the cell, click Insert, and then click Rectangle.

    Charts can stretch as the contents of a row grow. Putting the sparkline in a rectangle in the cell controls that.

  4. Right-click again, click Insert, and then click Sparkline.

  5. Take the default sparkline and click OK.

    This is too big for a sparkline. Sparklines are small, they are inline, and they are compact.

  6. Resize the sparkline.

  7. Resize the column and row to fit the smaller sparkline.

03:40

Attach data to the sparkline

Now I attach data to the sparkline.

  1. Right-click the chart.

  2. Click the plus (+) sign in Values, and then click Sales.

  3. Click the plus (+) sign in Category Groups, and then click SalesDate, so the sparkline will show sales over time.

  4. Click Run.

03:53

Run the report

There it is.

You see we have four columns in the first two sparklines, and five columns in the third one. This matches the number of values in the chart, because there are some blanks. But the columns in the sparklines should line up.

04:07

Align the sparklines horizontally

I’m going to make the columns in the sparkline line up horizontally.

  1. Click Design to return to design view.

  2. Right-click the sparkline, click Horizontal Axis Properties, and check Align axes in Tablix1.

    NoteNote
    The term tablix refers to a data region with rows and columns. It can have the characteristics of a table and matrix.

  3. Click OK.

  4. Click Run.

04:22

Run the report

That looks better. This time there are spaces in the sparkline to match the spaces in the data.

Notice that the largest bar in each sparkline is the maximum height, even though the largest value in one row is $21,900 and the largest value in another row is only $13,350.

04:40

Align the sparklines vertically

I’m going to set the height of the bars in each sparkline to be relative to the height of the bars in the other sparklines.

  1. Click Design to return to design view.

  2. Right-click the sparkline again, click Vertical Axis Properties, and check Align axes in Tablix1.

  3. Make the Minimum zero instead of Auto.

  4. Click OK.

  5. Click Run.

04:55

Run the report

There we go. Now we have spaces where there are no values, and the height of the bars in the sparklines are relative to each other.

  • Click Save and we are done.

05:05

Thanks!

Thanks for watching! Check out the other resources in this transcript page.

Hard-Coded Query

SELECT CAST('2010-01-04' AS date) as SalesDate, 
   'Carrying Case' as Product, CAST(1500.00 AS money) AS Sales, 20 as Quantity
UNION SELECT CAST('2010-01-05' AS date) as SalesDate, 
   'Carrying Case' as Product, CAST(1275.00 AS money) AS Sales, 17 as Quantity
UNION SELECT CAST('2010-01-08' AS date) as SalesDate,  
   'Carrying Case' as Product, CAST(5100.00 AS money) AS Sales, 68 as Quantity
UNION SELECT CAST('2010-01-04' AS date) as SalesDate, 
   'Carrying Case' as Product, CAST(1350.00 AS money) AS Sales, 18 as Quantity
UNION SELECT CAST('2010-01-06' AS date) as SalesDate,  
   'Carrying Case' as Product, CAST(1425.00 AS money) AS Sales, 19 as Quantity
UNION SELECT CAST('2010-01-06' AS date) as SalesDate,  
   'Carrying Case' as Product, CAST(5625.00 AS money) AS Sales, 75 as Quantity
UNION SELECT CAST('2010-01-06' AS date) as SalesDate,  
   'Carrying Case' as Product, CAST(6300.00 AS money) AS Sales, 84 as Quantity
UNION SELECT CAST('2010-01-05' AS date) as SalesDate, 
   'Carrying Case' as Product, CAST(2025.00 AS money) AS Sales, 27 as Quantity
UNION SELECT CAST('2010-01-08' AS date) as SalesDate, 
   'Carrying Case' as Product, CAST(3750.00 AS money) AS Sales, 50 as Quantity
UNION SELECT CAST('2010-01-04' AS date) as SalesDate, 
   'Budget Movie-Maker' as Product, CAST(2700.00 AS money) AS Sales, 9 as Quantity
UNION SELECT CAST('2010-01-07' AS date) as SalesDate,  
   'Budget Movie-Maker' as Product, CAST(3000.00 AS money) AS Sales, 10 as Quantity
UNION SELECT CAST('2010-01-04' AS date) as SalesDate,  
   'Budget Movie-Maker' as Product, CAST(1800.00 AS money) AS Sales, 6 as Quantity
UNION SELECT CAST('2010-01-04' AS date) as SalesDate, 
   'Budget Movie-Maker' as Product, CAST(7800.00 AS money) AS Sales, 26 as Quantity
UNION SELECT CAST('2010-01-08' AS date) as SalesDate, 
   'Budget Movie-Maker' as Product, CAST(3900.00 AS money) AS Sales, 13 as Quantity
UNION SELECT CAST('2010-01-06' AS date) as SalesDate,  
   'Budget Movie-Maker' as Product, CAST(780.00 AS money) AS Sales, 26 as Quantity
UNION SELECT CAST('2010-01-07' AS date) as SalesDate,  
   'Budget Movie-Maker' as Product, CAST(13200.00 AS money) AS Sales, 44 as Quantity
UNION SELECT CAST('2010-01-08' AS date) as SalesDate, 
   'Budget Movie-Maker' as Product, CAST(18000.00 AS money) AS Sales, 60 as Quantity
UNION SELECT CAST('2010-01-06' AS date) as SalesDate, 
   'Budget Movie-Maker' as Product, CAST(5400.00 AS money) AS Sales, 18 as Quantity
UNION SELECT CAST('2010-01-06' AS date) as SalesDate,  
   'Budget Movie-Maker' as Product, CAST(6000.00 AS money) AS Sales, 20 as Quantity
UNION SELECT CAST('2010-01-07' AS date) as SalesDate,  
   'Slim Digital' as Product, CAST(2550.00 AS money) AS Sales, 17 as Quantity
UNION SELECT CAST('2010-01-04' AS date) as SalesDate,  
   'Slim Digital' as Product, CAST(2700.00 AS money) AS Sales, 18 as Quantity
UNION SELECT CAST('2010-01-08' AS date) as SalesDate, 
   'Slim Digital' as Product, CAST(3450.00 AS money) AS Sales, 23 as Quantity
UNION SELECT CAST('2010-01-06' AS date) as SalesDate,  
   'Slim Digital' as Product, CAST(6600.00 AS money) AS Sales, 44 as Quantity
UNION SELECT CAST('2010-01-05' AS date) as SalesDate,  
   'Slim Digital' as Product, CAST(2250.00 AS money) AS Sales, 15 as Quantity
UNION SELECT CAST('2010-01-05' AS date) as SalesDate,  
   'Slim Digital' as Product, CAST(6000.00 AS money) AS Sales, 40 as Quantity
UNION SELECT CAST('2010-01-04' AS date) as SalesDate,  
   'Slim Digital' as Product, CAST(6000.00 AS money) AS Sales, 80 as Quantity
UNION SELECT CAST('2010-01-04' AS date) as SalesDate,  
   'Slim Digital' as Product, CAST(13200.00 AS money) AS Sales, 88 as Quantity
UNION SELECT CAST('2010-01-08' AS date) as SalesDate, 
   'Slim Digital' as Product, CAST(7650.00 AS money) AS Sales, 51 as Quantity
UNION SELECT CAST('2010-01-06' AS date) as SalesDate,  
   'Slim Digital' as Product, CAST(5100.00 AS money) AS Sales, 34 as Quantity