Microsoft Dynamics CRM

На досуге занимался отчетами и собрал несколько полезных примеров кода на T-SQL, которые можно использовать в отчетах для Microsoft Dynamics CRM 4.0.
1. Получение дня недели

 CASE DATEDIFF(DAY,0, createdon)%7
WHEN 0 THEN 'Понедельник'
WHEN 1 THEN 'Вторник'
WHEN 2 THEN 'Среда'
WHEN 3 THEN 'Четверг'
WHEN 4 THEN 'Пятница'
WHEN 5 THEN 'Суббота'
WHEN 6 THEN 'Воскресенье'
END as 'Дата, день недели'

2. Функция для получения списка фамилий контактов через точку с запятой. Параметры: guid организации.
Основную работу выполняет стандартная функция COALESCE, которая позволяет объединить строки через разделитель.

CREATE FUNCTION [dbo].[GetContactList](@AccountID varchar(40))
RETURNS varchar(max) AS
BEGIN
DECLARE @StringList varchar(max)
SELECT @StringList = COALESCE(@StringList + '; ', '') + c.fullname
from account a
left join contact c
	on c.parentcustomerid = @AccountID
IF @StringList IS NULL
SET @StringList = 'Нет контактов'
RETURN @StringList
END

Пример вызова:

[DB_Name].[dbo].[GetContactList](accountid) as 'Контакты'

Результат : Иванов; Петров; Сидоров

3. Конвертирование даты в формате дд.мм.гггг
3.1 T-SQL

(CONVERT(varchar(10),createdon, 104)

3.2 в SQL Server Business Intelligence Development Studio: Format(Parameters!startdate.Value,»dd.MM.yyyy»)

4. Форматирование поля типа money, float с разрядами
Format(Sum(Fields!Сумма.Value),»### ### ##0.0#»)

Прошлая небольшая подборка здесь: Полезные SQL-функции для использования в отчетах MS CRM

Пользовательские атрибуты, которые мы добавляем в сущности, хранятся в расширенной таблице этой сущности. На самом деле количество атрибутов, которые можно добавить в сущность,не безгранично. Microsoft SQL Server допускает до 1024 столбцов в таблице и количество данных, хранящихся в каждой строке не может превышать 8060 байт.

Если Вы решите добавить пользовательский атрибут в сущность, можете сначала проверить достаточно ли свободного места для атрибута, выполнив следущий SQL-запрос для базы MS CRM:

select e.name,bytes_remaining=8060-(sum(a.length))
from entity e join attribute a on e.entityid=a.entityid
where a.iscustomfield=1 and a.islogical=0
group by e.name

Также, Вы можете узнать количество байт уже использованных пользовательскими атрибутами для сущности, выполнив SQL-запрос для базы MS CRM:

select e.name,physical_size=sum(a.length)
from entity e join attribute a on e.entityid=a.entityid
where a.iscustomfield=1 and a.islogical=0
group by e.name

Если вдруг случилось так, что вы потеряли свой LicenseKey для MS CRM, то его можно подглядеть в базе MS SQL

Следует сделать следующий запрос для вашей базы MS CRM:

SELECT LicenseKey from License 

Данный SQL-скрипт, выполненный для базы MS CRM, позволит Вам узнать количество записей,количество объектов сущности, их размер,  индекс:

SELECT

 CAST(OBJECT_NAME(id) AS VARCHAR(50)) AS 'Table',

 rowcnt AS 'Row Count',

 dpages * 8/1024 AS 'Data - Mb',

 ((SUM(used) * 8) - (dpages * 8))/1024 AS 'Index - Mb',

 ((reserved * 8) - (SUM(used) * 8))/1024 AS 'Slack - Mb',

 reserved * 8/1024 AS 'Disk - Mb'

FROM sysindexes

WHERE indid IN (0,1)

 AND OBJECTPROPERTY(id, 'IsUserTable') = 1

GROUP BY id, rowcnt, reserved, dpages

ORDER BY 'Disk - Mb' desc;

GO

Читать полностью »

В базу MS CRM встроены некоторые полезные SQL-функции, которые доступны для использования в запросах для написания отчетов, выгрузки данных в Excel, или же просто для проверки данных в вашей базе. Вот некоторые из часто встречающихся функций:

1. fn_LastXYear (возвращает дату. Первый параметр-типа datetime,второй -  int). Пример:

fn_LastXYear

Читать полностью »