Мелкомягкий CRM: Панель управления для MS CRM с помощью Reporting Services

[Blog bot]

Источник: http://mmcrm.ru/?p=638
==============

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

1. Две воронки продаж, состоящих из Возможных сделок, Предложений и Заказов, в разрезах:
   • количественном;
   • стоимостном.
2. Две столбчатые диаграммы показывающих соотношение (за последний месяц):
   • интересов к клиентам;
   • оплаченных заказов к неоплаченным.
3. И линейную диаграмму показывающую интенсивность Действий совершаемых сотрудниками (соответственно в разрезе Встреч, Звонков, Писем и т.д.).

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

Прежде чем начать создайте некоторое количество записей для объектов Интересы, Бизнес-партнеры, Возможные сделки (обязательно заполните поле Предполагаемый доход), Предложения (должно быть заполнено поле Общая сумма) и Заказы (должно быть заполнено поле Общая сумма), а также насозлавайте различных действий (и в поле createdon поставьте различные значения – возможно, Вам придется предварительно вынести это поле на форму)!

Предварительная настройка

• Откройте Visual Studio (я буду показывать пример в Visual Studio 2008, т.к. у меня установлен SQL Server 2008, но работа в Visual Studio 2005, которая необходима для SQL Server 2005, очень похожа). Перейдите File – New – Project – в Business Intelligence Projects выберите Report Server Project и Ок!
• В Solution Explorer щелкните правой кнопкой мыши по Reports – Add – New Item – в качестве шаблона выберите Report1 и Ок!
• На левой панели Report Data выберите New – Data Source… – далее:
  • Введите произвольное имя нового источника данных;
  • В качестве тbпа подключения укажите Microsoft SQL Server;
  • Нажмите Edit. Откроется окно параметров подключения к SQL Server: укажите имя SQL сервера, способ проверки подлинности для подключении к SQL серверу и имя БД MS CRM (_MSCRM).
    Жмите Ок!

• На левой панели щелкните правой кнопкой мыши по Parameters – Add Parameter… – далее:
  • Введите имя переменной «startdate»;
  • Введите в поле Promt подпись для этого параметра, которая буду отображаться на форме отчета;
  • В качестве типа дата поставьте Date/Time;
  • На левой стороне перейдите к пункту Default Values, выделите Specify Valueу, нажмите Add и в Value вбейте:=DateAdd("d",-Day(Today())+1,Today())
    Таким образом, по умолчанию начальная дата периода, за который будут строиться диаграммы, будет равна первому дню текущего месяца.
• Создайте еще один аналогичный параметр, но уже с именем «enddate» и в качестве значения по умолчанию поставьте:=Today
  В результате чего, конечная дата периода, за который будут строиться диаграммы, по умолчанию будет равна сегодняшнему дню.

Воронки продаж

• На левой панели Report Data выберите New – Dataset… – далее:
  • Задайте имя нового набора данных
  • В качестве источника данных укажите тот, что мы сделали в предыдущих шагах
  • Вставьте в поле Query следующий SQL-запрос:select 'Возможные сделки' as Type, count(*) as Kolvo, 'Summa' = CASE WHEN sum(CRMAF_fo.estimatedvalue) is not null THEN sum(CRMAF_fo.estimatedvalue) ELSE 0 ENDfrom dbo.FilteredOpportunity as CRMAF_fowhere CRMAF_fo.createdon between DATEADD(day,-1,@startdate) and DATEADD(day,1,@enddate)UNIONselect 'Предложения' as Type, count(*) as Kolvo, 'Summa' = CASE WHEN sum(CRMAF_fq.totalamount) is not null THEN sum(CRMAF_fq.totalamount) ELSE 0 ENDfrom dbo.FilteredQuote as CRMAF_fqwhere CRMAF_fq.createdon between DATEADD(day,-1,@startdate) and DATEADD(day,1,@enddate)UNIONselect 'Заказы' as Type, count(*) as Kolvo, 'Summa' = CASE WHEN sum(CRMAF_fso.totalamount) is not null THEN sum(CRMAF_fso.totalamount) ELSE 0 ENDfrom dbo.FilteredSalesOrder as CRMAF_fsowhere CRMAF_fso.createdon between DATEADD(day,-1,@startdate) and DATEADD(day,1,@enddate)
    этот запрос возвращает табличку следующего вида:
    TypeKolvoSummaВозможные сделки49441,92Заказы33421,44Предложения21774,08
  • На левой стороне перейдите к Parameters. В этой области Вы увидите две колонки: в левой будут переменные, который Visual Studio смог идентифицировать в запросе, а с правой нужно задать им какие-либо значения по умолчанию (иначе отчет не будет автоматически запускаться, а будет запрашивать у Вас значения для этих параметров). Так вот… на правой стороне сопоставьте этим переменных параметры, которые мы создали в предыдущих шагах (т.е параметры startdate и enddate, соответственно)!

