Graphing the Sine Function in SQL Server Reporting Services

By: Scott Murray   |   Read Comments (2)   |   Related Tips: > Reporting Services Charts

Problem

Can a function, such as the sine or cosine trigonometry functions, be graphed in SQL Server Reporting Services (SSRS).  This particular question was recently asked in the Question and Answer section of MSSQLTips.com.  So can it?  If so, how do we graph it?  Check out this tip to learn more.

Solution

For many folks, even the mention of sine or cosine functions bears horrid memories of days in geometry, trigonometry, and calculus.  Have no fear, we will not be entertaining any proofs in this solution.  However, the question asked is a good one and the short answer is that SQL Server Reporting Services (SSRS) does not support a direct charting of these functions (or any functions).  In essence, SSRS does well what it is suppose to do at the presentation layer of the data warehouse spectrum, but it must be "fed" with data in order to consume and display it. Thus we need to revert back to running and generating the appropriate data set in SQL Server which will pump the appropriate X and Y values into SSRS.

Generating a Sine Graph Dataset

Step 1 in our process is to generate a dataset to feed the appropriate values into SSRS. Fortunately, SQL Server contains the necessary trigonometric functions to perform the appropriate Sine calculations, http://msdn.microsoft.com/en-us/library/ms177516.aspx.  In particular we will be using the SIN function to determine the appropriate Y value. Additionally, the RADIANS function will be used to convert a list of angles measured in degrees to radians values which is required by the SIN function. To help limit our graph, we are only going to graph the Sine function from 0 to 2 ð (Pi)  or 0 degrees to 360 degrees on unit circle ; otherwise, it could continue to infinity. Now we have the basics down, we can create an actual dataset; but how do we create a list from 0 to 360 degrees.  That is where using a Tally or Numbers table comes in handy.  I consider Jeff Moden one of the kings in the use of number tables and the below code to generate a Tally table is directly attributed to his work at:  http://www.sqlservercentral.com/articles/T-SQL/62867/.

USE [AdventureWorks2012]
GO

-- From http://www.sqlservercentral.com/articles/T-SQL/62867/ --Jeff Moden
--=============================================================================
--      Setup
--=============================================================================
  SET NOCOUNT ON --Suppress the auto-display of row counts for appearance/speed
  DECLARE @StartTime DATETIME    --Timer to measure total duration
  SET @StartTime = GETDATE() --Start the timer

--=============================================================================
--      Create and populate a Tally table
--=============================================================================
--===== Conditionally drop and create the table/Primary Key
  IF OBJECT_ID('dbo.Tally') IS NOT NULL 
        DROP TABLE dbo.Tally

  CREATE TABLE dbo.Tally 
        (N INT, 
         CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N))

--===== Create and preset a loop counter
DECLARE @Counter INT
    SET @Counter = 1

--===== Populate the table using the loop and counter
  WHILE @Counter <= 11000
  BEGIN
         INSERT INTO dbo.Tally
                (N)
         VALUES (@Counter)

            SET @Counter = @Counter + 1
    END
GO

Now that our Tally table is generated, we can use it to generate a list of data points from 0 to 360 degrees.  The below query creates our data set.  Note that the angle measure is first extended out to a precision of 10 with the use of a CTE.  Also, notice that N-1 is used to start the angle range at 0 degrees as the Tally Table starts at 1.  

;WITH TALLY
(
Angle_Measure
)
AS
(

SELECT
CAST(N-1 AS decimal(15,10)) AS Angle_Measure --Carry out to 10 place precision 
FROM
[dbo].[Tally]
)

SELECT
Angle_Measure, --Angle used in DEGREES, X coordinate
RADIANS(Angle_Measure) AS Radian_Measure, --Angle used in RADIANS, X coordinate
SIN(RADIANS(Angle_Measure)) AS Sin_Measure --Sine value of Angle used, Y coordinate
FROM
TALLY
WHERE
Angle_Measure <361 --360 + 1 to get the end value of 360 (starts at 0)

The result of the query is displayed below and becomes the source of the dataset for the graph 

Creating the Graph in SQL Server Reporting Services

Now that we have our dataset, we move on to designing the actual chart in SSRS. First, we need to create a new project in SQL Server Data Tools (AKA Visual Studio 2010).

Second, the data source connections must be defined as shown in the below figure.

Subsequently, we are ready to add a new report.  As viewed in next illustration, right click on Reports, Select Add, New Item.  Following this track, select Report as object type and name your report as appropriate.

Now, we can create a dataset using the previously listed code that utilizes the Tally table.  

Finally, we can drag a chart object from the toolbox to the report design grid.

Select the Smooth Line Chart as the Chart Type. 

The Smooth Line chart properties must be adjusted as follows:

• Add the Sin_Measure in the value field
• In the category field, use the Angle_Measure for the X axis
• No series is needed, but we could have potentially added a second series for the cosine value for instance.

To help the line chart look more appropriate, adjust the horizontal properties as noted in the below illustration.  These adjustments will assure that the axis starts at 0 and that the display interval will be marked every 30 degrees, and that the horizontal axis type is set to scalar.  Of course we could add tick marks, if needed.

The resulting Sine graph is displayed below.

Next Steps

• Using a combination of SQL Math functions and the SSRS Chart object, we are able to generate a chart of the Sine function.  The key is to generate the dataset in SQL first and then push that data into the dataset used as a basis for a chart.
• Please see these other tips about SSRS Charts:
  • SQL Server Reporting Services vs. PerformancePoint Services --http://www.mssqltips.com/sqlservertip/2737/sql-server-reporting-services-vs-performancepoint-services/.
  • SSRS Tutorial-- http://www.mssqltips.com/sqlservertutorial/222/sql-server-reporting-services-ssrs/
  • Displaying Multiple Series on an SSRS Chart using Chart Area -- http://www.mssqltips.com/sqlservertip/2468/displaying-multiple-series-on-an-ssrs-chart-using-chart-area/
  • How to handle empty values in a line chart in SSRS -- http://www.mssqltips.com/sqlservertip/2373/how-to-handle-empty-values-in-a-line-chart-in-ssrs/
• See this Khan Academy video for more information about graphing the Sine Function -- http://www.khanacademy.org/math/trigonometry/v/graph-of-the-sine-function

About the author

Scott has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips

We Recommend

Print

Tweet

Become a paid author

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code