I have a report that runs as expected in the SSMS environment, there is one column in specific, that does a count of an ID column. This column is accurate when is SSMS, but when I load the script into Report Builder everything else aside from that column outputs data.

I further have to upload the report builder file into our record-keeping system and I see the same blank row come through. When I put an ISNULL(Count(UserId), '0') on the column, it outputs a 0 when the report is run in SSRS, although it still outputs correctly in SSMS, this led me to believe that at some point that field passes a null value, but I couldn't find an instance of this while investigating in SSMS.

I am unsure what the disconnect is with the column when added into the report. This is however, a complex report that operates off of several temp tables that are fed into a Create Table statement and further pivoted against a date row and varies in column length depending on the # of days in a month.

I have included a small portion of the SQL script prior to where I create the column and further join it into another temp table.

IF OBJECT_ID('tempdb..#ShiftPeriodUps') IS NOT NULL 
    DROP TABLE #ShiftPeriodUps

SELECT
    ID, 
    ExternalID,
    Name,
    StartDate,
    EndDate,
    CreationDate,
    BiddingAvailableFromDays,
    BiddingClosedByDays, 
    BiddingPublicationDays,
    UpdaterID
INTO 
    #ShiftPeriodUps
FROM 
    dbo.ShiftPeriod_tbl
WHERE 
    IsDeleted IS NULL
ORDER BY 
    StartDate ASC 

-------------------- ShiftPeriod ----------------------
IF OBJECT_ID('tempdb..#ShiftPeriod') IS NOT NULL 
    DROP TABLE #ShiftPeriod 

SELECT
    [Name] AS ShiftPeriod,
    StartDate, EndDate 
INTO
    #ShiftPeriod
FROM
    #ShiftPeriodUps 
WHERE
    ID = @ShiftPeriod  

DECLARE @StartDate DATE, 
        @EndDate DATE,
        @MPName VARCHAR (2000)

SELECT 
    @StartDate = (SELECT StartDate FROM #ShiftPeriod), 
    @EndDate   = (SELECT EndDate FROM #ShiftPeriod);

IF OBJECT_ID('tempdb..#DateRow') IS NOT NULL 
    DROP TABLE #DateRow

SELECT  
    @StartDate as StartDate, 
    @EndDate as EndDate
INTO 
    #DateRow


-----------  Number of Training Positions by TP ------------
IF OBJECT_ID('tempdb..#PositionsCount') IS NOT NULL 
    DROP TABLE #PositionsCount

SELECT 
     TrainingProgramID
    ,TrainingProgramMetaDataFieldExternalID ExternalID
     ,MAX(CASE WHEN TrainingProgramMetaDataFieldExternalID = 'CA_Count' THEN  
        TrainingProgramMetaDataFieldValue ELSE 0 END) AS CA 
    ,MAX(CASE WHEN TrainingProgramMetaDataFieldExternalID = 'FO_Count' THEN  
        TrainingProgramMetaDataFieldValue ELSE 0 END) AS FO
INTO 
    #PositionsCount
FROM 
    rawViews.Bks_DWH_TrainingProgramsExtraDetails
WHERE
    TrainingProgramMetaDataFieldExternalID IN ('CA_Count') 
GROUP BY
    TrainingProgramID,TrainingProgramMetaDataFieldExternalID


----------------------- CA and FO Count Assigned to TP ------------------------------

IF OBJECT_ID('tempdb..#COUNTSTk2') IS NOT NULL 
    DROP TABLE #COUNTSTk2

SELECT DISTINCT
    TPA.TrainingProgramID,
    TPBD.TrainingProgram_MasterPlanName,
    COUNT(UserID) AS 'CA Assigned Count' ----**Field that is not outputting in SSRS**
INTO 
    #COUNTSTk2
FROM 
    rawviews.Bks_DWH_UserTrainingProgramAssociations AS TPA
INNER JOIN 
    rawviews.Bks_DWH_TrainingProgramsBasicData AS TPBD ON TPBD.TrainingProgramID = TPA.TrainingProgramID
GROUP BY
    TPA.TrainingProgramID, TPBD.TrainingProgram_MasterPlanName, TrainingPositionName
ORDER BY 
    TPA.TrainingProgramID DESC

--------------------  Training Program --------------------
IF OBJECT_ID('tempdb..#TrainingProgram') IS NOT NULL 
    DROP TABLE #TrainingProgram

SELECT
    'IND' AS A
    ,'EM7' AS B
    ,CASE PC.ExternalID
         WHEN 'CA_Count' THEN 'CA0' + CAST(PC.CA AS VARCHAR)
         WHEN 'FO_Count' THEN 'FO0' + CAST(PC.FO AS VARCHAR)
         END AS C
    ,TP.TrainingProgramID AS D
    ,ISNULL(CA.[CA Assigned Count], '0') AS 'CA Assigned'
    ,'L' AS E
    ,FORMAT(DATEADD(DAY,7,@StartDate),'yyMM') AS F 
    ,0 AS G ,0 AS H ,0 AS I
    ,'' AS J, '' AS K, '' AS L, '' AS M, '' AS N, '' AS O, '' AS P
    ,FORMAT(@StartDate,'yyyyMMdd') AS Q
INTO
    #TrainingProgram
FROM
    rawViews.Bks_DWH_TrainingProgramsBasicData TP
LEFT JOIN 
    #COUNTSTk2 AS CA ON CA.TrainingProgramID = TP.TrainingProgramID ---** I bring 
    the count into the table here. I chose a Left Join because I only want to select the 
    trainingprogram if it exists within the filtering conditions set by the temp table 
    #TrainingProgram**
INNER JOIN 
    #PositionsCount PC ON PC.TrainingProgramID = TP.TrainingProgramID
WHERE
    TP.TrainingProgramNewMetaDataTypeName IN ('CQ Flight')
    AND CAST(TP.TrainingProgramStartDate AS date) BETWEEN @StartDate AND @EndDate
    AND PC.CA >= 1 --- new addition by TW in 11/24
    AND CA.TrainingProgram_MasterPlanName in (@MPName)  -- added this line to create parameter in report builder.

Please let me know if you have encountered this and if there is a workaround? I wasn't sure if SSRS is unable to handle an aggregated column? I can share the entire script if it is needed for troubleshooting.

Any help is appreciated! Thank you!

I have tried to change the data type of this column from INT to VARCHAR in case that was the issue, but it didn't seem to make any impact. Separately, I have tried to bring the UserID that is counted into the Main table in the SQL script and without selecting the field in SSRS (as there are multiple Users per Training Program), but when doing the count aggregation in SSRS, it also only displays 0s, which is inaccurate.

I should note that I use the 2016 version of SSRS desktop application to create and run the report in question. Separately, I use SQL in SSMS to write the script.