How can I insert Blank Rows to my tablix in SSRS

• Newbi

  SSCertifiable

  Points: 6546

  More actions

  January 13, 2017 at 8:10 am

  #316924

  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?

• Thom A

  SSC Guru

  Points: 99130

  More actions

  January 13, 2017 at 8:58 am

  #1922597

  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

• Newbi

  SSCertifiable

  Points: 6546

  More actions

  January 13, 2017 at 9:18 am

  #1922607

  Yes.

• Thom A

  SSC Guru

  Points: 99130

  More actions

  January 13, 2017 at 9:26 am

  #1922618

  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

• Newbi

  SSCertifiable

  Points: 6546

  More actions

  January 13, 2017 at 9:37 am

  #1922625

  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.