SSRS timeout with stored procedure

Question

I have a stored procedure that creates a data set of 19 rows in 11 seconds in SSMS.

Once I have added the procedure into SSRS, I am getting a timeout even when the time out is set to 10 minutes.

I have some quite complex reports and this is the first time I have come across this type of issue

The procedure is well written and does use 3 temp tables but only has one table output

USE [ReportServer]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_SalesSnapshot]
    @year AS int,
    @period AS varchar(2)
AS
BEGIN
    DECLARE @date AS DATE 

    SET @date = CONVERT(DATE, CAST(@Year - 2 AS VARCHAR(4)) + '-01' + '-01') --concat('01-01-', @year - 1)

    SELECT DISTINCT 
        JOBNO, MATLIST 
    INTO
        #jqh 
    FROM
        FDCLIENT.dbo.JOBQUOTEHEADER (NOLOCK) 

    SELECT 
        inhouse.dbo.removeNumbers(slc.region) AS region,
        SUM(opsd.val) val,
        sli.slyear,
        sli.period,
        CASE
            WHEN opsd.warehouse IN ('12', '13') 
                THEN 'Panel'
            WHEN (UPPER(#jqh.MATLIST) LIKE '%FIRE%')
                 OR (UPPER(#jqh.matlist) LIKE '%FD%')
                 OR (opsd.warehouse = '21' AND stk.alpha LIKE 'CF%') 
                THEN 'Fire'
            WHEN opsd.warehouse = '21' 
                THEN 'Composite'
            ELSE 'Other'
        END AS panelcompfire
    INTO 
        #temp
    FROM 
        live.scheme.opsadetm (nolock) opsd
    INNER JOIN
        live.scheme.slcustm slc (nolock) ON opsd.customer = slc.customer
    INNER JOIN 
        live.scheme.slitemm (nolock) sli ON opsd.dated >= @date
                                         AND opsd.invoice = sli.item_no
    INNER JOIN
        live.scheme.stockm stk (nolock) ON stk.warehouse = opsd.warehouse
                                        AND stk.product = opsd.product
    LEFT OUTER JOIN 
        #jqh (nolock) ON sli.refernce = #jqh.JOBNO COLLATE database_default
    WHERE 
        (sli.slyear = @year)
        AND opsd.val <> 0
    GROUP BY
        inhouse.dbo.removeNumbers(slc.region),
        sli.slyear, sli.period,
        CASE
            WHEN opsd.warehouse IN ('12', '13') 
                THEN 'Panel'
            WHEN (UPPER(#jqh.MATLIST) LIKE '%FIRE%')
                  OR (UPPER(#jqh.matlist) LIKE '%FD%')
                  OR (opsd.warehouse = '21' AND stk.alpha LIKE 'CF%') 
                THEN 'Fire'
            WHEN opsd.warehouse = '21' 
                THEN 'Composite'
            ELSE 'Other'
        END 
SELECT 
    region,
    panelcompfire AS product,
    SUM(CASE WHEN period = '01' THEN val ELSE 0 END) AS [01],
    SUM(CASE WHEN period = '02' THEN val ELSE 0 END) AS [02],
    SUM(CASE WHEN period = '03' THEN val ELSE 0 END) AS [03],
    SUM(CASE WHEN period = '04' THEN val ELSE 0 END) AS [04],
    SUM(CASE WHEN period = '05' THEN val ELSE 0 END) AS [05],
    SUM(CASE WHEN period = '06' THEN val ELSE 0 END) AS [06],
    SUM(CASE WHEN period = '07' THEN val ELSE 0 END) AS [07],
    SUM(CASE WHEN period = '08' THEN val ELSE 0 END) AS [08],
    SUM(CASE WHEN period = '09' THEN val ELSE 0 END) AS [09],
    SUM(CASE WHEN period = '10' THEN val ELSE 0 END) AS [10],
    SUM(CASE WHEN period = '11' THEN val ELSE 0 END) AS [11],
    SUM(CASE WHEN period = '12' THEN val ELSE 0 END) AS [12]
into #pivot
FROM #Temp (nolock)
GROUP BY 
        region, panelcompfire
ORDER BY 
            region, panelcompfire;


select 
    #pivot.product,
    #pivot.region,
    ltrim(rtrim(a.Representative)) as Representative,
    case @period
        when '01' then [01]
        when '02' then [02]
        when '03' then [03]
        when '04' then [04]
        when '05' then [05]
        when '06' then [06]
        when '07' then [07]
        when '08' then [08]
        when '09' then [09]
        when '10' then [10]
        when '11' then [11]
        when '12' then [12]
    end as monthSales,
    case @period
        when '01' then bud.APRIL
        when '02' then bud.MAY
        when '03' then bud.JUNE
        when '04' then bud.JULY
        when '05' then bud.AUGUST
        when '06' then bud.SEPTEMBER
        when '07' then bud.OCTOBER
        when '08' then bud.NOVEMBER
        when '09' then bud.DECEMBER
        when '10' then bud.JANUARY
        when '11' then bud.FEBRUARY
        when '12' then bud.MARCH
    end as monthbudget,
    case 
        when @period in ('01','02','03') then [01]  + [02]  + [03]
        when @period in ('04','05','06') then [04]  + [05]  + [06]
        when @period in ('07','08','09') then [07]  + [08]  + [09]
        when @period in ('10','11','12') then [10]  + [11]  + [12]
    end as qtrSales,
    case 
        when @period in ('01','02','03') then bud.APRIL     + bud.MAY       + bud.JUNE
        when @period in ('04','05','06') then bud.JULY      + bud.AUGUST    + bud.SEPTEMBER
        when @period in ('07','08','09') then bud.OCTOBER   + bud.NOVEMBER  + bud.DECEMBER
        when @period in ('10','11','12') then bud.JANUARY   + bud.FEBRUARY  + bud.MARCH
    end as qtrbudget,
    [01]    + [02]  + [03]  +
    [04]    + [05]  + [06]  +
    [07]    + [08]  + [09]  +
    [10]    + [11]  + [12]  
    as YTDSales,
    bud.APRIL       + bud.MAY       + bud.JUNE      +
    bud.JULY        + bud.AUGUST    + bud.SEPTEMBER +
    bud.OCTOBER     + bud.NOVEMBER  + bud.DECEMBER  +
    bud.JANUARY     + bud.FEBRUARY  + bud.MARCH     as YTDBudget 
from #pivot (nolock)
    inner join [ReportServer].[dbo].[budget_fiscal] bud (nolock)
        on  #pivot.region = bud.REGION collate database_default
           and #pivot.product = bud.PRODUCT collate database_default
           and bud.[year] = @year
    inner join [ReportServer].[dbo].[cpio_area_fy24] a (nolock)
        on #pivot.region = a.Area collate database_default
order by #pivot.region, #pivot.product



-- Drop the temporary table
drop table #jqh
DROP TABLE #Temp;
DROP TABLE #pivot;


END