ГлавнаяРазноеЧто это ssrs

Как я улучшал производительность SSRS отчетов. Что это ssrs


Как я улучшал производительность SSRS отчетов / Хабр

Предыстория
Будучи «недомиддлом» в одной Харьковской конторе, я внезапно получил в LinkedIn приглашение переехать в Польшу и начать работать на один большой банк. После очень долгих раздумий я таки согласился, мотивируя себя надеждой, что это будет очень полезный опыт. Поработать на большую корпорацию это не только опыт программирования но и два, ато и три года полезных записей для резюме, а так же +100 к ношению костюма.(Как бы не так, но об этом в другой раз). Оказалось, что уровень говнокода в Польше намного привосходит оный в Украине, а средний уровень Senior девелопера, по крайней мере из тех кого я тут встречал, не идет ни в какое сравнение с украинскими коллегами.

Итак придя на новую работу, я по прошествии трех месяцев организационной рутины и поиска проекта для меня, получил себе тулзу на себя одного, которая генерирует SSRS репорты. Сказать, что здесь клали с высокой колокольни на любые принципы и советы от ведущих мировых инженеров, значит ничего не сказать. Но делать что-то надо — так что пенять уже не на кого.

Дано:
.Net Console Application, которое
— запускается по стандартному планировщику Windows; — скачивает два txt файла с ftp; — делает структурный анализ; — соединяет данные; — запихивает в базу данных; — как результат одна таблица на 100 строковых (преимущественно) колонок, в которую каждый день добавляется около 3000 записей, у которых даже нету id IDENTITY.
SQL Server, в котором хранится база данных, в которой
— охрененно здоровая таблица, с кучей колонок и строк; — нормализация умерла; — есть пара view'юх; — есть сопровождающие таблицы, как информация про пользователя, логи и так далее; — очень важная деталь — в большой таблице хранились два типа данных, которые содержали одинаковые колонки, но должны были сопостовляться друг к другу. Например, информация о сделке со стороны покупателя и покупаемого дома, улица цена и все остальное одинаковое, только несколько полей составляли ключ, по которому можно было соотнести дом и покупателя друг к другу. Это была основная задача отчета.
SSRS отчеты, которые
— лезут непосредственно в базу данных запросами, которые выбирают данные из view'юх, по определенным дням. Можно генерировать как дневной отчет, так и на месяц. Например:SELECT a.col1, a.col2 .... a.col100 FROM vw_Data_With_User_Info WHERE a.TimeStamp >= @StartDate AND a.TimeStamp <=@EndDate
Дополнительные условия:
— Нету доступа к UAT; — В DEV базе данных данные за месяц, тестовые, доступа к реальным данным нету; — Нету прав на Profiler.
Задача
— Отчет на один день загружается примерно 3 минуты, нужно около 40 секунд; — Отчет на месяц загружается около часа, или вообще не загружается — просто исправить по возможности.
Решения:
Нужно отметить, что про SSRS я до этого даже не читал, поэтому, возможно, некоторые действительно хорошие программисты уже видят, почему оно работает так плохо, но у меня решение заняло около месяца.
Попытка 1
Первое, самое логичное решение было уменьшить количество колонок в отчете. Ведь никто не может нормально просматривать данные со 100 колонок на 3000 записей, пускай и группированных по компаниям и пользователям. Поговорили с заказчиком, выяснили наиболее необходимые колонки, и оказалось, что вполне безболезнено можно уменьшить их количество до 16. Так появился на свет отчет Lite. Результат попытки 1. Репорт_Lite на один день — 40 секунд — 1 мин 20 секунд в зависимости настроения сервера и положения звезд. На месяц полчаса — час. На этом заказчики затихли на некоторое время довольствуясь результатом.
Попытка 2
В течение следующего месяца я постепенно старался уменьшать время загрузки отчета, наводя порядок в view'хах и запросах. Например, все расчеты, а с ними и логику сопоставления клиента и дома друг к другу, в консольное приложение, потому что требований к его скорости работы пока никто не предъявлял. После этого в таблице добавилась пара колонок, среди которых был MatchingKey, и резко упала скорость генерации репортов на UAT. Нужно также отметить, что за этот месяц скорость генерации медленно падала с 1минуты (в среднем) до 1.30. Тогда меня спутал факт добавления новой колонки. Я посмотрел, что для записей в таблице до моего релиза MatchingKey is NULL, что естественно заставляло запрос перебирать почти милион записей и не находить соответствий. Плюс я обратил внимание, что сначала выполняется view без ограничения по дате и времени, и только потом из нее выбираются данные по времени. По моей логике это должно было значительно улучшить производительность генерации отчета и, кстати, на DEV сервере работало более ни менее, вкладывался в 40 секунд, но на UAT никак не повлияло.

Результат попытки 2. Практически не виден.

Попытка 3 (успех и всеобщее признание)
После того как я разочаровал мое начальство попыткой два, мне поступило конкретное указание. Бросай все, и занимайся только производительностью. Первой идеей была ПОЛНАЯ ПЕРЕРАБОТКА ПРИЛОЖЕНИЯ. Я хотел разбить большую таблицу на две как файлы и свести их в третьей по id, полностью перенести любые расчеты в .net апликуху. Убрать как можно больше группировок из отчетов и в общем сделать все как велит Макаронный Бог. На это я потратил неделю, но когда дошел до части сохранения данных в базу данных, начал экспериментировать с таблицами и запросами и оказалось, что разницы в скорости выполнения запросов с выбором из view, join двух отдельных таблиц практически нет. По крайней мере не такая, чтобы отчет загружался 1 минуту. На 3000 записей запрос в SQL выполнялся 3-5 секунд. Значит, дело в группировке данных на клиент cтороне. Убираем все группировки, чистый отчет даже без сортировки — минус пара секунд к генерации. Получается на группировку уходит пара секунд, на SQL запрос 3-5. А куда девается еще почти минута?

