Getting totals in SSRS today(), Month, Quarterly and year to date

Question

Data from main table i have the following SQL query:

SELECT
ISNULL(SUM(intIntrosHot), 0) Hot, ISNULL(SUM(intIntrosCold), 0) Cold,
ISNULL(ISNULL(SUM(intIntrosHot), 0) + ISNULL(SUM(intIntrosCold), 0),0) Total
    FROM tblUBMReport
    WHERE tblUBMReport.intProp IN 865
    ---AND dtReport BETWEEN @StartDate AND @EndDate;

but for the life of me, I have no idea how to get or design my table in SSRS for total hot leads for today, total hot leads for the month, total hot leads for this quarter, and total hot leads for this year. I need to have my table look like the picture below any help will be appreciated.

This is an image showing what I'm looking to get as an outcome:

enter image description here

Please supply some sample data from you `tblUBMReport` table. Also, do you have a date table or calendar table as that would make this very easy. — Alan Schofield, Jul 27 '22 at 21:29
I cant seem to put the screen shot in here of the table. As this time there no date dimension table. — Jeannie Ramirez, Jul 27 '22 at 22:20
I have added a date table to the database now what can i do? — Jeannie Ramirez, Jul 28 '22 at 16:27

Hannover Fist · Answer 1 · 2022-07-28T21:49:10.347

0

You should be able to get the results desired by grouping your data by date and then filtering the data by the date range.

SELECT dtReport, 
ISNULL(SUM(intIntrosHot), 0) Hot, ISNULL(SUM(intIntrosCold), 0) Cold,
ISNULL(ISNULL(SUM(intIntrosHot), 0) + ISNULL(SUM(intIntrosCold), 0),0) Total
FROM tblUBMReport
WHERE tblUBMReport.intProp IN 865
GROUP BY dtReport

Then add the Expressions for each date range and each temp:

CURRENT DATE

=SUM(IIF(Fields!dtReport.Value = TODAY, Fields!Cold.Value, 0))

MTD

=SUM(IIF(Fields!dtReport.Value >= TODAY.AddDays(1 - TODAY.Day) AND Fields!dtReport.Value <= TODAY, Fields!Cold.Value, 0))

QTD

=SUM(IIF(Fields!dtReport.Value >= DateSerial(Today.Year, (Int((Today.Month - 1) / 3) + 1, 1) AND Fields!dtReport.Value <= TODAY, 
        Fields!Cold.Value, 
        0)
    )

If you hot and cold were on separate lines, it would be easier since the table could be grouped by the Hot/Cold.

edited Jul 28 '22 at 21:49

answered Jul 27 '22 at 22:50

Hannover Fist

10,393
1
18
39

I know I can for the life of me get hot and cold on separate lines since the data comes from the original table that way. – Jeannie Ramirez Jul 27 '22 at 22:58
How would I calculate for Quarter and Year? – Jeannie Ramirez Jul 27 '22 at 23:28
the start date range for the year is relatively easy - `TODAY.AddDays(1 - TODAY.DayOfYear)`. The starting date for the current Q is a little trickier - `DateSerial(Today.Year, (Int((Today.Month - 1) / 3) + 1, 1)`. Then the End of the date range would be the same. – Hannover Fist Jul 27 '22 at 23:55
I'm sorry but I am confused with a quarter can you please give me an example? – Jeannie Ramirez Jul 28 '22 at 14:23
@JeannieRamirez - I added the expression for the QTD. The DateSerial creates a date from the three parts - year, month and day. – Hannover Fist Jul 28 '22 at 21:50

score 0 · Answer 2 · edited Jul 28 '22 at 22:19

I was able to solve my issue and wanted to post it in here so other people who run across the issue can use the solution.

SELECT
'Hot' AS strType,
SUM(CASE WHEN dtReport = @EndDate THEN intIntrosHot ELSE 0 END) AS intDT,
SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfMonth AND @EndDate THEN intIntrosHot ELSE 0 END) AS intMTD,
SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfQuarter AND @EndDate THEN intIntrosHot ELSE 0 END) AS intQTD,
SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfYear AND @EndDate THEN intIntrosHot ELSE 0 END) AS intYTD
FROM
tblUBMReport WITH (NOLOCK)
INNER JOIN tblDate WITH (NOLOCK) ON tblDate.dtDate = @EndDate
WHERE
intProp IN (@Props) AND dtReport BETWEEN @StartDate AND @EndDate
UNION
SELECT
'Cold' AS strType,
SUM(CASE WHEN dtReport = @EndDate THEN intIntrosCold ELSE 0 END) AS intDT,
SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfMonth AND @EndDate THEN intIntrosCold ELSE 0 END) AS intMTD,
SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfQuarter AND @EndDate THEN intIntrosCold ELSE 0 END) AS intQTD,
SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfYear AND @EndDate THEN intIntrosCold ELSE 0 END) AS intYTD
FROM
tblUBMReport WITH (NOLOCK)
INNER JOIN tblDate WITH (NOLOCK) ON tblDate.dtDate = @EndDate
WHERE
intProp IN (@Props) AND dtReport BETWEEN @StartDate AND @EndDate

You can self accept your own answer. – Dale K Jul 29 '22 at 21:54 — Dale K, Jul 29 '22 at 21:54

Getting totals in SSRS today(), Month, Quarterly and year to date

2 Answers2