Отчеты
18
Июл
30

Фильтрация данных в отчетах MS CRM

Предварительная фильтрация CRM очень полезная опция, которая может быть задействована в отчетах о CRM сделав их контекстно-зависимыми и позволяет фильтровать данные отчета используя Расширенный поиск. Как ее включить?

Автоматическая предварительная фильтрация (CRMAF_)

Есть два способа включит предварительную фильтрацию в CRM. Самый простой способ – метод CRMAF_, который просто требует добавления для псевдонима вьюхи (filtered views) предлога «CRMAF_». Т.е. запрос:

SELECT
	name
FROM
	FilteredAccount

меняется на:

SELECT
	name
FROM
	FilteredAccount as CRMAF_Account

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

После включения предварительной фильтрации, используя метод CRMAF_, CRM возьмет Ваш запрос, такой как этот:

SELECT
	name,
	accountnumber
FROM
	FilteredAccount as CRMAF_Account

И изменит его при загрузке в CRM:

SELECT
	name,
	accountnumber
FROM
	(@P1) as CRMAF_Account

Далее CRM будет подставлять в параметр P1 запрос, в зависимости от того, как отчет фильтруется. Например: если Вы запускаете отчет из области Отчетов и используете функциональные возможности предварительной фильтрации, чтобы отобрать только активные записи Бизнес-партнеров, запрос будет примерно следующим:

SELECT
name,
accountnumber
FROM 
(select
	FilteredAccount.* 
 from
 	FilteredAccount
 where
 	statecode = 0) as CRMAF_Account

Если же Вы запустите отчет с формы Бизнес-партнера, запрос получится таким:

SELECT
	name,
	accountnumber
FROM
(select
	FilteredAccount.*
 from
	FilteredAccount
 where
	AccountId = '') as CRMAF_Account

Когда Вы запустите отчет из представления Бизнес-партнера с 3 выделенными записями, запрос будет похож на этот:

SELECT
	name,
	accountnumber
