Building Complex Reports in SSRS

Lately, I’ve been experimenting with the latest features in SQL Server Reporting Services (SSRS), and wanted to write up a few things I’ve learned about building more complex reports.

For instance, I recently tackled a project involving a multi-dimensional report. The goal was to show sales data across various dimensions like time, geography, and product categories. I used a combination of T-SQL and SSRS functionalities to accomplish this.

Here’s an outline of the SQL query and SSRS techniques I employed. I have replace table names.

The Query

WITH SalesCTE AS (
    SELECT 
        s.SalesPersonID,
        p.Name as ProductName,
        c.CategoryName,
        r.RegionName,
        SUM(s.SaleAmount) as TotalSales,
        ROW_NUMBER() OVER(PARTITION BY s.SalesPersonID ORDER BY SUM(s.SaleAmount) DESC) as SalesRank
    FROM 
        Sales s
    INNER JOIN 
        Products p ON s.ProductID = p.ProductID
    INNER JOIN 
        Categories c ON p.CategoryID = c.CategoryID
    INNER JOIN 
        Regions r ON s.RegionID = r.RegionID
    GROUP BY 
        s.SalesPersonID, p.Name, c.CategoryName, r.RegionName
)
SELECT 
    SalesPersonID,
    ProductName,
    CategoryName,
    RegionName,
    TotalSales,
    SalesRank
FROM 
    SalesCTE
WHERE 
    SalesRank <= 5

This query creates a Common Table Expression (CTE) to rank sales by salesperson and then filters to show the top 5 sales per salesperson. It’s a powerful way to analyse sales performance across different products and regions.

Presenting in SSRS

In SSRS, I used matrix reports and subreports to present this data. The matrix report provided an interactive way to explore the data across different dimensions, while the subreports offered detailed views of individual salesperson performance.

It takes some time to get the T-SQL and SSRS working together, but the end result is a report that’s genuinely useful for the people reading it.

I’ve covered more SSRS techniques in separate posts: column-level security and drill-down and cross-chart filtering with repeating headers. For the Power BI side, see my DAX Query View post.