SQL Server 2016: R Services, часть 6, графики в отчетах Reporting Services

Как я уже упоминал раньше, в языке R присутствует достаточно мощная система создания графиков всевозможных типов. Я планирую посвятить отдельные статьи разным типам графиков, а здесь я постараюсь рассказать, как построить простой график на R с помощью R Services и включить его в отчет Reporting Services.

Итак, для построения графика нам в первую очередь потребуются данные. Для этого сгенерируем небольшой тестовый набор данных. Предположим, что у нас есть магазин, который посещают клиенты из 5 различных районов города. По каждому из клиентов мы знаем, сколько раз в неделю в среднем он посещает наш магазин и на какую сумму делает покупки. Мы хотим наглядно увидеть, отличается ли поведение клиентов в нашем магазине в зависимости от района. Ниже представлен скрипт для генерации тестовых данных.

use [TEST];
go
 
if object_id('dbo.test_customer_activity_table', 'U') is not null
	drop table dbo.test_customer_activity_table;
go
 
with region as (
	select cast('A' as char(1)) as region, cast(floor(rand(checksum(newid())) * 10) as int) + 1 as n  union all
	select 'B', cast(floor(rand(checksum(newid())) * 10) as int) + 1 union all
	select 'C', cast(floor(rand(checksum(newid())) * 10) as int) + 1 union all
	select 'D', cast(floor(rand(checksum(newid())) * 10) as int) + 1 union all
	select 'E', cast(floor(rand(checksum(newid())) * 10) as int) + 1
)
select
	region.region,
	t.num_visits,
	t.amount_spent
into dbo.test_customer_activity_table
from region
cross apply (
	select top (n)
		cast(floor(rand(checksum(newid())) * 5) as int) + 1 as num_visits,
		cast(rand(checksum(newid())) * 1000 as decimal(28, 2)) + 1 as amount_spent
	from sys.all_columns
) as t
order by
	region.region;
go

Как видно, данные я сформировал совешенно случайным образом без какой-либо корреляции и вставил в таблицу dbo.test_customer_activity_table. Далее нам нужна будет хранимая процедура, которая возьмет эти данных и на базе них построит график, на котором на оси X будет обозначено количество посещений магазина, по оси Y – средний счет, а районы города мы обозначим на графике точками разного цвета. Для отрисовки я буду использовать пакет ggplot2, про установку которого я уже рассказывал ранее. Итак, процедура будет запускать код на R с помощью sp_execute_external_script, передавая ей набор данных из тестовой таблицы.

create procedure dbo.r_report_test
as
begin
 
	execute sp_execute_external_script
		@language = N'R',
		@script = N'
			library("ggplot2");
			image_file = tempfile();
			jpeg(filename = image_file, width=600, height = 800);
			print(qplot(num_visits, amount_spent, data = SqlIn, color = region,# size = amount_spent,
				xlab = "Number of visits", ylab = "Amount spent",
				main = "Customer activity by region") + geom_point(size = 5));
			dev.off();
			OutputDataSet <- data.frame(data=readBin(file(image_file, "rb"), what=raw(), n=1e6));
		',
		@input_data_1 = N'select region, num_visits, amount_spent from dbo.test_customer_activity_table',
		@input_data_1_name = N'SqlIn'
	with result sets ((plot varbinary(max)));
 
end;
go

Хранимая процедура возвращает набор данных, состоящий из одной строки и одного поля plot, в котором в бинарном формате возвращается графический файл в формате jpeg. Теперь нам только остается отобразить его в отчете Reporting Services. В Visual Studio создаем проект Report Server Project.


После этого справа в Solution Explorer кликаем правой клавишей мыши на папке Reports и добавляем в него пустой отчет.


Сначала в отчет нам нужно добавить Dataset. Для этого слева щелкаем правой клавишей мыши на папке Datasets и добавляем новый.

В нем нужно выбрать соединение к вашему тестовому серверу и базе данных, где мы расположили таблицу и храниму процедуру для формирования графика. В первом окне выбираем Use dataset embedded in my report, далее в Data Source выбираем кнопку New.

В появившемся окне нажимаем на кнопку Edit в поле Connection string.

В появившемся окне набираем имя сервера и базы данных. Т.к. у меня локальный сервер, имя экземпляра по умолчанию, я могу использовать просто точку вместо имени.

После этого нажимаем OK два раза, параметр Query type выставляем Stored procedure и в выпадающем списке выбираем нашу хранимую процедуру.

Нажимаем OK и у нас слева должен появиться наш новый Dataset.

Теперь на наш отчет мы добавляем элемент image и делаем его побольше в размерах, чтобы картинку было лучше видно. И открываем его свойства. В появившемся окне мы должны заполнить свойства Select the image source – Database, выбрать из выпадающего списка имя поля, в котором содержится картинка и выбрать ее формат image/jpeg.

После этого свойства можно закрыть и перейти в размел Preview. Если все хорошо, то у вас должно получиться примерно следующее.

Конечно, это всего-лишь простой пример, но я постарался сделать пошаговое руководство, как включить в отчет графики на R. Ваши отчеты в реальности скорее всего будут выглядить гораздо сложнее, содержать большее количество элементов. Надеюсь этот простой пример поможет вам быстрее начать и создать ваш первый отчет с графиками на R. Оставайтесь на связи, в следующих частях я начну рассказывать про обработку данных с помощью библиотеки ScaleR.