Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Graphing the Sine Function in SQL Server Reporting Services

MSSQLTips author Scott Murray By:   |   Read Comments (7)   |   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
--=============================================================================
-- 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
GO

--===================================================================
-- Create a Tally table from 1 to 11000
--===================================================================
--===== Create and populate the Tally table on the fly.

SELECT TOP 11000 IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.sys.ALL_Columns ac1
CROSS JOIN Master.sys.ALL_Columns ac2;

--===== Add a CLUSTERED Primary Key to maximize performance
ALTER TABLE dbo.Tally
 ADD CONSTRAINT PK_Tally_N
 PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100;
 
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC;

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 

Tally Table Data with Angle_Measure, Radian_Measure and Sin_Measure

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

Start a new project in Visual Studio 2010 Shell - SQL Server Data Tools


Create a new Report Server Project

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

Create a new data source for your SSRS report

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.

Add new report to your SSDT project


Add New Item - Report in SSDT tools

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

Create dataset on the Query tab in SSRS

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

Drag a chart onto the grid in the SSDT tool

Select the Smooth Line Chart as the Chart Type. 

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.
Graph properties in SSRS

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.

Define the Axis options in SSRS

The resulting Sine graph is displayed below.

Final Sine Wave Graph in SSRS


Next Steps


Last Update: 12/20/2012


About the author
MSSQLTips author Scott Murray
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, November 25, 2013 - 5:08:45 PM - Scott Murray Read The Tip

Thanks Jeff.... it has been updated


Saturday, November 23, 2013 - 11:03:36 PM - Jeff Moden Read The Tip

Jeff.. you are ABSOLUTELY correct.  I am even using the new version and should have noticed that.

 

So can you change it in the article so people don't end up using it?


Friday, August 23, 2013 - 12:31:27 PM - Scott Murray Read The Tip

Jeff.. you are ABSOLUTELY correct.  I am even using the new version and should have noticed that.


Friday, August 23, 2013 - 11:00:08 AM - Jeff Moden Read The Tip

And, that's the "old" version from way back when SQL Server 2000 was still very much in use.  The newer version uses a different table (view, really) which is SQL Server 2005+ compatible.

--===================================================================
--      Create a Tally table from 1 to 11000
--===================================================================
--===== Create and populate the Tally table on the fly.

 

 SELECT TOP 11000
        
IDENTITY(INT,1,1) AS N
   
INTO dbo.Tally
   
FROM Master.sys.ALL_Columns ac1
  
CROSS JOIN Master.sys.ALL_Columns ac2
;
--===== Add a CLUSTERED Primary Key to maximize performance
  ALTER TABLE dbo.Tally
    
ADD CONSTRAINT PK_Tally_N 
        
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;
--===== Allow the general public to use it
  GRANT SELECT ON dbo.Tally TO PUBLIC
;

 


Friday, August 23, 2013 - 10:53:23 AM - Jeff Moden Read The Tip

Oh dear.  Thanks for the honorable mention but it would appear that you've grabbed the wrong code from the article, Scott.  The code you posted is the WHILE loop example that I used to show why you SHOULDN'T use a WHILE loop for such things.  The better code for creating the Tally Table is in the next code section down from that in the article.  For everyone's convenience, here's the code.

 

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

--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
        IDENTITY(INT,1,1) AS N
   INTO dbo.Tally
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance
  ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally_N
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Let the public use it
  GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC


Thursday, December 20, 2012 - 1:50:05 PM - TimothyAWiseman Read The Tip

That is fantastic.  SQL Server is not exactly the best tool for displaying a mathematical function, but it is very interesting to see that it can be done and shows the versatility of the software.


Thursday, December 20, 2012 - 10:29:24 AM - Ed Read The Tip

This is pretty interesting.  Thanks for sharing.




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.