СТО МИ пользователя «OLAP-куб графиков платежей, составление отчётов с помощью Microsoft Excel»

Описание OLAP-куба графиков платежей

OLAP-куб графиков платежей предназначен для хранения и периодического обновления данных о документах графиков платежей, начислениям и фактическим платежам по графикам. Информация из куба графиков платежей может быть использована для составления пользовательских отчётов с выводом данных в сводную таблицу Microsoft Excel.

Подключение к OLAP-кубу графиков платежей

Прежде, чем приступить к составлению отчёта, необходимо подключиться к OLAP-кубу графиков платежей. Если подключение к этому кубу с вашего компьютера производится впервые, то сначала потребуется его создать. Для этого надо перейти на закладку Данные панели инструментов Excel в выбрать пункт «Из других источников | Из служб аналитики» (рисунок 1).

Затем задать имя сервера и нажать «Далее». Выбранный пример куба расположен на сервере datahouse, поэтому вводим это имя в поле «Имя сервера» (рисунок 2).

На следующем шаге выбрать из выпадающего списка базу данных, содержащую куб цен на квартиры. Выбираем базу данных RealtorOLAP, а в списке кубов выделяем куб Schedules и нажимаем «Далее» (рисунок 3).

Выбор базы, содержащей куб цен на квартиры

На следующем шаге задать подключению понятное имя и при необходимости заполнить поле описания, после чего нажать Готово (рисунок 4).

В окне «Импорт данных», которое появится сразу после этого, можно просто нажать OK (рисунок 5).

Если ранее вы уже создавали подключение к кубу Price на вашем компьютере, его можно просто выбрать из списка и нажать «Открыть» (рисунок 6).

Числовые параметры (меры) OLAP-куба графиков платежей

Группа «График платежей»

Источником данных для мер из группы «График платежей» являются значения соответствующих параметров из графика платежей покупателей квартир. Часть полей является вычисляемыми (таблица 1).

Мера Параметр 1С Итоговая функция

Агентское вознаграждение Агентское вознаграждение Сумма

Количество графиков платежей Количество графиков платежей покупателей квартир * Сумма

Отсрочка Отсрочка платежа в месяцах Максимальное

Первый взнос кв м Оплачено М2 Сумма

Первый взнос руб Сумма первого взноса Сумма

Процент пени в день Процент пени в день Среднее

Скидка в процентах Сумма всех скидок из графика в процентах ** Среднее

Скидка в рублях Сумма всех скидок из графика в рублях *** Сумма

Субагентское вознаграждение Субагентское вознаграждение Сумма

Сумма инвестирования Сумма инвестирования Сумма

Сумма по графику Сумма по документу графика платежей Сумма

Суммарный процент скидки Вычисляемое значение **** Среднее

Цена по прайсу Цена по прайсу Сумма

Цена продажи Цена продажи Сумма

* Количество графиков платежей соответствует количеству документов графиков платежей по текущей позиции.

** Скидка в процентах: если таблица скидок графика платежей покупателей содержит скидки в процентах, то берётся суммарное значение таких скидок из этой таблицы. Иначе, значение берётся из поля [Процент скидки на первый взнос] графика платежей покупателей.

*** Скидка в рублях: если таблица скидок графика платежей покупателей содержит скидки в рублях, то берётся суммарное значение таких скидок из этой таблицы. Иначе, значение равно 0.

**** Суммарный процент скидки: если [Цена по прайсу] не равна 0 и [Цена продажи] не равна 0, то значение вычисляется как 100 – [Цена продажи] / [Цена по прайсу]. Иначе, значение равно 0.

Группа «Квартиры из графика»

Источником данных для мер из группы «Квартиры из графика» является список квартир из графика платежей покупателей, таблица 2.

Мера Параметр 1С Итоговая функция

Количество квартир из графика Количество квартир из графика платежей покупателей квартир * Сумма

Площадь квартиры из графика Площадь квартиры с балконами из графика платежей Сумма

Цена за кв м Вычисляемое значение **

* Количество квартир из графика соответствует количеству квартир, включённых в график платежей по текущей позиции.

** Мера [Цена за кв м] вычисляется по формуле: [Цена по прайсу] / [Площадь квартир из графика], где [Цена по прайсу] – значение меры из группы «График платежей», а [Площадь квартир из графика] – значение меры из этой группы.

