In this article, I will show you how to use Sparkline in SSRS.

1. First of all open Visual Studio 2010 and open your SSRS project.Then add report named as DemoSparkline in your project.

Now create a Dataset. In the dataset, we will use following query

SELECT      PC.Name AS ProdCat, ROUND(SUM(SOD.LineTotal),2) AS Sales,DATENAME(MONTH, SOH.OrderDate)as OrderMonth

FROM        Sales.SalesOrderHeader AS SOH

INNER JOIN  Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID

INNER JOIN  Production.Product AS P ON SOD.ProductID = P.ProductID

INNER JOIN  Production.ProductSubcategory AS PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID

INNER JOIN  Production.ProductCategory AS PC ON PS.ProductCategoryID = PC.ProductCategoryID

WHERE       DATEPART(YEAR, SOH.OrderDate) = 2007

GROUP BY    PC.Name,DATENAME(MONTH, SOH.OrderDate)

ORDER BY    PC.Name

2. Then right click on report area and go to Insert–>Table.

3. Now select ProdCat in first column.

4. Then right click on ProdCat Textbox and select Row Group –> Group Properties.

5. A Group Properties page opens. In that add Group Expression as shown in below screenshot. Then click on OK button.

6. Now drag & drop Sparkline control into 2nd column.

7. A Select Sparkline Type window opens. In that select Line Type as shown in below screenshot. Then click on OK button.

8. Now your report design looks like below.

9. Then double-click on Sparkline chart and select value in Chart Data window as shown in below screenshot.

So we have done with Sparkline in SSRS.

10. Now click on preview tab to see the results. You will see results like below screenshot:

Congratulations! We successfully created Sparklines in SSRS.

1. Background

This article describes how to add Spark line and Data bar in the report. Addition of sparkline and small charts in the table and matrix makes report more interactive and simplified the report for the business users.

2. What is Spark Line?

Spark lines and Data bars are small, simple charts that convey a lot of information in a little space.
Spark lines and Data bars are often used in table and matrices.
Spark lines often represents multiple data points, often over time.
Data bars can represent multiple data points, but typically illustrate only one.
Spark lines and Data bars represent a single series.
We cannot add a spark line to a detail group in a table.
Spark line always shows aggregated data within a group. (Detail group not applicable)
Spark lines have the same basic chart elements of categories, series, and values.

Types of Spark lines:

3. Steps to add the Sparkline in the report:

Below is the sample Sales report which shows year wise sales amount for various regions:

We will add Sparkline and Chart data region into the above report to show year wise sales amount for regions.

I. Go to report layout and remove FiscalYear column from the tablix:

After deleting column, report is having only two columns:

II. Preview the report:

Here we can see that region is repeating as we have deleted the fiscal year column so we can group the data by region to avoid duplicate records.

III. To create a group for SalesRegion, click on Details group in Row Group pane and select Group Properties:

IV. It will show the Group Properties:

V. Click on “Add” button and select “Sales Region” and click Ok:

VI. Now go to tablix and right click on SalesAmount text box and select expression:

VII. It will open the expression builder, add the SUM function to sales Amount:

=SUM(Fields!SalesAmount.Value)

Click Ok.

VIII. Preview the report. It will show the aggregated Sales Amount for SalesRegion:

Now we can see the report showing region wise aggregated Sales Amount.

Note: Now we will add the Sparkline to show progress of Sales Amount for a region over years.

IX. For adding Sparkline, right click on Report layout then Insert and select Sparkline:

X. Select the sparkline type. For this report, we are using line sparkline:

It will show the Sparkline in the report layout:

XI. Set the property for sparkline. As we are planning to show the year wise sales amount in the report then set the below mentioned property:

· Category Group : Fiscal Year

· Value : Sales Amount

· Series : Sales Region.

XII. Now add a new column into a tablix named SalesProgress. Drag and drop the Sparkline into this newly created column:

XIII. Now report layout looks like below one:

XIV. Now run the report and see whether we are able to see the sales amount progress or not:

Report is showing sales amount progress from start year to till end.

XV. We can add data label to Sparkline. Right click on the lines on the Sparkline and select “Show Data Labels”:

XVI. Preview the report

Now report is showing with data labels.

4. Steps to change the Sparkline type in report:

I. Right click on the spark line box in the report layout and select “Change Sparkline Type”:

It will open the list of spark line.

We can select any spark line type and click ok.

II. Preview the report

Now we can see the report with different sparkline type.

5. How to change Sparkline to Chart

I. For Changing Sparkline to Chart, right click on the Sparkline and click on “Convert to Full Chart”

If we want we can change the chart type also by right click on sparkline/chart text box and click on select Change Chart Type :

For Demo purpose, I have selected 3D- Clustered cylinderical chart:

II. Preview the report:

Now report is showing with Chart.

Note: We can directly add Chart also to tablix.

6. Conclusion

By using the above steps, we can show Sparkline and Chart in the tabular report.

----------------------------------------------------End of Document---------------------------------------------------

Microsoft Technologies

Pages

Tuesday, October 6, 2015

Sparklines in SSRS