Tuesday, November 6, 2012

SSRS- How to add Sparkline in tabular report


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---------------------------------------------------

No comments:

Post a Comment