透過 RS 呈現 R 的圖形

參考:https://www.mssqltips.com/sqlservertip/4127/sql-server-2016-r-services-display-r-plots-in-reporting-services/

我寫的範例報表 ShowRImage.rdl

透過 T-SQL 呼叫 R Script 可以直接傳回圖檔的 binary 結果,就可以透過影像物件呈現

以如下的 R-Script 為例

EXEC   sp_execute_external_script
      @language = N’R’
     ,@script = N’    df <- inputDataSet; #read input data
                image_file = tempfile(); #create a temporary file
                jpeg(filename = image_file, width=500, height=500); #create a JPEG graphic device
                hist(df$Ages); #plot the histogram
                dev.off(); #dev.off returns the number and name of the new active device (after the specified device has been shut down). (device = graphical device)
                #file() opens a file, in this case the image. rb = read binary
                #readBin() reads binary data. what = described the mode of the data. In this case, it"s raw data. n = maximum number of records to read.
                #data.frame converts the data to a data frame, which is required as output by SQL Server. The result is written to the OutputDataset variable.
                OutputDataset <- data.frame(data=readBin(file(image_file,"rb"),what=raw(),n=1e6));
                    ‘
    ,@input_data_1 = N’SELECT Ages = DATEDIFF(YEAR,[BirthDate],GETDATE())
                        FROM [AdventureWorksDW].[dbo].[DimCustomer];’
    ,@input_data_1_name = N’inputDataSet’
    ,@output_data_1_name = N’OutputDataset’
WITH RESULT SETS ((plot varbinary(max)));

從 SSMS 的執行結果可以取到 varbinary(max) 的結果:

image

在 Report Builder 就以這段 T-SQL 當作資料集

image

在報表中加入影像物件,設定來源就是資料庫傳回:

image

透過 Report Builder 和 Report Server 都可以正常執行:

image

image

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: