SSRS DataSet different fields dependant on parameter

Question

I am supplying a dataset to my SSRS report using a stored proc. Within my stored proc is a parameter called @ReportType. I use this as I have two reports which use the same subset of data however they return different columns based on the @ReportType and in one the report types I aggregate the data.

So for example the end of my stored proc is as follows:

---Income
IF @Rpt_Type = 'Income'
    BEGIN

        SELECT  
                EXPENSE_TYPE    
            ,   ACCOUNT_NAME            
            ,   CURRENCY                
            ,   SETTLEMENT_DATE         
            ,   ISSUE           
            ,   ISSUE_DESCRIPTION       
            ,   SETTLEMENT_AMOUNT       
            ,   TRANSACTION_STATUS      
            ,   TRANSACTION_CATEGORY    
            ,   TRANSACTION_SUBCATEGORY
            ,   TRANSACTION_DESCRIPTION
        FROM #RESULT_SET 
        WHERE TRANSACTION_CATEGORY IN ('IN','IF') OR (TRANSACTION_CATEGORY IN ('CD','CW') AND TRANSACTION_SUBCATEGORY IN ('INT','SLI','RDS','INS','INC'))
        ORDER BY expense_type,settlement_date,issue_description
    END

---Expenses
IF @Rpt_Type = 'Expenses'
    BEGIN
        BEGIN
        SELECT 
                ACCOUNT_NAME            AS [Portfolio_Number]
            ,   TRANSACTION_SUBCATEGORY AS [Cash_flow_type]
            ,   TRANSACTION_DESCRIPTION AS [Cash_Flow_Description]
            ,   COUNT(ACCOUNT_NAME)     AS [Number Of Transactions]
            ,   @ReportEndDate          AS [EndDate]
        FROM #RESULT_SET 
        WHERE TRANSACTION_SUBCATEGORY IN ('CCD','CCW','FSB','FSS','FFB','FFS','FSO','FSC','FFO','FFC')
          AND INQ_BASIS_NUM = '1'
        GROUP BY ACCOUNT_NAME,TRANSACTION_SUBCATEGORY,TRANSACTION_DESCRIPTION,TRANSACTION_CATEGORY
        ORDER BY ACCOUNT_NAME,TRANSACTION_SUBCATEGORY,TRANSACTION_DESCRIPTION,TRANSACTION_CATEGORY
    END

My question is how can I get SSRS to return the different columns for each report type? Is there a way in how I create the dataset that I can give the parameter before the columns are returned into SSRS for me to use? Will I have to create two datasets in SSRS to cater for the different columns required?

Ron Smith Ron Smith · Accepted Answer · 2014-02-24T18:23:17

This will work so long as you return the same column names for each @Rpt_Type. For @Rpt_Type 'Income', return NULL values for the @Rpt_Type 'Expenses' data, and vice versa. Otherwise, the report will expect columns that it isn't getting from the dataset and the dataset will fail:

---Income
IF @Rpt_Type = 'Income'
    BEGIN

        SELECT  
                EXPENSE_TYPE    
            ,   ACCOUNT_NAME            
            ,   CURRENCY                
            ,   SETTLEMENT_DATE         
            ,   ISSUE           
            ,   ISSUE_DESCRIPTION       
            ,   SETTLEMENT_AMOUNT       
            ,   TRANSACTION_STATUS      
            ,   TRANSACTION_CATEGORY    
            ,   TRANSACTION_SUBCATEGORY
            ,   TRANSACTION_DESCRIPTION
            ,   NULL Portfolio_Number
            ,   NULL Cash_flow_type
            ,   NULL Cash_Flow_Description
            ,   NULL [Number Of Transactions]
            ,   NULL EndDate
        FROM #RESULT_SET 
        WHERE TRANSACTION_CATEGORY IN ('IN','IF') OR (TRANSACTION_CATEGORY IN ('CD','CW') AND TRANSACTION_SUBCATEGORY IN ('INT','SLI','RDS','INS','INC'))
        ORDER BY expense_type,settlement_date,issue_description
    END

---Expenses
IF @Rpt_Type = 'Expenses'
    BEGIN
        BEGIN
        SELECT  NULL EXPENSE_TYPE    
            ,   NULL ACCOUNT_NAME            
            ,   NULL CURRENCY                
            ,   NULL SETTLEMENT_DATE         
            ,   NULL ISSUE           
            ,   NULL ISSUE_DESCRIPTION       
            ,   NULL SETTLEMENT_AMOUNT       
            ,   NULL TRANSACTION_STATUS      
            ,   NULL TRANSACTION_CATEGORY    
            ,   NULL TRANSACTION_SUBCATEGORY
            ,   NULL TRANSACTION_DESCRIPTION
            ,   ACCOUNT_NAME            AS [Portfolio_Number]
            ,   TRANSACTION_SUBCATEGORY AS [Cash_flow_type]
            ,   TRANSACTION_DESCRIPTION AS [Cash_Flow_Description]
            ,   COUNT(ACCOUNT_NAME)     AS [Number Of Transactions]
            ,   @ReportEndDate          AS [EndDate]
        FROM #RESULT_SET 
        WHERE TRANSACTION_SUBCATEGORY IN ('CCD','CCW','FSB','FSS','FFB','FFS','FSO','FSC','FFO','FFC')
          AND INQ_BASIS_NUM = '1'
        GROUP BY ACCOUNT_NAME,TRANSACTION_SUBCATEGORY,TRANSACTION_DESCRIPTION,TRANSACTION_CATEGORY
        ORDER BY ACCOUNT_NAME,TRANSACTION_SUBCATEGORY,TRANSACTION_DESCRIPTION,TRANSACTION_CATEGORY
    END

SSRS DataSet different fields dependant on parameter

1 Answers