Вопрос не риторический, и по хорошему такие эксперименты нужно было проводить перед попыткой 1, но когда дошло — тогда дошло. Продолжаем эксперимент. Начинаем играться с запросами из отчета. К тому времени уже запросил у админов права на доступ к Profiler. Открыл Profiler, но поскольку особого (никакого) опыта работы с ним не имел, поглядывал не особо внимательно. И тут в какой-то момент мне надоело каждый раз вводить @StartDate и @EndDate, это раздражает, когда каждые полминуты нужно кликать два календаря. Поэтому в репорте без группировки я зашел в генератор запросов и вместо @StartDate и @EndDate написал '2014-06-11'. Хлоп, 3 секунды на генерацию! Как так? Наверное, имело место кэширование, очистил кэш везде, еще раз. 3 секунды. Не может быть. Теперь с группировкой — 5 секунд. Да вы шутите. После получаса чесания репы и оновременного копания в Profiler я, наконец-то, нашел ту тварь, что мучала меня месяц, а этот бедный проект — полгода.

Результат
Оказалось, что имея в отчете запрос например:SELECT * FROM Table WHERE time >= @StartDate AND time <= @EndDate

На стороне SQL он будет выполнена в таком виде:

EXEC sp_executesql N'SELECT * FROM Table WHERE time >= @StartDate AND time <= @EndDate','@StartDate datetime, @EndDate datetime','2014-06-11','2014-06-11'

И уже вот такой запрос выполняется около 1.30 секунд. Тогда как когда я подставил в запрос сразу значения, то выполнился не DSQL, а обычный запрос.

В результате я смог переубедить клиета перейти на Stored Procedre'ы и теперь дневной репорт загружается 6 секунд, месячный — 1 минуту.

Выводы
  • 1.Поспешишь людей насмешишь
  • 2.Поспешишь потеряешь месяц
  • 3.Поспешишь начальство разозлишь
  • 4.Лучше потратить время на понимание проблемы, а не предпологать возможные решения, особенно в теме, в которой ты новичек.

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

P.S.2 У меня есть пара предположений по поводу того, почему подобный запрос выполняется так долго, но я буду очень благодарен, если найдется кто-то, кто точно знает, почему — и объяснит мне.

UPD1: Я учел нарекания к этой статье и постарался исправить все слова, который вызвали критику.

UPD2: Сегодня в новом отчете, уже переделав его на хранимую процедуру все равно столкнулся с проблемой.

exec rOtex_Mis_DailyHighLevelReport @StartDate='2014-06-01 00:00:00',@EndDate='2014-06-30 00:00:00' во время генерации отчета выполнялось 6 минут, а руками 1 секунду. Поэтому залез в гугл с конкретным вопросом, «SSRS stored procedure slow». И получил вразумительный ответ. Горе мне гугл-рукожопу, он лежал на поверхности ПРАВИЛЬНОГО поиска. stackoverflow.com/questions/2283943/fast-query-runs-slow-in-ssrs Дело в том, что это известная проблема SQL Servera, которая относится к Parameter Sniffing, как мне справедливо посоветовали товарищи BelAnt и microuser — и решение довольно говнокодное и, как часто бывает с говнокодом, простое. Приведу пример из моей хранимой процедуры: @StartDate Date, @EndDate Date AS BEGIN declare @Start datetime declare @End datetime set @Start = @StartDate set @End = @EndDate

И это работает:

Вывод 2.0
-Гугли нормально, епта

