In this SSRS Tutorial I will show you how to create SSRS Sparkline. I will use SQL Server Reporting Services 2008 R2 and it should be the same for SQL Server Reporting Services 2012.
NOTE: I will use blank report to create sparkline. In order to use I would normally develop new report and embed the sparkline report using subreport which is my preferred way to do it. You could try to create a sparkline on your main report without subreport but usually Query you need to create is much more complicated.
What is SSRS sparkline
Sparklines also called trendlines are mini charts (usually very small) that are used to present trends and data variation.
Below is an example of a sparkline that we will try to create. In this case we used bars which I tend to use if there are less than 15 of them as it helps to me to compare them to each other; otherwise I use lines.
How to create SSRS Sparkline
Please see below tutorial that will help you to create a sparkline in SSRS 2008 R2.
In my new SSRS project first I will add new shared data source.
In Connection Properties type Server name and Select or enter a database name.
I I have also added data source in the report itself using shared data source.
Now we can add Dataset; to do that please click Dataset and then Add Dataset.
Dataset Properties setup Name, DataSource and type datasource string
SQL Query: My sparkline shows total visits from our websites in the last 14 weeks.
My TrendDayOfWeek dataset contains three fields: Year, WeekOfYear and TotalVisits:
NOTE: Year is needed for sorting purposes.
Right click on empty space in design tab; go to Insert > then click Sparkline.
In Select Sparkline Type window select the type you want; in my case it will be Column sparkline.
Once your graph is displayed; click it and select Values (TotalVisists field) and Category Groups (WeekOfYear field).
NOTE: Sorting of the group (weeks) is very important and needs to be in sequence. To set sorting open your Category Groups field in my case WeekOfYear Group and set sorting. In my case Year ASC and Week ASC. Year is important to handle last week of previous year and first week of current year.
That is all. Go to preview and you should see a sparkline.
Below you can see my sparkline.
Below is link how to create subreport which is actually using a sparkline and embeds it into main report.
SSRS Create subreport with parameter
I hope that helps
Katie
