{"id":73887,"date":"2017-10-04T13:52:34","date_gmt":"2017-10-04T13:52:34","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=73887"},"modified":"2021-09-15T13:22:18","modified_gmt":"2021-09-15T13:22:18","slug":"sql-server-r-services-generating-sparklines-types-spark-graphs","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/sql-server-r-services-generating-sparklines-types-spark-graphs\/","title":{"rendered":"SQL Server R Services: Generating Sparklines and Other Types of Spark Graphs"},"content":{"rendered":"

The series so far:<\/h4>\n
    \n
  1. SQL Server R Services: The Basics<\/a><\/li>\n
  2. SQL Server R Services: Digging into the R Language<\/a><\/li>\n
  3. SQL Server R Services: Working with ggplot2 Statistical Graphics<\/a><\/li>\n
  4. SQL Server R Services: Working with Data Frames<\/a><\/li>\n
  5. SQL Server R Services: Generating Sparklines and Other Types of Spark Graphs<\/a><\/li>\n
  6. SQL Server R Services: Working with Multiple Data Sets<\/a><\/li>\n<\/ol>\n\n

    Just about anything you can do with the R language you can do in SQL Server R Services. You can transform data, import R packages, and perform a wide range of analytics. You can even generate statistical visualizations that make the data easier to understand, as we discussed in the third article in this series, in which we used the ggplot2<\/strong> package to create several types of charts.<\/p>\n

    In this article, we continue our discussion on visualizations, but switch the focus to sparklines and other spark graphs. As with many aspects of the R language, there are multiple options for generating spark graphs. For this article, we\u2019ll focus on using the sparkTable<\/strong> package, which allows us to create spark graphs and build tables that incorporate those graphs directly, a common use case when working with spark images.<\/p>\n

    In the examples to follow, we\u2019ll import the sparkTable<\/strong> package and generate several graphs, based on data retrieved from the AdventureWorks2014<\/strong> sample database. We\u2019ll also build a table that incorporates the SQL Server data along with the spark graphs. Note, however, that this article focuses specifically on working with the sparkTable<\/strong> package. If you are not familiar with how to build R scripts that incorporate SQL Server data, refer to the previous articles in this series. You should understand how to use the sp_execute_external_script<\/strong> stored procedure to retrieve SQL Server data and run R scripts before diving into this article.<\/p>\n

    Creating the sparklines and other spark graphs<\/h2>\n

    Spark graphs are small images embedded directly in text, lists, or tables to provide quick insight into related data. The sparkTable<\/strong> package supports four graph types: sparklines, sparkbars, sparkboxes, and sparkhists (histograms). Although the process for generating them is similar, each graph takes a different approach to representing the target data.<\/p>\n

    The best way to understand how the graph types differ from one another is to see them in action. The following T-SQL code includes an R script that imports the sparkTable<\/strong> package, retrieves the SQL Server data, creates the four types of graphs, and exports each one to a .pdf file:<\/p>\n

    DECLARE @rscript NVARCHAR(MAX);\r\n  SET @rscript = N'\r\n  \u00a0 # import sparkTable package\r\n  \u00a0 library(sparkTable)\r\n  \u00a0 # retrieve SQL Server data\r\n  \u00a0 salesdf <- InputDataSet\r\n  \u00a0 # define spark objects\r\n  \u00a0 sline <- newSparkLine(values = salesdf$Sales, height = .8, width = 3,\u00a0\r\n  \u00a0 \u00a0 lineWidth = .4, pointWidth = 3, showIQR = TRUE,\u00a0\r\n  \u00a0 \u00a0 allColors = c(\"red3\", \"green4\", \"cyan3\", \"white\", \"blue\", \"gray75\"))\r\n  \u00a0 sbar <- newSparkBar(values = salesdf$Sales, height = .8, width = 3,\u00a0\r\n  \u00a0 \u00a0 barCol = rep(\"blue\", 3))\r\n  \u00a0 sbox <- newSparkBox(values = salesdf$Sales, height = .8, width = 2,\u00a0\r\n  \u00a0 \u00a0 boxCol = c(\"black\", \"green\"))\r\n  \u00a0 shist <- newSparkHist(values = salesdf$Sales, height = .8, width = 2,\u00a0\r\n  \u00a0 \u00a0 barCol = c(\"green\", \"green\", \"black\"))\r\n  \u00a0 # export spark objects\r\n  \u00a0 export(object = sline, outputType = \"pdf\",\u00a0\r\n  \u00a0 \u00a0 filename = file.path(\"C:\\\\DataFiles\", \"SparkLine\"))\r\n  \u00a0 export(object = sbar, outputType = \"pdf\",\u00a0\r\n  \u00a0 \u00a0 filename = file.path(\"C:\\\\DataFiles\", \"SparkBar\"))\r\n  \u00a0 export(object = sbox, outputType = \"pdf\",\u00a0\r\n  \u00a0 \u00a0 filename = file.path(\"C:\\\\DataFiles\", \"SparkBox\"))\r\n  \u00a0 export(object = shist, outputType = \"pdf\",\u00a0\r\n  \u00a0 \u00a0 filename = file.path(\"C:\\\\DataFiles\", \"SparkHist\"))';\r\n  DECLARE @sqlscript NVARCHAR(MAX);\r\n  SET @sqlscript = N'\r\n  \u00a0 SELECT t.Name AS Territories, h.Subtotal AS Sales\r\n  \u00a0 FROM Sales.SalesOrderHeader h INNER JOIN Sales.SalesTerritory t\r\n  \u00a0 \u00a0 ON h.TerritoryID = t.TerritoryID\r\n  \u00a0 WHERE t.[Group] = ''North America'' AND h.Subtotal > 20000;';\r\n  EXEC sp_execute_external_script\r\n  \u00a0 @language = N'R',\r\n  \u00a0 @script = @rscript,\r\n  \u00a0 @input_data_1 = @sqlscript;\u00a0\r\n  GO<\/pre>\n
    Again, you should refer to the previous articles in this series to understand how the sp_execute_external_script<\/strong> stored procedure works and how to import R packages and SQL Server data into the R script. With that in mind, let\u2019s jump to the first graph definition in the R script, in which we define is a sparkline graph:<\/p>\n
    sline <- newSparkLine(values = salesdf$Sales, height = .8, width = 3,\u00a0\r\n  \u00a0 lineWidth = .4, pointWidth = 3, showIQR = TRUE,\u00a0\r\n  \u00a0 allColors = c(\"red3\", \"green4\", \"cyan3\", \"white\", \"blue\", \"gray75\"))<\/pre>\n
    To create the sparkline, we use the newSparkLine<\/strong> function, providing several arguments that define the graph. The first argument, values<\/strong>, specifies the data on which to base the sparkline, which in this case, is the Sales<\/strong> column in the salesdf<\/strong> data set. The remaining arguments determine how to format the sparkline:<\/p>\n