What other expressions can I use to try to calculate average daily amount in SSRS Report

Question

I am building a report with 2 matrix tables with different (but very similar) data sets. I need to calculate the average daily transaction amount where the user inputs the month and year they want to see. I was able to use the following expression on the first table with no issue.

=sum(fields!tot_rev.Value)/fields!Day.Value

Where "Day" is the day of the month. I used "Day(trn_dt) as Day" in my query to extract the day from the datetime string.

I tried using the same expression in my second table but it only returns the sum total amount. If I use

=sum(fields!amt.Value)/countdistinct(fields!Day.Value)

it returns a value that seems to be random (I don't know how it came to that value with any calculation)

The only way I have been able to successfully get the average was by inputting the number of days in the expression.

=sum(fields!amt.Value)/22

But I need this to work with the parameters and the number of days will not be constant.

Here is a sample of my data from 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)

Month, Day, 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

Here is sample from dataset2 (table with the problem):

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)

Year, Month, Day, reg_no, amt, acc_by

2020, 10, 1, 40113, 377.96, JLMA

2020, 10, 1, 40115, 6637.07, NASO

2020, 10, 2, 40104, 790, MCGO

2020, 10, 2, 40106, 1406.25, MCGO

2020, 10, 3, 40100, 239.77, JEDX

In the attached picture the upper left corner expression is: =MonthName(Fields!MONTH.Value,false)

The day of month is listed along the left with the total count (count of transactions) and sum for each processor(acc_by/bill_by) in the data field.

I used =iif(isnothing(Sum(Fields!amt.Value)), 0, sum(fields!amt.Value)) as the expression for the amt field.

The column total is the total count and sum for that day from all processors.

The row total at is the average daily count and average daily amt by processor and where the total column and total row intersect it is the average daily count for the month and I would like to show the average daily transaction amt for the month as well but this is what isn't working properly.

See attached image:

image of table 2

Does anyone know what else I can try?

You you edit your question and add a sample of data from each dataset including datatypes, along with the expected results based on that sample data. If you report has any grouping, show a screenshot of your report including any row or column groups as that may have an impact on the answer. — Alan Schofield

Wes H Wes H · Accepted Answer · 2020-10-23T13:59:07

Don't break a date down into its component parts until you are presenting it, and even then use formatting to display the date part without converting the date to ints or strings. So many calculation bugs are introduced this way.

Leave inv_dt and trn_dt as Datetime values in your query and bring them into SSRS that way. Within SSRS, use the date formatting values to show just the year or month.

To calculate your daily average, create a grouping in the SSRS report on the date. Choose an easily understood group name, like InvoiceDate or something. Then use Avg(fields!amt.value,"InvoiceDate"). This will tell SSRS to average the amount within the grouping (each day). If desired, you can than average the average by Avg(Avg(fields!amt.value,"InvoiceDate")).

Please take the time to learn how to manipulate dates in SQL and SSRS. Your query results will be more accurate and your peers will thank you when maintaining your code.

As a sample of what I'm talking about, see if you can spot the logic bug in some DB2 code I'm updating this week (Hint problem happens in January:

  AND Month(CUST.acct_svc_strt_d) >= (Month(CURRENT DATE) - 1)
  AND Year(CUST.acct_svc_strt_d) = Year(CURRENT DATE)

What other expressions can I use to try to calculate average daily amount in SSRS Report

1 Answers