By: Scott Murray | Updated: 2012-12-20 | Comments (7) | Reporting Services Charts
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 --============================================================================= -- 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.
CAST(N-1 AS decimal(15,10)) AS Angle_Measure --Carry out to 10 place precision
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
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.
- 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
Last Updated: 2012-12-20
About the author
View all my tips