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