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/.
-- From http://www.sqlservercentral.com/articles/T-SQL/62867/ -- Jeff Moden
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
-- 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
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;
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.
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.
SELECTTOP 11000 IDENTITY(INT,1,1)AS N INTO dbo.Tally FROM Master.sys.ALL_Columns ac1 CROSSJOIN Master.sys.ALL_Columns ac2 ; --===== Add a CLUSTERED Primary Key to maximize performance ALTERTABLE dbo.Tally ADDCONSTRAINT PK_Tally_N PRIMARYKEYCLUSTERED(N)WITHFILLFACTOR= 100 ; --===== Allow the general public to use it GRANTSELECTON dbo.Tally TOPUBLIC ;
Friday, August 23, 2013 - 10:53:23 AM - Jeff Moden
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