Our reports, written in MS SQL, need to be uploaded to SSRS, allowing coworkers to generate them with different parameters and export SSRS reports to Excel files. We typically create stored procedures with parameters and use them in SSRS reports.

Currently, I have an issue: if the stored procedure returns more than one table, SSRS only recognizes the first one and ignores the others. However, I need to display all 5 tables when the customer exports the report to Excel. The first table should be on the first sheet, the second table on the second sheet, and so on. How can this be achieved in SSRS?”

The problem is that all these tables depend on each other, so I don’t want to create 5 different stored procedures for each table. Below, I will paste my SQL query. Don’t pay attention with tables and columns, I just wanted to show that we first need to get data from several tables, perform some calculations, and then use this result to generate the other 4 tables.

-- All Debit/Credit Transactions for '20240229' date
            
    
SELECT FT_ID, TRANSACTION_TYPE, DEBIT_ACCT_NO, CREDIT_ACCT_NO, f.DEBIT_CUSTOMER, f.CREDIT_CUSTOMER
    , ABS(DEBIT_AMOUNT) AMOUNT
    , DEBIT_THEIR_REF, CREDIT_THEIR_REF, RECORD_STATUS, DATE_TIME, INPUTTER, f.DEBIT_VALUE_DATE
INTO #All
FROM T24DATA.dbo.FUNDS_TRANSFER f
WHERE (f.DEBIT_ACCT_NO='GEL164589800' OR f.CREDIT_ACCT_NO='GEL164589800') AND
(f.DEBIT_VALUE_DATE=20240229 OR (f.DATE_TIME>=2402290000 AND f.DATE_TIME<=2402292359))

            

-- TransferOut Transactions

SELECT * , LEFT(Purpose,11) PID
INTO #DBTrOut FROM (
SELECT *            
FROM TransfersHub.dbo.TransfersHis WHERE SenderAccCode IN ('GE48CD1450000045010039') AND ValueDate='2024-02-29'
UNION ALL
SELECT * FROM TransfersHub.dbo.Transfers WHERE SenderAccCode IN ('GE48CD1450000045010039') AND ValueDate='2024-02-29'
             )m



-- TransferIn Transactions
             
SELECT 
*
INTO #CDTrIn
FROM TransfersHub.dbo.TransfersHis WHERE ReceiverAccCode IN ('GE48CD1450000045010039') AND ValueDate='2024-02-29'

UNION ALL

              SELECT
              * 
              FROM TransfersHub.dbo.Transfers WHERE ReceiverAccCode IN ('GE48CD1450000045010039') AND ValueDate='2024-02-29'




-- Debits 

            SELECT f.* , i.Pid, o.PID TrOutPID
            INTO #DEBIT
            FROM #All f
            LEFT JOIN ListOfBalance.dbo.InsLoan i ON i.LoanId=f.DEBIT_THEIR_REF
            LEFT JOIN #DBTrOut o ON f.DEBIT_THEIR_REF=o.Reference
            WHERE f.DEBIT_ACCT_NO='GEL164589800'


    

-- Credits
            
            SELECT f.* , i.Pid, o.PID TrOutPid
            INTO #CREDIT
            FROM #All f
            LEFT JOIN ListOfBalance.dbo.InsLoan i ON i.LoanId=f.DEBIT_THEIR_REF
            LEFT JOIN #DBTrOut o ON i.Pid=o.PID
            WHERE f.CREDIT_ACCT_NO='GEL164589800'






-- All Customer IDs for this date


    
            SELECT DISTINCT CREDIT_CUSTOMER AS CUSTOMER 
            INTO #Customers
            FROM #All
            WHERE CREDIT_CUSTOMER <> 0
            UNION   
            SELECT DISTINCT DEBIT_CUSTOMER 
            FROM #All
            WHERE DEBIT_CUSTOMER <> 0






-- Customers with their Credit Amounts Summed and Debit Amounts Summed
            

            SELECT cst.CUSTOMER
            , SUM(IIF(db.DEBIT_CUSTOMER = cst.CUSTOMER, db.AMOUNT, 0)) CreditAMOUNT
            , SUM(IIF(db.CREDIT_CUSTOMER = cst.CUSTOMER, db.AMOUNT, 0)) DebitAMOUNT
            INTO #AMOUNTsSummedCUST
            FROM #Customers cst
            LEFT JOIN #All db ON cst.CUSTOMER=IIF(db.CREDIT_CUSTOMER=0, db.DEBIT_CUSTOMER, db.CREDIT_CUSTOMER)
            GROUP BY  cst.CUSTOMER


        



`your text`

-- Find differences


SELECT m.CUSTOMER, m.LEGAL_ID, m.CU_NAME1, m.CU_NAME2, SUM(m.DebitAMOUNT) AS DebitAMOUNT,  SUM(m.CreditAMOUNT) AS CreditAMOUNT
INTO #Different
FROM (
            SELECT ams.CUSTOMER, cst.LEGAL_ID, cst.CU_NAME1, cst.CU_NAME2 
            , ams.DebitAMOUNT
            , ams.CreditAMOUNT
            FROM #AMOUNTsSummedCUST ams
            LEFT JOIN T24DATA.dbo.CUSTOMERS cst ON ams.CUSTOMER=cst.CUSTOMER_CODE
            ) m
            GROUP BY m.CUSTOMER, m.LEGAL_ID, m.CU_NAME1, m.CU_NAME2
            HAVING SUM(m.DebitAMOUNT) <> SUM(m.CreditAMOUNT)

            


-- Sheet1 in Excel Should be the customers which have different Credit and Debit Amounts


      SELECT DISTINCT ams.* , acc.ACCOUNT_NUMBER 
      , tot.ReceiverAccCode
      , CreditAMOUNT - DebitAMOUNT AS Difference
      , ABS(CreditAMOUNT - DebitAMOUNT) DifferenceABS
      FROM #Different ams
      LEFT JOIN T24DATA.dbo.ACCOUNTS AS ACC WITH (NOLOCK) ON ACC.CUSTOMER = ams.CUSTOMER AND ACC.CATEGORY=3100
      LEFT JOIN #DBTrOut tot ON ams.LEGAL_ID=tot.ReceiverTaxCode
      ORDER BY DifferenceABS
            
            

-- Sheet2 (Debits) Should contain the debit transactions which does not match with credit transactions


      SELECT * FROM  (
      SELECT db.*  
      FROM #DEBIT db
      INNER JOIN #Different df ON df.CUSTOMER=db.CREDIT_CUSTOMER
     ) db
     LEFT JOIN (  
      SELECT cd.* 
      FROM #CREDIT cd
      LEFT JOIN #Different df ON df.CUSTOMER=cd.DEBIT_CUSTOMER
      WHERE (df.CUSTOMER IS NOT NULL OR (cd.DEBIT_CUSTOMER=0 AND cd.CREDIT_CUSTOMER=0))
      ) cd ON db.AMOUNT=cd.AMOUNT AND ISNULL(db.TrOutPID, db.Pid) = cd.Pid

      WHERE cd.AMOUNT IS NULL





-- Sheet3 (Credits) Should contain the credit transactions which does not match with debit transactions


      SELECT * FROM  (

      SELECT cd.* 
      FROM #CREDIT cd
      LEFT JOIN #Different df ON df.CUSTOMER=cd.DEBIT_CUSTOMER
      WHERE (df.CUSTOMER IS NOT NULL OR (cd.DEBIT_CUSTOMER=0 AND cd.CREDIT_CUSTOMER=0))
      ) cd
      LEFT JOIN (
                    SELECT db.*   
                    FROM #DEBIT db
                    INNER JOIN #Different df ON df.CUSTOMER=db.CREDIT_CUSTOMER
                )    db
      ON db.AMOUNT=cd.AMOUNT AND ISNULL(db.TrOutPID, db.Pid) = cd.Pid

      WHERE db.AMOUNT IS NULL





-- SHeet4 (TransferOut) should contain this information


      SELECT * FROM #DBTrOut


-- Sheet5 (TransferIn) should contain this information

      SELECT * FROM #CDTrIn