• На крайней левой стороне Visual Studio есть закладка Toolbox – щелкните по ней и она раскроется. Перетащите с нее на рабочую (Design) область объект Chart (график). Сразу же появится диалоговое окно выбора типа графика. В нем выберите воронку продаж!
• Щелкните по диаграмме два раза, но не быстро – с небольшим интервалом! Появятся дополнительные поля. На левой панели (Report Data) раскройте подветку созданного ранее датасета (в данном примере это датасет Voronki) и перетащите в верхнюю добавочную область графика поле «Kolvo», а в правую добавочную область поле «Type».
• Щелкните правой кнопкой на изображении самой воронки, далее Series Properties. В области Action поставьте переключатель в Go to URL. Нажмите кнопочку «функции» справа от поля Select URL. В открывшемся диалоговом окне Expression введите:
  Пояснение: как вы поняли этим действием мы делаем нашу диаграмму кликабельной, но диаграмма состоит из нескольких областей и чтобы задать каждой области свой URL мы производим проверку на то, какая строчка (Возможные сделки, Предложения или Заказы) сейчас проходит обработку и в зависимости от этого задаем ей соответствующую урлу. Кстати урла имеет такой вид (нужно только поменять имя организации на Ваше):http://crm2008/superfirma/_root/homepage.aspx?etc=
  Коды стандартных объектов Вы можете посмотреть в статье «Прямой доступ к объектам MS CRM». А коды кастомных объектов можете посмотреть открыв одни из их записей и нажав Ctrl + N – откроется новое окно браузера в адресной строке которого и будет код объекта.
  Жмите Ок.

• Теперь отобразим количественное значение на диаграмме (т.к. все в мире относительно и наша воронка не исключение, то по красивой картинке сложно определить точные значения):
  • Щелкните правой кнопкой на изображении воронки, далее Series Properties. В области Series Data в поле Tooltip (т.е. всплывающая подсказка) выберите то же поле, что Вы добавили в верхнюю добавочную область (в данном случаи это «Kolvo»). Таким образом, при наведении на какую-либо область диаграммы будет появляться всплывающая подсказка со значением, которое соответствует этой области.
  • Второй способ: щелкните правой кнопкой мыши по полю Type (которую мы вынесли на правую добавочную область) и выберите Series Group Properties. Далее щелкните по кнопке функции справа от поля Label и вбейте в открывшемся окне Expression:=Fields!Type.Value & "\n[" & Fields!Kolvo.Value & "]"
    в результате в легенду у нас будет отображаться категории и количественное значение соответствующее этой категории (а \n переводит число в квадратных скобках на следующую строку). Ок.
  • Ну и последний способ – щелкните правой кнопкой на самой диаграмме, далее Show Data Labels. Появятся числовые выноски для воронки продаж! Но я не буду использовать его в данном примере (и так мало места ).

• Создайте еще один график воронки и поместите его рядом с первым (можете его скопипастить). Перетащите из того же датасета (Voronki) в верхнюю добавочную область поле «Summa», а в правую добавочную область так же поле «Type». А для Label’а легенды задайте:=Fields!Type.Value & "\n[" & Fields!Summa.Value & "]"
  т.е. таким образом, мы сделали так, что первая воронка будет показывать числовые значения, а вторая стоимостные! Также в качестве всплывающей подсказке установите «Summa». Все остальное аналогично!

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

Сравнение по интересам и клиентам

• Создайте новый датасет для столбчатой диаграммы, а в качестве запроса в ней укажите:select 'Интересы' as klients, 'Новые' as status, COUNT(*) as kolvofrom dbo.FilteredLead as CRMAF_flwhere 1=1 and CRMAF_fl.statecodename = 'Открыть' and CRMAF_fl.createdon between DATEADD(day,-1,@startdate) and DATEADD(day,1,@enddate)unionselect 'Интересы' as klients, 'Потеряные' as status, COUNT(*) as kolvofrom dbo.FilteredLead as CRMAF_flwhere 1=1 and CRMAF_fl.statecodename = 'Дисквалифицирован' and CRMAF_fl.modifiedon between DATEADD(day,-1,@startdate) and DATEADD(day,1,@enddate)unionselect 'Бизнес-партнеры' as klients, 'Новые' as status, COUNT(*) as kolvofrom dbo.FilteredAccount as CRMAF_fawhere 1=1 and CRMAF_fa.statecodename = 'Активный' and CRMAF_fa.createdon between DATEADD(day,-1,@startdate) and DATEADD(day,1,@enddate)unionselect 'Бизнес-партнеры' as klients, 'Потеряные' as status, COUNT(*) as kolvofrom dbo.FilteredAccount as CRMAF_fawhere 1=1 and CRMAF_fa.statecodename = 'Неактивный' and CRMAF_fa.modifiedon between DATEADD(day,-1,@startdate) and DATEADD(day,1,@enddate)он вернет вот такую табличку:
  
  klientsstatuskolvoБизнес-партнерыНовые2Бизнес-партнерыПотеряные1ИнтересыНовые2ИнтересыПотеряные4
• Добавьте в рабочую область столбчатую диаграмму.
• В качестве ссылки пропишите:= Switch ( Fields!klients.Value = "Интересы", "http://crm2008/superfirma/_root/homepage.aspx?etc=1", Fields!klients.Value = "Бизнес-партнеры", "http://crm2008/superfirma/_root/homepage.aspx?etc=4")
• Я визуально немного подкорректировал область диаграммы: легенду переместил вниз, удалил подпись нижней оси.

Сравнение по оплаченным и не оплаченным заказам

• Создайте новый датасет для столбчатой диаграммы, а в качестве запроса в ней укажите:select 'Оплачено' as Type, ( select SUM(CRMAF_fso.totalamount) from dbo.FilteredSalesOrder as CRMAF_fso where CRMAF_fso.createdon between DATEADD(day,-1,@startdate) and DATEADD(day,1,@enddate)) as Summaunionselect 'Задолженность' as Type, ( (select SUM(CRMAF_fso.totalamount) from dbo.FilteredSalesOrder as CRMAF_fso where CRMAF_fso.createdon between DATEADD(day,-1,@startdate) and DATEADD(day,1,@enddate) ) - ( select SUM(CRMAF_fi.totalamount) from dbo.FilteredInvoice as CRMAF_fi, dbo.FilteredSalesOrder as CRMAF_fso where 1=1 and CRMAF_fi.salesorderid is not null and CRMAF_fi.salesorderid = CRMAF_fso.salesorderid and CRMAF_fso.createdon between DATEADD(day,-1,@startdate) and DATEADD(day,1,@enddate) )) as Summaрезультат работы этого запроса будет следующим:
  
  TypeSummaЗадолженность1393,92Оплачено3421,44
• Добавьте в рабочую область круговую диаграмму и поместите в ее верхнюю добавочную область поле Summa, а в правую Type.
• Щелкните правой кнопкой мыши по самой диаграмме, затем Show Date Labels – тем самым мы отобразим на диаграмме числовые значения соответствующие участку диска.
• В качестве ссылки пропишите:= Switch ( Fields!Type.Value= "Оплачено", "http://crm2008/superfirma/_root/homepage.aspx?etc=1090", Fields!Type.Value = "Не оплачено", "http://crm2008/superfirma/_root/homepage.aspx?etc=1088")
• Измените другие элементы диаграммы (легенду заголовки и т.д.)

Линейная диаграмма по действиям

• Создайте новый датасет для линейной диаграммы и в качестве запроса укажите в ней:select CRMAF_fap.activitytypecodename as Type, DATEPART(WW, CRMAF_fap.createdon) as Nedelya, count(*) as Kolvofrom dbo.FilteredActivityPointer as CRMAF_fapwhere CRMAF_fap.createdon between DATEADD(day,-1,@startdate) and DATEADD(day,1,@enddate)group by CRMAF_fap.activitytypecodename, DATEPART(WW, CRMAF_fap.createdon)Этот sql-запрос возвращает данные в таком виде:
  
  TypeNedelyaKolvoВстреча313Встреча323Задача322Звонок321Встреча336………
• Добавьте в рабочую область линейную диаграмму и поместите в ее верхнюю добавочную область поле Kolvo, в правую Type, а в нижнуюю Nedelya.
• В качестве ссылки пропишите:= Switch ( Fields!Type.Value= "Звонок", "http://crm2008/superfirma/_root/homepage.aspx?etc=4210", Fields!Type.Value = "Письмо", "http://crm2008/superfirma/_root/homepage.aspx?etc=4207", Fields!Type.Value = "Электронная почта", "http://crm2008/superfirma/_root/homepage.aspx?etc=4202", Fields!Type.Value = "Факс", "http://crm2008/superfirma/_root/homepage.aspx?etc=4204", Fields!Type.Value = "Задача", "http://crm2008/superfirma/_root/homepage.aspx?etc=4212", Fields!Type.Value = "Встреча", "http://crm2008/superfirma/_root/homepage.aspx?etc=4201", Fields!Type.Value = "Действия сервиса", "http://crm2008/superfirma/_root/homepage.aspx?etc=4214", Fields!Type.Value = "Контракты от ккампании", "http://crm2008/superfirma/_root/homepage.aspx?etc=4401")
• Ну, и подкорректируйте внешний вид диаграммы

Подключаем панель управления к CRM

• В Visual Studio, находясь на вкладке Design основной рабочей области, выполните File – Save as – и сохраните где-нибудь на диске!
• Откройте менеджер отчетов Reporting Services. По умолчанию его адрес всегда http:///Reports
  На верхней панели инструментов нажмите Передать файл.
• Откроется страница загрузки фала, нажмите Browse и выберите rdl-файл выгруженный из VS. Ок. Файлик успешно (должен по крайней мере ) подгрузится…
• Далее нам нужно открыть его через Report Viewer (это инструмент Reporting Services для отображения отчетов, а репорт менеджер это инструмент администрирования, хотя с некоторыми ограничениями и через него можно смотреть отчеты) и запомнить его URL. Откройте следующую урлу http:///ReportServer. Тут Вы увидите среди прочего название загруженного отчет – щелкните по нему. Сформируется отчет – скопируйте куда-нибудь строчку URL, которая будет выглядеть примерно так:http://crm2008/ReportServer/Pages/Re...mmand=Renderи припишите к ней вот такую строчку (она делает так, чтобы при формировании отчета строка с параметрами была по умолчанию свернута, другие параметры можете посмотреть в статье «Using URL Access Parameters»):
  
  &rc:Parameters=collapsedВ получившейся строке замените все символы & на &
  В конечном итоге должно получиться что-то вроде этого:
  
  http://crm2008/ReportServer/Pages/Re...ters=collapsed

• Теперь выгрузите из CRM файл SiteMap (Схему узла) и откройте его в каком-нибудь текстовом редакторе и добавьте в область Workplace следующие строчки:
  и замените параметр Url для отчета на свой (тот, что мы скопировали на предыдущем шаге)!
• Экспортируйте схему сайта обратно;

На этом Все Обновите сайт CRM (F5) и перейдите к узлу Панель управления.



Маленькие подсказки

• Если хотите чтобы по дефолту в CRM отображалась панель управления перейдите Сервис – Параметры… – на первой вкладке в поле «Вкладка по умолчанию» выберите Панель управления!
• Чтобы задать другой период отбора данных, раскройте спойлер (справа вверху в панели управлении) и введите нужный Вам период.
• Щелкнув средней кнопкой мыши какой-либо области графика откроется окно содержащее список соответствующих объектов!

Улучшения

Помимо ссылок на CRM можно ссылаться на подотчеты, т.е. при щелчке на какой-либо области графика будет открывать другой отчет, содержащий более детализированную информации по этой области! Может быть расскажу как-нибудь в другой раз



Источник: http://mmcrm.ru/?p=638