Создать диаграмму, используя записи определенного типа, сгруппированные по месяцам, с подвижным балансом

Я пытаюсь создать диаграмму (гистограмму или линию) в кристалле из одной таблицы в моей базе данных (Sage CRM).

Записи следующие

CustomerId      Date            Invoice              Amount

1234           3/4/2013        Cust Invoice           3322.00   
1234           3/4/2013        Payment                2445.00  
1234           4/5/2013        A/c transaction         322.00  
1234           5/6/2013        interest                 32.00 
1234           6/6/2013        payment                 643.00

Поэтому я хотел бы иметь отчет, который соответствует следующим критериям

  1. Только записи за последние 12 месяцев, сгруппированные по месяцам
  2. Только счета-фактуры оплаты, счета и проценты
  3. Движущийся баланс, который вычисляет все суммы счета, т.е.

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

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

Источник

3 ответа

Решение

Потратив почти неделю на эту проблему, с большой помощью эксперта, я наконец-то нашел решение.

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

Select 
  //All of the fields for the report,
  movingBalance.Amount
from myInvoiceTable as mit
  <join to any other tables for the report>
  left join (
    select customerID, sum(amount) as Amount
    from myInvoiceTable
    where Record_Type in ('Payment', 'Invoice','Interest')
      and Date < {?Report Start Date}
    group by customerID) movingBalance
   on mit.customerID = movingBalance.customerID
where mit.RecordType in ('Payment', 'Invoice','Interest')
  and mit.Date >= {?Report Start Date}
  and mit.Date <= {?Report End Date}

Есть несколько приемов использования команд:

  1. Из соображений производительности вы обычно хотите включить ВСЕ данные для отчета в одну команду. Избегайте объединения нескольких команд или объединения одной или нескольких таблиц в команду.
  2. Фильтруйте данные в команде, а НЕ в эксперте выбора в отчете.
  3. Создайте любые параметры в редакторе команд, а не в основном отчете. Параметры, созданные в отчете, не будут работать в редакторе команд.

Это сделало свое дело.

Источник

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

Теперь вы можете манипулировать данными внутри отчета, используя поля формул.

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

Источник

В этом случае Rachsherry я бы порекомендовал следующее.

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

Для счетов щелкните правой кнопкой мыши поле счета, затем "Формат поля" на вкладке "Общие" рядом с опцией "Подавить", там есть кнопка формулы, введите следующее...

 IF {YourInvoiceField} IN ["Payment", "Invoice", "Interest] THEN FALSE ELSE TRUE

Для вашего требования даты вы должны использовать формулу выбора... Код для этого должен выглядеть примерно так

 {YourDateHere} > DateAdd ("yyyy", -1, CurrentDate) AND {YourDateHere} < CurrentDate

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

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

Редактировать - альтернативой этому является использование полей параметров (которые мне лично не нравятся), это просто означает необходимость ввода параметров каждый раз при обновлении отчета, они говорят сами за себя, но вы можете найти руководство здесь

Источник
Другие вопросы по тегам