FROM
(select
	FilteredAccount.*
 from
	FilteredAccount
 where
	AccountId in ('<1stAccountId>', '<2ndAccountId>', '<3rdAccountId>') as CRMAF_Account

Явная фильтрация

Метод CRMAF_ работает в большинстве случаев, но для сложных запросов, например, для запросов с использованием UNION’ов, Вам, возможно, придется использовать второй способ, называемый Явной Фильтрацией.

Проблема состоит в том, что, когда Вы используете метод CRMAF_, CRM должен добавить параметр за Вас (в противоположность явной фильтрации, при которой Вы создаете параметр самостоятельно), а при сложном запросе (например, при использовании операторов UNION) это может привести к неожиданным результатам, поскольку CRM может добавить только один параметр для первого запроса.

Предположим, что у Вас есть такой запрос:

SELECT
	name,
	accountnumber
FROM
	FilteredAccount as CRMAF_Account
where
	address1_stateorprovince = 'FL'
UNION
SELECT
	name,
	accountnumber
FROM
	FilteredAccount as CRMAF_Account
where
	address1_stateorprovince = 'CA'

Когда Вы загрузите отчет, CRM сможет отфильтровать только первый подзапрос, подставив в него параметр. Но вторая часть запроса останется без внимания:

SELECT
	name,
	accountnumber
FROM
	(@P1) as CRMAF_Account
where
	address1_stateorprovince = 'FL'
UNION
SELECT
	name,
	accountnumber
FROM
	FilteredAccount as CRMAF_Account
where
	address1_stateorprovince = 'CA'

Выполнив этот отчет из области Отчетов и выбрав фильтр – Годовой доход > 1 000 000 – сформированный запрос будет таким:

SELECT
	name,
	accountnumber
FROM
(select
	FilteredAccount.*
 from
	FilteredAccount
 where
	AnnualRevenue > 1000000) as CRMAF_FilteredAccount
where
	address1_stateorprovince = 'FL'
UNION
SELECT
	name,
	accountnumber
FROM
	FilteredAccount as CRMAF_Account
where
	address1_stateorprovince = 'CA'

Но это означало бы, что он возвратит Бизнес-партнеров во Флориде с годовым доходом более 1 000 000 или любого Бизнес-партнера в Калифорнии (но не только с годовым доходом более 1 000 000). Если Вы выгрузите отчет из CRM и откроете его в Visual Studio, то Вы будете видеть исходную версию отчета – ту что Вы загрузили в CRM. Если же Вы выгрузите отчет напрямую из Reporting Services (используя диспетчер отчетов, который обычно доступен по адресу http://<сервер_отчетов>/reports), то сможете увидеть как CRM изменил запрос, но не разместил параметры во всех нужных местах:

SELECT
	name,
	accountnumber
FROM
	(@P1) as CRMAF_FilteredAccount
where
	address1_stateorprovince = 'FL'
UNION
SELECT
	name,
	accountnumber
FROM
	FilteredAccount as CRMAF_FilteredAccount
where
	address1_stateorprovince = 'CA'


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

SELECT
	name,
	accountnumber
FROM
	FilteredAccount as CRMAF_Account

Вы должны создать параметр с именем CRM_FilteredAccount и запрос будет таким:

DECLARE @SQL nvarchar(4000)

DECLARE @CRM_FilteredAccount nvarchar(2000)
Set @CRM_FilteredAccount = 'Select FilteredAccount.* FROM FilteredAccount'

SET @SQL = 'SELECT
				name,
				accountnumber
			FROM
				('+@CRM_FilteredAccount+') AS FA'
EXEC (@SQL)

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

Траблы с фильтрацией отчетов

Отчеты отображают меньше данных чем ожидается

Возможная причина 1:

Заданный по умолчанию фильтр (например, Измененные за последние 30 Дней). Вы можете посмотреть заданные по умолчанию фильтры, выбирав отчет в области Отчетов и нажимая Изменить фильтр по умолчанию из меню Другие действия.


Возможная причина 2:

Фильтры по умолчанию разрешены для объектов, для которых не доступен расширенный поиск. Не допускайте предварительной фильтрации для объектов, для которых не доступен расширенный поиск (например, Продукта для возможной сделки), поскольку заданный по умолчанию фильтр будет включен, но это не будет видно в пользовательском интерфейсе. На эту тему есть хотфиксик http://support.microsoft.com/kb/956079.

Отчеты отображают больше данных чем ожидается

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

Комментарии (30)
  • Евгений 18.07.2009

    работая с явной фильтрацией: пытался разобраться с отчетом «Действия», как только меня что угодно в отчете при нажатие в VS2005 на привью выдается ошибка: Must declare the scalar variable «@CRM_FilteredActivityPointer».

    может сталкивались с такой проблемой?

  • slivka_83 18.07.2009

    Добрый день!
    Если речь идет о стандартном отчете, то, к сожалению, не приходилось!
    П.С. прежде чем переходить на превью, попробуйте запаустить его с вкладке Data (нажав на восклицательный знак). Скорее всего что то в запросе не так!

  • Евгений 18.07.2009

    Добрый =).

    весь прикол что в дате как раз все работало. да и сам отчет который тока из СРМ в VS вставляешь работает. но после редактирования шляпа.
    переписал все заново, остался вопрос как работать со вложенными отчетами может есть какой-нибудь примерчик?

  • slivka_83 18.07.2009

    про вложенные отчеты можете посмотреть тут
    http://technet.microsoft.com/ru-ru/library/ms159837(SQL.90).aspx

  • Алексей 18.07.2009

    День добрый!
    По поводу фильтрации не приходилось ли вам решать вот такую задачу: создаю новый отчет, хочу видеть все Действия, которые созданы или изменены неким пользователем. В конструкторе отчетов создаю фильтр:
    Создано Равно Значение ИЛИ Изменено Равно Значение. Можно ли каким-то образом не задавать, по сути одно и то же значение, для двух условий, а обойтись одним полем?… Спасибо за совет.

  • slivka_83 18.07.2009

    Ну, если правильно понял, то нет. Вам нужно сделать проверку для двух полей — вот и два условия — все логично 🙂

  • Алексей 18.07.2009

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

  • Александр 18.07.2009

    Добрый день!
    Подскажите кто хорошо знаком с фильтрацией CRM в отчетах.
    Есть отчет по действиям. В фильтре в CRM задается условие, например «дата создания» = «последние Х дней». В отчете формируется временная таблица вида:
    день1 встреча,
    день1 задача,
    день2 встреча,
    день2 задача…..
    необходимо получить начальную и конечную дату фильтра для формирования таблицы, например: задали фильтр «дата создания последние 5 дней» получили @date1 = ‘22.07.2010’ и @date2 = ‘27.07.2010’ и по возможности получение поля по которому фильтр.
    Заранее спасибо!

  • Евгений 18.07.2009

    Александр, насколько я понял, в CRM из отчетов нет возможности явно добраться до настроек фильтра. Предлагаю для задания временного интервала использовать обычные параметры отчета, а не фильтр CRM.

  • Александр 18.07.2009

    Спасибо, так сейчас и делаю:)

  • Иван 18.07.2009

    Добрый день.
    Помогите разобраться, что не так. Пытаюсь задать явную фильтрацию на отчете. Задаю параметр как указано в примере:
    «SELECT …..
    FROM ‘(+@CRM_P1+’)’
    но загрузить данный отчет в ЦРМ не получается дает ошибку. пробовал прописывать параметр в параметрах отчета, как внешний, ничего не дает. причем даже не хочет в конструкторе отчетов выдавать список полей. если их прописать руками они удаляются.
    Смотрел встроенные отчеты. Отличий от того, что делаю я, не нашел.
    Подскажите что яй делаю не так.

  • slivka_83 18.07.2009

    Добрый день 🙂
    Движок блога скушал Ваш запрос 🙂
    Для размещения кода используйте BB-теги sql, text, javascript или csharp 🙂

  • Иван 18.07.2009

    не совсем понял ответа. если про сам отчет, то command tipe=text. работаю в Bisness Intellegence (VS 2005). тип отчета *.rdl. Вот полученные отчеты загружаться и не хотят.

  • slivka_83 18.07.2009

    По коду приведенному Вами:
    >«SELECT …..
    >FROM ‘(+@CRM_P1+’)’
    я не совсем понимаю что Вы делаете? Т.е. не могу судить об ошибке. Приведите код полностью и заключите его в BB-теги (http://ru.wikipedia.org/wiki/BBCode) sql, text, javascript или csharp.

  • Иван 18.07.2009

    Если правильно понял что от меня нужно то вот:

    String

    SELECT * FROM Svc_invoice_tax

    true

    и собственно запрос:

    CRM
    DECLARE @SQL AS nvarchar(max)
    SET @SQL = ‘
    SELECT qq.*
    FROM (‘ + @P1 + ‘) as qq’
    EXEC (@SQL)

    =Parameters!P1.Value

    true

  • Иван 18.07.2009

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

  • slivka_83 18.07.2009

    Подправил последний запрос в разделе «Явная фильтрация» 🙂 В нем была ошибка 🙂 теперь работает 🙂 проверил 🙂

  • Иван 18.07.2009

    Исходя из кода, работать не должно (мы задаем параметр в самом запросе и присваиваем ему выбирать все!!!):(. Хотя все равно попробовал, не добавляеться… В том форуме где смотрел на похожую ошибку, проблема решилась когда:» Решил проблему через включение в SQL Server Configuration Manager/Client Protocols/Named Pipes — Enable»
    но у меня не все отчеты не добавляються, а там шла речь именно про это.
    Так же смотрел 2 отчета, где уже используется явная фильтрация, редактровал их и они все равно загружаються, причем в них:
    1 — самописный, а параметр не объявляеться в запросе как в примере
    2 — микрософтовский, там параметр объявляеться в параметрах отчета и ему задаються параметры по умолчанию и пишеться что он внешний.
    Теперь суть вопроса: ЧТО Я ДЕЛАЮ НЕ ТАК ИЛИ ЧТО подправить что бы все работало?

  • slivka_83 18.07.2009

    1. Все верно 🙂 В данном запросе выбирается все 🙂 но ничего не мешает Вам выирать то что нужно а не все 🙂 И детализировать запрос дальнейшем (в переменной @SQL).
    Данный пример почти копия описанного в MSDN’е http://msdn.microsoft.com/en-us/library/bb955092.aspx
    2. Может у Вас с ервером что то не так? Пришлите Ваш RDL-файл (который не загружается и который написан в соответствии с примером) на slivka_83@mail.ru 🙂 Только сделайте запрос как можно проще 🙂

  • Иван 18.07.2009

    отправил.

  • Alex 18.07.2009

    Привет!
    Ваш блог по-моему содержит наиболее полезную информацию по CRM, поэтому решил обратиться с вопросом сюда.
    Стоит задача для кастомной сущности «Приказ» в CRM создавать Word-документ, красиво этот приказ отображающий, естественно, по некоему шаблону. Какие средства для этого лучше использовать — SRSS или Mail Merge? Или что-то ещё?
    Спасибо!

  • slivka_83 18.07.2009

    Добрый день. Из поставленной задачи не вижу принципиальной разницы между SRSS или Mail Merge 🙂

  • Alex 18.07.2009

    Дело в том, что я не очень знаком с srss. Насколько легко с его помощью формировать документ word по шаблону? Из поиска по интернету я понял, что для этого используются специальные платные пакеты…
    Спасибо!

  • slivka_83 18.07.2009

    Не знаю что Вы подразумеваете под «шаблоном» но подставлять в данные в преднастроенный(запрограммированный) «скелет» относительно легко (относительно меня — Вам нужно еще изучить эту технологию). И SSRS выгружает данные в виде HTML — а уж затем можно выгрузить в Word (стандартный функционал).

    Лично мне SSRS нравится больше, т.к. чтобы сформировать документ методом Слияния почты нужно совершить много кликов мышкой!

  • Alex 18.07.2009

    Спасибо за ответ!
    Под «шаблоном» подразумеваю файл word с необходимым оформлением, где поля, которые необходимо подтягивать из базы, названы соответствующим образом, типа «НомерПриказа».
    Наверное, остановлюсь на Mail Merge, т.к. на выходе нужен документ Word — формировать в html кажется лишним шагом.

  • slivka_83 18.07.2009

    SSRS с такими шаблонами работать не умеет!

  • losk 18.07.2009

    [b]Доброго времени суток![/b]
    Не удается получить отфильтрованный Select в параметр @CRM_FilteredEntity, получаю в отчете выборку всех записей. Отчет настроен на запуск с формы объекта, в отчете два dataset, один с неявной и второй с явной фильтрацией. Есть идеи почему не работает?

    [u]Запрос из dataset с явной фильтрацией: [/u]

    declare @sql nvarchar(max)
    set @sql = 
    'select new_claimid, new_name
    from ('+ @CRM_Filterednew_claim + ') as claim'
    
    exec (@sql)
    

    В параметр передается

    select [new_claim0].* from Filterednew_claim
    

    хотя отчет вызывается с формы new_claim!

  • slivka_83 18.07.2009

    Добрый день!

    Не совсем понял смысл маневра… Если отчет вызывается с формы записи, то значит Вам нужно получить информацию только по этой записи, плюс (возможно), связанных с ней.

    Для начала давайте просто отобразим информацию по текущей записи new_claim:

    SELECT
    	new_claimid, new_name 
    FROM
    	Filterednew_claim as CRMAF_claim
    

    Получается? 🙂

  • losk 18.07.2009

    Получается, dataset1 с неявной передачей параметра отрабатывает корректно.
    Но есть потребность отфильтровать dataset2 также по текущей записи, чтобы вывести в отдельной таблице в отчете связанные объекты с сущностью new_claim.
    И вот здесь уже ничего не получается 🙂

  • losk 18.07.2009

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

*

code