Отчеты
23
Авг
24

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

Как Вы уже поняли из заголовка, сегодня будем делать небольшой пример панели управления на основе отчетов 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 (<orgName>_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
      		END
      from 
      	dbo.FilteredOpportunity as CRMAF_fo
      where
      	CRMAF_fo.createdon between DATEADD(day,-1,@startdate) and DATEADD(day,1,@enddate)
      
      UNION
      
      select
      	'Предложения' as Type,
      	count(*) as Kolvo,
      	'Summa' =
      		CASE 
      			WHEN sum(CRMAF_fq.totalamount) is not null THEN sum(CRMAF_fq.totalamount)
      			ELSE 0
      		END
      from 
      	dbo.FilteredQuote as CRMAF_fq
      where
      	CRMAF_fq.createdon between DATEADD(day,-1,@startdate) and DATEADD(day,1,@enddate)
      
      UNION
      
      select
      	'Заказы' as Type,
      	count(*) as Kolvo,
      	'Summa' =
      		CASE 
      			WHEN sum(CRMAF_fso.totalamount) is not null THEN sum(CRMAF_fso.totalamount)
      			ELSE 0
      		END
      from 
      	dbo.FilteredSalesOrder as CRMAF_fso
      where
      	CRMAF_fso.createdon between DATEADD(day,-1,@startdate) and DATEADD(day,1,@enddate)
      

      этот запрос возвращает табличку следующего вида:

      Type Kolvo Summa
      Возможные сделки 4 9441,92
      Заказы 3 3421,44
      Предложения 2 1774,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 kolvo
    from
    	dbo.FilteredLead as CRMAF_fl
    where
    	1=1
    	and CRMAF_fl.statecodename = 'Открыть'
    	and CRMAF_fl.createdon between DATEADD(day,-1,@startdate) and DATEADD(day,1,@enddate)
    
    union
    
    select
    	'Интересы' as klients,
    	'Потеряные' as status,
    	COUNT(*) as kolvo
    from
    	dbo.FilteredLead as CRMAF_fl
    where
    	1=1
    	and CRMAF_fl.statecodename = 'Дисквалифицирован'
    	and CRMAF_fl.modifiedon between DATEADD(day,-1,@startdate) and DATEADD(day,1,@enddate)
    
    union
    
    select
    	'Бизнес-партнеры' as klients,
    	'Новые' as status,
    	COUNT(*) as kolvo
    from
    	dbo.FilteredAccount as CRMAF_fa
    where
    	1=1
    	and CRMAF_fa.statecodename = 'Активный'
    	and CRMAF_fa.createdon between DATEADD(day,-1,@startdate) and DATEADD(day,1,@enddate)
    
    union
    
    select
    	'Бизнес-партнеры' as klients,
    	'Потеряные' as status,
    	COUNT(*) as kolvo
    from
    	dbo.FilteredAccount as CRMAF_fa
    where
    	1=1
    	and CRMAF_fa.statecodename = 'Неактивный'
    	and CRMAF_fa.modifiedon between DATEADD(day,-1,@startdate) and DATEADD(day,1,@enddate)
    

    он вернет вот такую табличку:

    klients status kolvo
    Бизнес-партнеры Новые 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 Summa
    union
    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)
    	) - (	
    	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
    

    результат работы этого запроса будет следующим:

    Type Summa
    Задолженность 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 Kolvo
    from
    	dbo.FilteredActivityPointer as CRMAF_fap
    where
    	CRMAF_fap.createdon between DATEADD(day,-1,@startdate) and DATEADD(day,1,@enddate)	
    group by
    	CRMAF_fap.activitytypecodename,
    	DATEPART(WW, CRMAF_fap.createdon)
    

    Этот sql-запрос возвращает данные в таком виде:

    Type Nedelya Kolvo
    Встреча 31 3
    Встреча 32 3
    Задача 32 2
    Звонок 32 1
    Встреча 33 6
  • Добавьте в рабочую область линейную диаграмму и поместите в ее верхнюю добавочную область поле 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 <имя_отчета.rdl> as – и сохраните где-нибудь на диске!
  • Откройте менеджер отчетов Reporting Services. По умолчанию его адрес всегда http://<сервер_отчетов>/Reports
    На верхней панели инструментов нажмите Передать файл.
  • Откроется страница загрузки фала, нажмите Browse и выберите rdl-файл выгруженный из VS. Ок. Файлик успешно (должен по крайней мере 🙂 ) подгрузится…
  • Далее нам нужно открыть его через Report Viewer (это инструмент Reporting Services для отображения отчетов, а репорт менеджер это инструмент администрирования, хотя с некоторыми ограничениями и через него можно смотреть отчеты) и запомнить его URL. Откройте следующую урлу http://<сервер_отчетов>/ReportServer. Тут Вы увидите среди прочего название загруженного отчет – щелкните по нему. Сформируется отчет – скопируйте куда-нибудь строчку URL, которая будет выглядеть примерно так:
    http://crm2008/ReportServer/Pages/ReportViewer.aspx?%2f%d0%9f%d0%b0%d0%bd%d0%b5%d0%bb%d1%8c+%d1%83%d0%bf%d1%80%d0%b0%d0%b2%d0%bb%d0%b5%d0%bd%d0%b8%d1%8f&rs:Command=Render
    

    и припишите к ней вот такую строчку (она делает так, чтобы при формировании отчета строка с параметрами была по умолчанию свернута, другие параметры можете посмотреть в статье «Using URL Access Parameters»):

    &rc:Parameters=collapsed
    

    В получившейся строке замените все символы & на &amp;
    В конечном итоге должно получиться что-то вроде этого:

    http://crm2008/ReportServer/Pages/ReportViewer.aspx?%2f%d0%9f%d0%b0%d0%bd%d0%b5%d0%bb%d1%8c+%d1%83%d0%bf%d1%80%d0%b0%d0%b2%d0%bb%d0%b5%d0%bd%d0%b8%d1%8f&rs:Command=Render&rc:Parameters=collapsed
    



  • Теперь выгрузите из CRM файл SiteMap (Схему узла) и откройте его в каком-нибудь текстовом редакторе и добавьте в область Workplace следующие строчки:
    <Group Id="dashboard" Title="Панель управления">
    	<SubArea Id="nav_dashboard" Title="Панель управления" Icon="/_imgs/presence/imnon.png" Url="http://crm2008/ReportServer/Pages/ReportViewer.aspx?%2f%d0%9f%d0%b0%d0%bd%d0%b5%d0%bb%d1%8c+%d1%83%d0%bf%d1%80%d0%b0%d0%b2%d0%bb%d0%b5%d0%bd%d0%b8%d1%8f&amp;rs:Command=Render&amp;rc:Parameters=collapsed" />
    </Group>
    

    и замените параметр Url для отчета на свой (тот, что мы скопировали на предыдущем шаге)!

  • Экспортируйте схему сайта обратно;


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

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

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


Улучшения

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

Исходники

Исходники проекта – чтобы воспользоваться ими, Вам нужно только изменит параметры подключения к SQL серверу!

Комментарии (24)
  • Костя 23.08.2009

    Привет, есть вопрос: можно ли сделать, чтобы данные отображались только по авторизированному пользователю? Т.е. я авторизировался и увидел только свои продажи и прочее.

  • slivka_83 23.08.2009

    Привет 🙂
    Ну, в данном случаи они и так отображаются в соответствии с ролями безопасности, т.к. я использую CRMAF_ в запросах (более подробно http://mmcrm.ru/?p=539 ).

  • Костя 23.08.2009

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

  • slivka_83 23.08.2009

    Т.е. Вы хотите отобразаить только те записи у которых владелец текущий пользователь (который запустил эту панель управления)?

  • Роман 23.08.2009

    Привет
    почему то консоль только у Администратора работает
    у других говорит что не прав

    что это может быть?
    где назначить права другим пользователям?

  • slivka_83 23.08.2009

    Привет 🙂
    Хм… я не проводил полномасштабного тестирования, поэтому на вскидку не скажу… но на этой неделеи обязательно попробую 🙂

  • slivka_83 23.08.2009

    2Костя
    Итак… если я правильно Вас понял, то порядок действий примерно таков:
    1. Добавляете още один праметр в отчет (помимо stardate и enddate) с названием, например, @id;
    2. Дописываете сами SQL запросы, так чтобы они прверяли id владельца записис;
    3. далее в ISV.config нужно добавить параметр id (текущего пользователя) в URL отчета и передать его — можете воспользоваться для этого функцией из этой статьи http://mmcrm.ru/?p=291

  • Костя 23.08.2009

    Попробую на днях, есть пару идей, вопрос на будущее задавал 🙂

  • PanSem 23.08.2009

    Добрый день!
    При попытки отправки панели через диспетчер отчетов получаю ошибку и сообщение:
    Недопустимое определение отчета. Подробности: Определение отчета имеет недопустимое целевое пространство имен «http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition», которое невозможно обновить. (rsInvalidReportDefinition) Вызов справки в Интернете

    Прошу помочь!

  • slivka_83 23.08.2009

    1. В VS панель нормально работет?
    2. Другие отчеты работают?
    3. Панель работает через менеджер отчетов Reporting Services?

  • Дмитрий 23.08.2009

    Привет!

    подскажи пожалуйста в какой таблице хранятся связи n..n

  • slivka_83 23.08.2009

    Как-то подзабыл ответить 🙂 сори 🙂 если мне не изменяет память, то для связей N:N создается своя таблица с названием соответствующим названию связи 🙂

  • Dmitry 23.08.2009

    привет!

    может подскажешь как использовтаь параметры в отчете которые могут содержать несколько значений?

    т.е. у меня в отчете есть параметр содержащий список городов (возможен выбор нескольких значений). в sql запросе пытаюсь использовать этот параметр в контсрукции WHERE … IN @cities

    но выполнение завершается с ошибкой.

    буду признателен за помощь

  • slivka_83 23.08.2009

    Добрый день 🙂

    Если параметр @cities правильно настроен, то нада так:

    WHERE … IN (@cities)
  • Александр 23.08.2009

    Добрый день,

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

  • slivka_83 23.08.2009

    Добрый день.

    ну, если вам нужно отобрать уникальные значения, то нужно воспользоваться дистинктом: http://www.ageent.ru/sql-distinct.html

  • Александр 23.08.2009

    Добрый день,

    Отчет был мной создан в Reporting Services, когда я его просматриваю в crm 4.0 то всё нормально работает. А вот когда его сотрудник просматривает под своим логином он не открывается(права на отчеты инструментами crm 4.0 предоставлены). Может проблема быть из-за того что в query я данные беру из таблиц …ExtensionBase? Может надо какой то доступ еще предоставлять на уровне сервера???

  • slivka_83 23.08.2009

    Именно из-за этого. У простых юзверов CRM нет непосредственного доступа к таблицам CRM. У них есть доступ только ко вьюхам — оттуда и выбираете данные.

  • Александр 23.08.2009

    спасибо разобрался!

  • ТОЛЯ 23.08.2009

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

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

  • slivka_83 23.08.2009

    Что такое «анимированный отчет»? Что за анимацию Вы хотите там видеть? 🙂

  • Александр 23.08.2009

    Добрый день, вопрос по Reporting Services. В отчете есть круговая диаграмма, метки я вынес за пределы диаграммы, но не отображаются линии выноса, подскажите где это можно настроить. SQL server 2008 R2.

  • slivka_83 23.08.2009

    Здрасьте.
    Вот тут показан пример:
    http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/A_1884-Pie-Chart-Techniques.html
    Нужно задать для серии свойства 3DLabelLineSize, MinimumRelativePieSize и PieLabelStyle.

  • Александр 23.08.2009

    спасибо большое!!!

*

code