CODEWITHSUNDEEP
sqlsql-serverreporting-services
Ankit Khetan
Ankit Khetan

Reputation: 138

SSRS Report per month with 3 Columns count

I am working on SSRS report where I have 3 dates to deal with and get the count of each column per month starting September. Here is an image of what I am trying to achieve and I am not sure of what exactly I am missing in the Groupings. Any help would be really appreciated. Query - 

SELECT  CONVERT(NVARCHAR(10), TableA.DueDate, 101) AS DueDate ,
        CONVERT(NVARCHAR(10), TableB.DateFrom, 101) AS DateFrom ,
        CONVERT(NVARCHAR(10), TableB.DateTo, 101) AS DateTo
FROM    dbo.TableA
        INNER JOIN dbo.TableB ON dbo.TableA.Id = dbo.TableB.TableAid
WHERE   ( TableA.DueDate BETWEEN '2015-08-01'
                         AND     '2016-07-30' )
        AND ( TableB >= '08/01/2013' )
        AND ( TableB <= '07/30/2014' )

enter image description here

Upvotes: 0

Views: 535

Answers (2)

SQLnbe
SQLnbe

Reputation: 868

Ankit Khetan, What you're displaying in your report layout is an SSRS MATRIX format (like excel). See if this works you instead of using a another query .... In your Select Statements extract the month number for each date variable. Then try to use these numbers as your columns. and use your Date variables as your rows in the matrix layout.

Upvotes: 0

TMNT2014
TMNT2014

Reputation: 2130

Its a little ugly but will get you the intended results - Just paste your query inside the definition of the CTE -

WITH DateCTE AS (SELECT
        DueDate, DateFrom, DateTo FROM DateTable)

SELECT MONTH, SUM(DueDate), SUM(DateFROM), SUM(DateTo)
FROM (
  SELECT DateName(month,DueDate) MONTH, Count(*) AS DueDate, 0 DateFROM , 0 DateTo
  FROM DateCTE 
  GROUP BY DateName(month,DueDate)
  UNION
  SELECT DateName(month,DateFrom) MOntH, 0 AS DueDate, COUNT(*) DateFROM , 0 DateTo
  FROM DateCTE 
  GROUP BY DateName(month,DateFrom)
  UNION
  SELECT DateName(month,DateTo) Month, 0 AS DueDate, 0 DateFROM , COUNT(*)  Dateto
  FROM DateCTE 
  GROUP BY DateName(month,DateTo)) UnionTable
GROUP BY MONTH

Heres the SQL fiddle - http://sqlfiddle.com/#!6/0a639/10

Upvotes: 1

Related Questions