#reporting-services #expression #average
#службы отчетов #выражение #среднее
Вопрос:
Я создаю отчет с 2 матричными таблицами с разными (но очень похожими) наборами данных. Мне нужно рассчитать среднесуточную сумму транзакции, в которой пользователь вводит месяц и год, которые они хотят видеть. Я смог использовать следующее выражение в первой таблице без проблем.
=sum(fields!tot_rev.Value)/fields!Day.Value
Где «День» — это день месяца. Я использовал «День (trn_dt) как день» в своем запросе, чтобы извлечь день из строки даты и времени.
Я попытался использовать то же выражение во второй таблице, но оно возвращает только общую сумму. Если я использую
=sum(fields!amt.Value)/countdistinct(fields!Day.Value)
он возвращает значение, которое кажется случайным (я не знаю, как оно пришло к этому значению при каких-либо вычислениях)
Единственный способ, которым я смог успешно получить среднее значение, — это ввести количество дней в выражении.
=sum(fields!amt.Value)/22
Но мне это нужно для работы с параметрами, и количество дней не будет постоянным.
Вот пример моих данных из dataset1:
SELECT MONTH(inv_dt) AS Month, DAY(inv_dt) AS Day, YEAR(inv_dt) AS Year, bill_by, inv_no, tot_rev
FROM inv_info
WHERE (status_cd IN ('FF', 'FP')) AND (MONTH(inv_dt) = @Month) AND (YEAR(inv_dt) = @Year)
Месяц, день, год, bill_by, inv_no, tot_rev
10, 23, 2020, ERSA, 40444, 4881
10, 23, 2020, ERSA, 40443, 2043
10, 22, 2020, DYCO, 40435, 2504
10, 22, 2020, ERSA, 40431, 20524
Вот пример из dataset2 (таблица с проблемой):
SELECT YEAR(trn_info.trn_dt) AS YEAR, MONTH(trn_info.trn_dt) AS MONTH, DAY(trn_info.trn_dt) AS Day, trn_info.reg_no, trn_dtail.amt, trn_info.acc_by
FROM trn_info LEFT OUTER JOIN
trn_dtail ON trn_info.trn_no = trn_dtail.trn_no
WHERE (trn_info.status IN ('FP', 'FF')) AND (trn_info.tpe_cd IN ('AI', 'BI')) AND (trn_dtail.rte_cd = 'TT') AND (MONTH(trn_info.trn_dt) = @Month) AND (YEAR(trn_info.trn_dt) = @Year)
Год, месяц, день, reg_no, amt, acc_by
2020, 10, 1, 40113, 377.96, JLMA
2020, 10, 1, 40115, 6637.07, НАЗО
2020, 10, 2, 40104, 790, МАКГО
2020, 10, 2, 40106, 1406.25, МАКГО
2020, 10, 3, 40100, 239.77, JEDX
На прикрепленном рисунке выражение в верхнем левом углу: =MonthName(Fields!MONTH.Value,false)
День месяца указан слева с общим количеством (количество транзакций) и суммой для каждого процессора (acc_by / bill_by) в поле данных.
Я использовал =iif(isnothing(Sum(Fields!amt.Value)), 0, sum(fields!amt.Value)) в качестве выражения для поля amt.
Общее количество столбцов — это общее количество и сумма за этот день от всех процессоров.
Общая сумма строк — это среднее ежедневное количество и среднесуточный amt по процессору, а там, где пересекаются столбец total и строка total, это среднее ежедневное количество за месяц, и я хотел бы также показать среднесуточный amt транзакций за месяц, но это то, что не работает должным образом.
Смотрите прикрепленное изображение:
Кто-нибудь знает, что еще я могу попробовать?
Комментарии:
1. Вы редактируете свой вопрос и добавляете выборку данных из каждого набора данных, включая типы данных, вместе с ожидаемыми результатами на основе этих выборочных данных. Если в вашем отчете есть какая-либо группировка, покажите скриншот вашего отчета, включая любые группы строк или столбцов, поскольку это может повлиять на ответ.
2. Дайте мне знать, если вы хотите, чтобы я уточнил что-нибудь еще.
Ответ №1:
Не разбивайте дату на составные части, пока вы ее не представите, и даже тогда используйте форматирование для отображения части даты без преобразования даты в целые числа или строки. Таким образом появляется так много ошибок в вычислениях.
Оставьте inv_dt и trn_dt в качестве значений даты и времени в вашем запросе и таким образом внесите их в SSRS. В SSRS используйте значения форматирования даты, чтобы отображать только год или месяц.
Чтобы рассчитать среднесуточную сумму, создайте группировку в отчете SSRS по дате. Выберите понятное название группы, например InvoiceDate или что-то в этом роде. Затем используйте Avg(fields!amt.value,"InvoiceDate") . Это подскажет SSRS усреднять сумму внутри группировки (каждый день). При желании вы можете усреднить среднее значение на Avg(Avg(fields!amt.value,"InvoiceDate")) .
Пожалуйста, найдите время, чтобы узнать, как управлять датами в SQL и SSRS. Результаты вашего запроса будут более точными, и ваши коллеги будут благодарны вам за поддержку вашего кода.
В качестве примера того, о чем я говорю, посмотрите, можете ли вы обнаружить логическую ошибку в каком-либо коде DB2, который я обновляю на этой неделе (подсказка проблема возникает в январе:
AND Month(CUST.acct_svc_strt_d) >= (Month(CURRENT DATE) - 1) AND Year(CUST.acct_svc_strt_d) = Year(CURRENT DATE)