SSRS: Как объединить данные из двух датасетов в одну таблицу

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

В моем случае было два датасета (ds_regs и ds_sales), обращение шло к двум разным OLAP-кубам. В каждом было поле даты. В одном кубе это была дата регистрации пользователей, во втором - дата платежей.

Далее необходимо для таблицы выбрать в качестве источника данных один из датасетов, я выбрал ds_regs.  Поля из этого датасета будут добавляться в таблицу как обычно. Для добавления полей из второго датасета нужно добавить пустые ячейки и в Expression добавить функцию Lookup:

=Lookup(Fields!Day.Value, Fields!Day.Value, Fields!Sales.Value, "ds_sales")

Аргументы следующие: 1 - Ключевое поле из первого датасета (из ds_regs); 2 - ключевое поле из второго датасета (из ds_sales); 3 - поле из второго датасета, которое надо отобразить; 4 - название второго датасета.

Ключевые атрибуты можно варьировать, например, у меня из одно куба день выводился просто числом, а из второго куба с добавлением слова "Day" перед самим числом, поэтому функцию Lookup пришлось записать вот так:

=Lookup("Day " + Fields!Day.Value, Fields!Day.Value, Fields!Sales.Value, "ds_sales")

SSRS: Как рассылать отчеты по подписке НЕ по расписанию

Речь в статье пойдет о рассылке отчетов по подписке, но не по расписанию, а в те моменты, когда это необходимо. Например только в случае успешного процессинга OLAP-куба. Как раз такой случай я и буду рассматривать.

Все относиться к SQL Server 2008 R2 Enterprise Edition, т.к. от версии к версии база SSRS может меняться. Я делал подписку управляемую данными и не уверен, что все нижеследующее подойдет к обычной подписке, не проверял.

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

Так вот, при создании расписания рассылки отчета, SSRS создает на сервере, где лежит его база, джоб. Называться он будет как-нибудь вот так: 2D45C6DB-559E-43CB-A019-F8F1484E17FF и он будет запускаться по расписанию, которое было указано в SSRS.

В джобе вызывается процедура ReportServer.dbo.AddEvent, которая кладет ID подписки в таблицу ReportServer.dbo.Event, из этой таблицы SSRS понимает, что пора отчет отсылать, после отсылки запись из таблицы ReportServer.dbo.Event удаляется. 

Исходя из вышесказанного, задача сводиться к тому, что надо добавить запись с ID подписки в таблицу ReportServer.dbo.Event в тот момент, когда пора отсылать отчет.

Шаг 1 - узнаем ID отчета (все селекты идут в БД ReportServer):

select ItemID from catalog where name = @name

Шаг 2 - узнаем ID подписки:

select SubscriptionID from Subscriptions where report_OID =@ItemID

 Шаг 3 - записываем данные в таблицу Event:

exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData=@SubscriptionID

В моем случае, в джобе, после шага, который процессит куб, я добавил шаг, который выполняет:

exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='fa502959-73cf-59a1-b018-8ddcdae2e01'

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

И еще есть важный момент - надо сделать так, что бы расписание самого отчета не срабатывало, для чего в интерфейсе SSRS на странице создания расписания надо поставить Однократно.

Перевод строки в выражениях SSRS

Для добавления новых строк в выражениях SSRS используется константа vbCrLf. Например, вот так я добавлял тултип для графика:

="MIN: " + Format(Min(Fields!Сумма_покупок.Value), "#,##0") + vbCrLf + "MAX: " +   Format(Max(Fields!Сумма_покупок.Value), "#,##0")

Конвертация даты в строку в SSRS 2008

Для того, что бы преобразовать дату в строку в произвольном формате в SQL Server Reporting Services, надо воспользоваться функцией Format:

=Format(Parameters!par_date.Value, "yyyy-MM-dd")

Так же можно написать так:

=Parameters!par_date.Value.ToString()

или

=FormatDateTime(Parameters!par_date.Value, DateFormat.ShortDate)

но в обоих последних примерах нельзя выбрать произвольный формат даты, в ToString вообще нельзя указать формат, а в FormatDateTime можно выбрать из нескольких вариантов.

Разбиение отчета в SSRS на страницы через параметр отчета

При выводе отчета в Web SSRS автоматически разбивает его на страницы, но есть возможность управлять этим процессом через параметр отчета. Все нижеследующее относиться к SQL Server 2008 R2. Я раньше делал это на SQL Server 2005 таким же способом, но какие то детали могут отличаться.

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

=Ceiling(RowNumber(Nothing)/Parameters!rows_per_page.Value)

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

Далее, в свойствах группы, в разделе Page Breaks ставим галку Between each instance of a group. После этого я запустил отчет и он мне выдал ошибку, суть которой в том, что у таблицы выражение сортировки использует функцию RowNumber, а такого не может быть. После некоторых попыток избавиться от этого пришел к такому решению - для таблицы и для группы (на всякий случай) установил значения сортировки на первом попавшемся поле, а потом сбросил эти значения. Видимо при создании группы, выражение группировки автоматом попало в выражение сортировки (Мелкомягким привет!). 

Далее на кнопке строки отчета, которая появляется слева от самой таблицы при попадании таблицы в фокус ввода, тыкаем правой кнопкой и выбираем пункт Tablix Properties. На вкладке General нужно поставить галку Keep together on one page if possible, иначе могут появиться странности, например у меня при значении 50 строк на страницу выдавало каждые несколько страниц по 3-4 записи. Видимо автоматическое разбиение на страницы создается  без учета других разбиений.

Единственное чего я не смог сделать, так это оставить названия колонок на каждой странице. В окне Tablix Properties есть галка Repeat header rows on each page, но что-то ее установка ни к чему не привела.

К стати, здесь про разбиение на странице кратко написано: http://msdn.microsoft.com/en-us/library/ms157328.aspx , раздел Page Breaks.

SSRS 2008: Добавление параметров к отчету

Что бы добавить в отчет параметры, нужно открыть окно Report Data, для этого надо сначала левой кнопкой мышки тыкнуть в области редактирования отчета, а потом нажать Ctrl-Alt-D или через меню View -> Report Data.  Если фокус ввода имеет другое окно, например окно Proprties, то в меню View не будет Report Data и сочетание клавиш Ctr-Alt-D работать не будет.

Я раньше работал с SSRS 2005, в первый раз, когда стал делать отчет в SSRS 2008, я 10 минут искал, как добавить параметры - это жесть!!! Я был просто в шоке, как так можно было сделать интерфейс. В общем, Мелкомягкие жгут!