Использование multi-value параметров в отчетах SSRS

Multi-value параметры со значениями доступными для выбора

С одной стороны, multi-value параметр со значениями доступными для выбора – вещь весьма удобная, с другой стороны, использование таких параметров накладывает дополнительное ограничение. А именно, не только невозможность передать параметру значение NULL, этим страдают все multi-value параметры, но и пустую строку – blank value.
Почему разработчики SSRS  не учли такой вариант, непонятно. Как минимум, можно было бы автоматически добавлять пункт (Blank) или (None) в выпадающий список. Это особенно странно в связи с тем, что вариант (Select All) имеет место быть.

Получается, что если пользователь не выбрал из выпадающего списка ниодного пункта, то отчет банально не будет рендериться, выдавая сообщение об ошибке.

Выходит, что в выпадающий список нужно добавить еще один пункт, который, если его передать в SQL-запрос заведомо не вернет никаких результатов. Когда список доступных значений для такого dropdown’a формируется с помощью запроса, то решение вполне очевидно:

SELECT [Column1] FROM Table1 GROUP BY [Column1]
UNION ALL
SELECT '(None)' as Column1

Теперь к SELECT’у из боевой таблицы будет добавлена еще одна строка со значением (None).

NULL в multi-value параметрах

Как было указано выше, multi-value параметры не могут принимать значение NULL. В то же время, такой параметр может равняться пустой строке (blank value). Достаточно, всего навсего, в настройках параметра указать пустую строку в качестве значения по умолчанию.

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

Если бы при создании репорта использовался single-value параметр, то можно было бы применить конструкцию:

WHERE [Column1] = ISNULL(@param1,[Column1])

Которая возвращает из таблицы все строки, если параметр равен NULL, но с multi-value это не работает.

Первое, что приходит на ум, для решения описанной проблемы – использование CASE.

WHERE [Column1] IN (
CASE WHEN (@param1) <> ‘’ THEN (@param1) ELSE [Column1] END)

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

Значит нужно сформировать SQL запрос динамически. Вызов такого запроса можно оформить с помощью процедуры sp_executesql, которая имеет следующий синтаксис:

EXEC  sp_executesql @sql_query, N'@param type',@param

Кстати если перехватить SQL-запрос от репорт-сервера к SQL-серверу с помощью SQL Profiler, будет видно, что на сервер БД запросы передаются именно в процедуру sp_executesql.

А можно пойти другим путем…

Generic Query Designer

SSRS предлагает в распоряжение разработчика очень мощное, но при этом крайне неудобное средство для создание динамических запросов – Generic Query Designer.

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

Неплохой мануал по работе с GQD можно найти на MSDN: http://msdn.microsoft.com/en-us/library/aa237477(v=sql.80).aspx

Основное неудобство при работе с этим дизайнером (хотя назвать его таковым можно только с большой натяжкой) – отсутствие форматирования, поэтому весь запрос будет записан в одну строку. Ориентировать в таком запросе становится довольно сложно уже на начальном этапе, что уж говорить о более-менее сложных SQL-запросах.

Второе неудобство заключается в том, что при формировании динамического запроса SSRS не может подтянуть названия полей, поэтому прежде чем формировать запрос с помощью Generic Query Designer стоит сделать и отладить отчет, накидать поля, а уже после заниматься формированием запроса динамически.

В GQD динамический запрос, в котором нужно обработать multi-value параметр будет выглядеть примерно так:

=”SELECT [Column1],[Column2],…,[ColumnN] FROM tbl1 
WHERE [Column1] IN (@param1) “ 
& IIF(Parameters!Param2.Value(0) <> “”,”AND [Column2] IN (‘” 
& JOIN(Parameters!Param2.Value,”’,’”) & “’)”,””) & “GROUP BY ….”

Здесь требуется дать несколько пояснений относительно того как SSRS хранит multi-value параметры. Все значения в multi-value параметрах записываются в массив, первая строка которого имеет индекс 0 (ноль), соответственно в Parameters!Param2.Value(0) <> “” проверяется, а не равно ли певое значение пустой строке (переданы ли параметру какие-либо значения или он оперирует значением по умолчанию).

Если первое значение – не пустая строка то SQL-запрос дополняется конструкцией [ColumnN] IN (‘ ,после чего JOIN(Parameters!Param2.Value,”’,’”) объединяет все значения массива в строку, разделяя их сочетанием апостроф-запятая-апостроф, после чего ставится закрывающие параметр апостроф и скобку. Таким образом в тело SQL-запроса внедряется строка вида ‘value1′,’value2′,’valueN’.

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

Ссылки:

Небольшой справочник по single-value и multi-value параметрам на MSDN:
http://msdn.microsoft.com/en-us/library/aa337292.aspx

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

2 Responses to Использование multi-value параметров в отчетах SSRS

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

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