UPD3: После нескольких тестов, оказалось что хранимые процедуры тоже были выдуманы от «большого ума». Оказалось, что достаточно добавить в конец запроса OPTIONS(RECOMPILE), как и советовал товарищ BelAnt. Тут выводы писать не буду, коли воно без мозоку то його выводы до сраки:(

habr.com

Программное совершенствование отчетов SSRS | Windows IT Pro/RE

Службы SQL Server Reporting Services (SSRS) — платформа для подготовки отчетов корпоративного уровня, размещаемая над широко распространенными службами в серверных технологиях Windows, в том числе SQL Server, ASP.NET и Microsoft. NET Framework. Одно из самых важных качеств SSRS — возможность расширить функции подготовки отчетов с помощью пользовательского кода и программных модулей. В этой статье я поясню, насколько пользовательский код и модули практически применимы при формировании ежедневных отчетов, и расскажу о встроенных модулях SSRS и концепции настраиваемых сборок. Затем будут даны пошаговые инструкции по подготовке специализированной сборки с помощью Visual Studio и ее использования для построения набора данных динамического отчета.

Выбор инструмента

Программисты привыкли использовать инструменты с возможностями объектно-ориентированного проектирования (например, управление экземплярами объектов, интерфейсы, наследование классов) и моделями программирования, управляемыми событиями, для построения пользовательских приложений и компонентов приложений. Важно понимать, что для практического применения SSRS не поддерживает таких программных методов и моделей, и на то есть веские причины. SSRS — платформа подготовки отчетов, а не инструмент программирования.

Во время обсуждений методов подготовки отчетов в моем блоге по SQL Server BI и на других форумах мне иногда приходится встречать такие мнения программистов: «Мне не нужен такой инструмент подготовки отчетов, как SSRS. Я могу просто спроектировать специализированное веб-приложение, а затем самостоятельно управлять всем с помощью программных объектов. NET». Я уважаю это мнение и считаю такой подход обоснованным, если требуется управлять каждым элементом пользовательского интерфейса, поведением и взаимодействием. С помощью кода событий и пользовательских элементов управления можно добавить удобные функции, просто недоступные в SSRS. Но при этом необходимо предусмотреть, что произойдет, если объем данных в пользовательском отчете окажется в 100 раз больше ожидаемого или отчет будет запущен несколькими пользователями одновременно. И как удовлетворить потребность пользователя просмотреть отчет в разнообразных форматах? Такие вопросы успешно решаются в SSRS без дополнительных усилий со стороны программиста.

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

Модули

В основе SSRS лежит расширяемая архитектура. Признаюсь, что начиная работать с SSRS в 2003 году, я был очарован расширяемостью служб и возможностью добавлять программный код в решения для отчетов. Как программист, работающий в. NET, я полагал, что отчеты будут естественным продолжением моих усилий как программиста. Но построив несколько сложных решений для отчетов и работая с другими программистами над архитектурой крупных решений в течение десяти лет, я понял, что огромному большинству решений SSRS не требуется дополнительное программирование. Лучшее применение библиотеки пользовательского кода — расширить встроенные возможности архитектуры SSRS, а не заменить удачные функции. Подготовка пользовательских модулей — непростая задача, и обычно она выходит за рамки практического применения для подготовки корпоративных отчетов.

Во внутренних механизмах SSRS используется набор сборок. NET, представляющий собой модули для работы с большинством основных функций. SQL Server версий Standard, Business Intelligence и Enterprise Editions автоматически устанавливает некоторые из этих модулей. Например, при экспорте отчета в Microsoft Excel применяется модуль подготовки отчетов Excel. При планировании подписки модуль доставки электронной почты или файловой системы используется вместо стандартного модуля доставки интерактивных отчетов.

В SQL Server версий Standard, Business Intelligence и Enterprise Editions службы SSRS обеспечивают расширение следующими способами.

  • Модули обработки данных. Эти модули предоставляют доступ к различным типам данных с использованием согласованной программной модели. Среди модулей для обработки данных — модули для SQL Server, SQL Server Analysis Services (SSAS), SQL Server Parallel Data Warehouse, SQL Azure, списков Microsoft SharePoint, моделей отчетов, ODBC, OLE DB, Hyperion Essbase, Oracle, SAP NetWeaver Business Intelligence, Teradata и XML. Кроме того, компания Microsoft предоставляет модули для обработки данных для SAP Relational DB и DB2.
  • Модули доставки. Эти модули взаимодействуют с каким-либо механизмом доставки для отправки отчетов пользователям. Среди них модули для интерактивных отчетов, электронной почты, файловой системы и поставщика NULL; они позволяют воспроизводить отчеты без сохранения вывода.
  • Модули подготовки отчетов. Эти модули управляют типом документа или носителя, создаваемого при выполнении отчета. Компания Microsoft предоставляет несколько модулей, охватывающих широко распространенные и полезные типы документов, в том числе HTML, веб-архив, PDF, Excel, Microsoft Word, изображения, XML и CSV (с разделением запятыми).
  • Модули безопасности. Они обеспечивают проверку подлинности и авторизацию пользователей для запуска отчетов на сервере отчетов. Предоставляемые модули поддерживают встроенную в Windows защиту и проверку подлинности ASP.NET на основе форм. Можно реализовать и собственную модель безопасности на основе ролей с любым критерием проверки подлинности.
  • Модули обработки отчетов. Эти модули обеспечивают создание пользовательских элементов отчетов, управляемых механизмом обработки отчетов. С их помощью можно расширить стандарт языка определения отчетов (RDL), дополнив его функциональностью, изначально отсутствующей в RDL, в частности пользовательскими картами MapPoint и горизонтальными списками. Также можно расширить имеющиеся элементы отчета, добавив альтернативные версии, более подходящие для конкретного применения.
  • Модули настройки определения отчета предоставляют выход к предварительной обработке определения отчета. Можно подключить пользовательский код, который изменяет поток определения отчета перед его обработкой. Это удобно, если необходимо изменить структуру отчета в зависимости от культурных или местных особенностей, либо личности пользователя, указанного в запросе отчета.

При необходимости создать пользовательские модули можно учиться на опыте других разработчиков. Я работал с компаниями, в которых SSRS применяется как составная часть набора служб, и некоторые из них создали собственные модули. Одна компания построила специальный модуль безопасности, чтобы пользователи могли выполнять регистрацию и проходить проверку подлинности в ее программе, не прибегая к Windows. Другие компании со сложными уровнями данных и прикладного программирования строят специальные модули обработки данных, которые используются вместо стандартных поставщиков данных в SSRS. Например, одна компания построила модуль для обработки данных, применив его к сложным бизнес-правилам и логике фильтров, специфичным для отрасли. В каждом случае группам программистов потребовались месяцы, чтобы создать продукт, устранить ошибки, протестировать и внедрить специализированные модули в производство.

Настраиваемые сборки

Сборка — модуль повторно используемого программного кода, обычно подготовленный с помощью языка программирования. NET в среде Visual Studio и часто скомпилированный в DLL-файл, содержащий повторно используемые функции и другую программную логику. Специализированную сборку можно применить, чтобы расширить функциональность отчетов на многие отчеты на одном сервере. Например, если нужно обработать параметрические бизнес-правила, распространяющиеся за рамки возможностей языка запросов, такого как T-SQL или MDX, можно задействовать специализированную сборку для управления запросом к набору данных отчета и связанным поведением отчета. SQL — мощный язык, с помощью которого можно обработать достаточно громоздкую бизнес-логику. При возможности можно задействовать язык запросов — и особенно устойчивые объекты базы данных, такие как хранимые процедуры — для инкапсуляции сложных правил. Лучшие примеры наиболее эффективного применения этого метода гораздо сложнее, чем я привожу в данной статье. Однако показанные приемы представляют интерес и, в некоторых случаях, могут оказаться необходимыми.

Создание специализированной сборки

В следующем примере показано, как построить специализированную сборку, которая собирает инструкцию T-SQL на основе нескольких параметризованных аргументов. Сборка проектируется в среде Visual Studio 2010 в проекте библиотеки классов, который может быть частью того же решения, что и проект отчета. После тестирования сборка развертывается в доверенных папках, где ее можно использовать в SQL Server Data Tools (SSDT) или конструкторе отчетов Business Intelligence Development Studio (BIDS) на сервере отчетов. Проект выполнен в Visual Basic. NET, но с тем же успехом можно использовать C#. Рабочий экземпляр отчета и образцовый программный код проекта можно загрузить, нажав кнопку Download.

Создание представления и проекта библиотеки классов

Первая задача — создать представление vSalesByTimeAndTerritory в базе данных AdventureWorksDW2012. Убедившись, что вы имеете разрешение на создание объектов в этой базе данных, откройте окно запросов SQL Server Management Studio (SSMS) и выполните сценарий листинга 1, чтобы создать представление.

Затем необходимо создать проект библиотеки классов в среде Visual Studio, выполнив следующие действия.

  1. Из меню File в Visual Studio выберите New и пункт Project.
  2. В окне Add New Project («Добавление нового проекта») выберите Visual Basic в области Installed Templates («Установленные шаблоны»).
  3. На центральной панели выберите Class Library («Библиотека классов») и назовите проект Report_Class_Lib.
  4. В обозревателе решений измените имя класса файлов по умолчанию на ReportLib.cls.
  5. Откройте ReportLib.cls в редакторе кода Visual Basic. Работая с этим редактором, рекомендуется включить функции завершения и отладки, чтобы упростить работу. Например, если представлен список объектов, можно выбрать нужный объект с помощью клавиш со стрелками, а затем добавить свой код нажатием клавиши Tab. Можно также нажать клавишу Enter, чтобы выбрать объект и добавить новую строку кода.
  6. После объявления Public Class ReportLib введите исходный текст из листинга 2. Он создает функцию SalesQuery, которая строит запрос T-SQL на основе условной логики в программном коде. Обратите внимание, что символы продолжения строки необязательны и приведены только для того, чтобы исходный текст уместился на странице. При желании можно пропустить символы подчеркивания (_) и возвраты каретки в каждой строке, завершающейся символом подчеркивания.
  7. Выполните проверку на наличие ошибок и предупреждений с красными или зелеными извилистыми линиями под ключевыми словами.
  8. В обозревателе решений щелкните правой клавишей мыши проект Report_Class_Lib и выберите пункт Build («Сборка»).

Развертывание библиотеки классов

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

В этом примере используется путь C:Program Files (x86)Microsoft Visual Studio 10.0Common7IDEPrivateAssemblies для папки сборок Visual Studio 2012. Для сервера отчетов SSRS применяется путь отчетов по умолчанию в SSRS 2012 — C:Program FilesMicrosoft SQL ServerMSRS11.MSSQLSERVERReporting ServicesReportServerbin. Обратите внимание, что если используется SSRS 2012 в SharePoint, то путь по умолчанию — C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14WebServicesReportingbin.

После того, как пути внесены в Notepad, выполните следующие действия.

1. Откройте свойства проекта. В разделе Compile («Компилировать») выберите Build Events («События построения»).

2. В текстовом поле Post-build event command line («Командная строка события после построения») введите следующие команды, указывая пути, скопированные в Notepad:

COPY /Y «C:UsersAdministratorDocuments Visual Studio 2010ProjectsReport_Class_LibbinDebug Report_Class_Lib.dll» «C:Program Files (x86) Microsoft Visual Studio 10.0Common7IDEPrivateAssemblies» COPY /Y «C:UsersAdministratorDocuments Visual Studio 2010ProjectsReport_Class_LibbinDebug Report_Class_Lib.dll» «C:Program Files Microsoft SQL ServerMSRS11.MSSQLSERVER Reporting ServicesReportServerbin»

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

COPY /Y «C:UsersAdministratorDocuments Visual Studio 2010ProjectsReport_Class_LibbinDebug Report_Class_Lib.dll» «C:Program FilesCommon Files Microsoft SharedWeb Server Extensions14 WebServicesReportingbin»

3. Настройте событие после построения для запуска после успешного завершения сборки, как показано на экране 1.

 

Экран 1. Настройка события для запуска после успешного завершения сборки

4. Нажмите кнопку OK, чтобы сохранить изменения.

5. Постройте проект заново.

С помощью проводника Windows убедитесь, что файл Report_Class_Lib.dll был скопирован куда нужно и имеет недавнюю дату изменения.

Создание отчета

Теперь можно создать отчет. К нему предстоит добавить ссылку на сборку (экран 2), параметры и выражение. Выполните следующие действия.

 

Экран 2. Добавление ссылки на сборку в отчет

1. Создайте новый отчет с именем Generated Query.

2. Выберите Report Properties («Свойства отчета») из меню Report («Отчет»).

3. В диалоговом окне Report Properties выберите страницу References («Ссылки»).

4. В разделе Add or remove assemblies («Добавить или удалить сборки») нажмите кнопку Add («Добавить»).

5. Нажмите кнопку с многоточием (...) справа от новой строки.

6. В диалоговом окне Add Reference («Добавление ссылки») перейдите на вкладку Browse («Обзор») и найдите файл Report_ClassLib.dll в папке доверенных сборок.

7. Выделите файл Report_ClassLib.dll и нажмите кнопку OK, чтобы добавить reference.

8. В разделе Add or remove classes («Добавить или удалить классы») страницы References нажмите кнопку Add.

9. В поле под столбцом Class Name («Имя класса») введите Report_Class_Lib.ReportLib.

10. В поле под столбцом Instance Name («Имя экземпляра») введите m_ReportLib. Это псевдоним имени класса, который будет использоваться в отчете.

11. В окне Report Data («Данные отчета») в левой стороне конструктора отчетов щелкните правой кнопкой мыши узел Parameters, чтобы добавить новый параметр.

13. Воспользуйтесь окном Report Parameters («Параметры отчета»), чтобы добавить каждый параметр. Используйте информацию из таблицы, чтобы задать имя, приглашение, тип и значение каждого параметра по умолчанию.

 

14. Для параметра SalesQuery установите видимость Hidden («Скрытый») на странице General («Общие») в диалоговом окне Report Parameters («Параметры отчета»), а затем перейдите на страницу Default Value («Значение по умолчанию»).

15. Выберите Specific values («Определенные значения»), чтобы добавить значение по умолчанию.

16. Нажмите кнопку Expression («Выражение») (fx) справа от раскрывающегося списка.

17. Введите следующий текст, а затем поместите курсор между скобками:

=Code.m_ReportLib.SalesQuery( )

18. Выберите Parameters («Параметры») из списка Category («Категория») и дважды щелкните параметр YearFrom, чтобы добавить reference в выражение. Введите после этого текста запятую с последующим пробелом.

19. Используйте ту же процедуру для ввода дополнительных параметров, необходимых для построения следующего выражения (без возвратов каретки):

=Code.m_ReportLib.SalesQuery(Parameters!YearFrom.Value , Parameters!YearTo.Value, Parameters!Country.Value , Parameters!MaxRows.Value, Parameters!IncludeFreight.Value)

20. Нажмите кнопку OK, чтобы сохранить выражение.

21. Добавьте текстовое поле к отчету.

22. Щелкните правой кнопкой мыши текстовое поле и выберите пункт Expression («Выражение»).

23. Используйте Expression Builder («Построитель выражений»), чтобы присвоить выражению текстового поля следующее значение:

=Parameters!SalesQuery.Value

24. Просмотрите сформированный запрос. Он будет показан в текстовом поле, как на экране 3.

 

Экран 3. Предпросмотр сгенерированного отчета

25. Убедитесь в правильности синтаксиса сформированного запроса. Он должен быть следующим:

  • ВЕРХНЕЕ значение должно быть задано параметром MaxRows;
  • столбец Freight должен войти в список столбцов, так как параметру IncludeFreight присвоено значение True;
  • значения YearFrom и YearTo в предложении WHERE должны соответствовать значениям параметров YearFrom и YearTo, соответственно;
  • предложение WHERE не должно содержать столбца SalesTerritoryCountry, так как параметру Country присвоено значение All.

26. На вкладке предварительного просмотра измените значения параметров и повторно запустите отчет, чтобы увидеть последствия сформированного запроса.

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

Использование сформированного запроса для создания набора данных отчета

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

  1. Разверните отчет на сервере отчетов и запустите отчет.
  2. Пока отчет выполняется в браузере, выделите все содержимое текста параметра SalesQuery, показанное в верхней части отчета, и скопируйте сценарий SQL в буфер обмена.
  3. В конструкторе отчетов перейдите в представление конструирования.
  4. Добавьте источник данных, чтобы подключиться к базе данных AdventureWorksDW2012. Можно использовать встроенный или общий источник данных.
  5. Добавьте новый встроенный набор данных к отчету и назначьте набору данных имя ReportData.
  6. В диалоговом окне Properties набора данных вставьте скопированный сценарий SQL в поле Query («Запрос»).
  7. Нажмите кнопку OK. Обратите внимание, что конструктор отчетов выполняет запрос, чтобы сформировать список полей набора данных.
  8. Разверните список полей набора данных на панели Report Data.
  9. Добавьте таблицу в текст отчета.
  10. Переместите с помощью мыши поля CalendarYear и MonthNumberOfYear на панель Row Groups («Группы строк») в нижней части конструктора.
  11. Измените ячейку заголовка строки MonthNumberOfYear в таблице, чтобы показать поле MonthName.
  12. Переместите с помощью мыши поле SalesTerritoryCountry в первую открытую ячейку сведений в таблице.
  13. Отредактируйте группу Details на панели Row Groups. Добавьте группу с использованием поля SalesTerritoryCountry.
  14. Переместите с помощью мыши поля SalesAmt и Freight в ячейки сведений таблицы.
  15. В целях отладки измените значение параметра SalesQuery на Visible, но обязательно скройте его после завершения отладки. В противном случае отчет не будет выполняться корректно.

Функционально отчет завершен, но в нем есть ряд уязвимых мест. Разработчики служб SSRS предусмотрели несколько мер безопасности, но не играйте с огнем. В этот пример не нужно вносить изменений, а при внедрении в производство рекомендуется изучить материал раздела «Безопасность — в первую очередь», чтобы исключить возможность атаки путем внедрения кода SQL.

Динамическое скрытие столбца Freight

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

1. Щелкните ячейку таблицы, чтобы показать маркеры выделения столбца и строки.

2. Щелкните правой кнопкой мыши серый заголовок для столбца Freight и выберите пункт Column Visibility.

3. В диалоговом окне Column Visibility выберите переключатель с меткой Show or hide based on an expression («Показать или скрыть в зависимости от выражения»).

4. Откройте диалоговое окно Expression Builder («Построитель выражений») и введите выражение

=(Fields!Freight.IsMissing).

5. Нажмите кнопки OK, чтобы сохранить и закрыть диалоговые окна Expression Builder и Column Visibility («Видимость столбца»).

Изменение набора данных для использования сформированного кода

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

1. На панели Report Data дважды щелкните ReportDatadataset.

2. В диалоговом окне Dataset Properties («Свойства набора данных») нажмите кнопку Expression («Выражение») (fx) рядом с полем Query («Запрос»).

3. В диалоговом окне Expression Builder замените текст команды ссылкой на параметр SalesQuery:

=Parameters!SalesQuery.Value

4. Нажмите кнопку OK, чтобы сохранить и закрыть диалоговые окна Expression Builder и Dataset Properties.

5. Убедитесь, что параметр SalesQuery имеет значение Hidden.

6. Измените свойство Hidden, чтобы скрыть текстовое поле, в котором показано значение параметра SalesQuery.

7. Просмотрите отчет и поэкспериментируйте с различными значениями параметра. Образец отчета показан на экране 4.

 

Экран 4. Предпросмотр отчета

Отладка запроса

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

Если вас не устраивают полученные результаты, следует сначала нажать кнопку Refresh («Обновить») на инструментальной панели отчетов, чтобы очистить кэш выполнения и перезапустить запрос. SSRS устроен так, что прошлые результаты сохраняются в кэше.

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

Безопасность — в первую очередь

Всегда, когда запрос формируется из данных пользовательского ввода, необходимо обратить внимание на безопасность. Внедрение кода SQL — метод, используемый хакерами для несанкционированного доступа к данным и компонентам сервера базы данных. Службы SSRS менее уязвимы для атак путем внедрения кода SQL благодаря ряду встроенных мер безопасности, однако важно проявлять

www.osp.ru

Reporting Services 2008 под Sharepoint 2010 / Хабр

В данной статье я хочу рассказать о моем опыте развертывания и использования SSRS 2008 в крупной компании. Процесс настройки и развертывания был 3 года назад, а сама система Reporting Services используется организацией уже 4 года.

Изначально ситуация была такая – фирма купила Sharepoint 2010 и хотела, чтобы на нем были отчеты, которые могли бы просматривать сотрудники предприятия. Данные для отчетов хранились в Oracle. Выбор был сделан в пользу Reporting Services 2008. Так как SSRS 2008 интегрирован в Sharepoint и у предприятия был в наличие MS SQL Server 2008, со всеми его компонентами. Параллельно принимались попытки реализовать отчеты на Oracle BI, но по неизвестным мне причинам, эти разработки прекратились, после того как массово получилось внедрить SSRS 2008. При работе с Reporting’ом возник ряд проблем, о которых я хочу рассказать ниже: Первая проблема, с которой пришлось столкнуться – это зависание при возрастании числа запросов от пользователей. Эта проблема чуть не сгубила всю затею, т.к. сервер переставал отвечать на запросы, и его приходилось перегружать. А так как пользователей много, то он практически сразу же после перезагрузки ложился опять.

Проблема оказалась в пуле коннекций к базе. Если его отключить в параметрах соединения с базой, то всё работало стабильно, но медленно. Это, в свою очередь, сильно не устраивало пользователей. Так же можно было изменить значение ConnectTimeout, но привело к тому, что с увеличением нагрузки на сервер, отчеты, которые раньше всегда выполнялись стали отваливаться по истечении времени ожидания. Проблему решили, установив одинаковые значения max pool size и min pool size. На данный момент оба эти значения равны 23.

Вторая проблема состояла в том, что появились отчеты, которые выполнялись очень долго. Решением этой проблемы было выкручивание двух таймаутов — SessionAccessTimeout и SessionTimeout. Их можно выставить с помошью SQL скрипта в базе SSRS:

SetConfigurationInfo 'SessionAccessTimeout', '7200' SetConfigurationInfo 'SessionTimeout', '7200'

До какого-то момента это помогает. Но рано или поздно появляются такие отчеты, которые все равно приводят к timeout expired. Грузить же их в offline было проблематично, т.к. доступ к данным в отчете зависел от учетной записи пользователя, а массив загруженных данных отличался существенно, в зависимости от разрешений пользователя. Текущее решение заключается в специальной утилите, разработанной мной, для выгрузки отчетов, которые очень долго формируются из базы.

Так же ряд отчетов, с не очень большим временем выполнения, падали с ошибкой: “Request timed out” или по-русски пишет “Превышен интервал ожидания для запроса”. Эта проблема связанна уже с размером отчета. Для исправления этой ошибки необходимо совершить три действия:

В процессе внедрения, выяснилось, что практически все пользователи в организации предпочитают работать в Excel, а не с Web-формой отчета. Поэтому они каждый раз выгружали отчет в формате Excel к себе на компьютер и далее работали с ним. Когда размер отчета становился слишком большим, то SSRS не мог его выгрузить. Он зависал с сообщением об ошибке.

Возможно, проблема тут была не в SSRS 2008, а в расширении от фирмы Aspose. Это расширение было установлено для поддержки формата xlsx, так как Reporting Services 2008 не поддерживали данный формат. Данная проблема возникала только при экспорте в Excel (*.xlsx), при экспорте в csv формат подобного не наблюдалось. В итоге, задача была решена с помощью отдельной утилиты, благо таких отчетов оказалось не так много.

Стоит отметить так же пару моментов при разработке отчетов под SSRS 2008:Первый момент – это ошибка при делении на 0. Если, например, вам надо вывести значения в колонке, которое является результатом деления одного поля выборки на другое, то выражение

Iif(exp2 = 0, 0, exp1/exp2 ) , приведет к ошибке. Т.к. Reporting сначала выполняет все операции внутри iif, а потом уже проверяет условие. Я решил эту проблему через VB скрипт (поле Code в свойствах отчета). Мой скрипт выглядит так:Public Function DivM(ByVal exp1, ByVal exp2) if exp2 = 0 Then DivM = 0 Else DivM = exp1/exp2 End If End Function

Его можно прописать в выводимой колонке вместо iif.

Второй момент – это передача в запрос multiple values параметров. В случае если тип параметров числовой, то проблем не возникнет. Но если надо передать строковые значения, то так же необходимо будет внести изменения в поле “Значения параметра”. Надо вместо

Parameters!МойПараметр.Value написать split(join(Parameters!МойПараметр.Value,”,”),”,”).Ещё при работе в Reporting Services c БД Oracle следует все числовые поля явно округлять непосредственно в SQL запросе, т.к. у этих систем различная точность.

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

Что касается установки SSRS 2008, то изначально он устанавливался вместе с базой данных Shrepoint 2010, но позже, когда разжились дополнительными серверами, SSRS был установлен на отдельной машине.

На данный момент организация приобрела Sarepoint 2013 и SSRS 2012, но мое непосредственное руководство не проявляет интереса к переходу на новые версии, мотивируя это стабильностью старых.

При работе с большими объемами данных, там, где это возможно, используется Analysis Services. В сравнении с Analysis Services, основное удобство SSRS, в том что отчет может создавать программист баз данных, на основе уже написанного им запроса. Создание же сущностей на Analysis Services требует больших усилий и времени.

Надеюсь, что данная информация будет полезной и либо поможет в эксплуатации SSRS 2008, либо повлияет на принятие решения об эксплуатации данного продукта.

habr.com

SQL Server Reporting Services - это... Что такое SQL Server Reporting Services?

SQL Server Reporting Services (сокр. SSRS, рус. Службы отчетности SQL Server) — программная серверная система создания отчетов, разработанная корпорацией Microsoft. Она может быть использована для подготовки множества интерактивных и печатных отчетов. Система администрируется через веб-интерфейс. Reporting services используют интерфейс веб-служб для поддержки разработки обычных отчетных приложений.

SSRS соперничает с Crystal Reports и другими BI-инструментами, и входит в состав Express, Workgroup, Standard и Enterprise версий Microsoft SQL Server в качестве устанавливаемого дополнения. Reporting Services были впервые выпущены в 2004 году как дополнение для SQL Server 2000. Вторая версия была выпущена в виде составной части SQL Server 2005 в ноябре 2005 года. Последняя на данный момент версия была выпущена как часть SQL Server 2008 в августе 2008.

В SSRS отчеты описываются при помощи Report Definition Language (RDL) на языке разметки XML. Отчеты могут проектироваться при помощи последних версий Microsoft Visual Studio (включая Visual Studio.NET 2003 и Visual Studio 2005) с входящим в них дополнением Business Intelligence Projects или при помощи входящего в комплект Report Builder — упрощенного инструмента, не предлагающего полного функционала Visual Studio. Отчеты, определенные при помощи RDL, могут создаваться во множестве различных форматов [1], включая Excel, PDF, CSV, XML, TIFF (и других графических форматах[2]), а также HTML Web Archive. SQL Server 2008 SSRS также может подготавливать отчеты в формате Microsoft Word (DOC).

Сторонние генераторы отчетов предлагают возможность создания отчетов в дополнительных форматах.

Пользователи могут работать с веб-службой Report Server напрямую или использовать Report Manager — веб-приложение, взаимодействующее с веб-службой Report Server. При помощи Report Manager могут просматривать и управлять отчетами, также как и управлять и оперировать источниками данных и настройками безопасности. Отчеты могут рассылаться по электронной почте или записываться на файловую систему как обычный файл. Защита выполняется на основе ролей и может накладываться на отдельные элементы, как например, отчет или источник данных, каталог элементов или сайт вообще. Роли безопасности и права являются наследуемыми и могут быть переопределены.

В дополнение к использованию отдельного Report Server, поставляемого с SQL Server, RDL-отчеты можно просматривать при помощи веб-контрола ASP.NET ReportViewer или Windows Forms-контрола ReportViewer. Это позволяет встраивать отчеты прямо в веб-страницы или .NET-приложения. Контрол ReportViewer обрабатывает отчеты одним из двух способов: (a) на стороне сервера, где отчет обрабатывается Report Server; и (b) локальная обработка, где соответствующий контрол самостоятельно обрабатывает RDL-файл.

SQL 2005 reporting services также поддерживает обработку произвольных отчетов: разработчик создает схему отчета и развертывает ее на сервере отчетности, где пользователь может выбирать нужные поля/данные и создавать отчеты в соответствии со своими нуждами. Потом пользователи могут загружать отчеты локально.

См. также

Примечания

Ссылки

med.academic.ru

SQL Server Reporting Services - это... Что такое SQL Server Reporting Services?

SQL Server Reporting Services (сокр. SSRS, рус. Службы отчетности SQL Server) — программная серверная система создания отчетов, разработанная корпорацией Microsoft. Она может быть использована для подготовки множества интерактивных и печатных отчетов. Система администрируется через веб-интерфейс. Reporting services используют интерфейс веб-служб для поддержки разработки обычных отчетных приложений.

SSRS соперничает с Crystal Reports и другими BI-инструментами, и входит в состав Express, Workgroup, Standard и Enterprise версий Microsoft SQL Server в качестве устанавливаемого дополнения. Reporting Services были впервые выпущены в 2004 году как дополнение для SQL Server 2000. Вторая версия была выпущена в виде составной части SQL Server 2005 в ноябре 2005 года. Последняя на данный момент версия была выпущена как часть SQL Server 2008 в августе 2008.

В SSRS отчеты описываются при помощи Report Definition Language (RDL) на языке разметки XML. Отчеты могут проектироваться при помощи последних версий Microsoft Visual Studio (включая Visual Studio.NET 2003 и Visual Studio 2005) с входящим в них дополнением Business Intelligence Projects или при помощи входящего в комплект Report Builder — упрощенного инструмента, не предлагающего полного функционала Visual Studio. Отчеты, определенные при помощи RDL, могут создаваться во множестве различных форматов [1], включая Excel, PDF, CSV, XML, TIFF (и других графических форматах[2]), а также HTML Web Archive. SQL Server 2008 SSRS также может подготавливать отчеты в формате Microsoft Word (DOC).

Сторонние генераторы отчетов предлагают возможность создания отчетов в дополнительных форматах.

Пользователи могут работать с веб-службой Report Server напрямую или использовать Report Manager — веб-приложение, взаимодействующее с веб-службой Report Server. При помощи Report Manager могут просматривать и управлять отчетами, также как и управлять и оперировать источниками данных и настройками безопасности. Отчеты могут рассылаться по электронной почте или записываться на файловую систему как обычный файл. Защита выполняется на основе ролей и может накладываться на отдельные элементы, как например, отчет или источник данных, каталог элементов или сайт вообще. Роли безопасности и права являются наследуемыми и могут быть переопределены.

В дополнение к использованию отдельного Report Server, поставляемого с SQL Server, RDL-отчеты можно просматривать при помощи веб-контрола ASP.NET ReportViewer или Windows Forms-контрола ReportViewer. Это позволяет встраивать отчеты прямо в веб-страницы или .NET-приложения. Контрол ReportViewer обрабатывает отчеты одним из двух способов: (a) на стороне сервера, где отчет обрабатывается Report Server; и (b) локальная обработка, где соответствующий контрол самостоятельно обрабатывает RDL-файл.

SQL 2005 reporting services также поддерживает обработку произвольных отчетов: разработчик создает схему отчета и развертывает ее на сервере отчетности, где пользователь может выбирать нужные поля/данные и создавать отчеты в соответствии со своими нуждами. Потом пользователи могут загружать отчеты локально.

См. также

Примечания

Ссылки

dis.academic.ru

SQL Server Reporting Services - это... Что такое SQL Server Reporting Services?

SQL Server Reporting Services (сокр. SSRS, рус. Службы отчетности SQL Server) — программная серверная система создания отчетов, разработанная корпорацией Microsoft. Она может быть использована для подготовки множества интерактивных и печатных отчетов. Система администрируется через веб-интерфейс. Reporting services используют интерфейс веб-служб для поддержки разработки обычных отчетных приложений.

SSRS соперничает с Crystal Reports и другими BI-инструментами, и входит в состав Express, Workgroup, Standard и Enterprise версий Microsoft SQL Server в качестве устанавливаемого дополнения. Reporting Services были впервые выпущены в 2004 году как дополнение для SQL Server 2000. Вторая версия была выпущена в виде составной части SQL Server 2005 в ноябре 2005 года. Последняя на данный момент версия была выпущена как часть SQL Server 2008 в августе 2008.

В SSRS отчеты описываются при помощи Report Definition Language (RDL) на языке разметки XML. Отчеты могут проектироваться при помощи последних версий Microsoft Visual Studio (включая Visual Studio.NET 2003 и Visual Studio 2005) с входящим в них дополнением Business Intelligence Projects или при помощи входящего в комплект Report Builder — упрощенного инструмента, не предлагающего полного функционала Visual Studio. Отчеты, определенные при помощи RDL, могут создаваться во множестве различных форматов [1], включая Excel, PDF, CSV, XML, TIFF (и других графических форматах[2]), а также HTML Web Archive. SQL Server 2008 SSRS также может подготавливать отчеты в формате Microsoft Word (DOC).

Сторонние генераторы отчетов предлагают возможность создания отчетов в дополнительных форматах.

Пользователи могут работать с веб-службой Report Server напрямую или использовать Report Manager — веб-приложение, взаимодействующее с веб-службой Report Server. При помощи Report Manager могут просматривать и управлять отчетами, также как и управлять и оперировать источниками данных и настройками безопасности. Отчеты могут рассылаться по электронной почте или записываться на файловую систему как обычный файл. Защита выполняется на основе ролей и может накладываться на отдельные элементы, как например, отчет или источник данных, каталог элементов или сайт вообще. Роли безопасности и права являются наследуемыми и могут быть переопределены.

В дополнение к использованию отдельного Report Server, поставляемого с SQL Server, RDL-отчеты можно просматривать при помощи веб-контрола ASP.NET ReportViewer или Windows Forms-контрола ReportViewer. Это позволяет встраивать отчеты прямо в веб-страницы или .NET-приложения. Контрол ReportViewer обрабатывает отчеты одним из двух способов: (a) на стороне сервера, где отчет обрабатывается Report Server; и (b) локальная обработка, где соответствующий контрол самостоятельно обрабатывает RDL-файл.

SQL 2005 reporting services также поддерживает обработку произвольных отчетов: разработчик создает схему отчета и развертывает ее на сервере отчетности, где пользователь может выбирать нужные поля/данные и создавать отчеты в соответствии со своими нуждами. Потом пользователи могут загружать отчеты локально.

См. также

Примечания

Ссылки

dikc.academic.ru

Конфигурация Reporting Services

Конфигурация Reporting Services:

Давайте рассмотрим ещё несколько параметров, способные повлиять на производительство SQL Server Reporting Services, которые можно настроить в rsreportserver.config

Содержит время в минутах, после которого старые сеансы и моментальные снимки с истекшим сроком жизни удаляются из базы данных сервера отчетов. Диапазон допустимых значений — от 0 до максимального целого числа. Значение по умолчанию — 10. Если установить значение 0, очистка базы данных будет отключена. Стоит отметить, что эти данные хранятся в базе ReportServerTempDB. Если размер базы не критичен, то стоит уменьшить частоту очистки этой базы, а значит можно поставить значение побольше у этого параметра.

Указывает, как часто сервер отчетов проверяет запущенные задачи на превышение времени выполнения отчета и когда сведения по этим задачам предоставляются на страницу управления задачами диспетчера отчетов. Значение указывается в секундах. Допустимы значения от 0 до 2147483647. Значение по умолчанию равно 60. Можно поставить значение поменьше, что бы SQL Server Reporting Services чаще проверял наличие отчетов, которые превысили время выполнения.

  • RunningRequestsScavengerCycle

Указывает, с какой частотой истекшие и лишенные родителей запросы отменяются. Значение указывается в секундах. Диапазон допустимых значений — от 0 до максимального целого числа. Значение по умолчанию равно 60. Можно увеличить это значение.

Задает максимальное число отчетов, которые один пользователь может обрабатывать одновременно. При превышении этого предела все дальнейшие запросы на обработку отчетов запрещаются. Допустимые значения: от 1 до максимального значения, заданного целым числом. Значение по умолчанию равно 20. Обратите внимание, что большинство запросов выполняется очень быстро, поэтому маловероятно, что для одного пользователя одновременно будет открыто более 20 соединений. Если пользователи открывают одновременно более 15 отчетов, интенсивно использующих процессы, то, возможно, это значение нужно увеличить. Если же вы хотите принудительно ограничить количество отчетов доступных одному пользователю, то значение этого параметра можно уменьшить. Следует отметить что эта настройка не обрабатывается серверами отчетов, работающими в режиме интеграции с SharePoint.

Автор: Андрей Коршиков

***   ***   ***   ***   ***   ***   ***   ***   ***   ***   ***   ***Полезные Скрипты

Рубрика Проверь себя

Ссылка на наш канал YouTube

Рекомендуем ознакомиться с другими темами по SQL Server Reporting Services тут

Facebook

Twitter

Вконтакте

Google+

sqlcom.ru