How to do a row count with a specific set of Data in SSRS

Question

I have an SSRS report that shows me total revenue grouped by fiscal year, fiscal quarter, and business type. I need to add a final column that counts the number rows that make up the total revenue number where each row represents a single business.

Basically I need to know how many individual businesses in a particular business type contributed to the total for that time frame. I will explain in detail below.

First here is the stored procedure that feeds the report data:

SELECT 
id
,assess_year
,CASE
    WHEN MONTH(date_received) IN (7,8,9) THEN 1
    WHEN MONTH(date_received) IN (10, 11, 12) THEN 2
    WHEN MONTH(date_received) IN (1, 2, 3) THEN 3
    WHEN MONTH(date_received) IN (4, 5, 6) THEN 3
    WHEN date_received is null THEN 0
    ELSE 0
END as Fiscal_Quarter
,s_gross_revenue
,CASE 
    WHEN LEFT(segment, 1) = 'A' THEN 'Accommodations' 
    WHEN segment BETWEEN 'B100' AND 'B699' THEN 'Restaurant' 
    WHEN segment BETWEEN 'B700' AND 'B999' THEN 'Retail' 
    WHEN LEFT(segment, 1) = 'C' THEN 'Attractions/Recreation' 
    WHEN LEFT(segment, 1) = 'D' THEN 'Travel Services' 
    WHEN LEFT(segment, 1) = 'E' THEN 'Rental Cars' 
    WHEN LEFT(segment, 1) = 'N' THEN 'Other' 
    ELSE 'UNDEFINED'
END as Segement
FROM mytable 
WHERE s_gross_revenue >0 AND assess_year IS NOT NULL 
    AND assess_year <> '' AND segment IS NOT NULL AND segment <>''
ORDER BY ASSESS_YEAR, FISCAL_QUARTER, SEGEMENT

I attached an image (Raw Data Image) to show an example of the raw data returned by the procedure.

I am doing all the grouping by year, quarter, and business type in the SSRS report.

I just need a formula or grouping to count all the rows for each business that make up the total revenue.

I also attached an image of the report (SSRS Report Image) with the empty column at the end where the row count should go.

So in the SSRS Report image the business type "Accommodations" for the fiscal year 2014/15 and fiscal Quarter 1 should have a row count of 4455 in the final column because there were 4455 individual businesses that were listed as accommodations that had revenue in Q1 of 2014/15. I just don't know how to do a row count based on specific criteria in SSRS.

(Also, I know the quarters look weird in the SQL, our business year starts in July)

Is the MONTH(date_received) IN (4,5,6) bit supposed to return 3 or 4? — Kidiskidvogingogin
It is supposed to return a 4. I saw it and corrected it but only after I posted the old code. Thanks though, good catch. — ekim110575

Kidiskidvogingogin Kidiskidvogingogin · Accepted Answer · 2016-04-28T21:00:41

If I understand your question correctly and if you're using SQL Server 2008+ then you can you the OVER(...) clause to do a COUNT(...) while keeping your detail data.

For the business count it would look like:

COUNT(*) OVER (PARTITION BY assess_year, Fiscal_quarter, Segement)

Here is the fully modified query, but the above part is really the only change:

SELECT 
    id
    ,assess_year
    ,Fiscal_Quarter
    ,s_gross_revenue
    ,Segement
    ,COUNT(*) OVER (PARTITION BY assess_year, Fiscal_quarter, Segement) BusinessCount
FROM (
        SELECT 
            id
            ,assess_year
            ,CASE
                WHEN MONTH(date_received) IN (7,8,9) THEN 1
                WHEN MONTH(date_received) IN (10, 11, 12) THEN 2
                WHEN MONTH(date_received) IN (1, 2, 3) THEN 3
                WHEN MONTH(date_received) IN (4, 5, 6) THEN 4
                WHEN date_received is null THEN 0
                ELSE 0
            END as Fiscal_Quarter
            ,s_gross_revenue
            ,CASE 
                WHEN LEFT(segment, 1) = 'A' THEN 'Accommodations' 
                WHEN segment BETWEEN 'B100' AND 'B699' THEN 'Restaurant' 
                WHEN segment BETWEEN 'B700' AND 'B999' THEN 'Retail' 
                WHEN LEFT(segment, 1) = 'C' THEN 'Attractions/Recreation' 
                WHEN LEFT(segment, 1) = 'D' THEN 'Travel Services' 
                WHEN LEFT(segment, 1) = 'E' THEN 'Rental Cars' 
                WHEN LEFT(segment, 1) = 'N' THEN 'Other' 
                ELSE 'UNDEFINED'
            END as Segement
        FROM mytable 
        WHERE s_gross_revenue > 0 
            AND ISNULL(assess_year,'') <> '' 
            AND ISNULL(segement,'') <> ''
    ) data
ORDER BY ASSESS_YEAR, FISCAL_QUARTER, SEGEMENT

I also made a slight syntactic change in your where clause, it's the same logic but a kind of shorthand way to do it. Rather than check if SEGEMENT and ASSESS_YEAR are NOT NULL AND NOT '' in separate statements you can combine them into an ISNULL(...,'') <> '' if SEGEMENT and ASSESS_YEAR are text type values, otherwise you will have to use COALESCE(...) to accomplish the same thing. ISNULL(...) can be replaced with COALESCE(...) if preferred.

This is also entirely possible by simply using count(<field name>) in the id column on your SSRS report. It should give you the same results.

How to do a row count with a specific set of Data in SSRS

1 Answers