Hey there, let's tackle your SSRS problems step by step—first we'll verify if your report creation workflow follows best practices, then we'll go through practical ways to speed up that slow report.

一、报表创建流程规范检查 #

Let's go through key points to see if your current setup aligns with SSRS best practices:

• Dataset & Query Design
  Using ad-hoc SQL instead of stored procedures isn't inherently "wrong," but watch out for these red flags: Do you have duplicate logic across your 5 datasets? For example, are multiple datasets pulling the same base data but not reusing it? Also, how are you handling multi-value parameters? If you're using WHERE Column IN (@MultiParam) directly, that might lead to inefficient execution plans or even SQL injection risks.
• Parameter Configuration
  For your multi-value parameter (like the Company one), are you populating its available values from a dedicated dataset? Manual input can cause inconsistencies. Also, do you have reasonable default values? Setting a default to a single value instead of "select all" can avoid overloading the query with too much data upfront. Are parameters set up with dependencies (e.g., filtering other parameter options after selecting a company)? This reduces unnecessary data loading.
• Report Layout & Calculations
  Are you doing heavy aggregations, sorting, or calculations directly in report controls (like tables/matrices) instead of in your SQL queries? Shifting these operations to the database (where it's optimized for such tasks) is always better. Also, do you have overly nested tables, matrices, or subreports? Subreports nested in detail rows can trigger hundreds of extra database calls, killing performance.
• Data Source Setup
  Are you using shared data sources, or does each dataset have its own? Shared data sources let you reuse connection pools, reducing connection overhead. Also, check if your connection strings have reasonable timeout settings to avoid unnecessary delays.

二、提升多值参数报表性能的有效方法 #

Here are actionable fixes to speed up your report:

1. Optimize Multi-Value Parameter Queries #

• Ditch the direct IN (@MultiParam) syntax. SSRS converts multi-value parameters into comma-separated strings, which SQL Server often handles with inefficient execution plans. Instead, use a string-splitting function to turn the parameter into a table variable:

  -- First create a reusable string-splitting function if you don't have one
  CREATE FUNCTION dbo.SplitMultiValueParam (@Param NVARCHAR(MAX), @Delimiter CHAR(1))
  RETURNS @ParamValues TABLE (Value NVARCHAR(100))
  AS
  BEGIN
      WHILE CHARINDEX(@Delimiter, @Param) > 0
      BEGIN
          INSERT INTO @ParamValues VALUES(SUBSTRING(@Param, 1, CHARINDEX(@Delimiter, @Param)-1))
          SET @Param = SUBSTRING(@Param, CHARINDEX(@Delimiter, @Param)+1, LEN(@Param))
      END
      INSERT INTO @ParamValues VALUES(@Param)
      RETURN
  END
  
  -- Use it in your query like this
  SELECT * 
  FROM YourReportTable
  WHERE CompanyCode IN (SELECT Value FROM dbo.SplitMultiValueParam(@CompanyParam, ','))
    AND TransactionDate BETWEEN @DateFrom AND @DateTo
  

• Make sure columns used in parameter filters (like CompanyCode, TransactionDate) have proper indexes. Consider covering indexes that include all columns your query returns—this lets SQL Server retrieve data directly from the index without hitting the main table.

2. Switch to Stored Procedures #

Stored procedures let SQL Server cache execution plans, which is a huge win for multi-value parameter reports (since they often generate unstable plans with ad-hoc SQL). Use table-valued parameters (supported in SQL Server 2008+) to handle multi-values cleanly:

-- First create a table-valued type
CREATE TYPE dbo.CompanyCodeList AS TABLE (CompanyCode NVARCHAR(50))

-- Create the stored procedure
CREATE PROCEDURE dbo.GetReportData
    @DateFrom DATE,
    @DateTo DATE,
    @Companies dbo.CompanyCodeList READONLY
AS
BEGIN
    SELECT * 
    FROM YourReportTable
    WHERE TransactionDate BETWEEN @DateFrom AND @DateTo
      AND CompanyCode IN (SELECT CompanyCode FROM @Companies)
END

Then in your SSRS dataset, call this stored procedure and map your multi-value parameter to the table-valued parameter.

3. Reuse & Cache Datasets #

• If multiple datasets share the same filter logic (e.g., all use @DateFrom, @DateTo, and @CompanyParam), create one base dataset with the core data, then use SSRS's LOOKUP or LOOKUPSET functions to pull related data into other parts of the report. This cuts down on redundant database calls.
• Enable dataset caching: Go to your dataset properties, check "Cache this dataset," and set an expiration time. This way, repeated requests with the same parameters will use cached data instead of hitting the database every time.

4. Streamline Report Layout #

• Move all aggregations (SUM, COUNT, GROUP BY) to your SQL queries—databases are far better at crunching numbers than SSRS report controls.
• Avoid nested subreports, especially in detail rows. If you must use a subreport, make sure it shares parameters with the main report and only runs once per group, not per row.
• Simplify conditional formatting: Too many dynamic style rules can slow down report rendering.

5. Tweak SSRS Server Settings #

• Check your SSRS server's memory allocation: Ensure there's enough RAM to cache reports and datasets. You can adjust this in the Reporting Services Configuration Manager under "Server Properties > Memory".
• Adjust report execution timeouts: In your report's properties, set a reasonable "Execution timeout" value (but focus on fixing the query first—this is just a band-aid for long-running reports).

内容的提问来源于stack exchange，提问作者whitz11