Группа «Контакты с клиентами»

Источником данных для мер из группы «Контакты с клиентами» являются данные Lync по всем принятым входящим и исходящим голосовым сеансам связи. Информация о телефонах контрагентов и учётных записях Lync ответственных за график платежей поступают из каталога лиц, после чего для каждой пары «контрагент – ответственный из графика» устанавливается длительность и количество телефонных звонков (рисунок 3).

Мера Параметр БД Lync Итоговая функция

Длительность звонков Длительность входящих и исходящих звонков в минутах Сумма

Количество звонков Количество входящих и исходящих звонков Сумма

Мера	Параметр БД Lync	Итоговая функция
Длительность звонков	Длительность входящих и исходящих звонков в минутах	Сумма
Количество звонков	Количество входящих и исходящих звонков	Сумма

Группа «Начисления по графикам»

Источником данных для мер из группы «Начисления по графикам» являются начисления по графикам из 1С (таблица 4)

Мера Параметр 1С Итоговая функция

Начислено кв м Площадь начисления в квадратных метрах Сумма

Начислено руб Сумма начисления в рублях Сумма

Сумма скидки руб Сумма скидки в рублях Сумма

Группа «Оплачено»

Источником данных для мер из группы «Оплачено» являются распределённые платежи из 1С (таблица 5).

Мера	Параметр 1С	Итоговая функция
Оплачено кв м	Площадь распределённого платежа в квадратных метрах	Сумма
Оплачено всего руб	Сумма распределённого платежа в рублях	Сумма
Цена платежа	Цена платежа за квадратный метр в рублях	Среднее
Оплачено по допсоглашению руб	Сумма распределённого платежа в рублях для вида платежа	Сумма

Группа «Осталось оплатить»

Мера «Осталось оплатить» вычисляется путём вычитания суммы всех распределённых платежей по графику из суммы всех начислений. В расчёт берутся все начисления и платежи, внесённые в 1С до момента перекачки данных в куб (таблица 6).

Мера Параметр 1С Итоговая функция

Осталось оплатить Сумма остатка оплаты по графику платежей Сумма

Группа «Параметры квартир объектов строительства»

Источником данных для мер из группы «Параметры квартир объектов строительства» являются параметры квартир объектов строительства из 1С, кроме значений по БТИ, которые берутся из документов замера по БТИ. Для каждой меры из 1С извлекается последнее ненулевое (непустое) значение соответствующего параметра (таблица 7).

Мера Параметр 1С Итоговая функция

Количество балконов Количество балконов Сумма

Количество квартир Количество уникальных записей по квартирам * Сумма

Количество комнат Количество комнат Сумма

Окон на восток Окон на восток Сумма

Окон на запад Окон на запад Сумма

Окон на север Окон на север Сумма

Окон на юг Окон на юг Сумма

Площадь без балконов Площадь без балконов Сумма

Площадь с балконами Площадь с балконами Сумма

Стоимость остекления Стоимость остекления Сумма

* Количество квартир соответствует количеству уникальных пар «объект строительства» + «квартира» в рамках текущего отчёта.

Для всех параметров квартир, указанных в таблице 7, из 1С берётся последнее установленное ненулевое значение.

Группа «Покупатели»

Группа «Покупатели» содержит всего одну меру – [Количество покупателей], которая вычисляется как количество записей в таблице Договора графика платежей покупателей (таблица 8).

Мера Параметр 1С Итоговая функция

Количество покупателей Количество покупателей из графика платежей Сумма

Измерения OLAP-куба графиков платежей

Измерение «Виды оплат»

Источником данных для измерения «Виды оплат» является перечисление видов оплат из 1С со значениями.

Касса.

Банк.

Векселя.

Переоформление, корректировка.

Прочее.

Предоплата.

Ввод остатков.

Через агента.

Касса через агента.

Банк через агента.

Расторжение.

Агентские по акции.

Оплата за риэлторские услуги (ИП).

Векселя собственные.

Деньги по предъявл. Векселю.

Измерение «Графики платежей»

Источником данных для атрибутов измерения «Графики платежей» являются документы графиков платежей покупателей квартир из 1С (таблица 9).

Атрибут измерения Параметр 1С

Агент Агент

Вариант заключения договора Вариант заключения договора

Вид операции Вид операции:
График платежей

График платежей через другую организацию

График платежей покупателей квартир

Переоформления графика платежей

Остатки по графику платежей

График платежей Номер и дата графика платежей

Дата графика платежей Дата документа графика платежей

Договор агента Номер и дата договора агента

Договор инвестирования Номер и дата договора инвестирования

Договор контрагента Номер и дата договора контрагента

Застройщик Наименование застройщика

Категория квартиры для агента Категория квартиры для агента:
Первая категория

Вторая категория

Третья категория

Категория квартиры для субагента Категория квартиры для субагента:
Первая категория

Вторая категория

Третья категория

Контрагент Наименование контрагента

Метод расчета платежей Метод расчета платежей:
Произвольный график

С увеличением остаточной стоимости

С уплатой процента на остаток долга

Объект строительства Объект строительства

Организация из графика Организация

Ответственный из графика Ответственный

ПИФ на регистрации ПИФ на регистрации

Риэлторское агентство Риэлторское агентство

Тип помещения Тип помещения:
Новая квартира

Вторичная квартира

Тип цены Тип цены

Атрибут измерения	Параметр 1С
Агент	Агент
Вариант заключения договора	Вариант заключения договора
Вид операции	Вид операции: График платежей График платежей через другую организацию График платежей покупателей квартир Переоформления графика платежей Остатки по графику платежей
График платежей	Номер и дата графика платежей
Дата графика платежей	Дата документа графика платежей
Договор агента	Номер и дата договора агента
Договор инвестирования	Номер и дата договора инвестирования
Договор контрагента	Номер и дата договора контрагента
Застройщик	Наименование застройщика
Категория квартиры для агента	Категория квартиры для агента: Первая категория Вторая категория Третья категория
Категория квартиры для субагента	Категория квартиры для субагента: Первая категория Вторая категория Третья категория
Контрагент	Наименование контрагента
Метод расчета платежей	Метод расчета платежей: Произвольный график С увеличением остаточной стоимости С уплатой процента на остаток долга
Объект строительства	Объект строительства
Организация из графика	Организация
Ответственный из графика	Ответственный
ПИФ на регистрации	ПИФ на регистрации
Риэлторское агентство	Риэлторское агентство
Тип помещения	Тип помещения: Новая квартира Вторичная квартира
Тип цены	Тип цены

Измерение «Дата графика платежей»

Источником данных для измерения «Дата графика платежей» является дата документа из 1С, соответствующего графику платежей покупателей квартир. Установка фильтра по этому измерению влияет на выборку документов графика платежей. Измерение «Дата графика платежей» связано с общей для всех документов шкалой времени OLAP-куба.

Измерение «Дата платежа»

Источником данных для измерения «Дата платежа» является дата, на которую распределён платёж в 1С (дата зачисления в график). Установка фильтра по этому измерению влияет на выборку числовых значений из группы «Оплачено». Измерение «Дата платежа» связано с общей для всех документов шкалой времени OLAP-куба.

Измерение «Квартиры объектов строительства»

Источником данных для измерения «Квартиры объекта строительства» являются связанные пары «объект строительства – квартира» и назначенные им параметры квартир из 1С (таблица 10).

Атрибут измерения Параметр 1С

Без скидок Без скидок

Город Город

Кадастровый номер Кадастровый номер

Квартира Квартира

Комнат Количество комнат

Комплекс Комплекс

Микрорайон Микрорайон

Почтовый номер дома Дом

Район Район

Секция Секция

Строительный номер дома Строительный номер

Тип дома Тип дома

Тип отделки Тип отделки

Удобна под магазин Удобна под магазин

Удобна под нежилое Удобна под нежилое

Улица Улица

Улучшенный фасад Улучшенный фасад

Этаж Этаж

Этажность Этажность

Этажность нежилых Этажность нежилых

Атрибут измерения	Параметр 1С
Без скидок	Без скидок
Город	Город
Кадастровый номер	Кадастровый номер
Квартира	Квартира
Комнат	Количество комнат
Комплекс	Комплекс
Микрорайон	Микрорайон
Почтовый номер дома	Дом
Район	Район
Секция	Секция
Строительный номер дома	Строительный номер
Тип дома	Тип дома
Тип отделки	Тип отделки
Удобна под магазин	Удобна под магазин
Удобна под нежилое	Удобна под нежилое
Улица	Улица
Улучшенный фасад	Улучшенный фасад
Этаж	Этаж
Этажность	Этажность
Этажность нежилых	Этажность нежилых

Для всех параметров квартир, указанных в таблице 10, из 1С берётся последнее установленное непустое значение.

Измерение «Коды операций»

Источником данных для атрибутов измерения «Коды операций» являются коды операций по графику из 1С со следующими значениями.

Заключение договора.

Оплата по графику.

Коррекция по итогам платежей.

Расторжение договора.

Акт передачи.

Коррекция графика.

Зачет нераспределенных сумм.

Коррекция оплаченной площади.

Оплата по доп. соглашениям.

Возврат оплаты по доп. соглашениям.

Возврат оплаты.

Заключение допсоглашения.

Расторжение допсоглашения

Изменение цены.

Сторно по изменению цены.

Измерение «Объекты строительства»

Источником данных для измерения «Объекты строительства» является справочник объектов строительства из 1С, таблица 11.

Атрибут измерения Параметр 1С

Объект строительства Наименование объекта строительства

Дата ввода в эксплуатацию Дата ввода в эксплуатацию объекта строительства

Предварительная дата ввода в эксплуатацию Предварительная дата ввода в эксплуатацию объекта строительства

Атрибут измерения	Параметр 1С
Объект строительства	Наименование объекта строительства
Дата ввода в эксплуатацию	Дата ввода в эксплуатацию объекта строительства
Предварительная дата ввода в эксплуатацию	Предварительная дата ввода в эксплуатацию объекта строительства

Измерение «Период платежа»

Источником данных для измерения «Период платежа» является дата платежного документа из 1С. Установка фильтра по этому измерению влияет на выборку числовых значений из группы «Оплачено». Измерение «Период платежа» связано с общей для всех документов шкалой времени OLAP-куба.

Измерение «Телефоны контрагентов»

Источником данных для атрибута «Телефон контрагента» измерения «Телефоны контрагентов» являются телефоны контрагентов из контактной информации каталога лиц.

Пример составления отчёта

Отчёт: Графики платежей покупателей квартир за период 01.04.2012 – 30.04.2012.

Порядок действий составления отчёта:

1) Добавить меры Количество графиков платежей в область значений (рисунок 7).

2) Добавить меру Сумма документа в область значений (рисунок 8).

3) Добавить меру Первый взнос руб в область значений (рисунок 9).

4) Добавить меру Отсрочка макс в область значений (рисунок 10).

5) Добавить меру Площадь с балконами в область значений (рисунок 11).

6) Добавить меру Площадь с балконами в область значений (рисунок 12).

7) Добавить меры Цена продажи и Цена по прайсу в область значений (рисунок 13).

8) Добавить меру Площадь без балконов в область значений (рисунок 14).

Добавление меры Площадь без балконов в область значений

9) Добавить меру Агентское вознаграждение в область значений (рисунок 15).

10) Добавить меру Субагентское вознаграждение в область значений (рисунок 16).

11) Добавить меру Количество дольщиков в область значений (рисунок 17).

12) Добавить атрибут измерения Дата графика платежей.Месяц в область фильтров (рисунок 18).

13) Установить фильтр по атрибуту измерения Дата графика платежей.Месяц равным апрелю 2012 (рисунок 19).

14) Добавить атрибуты измерения График платежей и Вид операции в область названий строк (рисунок 20).

15) Добавить атрибут измерения Объект строительства в область названий строк (рисунок 21).

16) Добавить атрибут измерения Квартира в область названий строк (рисунок 22).

17) Добавить атрибут измерения Контрагент в область названий строк (рисунок 23).

18) Добавить атрибут измерения Договор контрагента в область названий строк (рисунок 24).

19) Добавить атрибут измерения Риэлторское агентство в область названий строк (рисунок 25)

20) Добавить атрибут измерения Вариант заключения договора в область названий строк (рисунок 26).

В результате по лучим отчёт, содержимое которого совпадает с отчётом «Графики платежей покупателей квартир», сформированным на период с 1.04.2012 по 30.04.2012 (рисунок 27).

Отчет, содержимое которого совпадает с отчётом «Графики платежей покупателей квартир»

Добавляя в различные области шаблона другие параметры куба, имеется возможность формировать собственные отчёты, в том числе и такие, аналогов которых в 1С нет.