Dynamic Pivot and MAX data is not coming properly

I have to show data in pivot format where Broker will be coming horizontally. SQL query run fine but often MAX(ItemValue_NoFormat) getting NULL. Not able to diagnosis the area for which desired value is not coming.

Here i am giving my full code what i have tried.

DECLARE @SQL as VARCHAR(MAX)  
DECLARE @Columns as VARCHAR(MAX)  
DECLARE @Ticker VARCHAR(20),   
@TickerID VARCHAR(20),  
@ClientCode VARCHAR(20)  
  
DECLARE @CSM_ID INT  
SET @TickerID='ADS'  
SET @ClientCode='ADS'  
  
IF OBJECT_ID(N'tempdb..#Brokers') IS NOT NULL  
BEGIN  
    DROP TABLE #Brokers  
END  
IF OBJECT_ID(N'tempdb..#tmpData1') IS NOT NULL  
BEGIN  
    DROP TABLE #tmpData1  
END  
  
SELECT DISTINCT @Ticker=Ticker FROM tblTickerMasterId WHERE MasterId=@TickerID  
SELECT TOP 1 @CSM_ID=CSM_ID FROM tblCSM_Tuner_Client WHERE TickerID=@TickerID  
  
SELECT * Into #Brokers FROM  
(  
    Select A.BrokerCode, B.BrokerName                                                    
    From tblClientBroker_Earnings A                                                    
    Join tblBroker B ON SUBSTRING(A.BrokerCode,1,len(A.BrokerCode)-charindex('-',A.BrokerCode))=B.Brokercode                                                         
    Where A.ClientCode=@ClientCode And A.Ticker=@Ticker                                            
    /*AND A.BrokerCode IN (SELECT [DATA] FROM SplitStringToTable(@SelectedBrokers,',') WHERE TRIM([DATA])<>'')*/  
) x  
  
SELECT @Columns = COALESCE(@Columns + ', ','') + QUOTENAME(BrokerCode)  
FROM  
(  
    SELECT DISTINCT BrokerCode  
    FROM #Brokers where TRIM(BrokerCode) <> ''  
) AS B  
ORDER BY B.BrokerCode  
  
;WITH DirectReports as   
(  
    SELECT      CSM_ID,   
                ID,   
                ParentID,   
                DisplayInCSM,   
                Type,  
                FontName,  
                FontStyle,  
                FontSize,  
                UnderLine,  
                BGColor,  
                LineItemID,  
                Presentation,  
                BrokerOrientation,  
                AnnualFormat,  
                CalculationMethod,  
                Indent,  
                FGColor,  
                Box,  
                HeadingSubHeading,  
                ColOrder,  
                @TickerID AS TickerID,  
                hierarchy = FORMAT(ID,'0000'),   
                level = 0  
  
    FROM        tblCSM_ModelDetails   
    WHERE       ISNULL(ParentID, 0) = 0  
                AND Type<>'BM'  
                AND CSM_ID=@CSM_ID  
  
    UNION ALL  
    SELECT      e.CSM_ID,   
                e.ID,   
                e.ParentID,   
                e.DisplayInCSM,   
                e.Type,  
                e.FontName,  
                e.FontStyle,  
                e.FontSize,  
                e.UnderLine,  
                e.BGColor,  
                e.LineItemID,  
                e.Presentation,  
                e.BrokerOrientation,  
                e.AnnualFormat,  
                e.CalculationMethod,  
                e.Indent,  
                e.FGColor,  
                e.Box,  
                e.HeadingSubHeading,  
                e.ColOrder,  
                @TickerID AS TickerID,  
                hierarchy = d.hierarchy + '.' + FORMAT(e.id,'0000'),   
                level = level + 1  
  
    FROM        tblCSM_ModelDetails e  
    JOIN        DirectReports d on e.ParentID = d.ID  
    WHERE       e.Type<>'BM'  
                AND e.CSM_ID=@CSM_ID  
)  
  
,Cte1 as   
(  
    SELECT AA.EarningID,AA.Section,AA.LineItem,AA.Ticker, r.DisplayInCSM, r.Type,r.hierarchy, AA.Broker,AA.BrokerName, AA.ItemValue_NoFormat, AA.Period,r.ColOrder  
    FROM DirectReports r   
    LEFT OUTER JOIN  
    (                                                    
        Select b.*,L.ID AS LineItemID,L.TickerID,c.BrokerName                                                       
        From tblOutputDetl_CSMTuner b                                                    
        INNER JOIN TblLineItemTemplate L   
        ON b.LineItem= L.LineItem   
        INNER JOIN #Brokers c  
        ON SUBSTRING(b.Broker,1,len(b.Broker)-charindex('-',b.Broker))=c.BrokerCode  
        WHERE b.Ticker=@Ticker AND L.TickerID=@TickerID AND TRIM(b.Broker)<>''   
    ) AA  
    ON AA.LineItemID=r.LineItemID  
  
)  
  
SELECT * INTO #tmpData1 FROM Cte1  
  
SET @SQL='SELECT *  
FROM  
(  
    SELECT EarningID,Section,LineItem, DisplayInCSM, Type, Broker,BrokerName, ItemValue_NoFormat, Period,hierarchy  
    from #tmpData1   
) t  
PIVOT  
(  
    MAX(ItemValue_NoFormat)  
    FOR Broker IN ('+@Columns+')  
) AS P  
ORDER BY hierarchy,EarningID  
'  
  
EXEC(@SQL)    
  
  
IF OBJECT_ID(N'tempdb..#Brokers') IS NOT NULL  
BEGIN  
    DROP TABLE #Brokers  
END  
IF OBJECT_ID(N'tempdb..#tmpData1') IS NOT NULL  
BEGIN  
    DROP TABLE #tmpData1  
END  

Specially this area not showing Broker value properly.

SET @SQL='SELECT *  
FROM  
(  
    SELECT EarningID,Section,LineItem, DisplayInCSM, Type, Broker,BrokerName, ItemValue_NoFormat, Period,hierarchy  
    from #tmpData1   
) t  
PIVOT  
(  
    MAX(ItemValue_NoFormat)  
    FOR Broker IN ('+@Columns+')  
) AS P  
ORDER BY hierarchy,EarningID  
'  
EXEC(@SQL)    

please tell me what kind of mistake is there in my code where Broker Pivot data not showing value properly. some broker showing value but some broker not showing value in PIVOT.

When i query my temporary to see that is there a value for specific condition and that showing value is there in temporary table from where PIVOT is created. here is that query

select * from #tmpData1   
where ticker='ADS'   
and section='Segment Details'   
and lineitem='LoyaltyOne - Revenue'   
and period='2Q 2017A'  
and broker='CL'  
ORDER BY hierarchy,EarningID  

PIVOT is created from this above temporary table #tmpData1. when query temp table with a condition then i am getting right value. pivot is created from the same temp table #tmpData1. when querying same temp table with clause like section->Segment Details, lineitem->LoyaltyOne - Revenue, broker-> CL and Period-> 2Q 2017A
having brokers in pivot format then often NULL is showing for ItemValue_NoFormat

Please suggest me which area i need to fix or change to solve this issue. Thanks