Динамические отчеты в SSRS

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

Задача
Необходимо провести проверку большого блока исходных данных по различным критериям и подготовить сводный отчет с агрегированными показателями, однако пользователь должен иметь возможность провалиться в исходные данные. Количество критериев, читай SQL запросов для сбора информации может варьироваться, поэтому для целей масштабирования системы должно требоваться минимальное вмешательство.

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

create  procedure [sp_DynamicReporting]
@QryID int -- Идентификатор запроса к БД
as
begin
set nocount on;
declare @sql varchar(max) -- Переменная для хранения динамического SQL
set @sql =
case
when @QryID = 0	then 
'
select ''1'' as QryID, count(distinct Field1) as AggValue1 from Table1 Where A = 1 and B =2
union
select ''2'' as QryID sum(Field2) as AggValue1 from Table 1 where A = 2 and B =3
'
when @QryID = 1	then 'select * from Table1 Where A = 2 and B = 3'
when @QryId = 2 then 'select * from Table1 Where A = 1 and B =2'
end

exec (@sql)
end

Приведенная хранимая процедура возвращает таблицу агрегированных значений, если параметр @QryID равен 0, и наборы исходных данных, если параметру переданы иные значения.
Также хочу обратить внимание на запрос, который выполняется для @QryId = 0. В каждой строке помимо данных будет возвращаться поле [QryID]. Это сделано для того, чтобы серверу SSRS было откуда брать ID запросов при вызове хранимой процедуры для детализированных отчетов.

К большому сожалению нынешний SSRS не позволяет динамически менять названия столбцов в dataset, который используется в отчете, плюс нет возможности динамически изменять количество столбцов.

Чтобы решить первую проблему, придется немного подправить хранимую процедуру.

alter procedure [sp_DynamicReporting]
@QryID int -- Идентификатор запроса к БД
as
begin
set nocount on;
declare @sql varchar(max) -- Переменная для хранения динамического SQL
set @sql =
case
when @QryID = 0	then -- Сводный отчет
'
select ''1'' as QryId, count(distinct Field1) as AggValue1 from Table1 Where A = 1 and B =2
union
select ''2'' as QryID, sum(Field2) as AggValue1 from Table 1 where A = 2 and B =3
'
when @QryID = 1	then 
'
select ''Field1_Name'' as Col1, ''Field2_Name'' as Col2, ''Field3_Name'' as Col3
union all
select Col1, Col2, Col3 from Table1 Where A = 2 and B = 3
' 
when @QryId = 2 then 
'
select ''Field1_Name'' as Col1, "Field2_Name'' as Col2, ''Field3_Name'' as Col3, ''Field4_Name'' as Col4
union all
select Col2_1, Col2_2, Col2_3, Col2_4 from Table1 Where A = 1 and B =2
'
end

exec (@sql)
end

При использовании динамического SQL не забываем экранировать апостроф другим апострофом!!!

В измененной процедуре особое внимание стоит уделить изменениям в динамическом SQL для формирования детализированных отчетов.

select ''Field1_Name'' as Col1, ''Field2_Name'' as Col2, ''Field3_Name'' as Col3
union all
select Col1, Col2, Col3 from Table1 Where A = 2 and B = 3

Единственный способ скормить SSRS табличные данные, полученные с помощью разных запросов – унифицировать названия столбцов. Первый вариант – присвоить алиасы столбцам в исходном запросе, но он плох тем, что при работе с репортом SSRS пользователь никак не сможет понять какие данные лежат в ячейках. Поэтому перед выборкой из боевой таблицей я добавляю еще один SELECT, где банально указываю названия столбцов и присваиваю алиасы Col1, Col2,…,ColN, т.к. именно их я буду использовать при создании отчета.
Стоит обратить внимание, что типы данных для всех полей в строке 1 – nvarchar, поэтому если SELECT в строке 3 возвращает значения с типами данных отличными от nvarchar, нужно конвертировать эти данные в текст руками, например с помощью CAST.

select ''Field1_Name'' as Col1, ''Field2_Name'' as Col2, ''Field3_Name'' as Col3
union all
select Col1, CAST(Col2 as nvarchar(15)) as Col2, Col3 from Table1 Where A = 2 and B = 3

Данные, возвращаемые процедурой будут выглядеть примерно так:

Подобным образом стоит изменить все запросы, которые возвращают исходные данные.
Унифицированные названия столбцов решают проблему с формированием одного dataset’а для всех отчетов, а наличие в первой строке реальных названий столбцов – вопрос с заголовками столбцов в отчете SSRS.

Отчет SSRS

Теперь в двух словах остановлюсь на создании отчета.

При создании dataset’а, я прикрутил к нему описанную ранее хранимую процедуру и руками прописал имена столбцов, которые она возвращает. Так как названия столбцов одинаковы для всех возвращаемых результатов, то единственная переменная величина – их количество. В данном случае действует простое правило – количество столбцов в dataset должно быть равно максимальному количеству столбцов, которое может вернуть хранимая процедура.

И прикручиваю к dataset’у параметр QryID:

Если предполагается, что пользователь не должен иметь возможности задавать значение параметра руками, то его стоит скрыть.

Таблицу в SSRS нужно настроить таким образом, чтобы она могла возвращать все столбцы dataset’а:

Но для каждой колонки стоит создать выражение, которое будет скрывать столбец, если в нем отсутствуют данные:

=iif(First(Fields!Col6.Value, "Dataset_Name") = "",True,False)

И теперь последний штрих – выражение, чтобы подменять заголовки столбцов в таблице:

=First(Fields!Col1.Value, "Dataset_Name")

Очевидно, что здесь главное, чтобы имена столбцов хранимая процедура выдавала в первом ряду.

Далее можно создать еще один отчет, в котором будут отображаться сводные данные. Иными словами, нужно прикрутить к этому отчету dataset, который будет вызывать хранимую процедуру с параметром @QryId = 0, а для ячеек таблицы настроить вызов детализированного отчета по клику мыши и передать ему в качестве значения параметра ID запроса.

Итог
Теперь если возникнет необходимость добавить какие-либо дополнительные сводные данные и детализацию в отчеты, достаточно отредактировать только хранимую процедуру.

Запись опубликована в рубрике Business Intelligence, TSQL с метками , , . Добавьте в закладки постоянную ссылку.

4 Responses to Динамические отчеты в SSRS

  1. Thanks for
    providing this information.

  2. biaya umroh 2017 пишет:

    Hi, thank you so a great deal for these ideas!

  3. wanchai hotel пишет:

    My little comment

  4. Paypal пишет:

    is very interesting

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *