How can I insert Blank Rows to my tablix in SSRS

  • I'd like to insert rows(blanks rows in where there is no data),in order to keep my  standard tablix shape.How can I achieve in SSRS?https://www.sqlservercentral.com/Forums/Uploads/Images/675c8d28-1897-4db9-be4d-6a50.jpg

  • What do you mean by insert blank rows. Do you mean that you want rows for Months 1-8 and 10 displayed on your top account?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Yes.

  • You need to use a date table. Have a look at Bones of SQL - The Calendar Table[/url]

    So, for example, you can then do the following:
    CREATE TABLE #Account (AccountID INT IDENTITY(1,1),
                           AccountNumber INT);
    INSERT INTO #Account (AccountNumber)
    VALUES (12345);

    CREATE TABLE #Transaction (TransactionID INT IDENTITY (1,1),
                               AccountID INT,
                               TransactionDate DATE,
                               TransactionValue DECIMAL(12,2));
    GO

    INSERT INTO #Transaction (AccountID, TransactionDate, transactionValue)
    VALUES (1, '01-Sep-2016', 100),
           (1, '09-Sep-2016', -50),
           (1, '11-Sep-2016', -200),
           (1, '15-Sep-2016', 90.60),
           (1, '11-Sep-2016', 9.20),
           (1, '11-Sep-2016', 41.20),
           (1, '11-Sep-2016', 19);
    GO

    --Missing Other Months
    SELECT A.AccountNumber AS Acc,
           DATEPART(Year, TransactionDate) AS Yr,
           DATEPART(Month, TransactionDate) AS Mth,
           SUM(TransactionValue) AS Total
    FROM #Account A
         JOIN #Transaction T ON A.AccountID = T.AccountID
    GROUP BY A.AccountNumber,
             DATEPART(Year, TransactionDate),
             DATEPART(Month, TransactionDate);
    GO

    /*I already have a Date table, which I'm going to use, however, you should use the one you create*/
    SELECT A.AccountNumber AS Acc,
           DD.[Calendar Year] AS Year,
           DD.[Calendar Month] AS Mth,
           SUM(ISNULL(TransactionValue,0)) AS Total
    FROM #Account A
         CROSS APPLY DimDate DD
         LEFT JOIN #Transaction T ON DD.[Date] = T.TransactionDate
    WHERE DD.[Calendar Year] = 2016
    GROUP BY A.AccountNumber,
             DD.[Calendar Year],
             DD.[Calendar Month];
    GO

    DROP TABLE #Transaction;
    DROP TABLE #Account;
    GO

    This produces the following datasets respectively:

    (1 row(s) affected)
    Acc         Yr          Mth         Total
    ----------- ----------- ----------- ---------------------------------------
    12345       2016        9           10.00

    ---------------------------------------------------------------------------

    Acc         Year        Mth         Total
    ----------- ----------- ----------- ---------------------------------------
    12345       2016        1           0.00
    12345       2016        2           0.00
    12345       2016        3           0.00
    12345       2016        4           0.00
    12345       2016        5           0.00
    12345       2016        6           0.00
    12345       2016        7           0.00
    12345       2016        8           0.00
    12345       2016        9           10.00
    12345       2016        10          0.00
    12345       2016        11          0.00
    12345       2016        12          0.00

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Thom A - Friday, January 13, 2017 9:26 AM

    You need to use a date table. Have a look at Bones of SQL - The Calendar Table[/url]

    So, for example, you can then do the following:
    CREATE TABLE #Account (AccountID INT IDENTITY(1,1),
                           AccountNumber INT);
    INSERT INTO #Account (AccountNumber)
    VALUES (12345);

    CREATE TABLE #Transaction (TransactionID INT IDENTITY (1,1),
                               AccountID INT,
                               TransactionDate DATE,
                               TransactionValue DECIMAL(12,2));
    GO

    INSERT INTO #Transaction (AccountID, TransactionDate, transactionValue)
    VALUES (1, '01-Sep-2016', 100),
           (1, '09-Sep-2016', -50),
           (1, '11-Sep-2016', -200),
           (1, '15-Sep-2016', 90.60),
           (1, '11-Sep-2016', 9.20),
           (1, '11-Sep-2016', 41.20),
           (1, '11-Sep-2016', 19);
    GO

    --Missing Other Months
    SELECT A.AccountNumber AS Acc,
           DATEPART(Year, TransactionDate) AS Yr,
           DATEPART(Month, TransactionDate) AS Mth,
           SUM(TransactionValue) AS Total
    FROM #Account A
         JOIN #Transaction T ON A.AccountID = T.AccountID
    GROUP BY A.AccountNumber,
             DATEPART(Year, TransactionDate),
             DATEPART(Month, TransactionDate);
    GO

    /*I already have a Date table, which I'm going to use, however, you should use the one you create*/
    SELECT A.AccountNumber AS Acc,
           DD.[Calendar Year] AS Year,
           DD.[Calendar Month] AS Mth,
           SUM(ISNULL(TransactionValue,0)) AS Total
    FROM #Account A
         CROSS APPLY DimDate DD
         LEFT JOIN #Transaction T ON DD.[Date] = T.TransactionDate
    WHERE DD.[Calendar Year] = 2016
    GROUP BY A.AccountNumber,
             DD.[Calendar Year],
             DD.[Calendar Month];
    GO

    DROP TABLE #Transaction;
    DROP TABLE #Account;
    GO

    This produces the following datasets respectively:

    (1 row(s) affected)
    Acc         Yr          Mth         Total
    ----------- ----------- ----------- ---------------------------------------
    12345       2016        9           10.00

    ---------------------------------------------------------------------------

    Acc         Year        Mth         Total
    ----------- ----------- ----------- ---------------------------------------
    12345       2016        1           0.00
    12345       2016        2           0.00
    12345       2016        3           0.00
    12345       2016        4           0.00
    12345       2016        5           0.00
    12345       2016        6           0.00
    12345       2016        7           0.00
    12345       2016        8           0.00
    12345       2016        9           10.00
    12345       2016        10          0.00
    12345       2016        11          0.00
    12345       2016        12          0.00

    Thank you. I will try it out tomorrow. My brain has stopped working and seriously I need a rest now.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply