CODEWITHSUNDEEP
reporting-servicesssrs-2008-r2bidsssrs-expression
Prasanth
Prasanth

Reputation: 3

How to display all the dates on current month in ssrs expression

Consider May is current month

I have list of dates Ex:

Date No of Items

05/3/2016 4

05/3/2016 5

05/4/2016 7

05/10/2016 10

05/11/2016 50

05/30/2016 100

I want to display all dates in may and sum of the items in their date and if there is no record in the date then it should be left blank Ex:

Date No of Items

05/1/2016

05/2/2016

05/3/2016 9

05/4/2016 7

05/5/2016
.

.

.

05/10/2016 10

05/11/2016 50

05/12/2016

05/13/2016

.

.

.

.

.

05/30/2016 100

Any Help on this

Upvotes: 0

Views: 1524

Answers (2)

Pedram
Pedram

Reputation: 6508

Exactly, we cannot do this things in SSRS.

So to achieve this thing, we need to make a table of the Dates and then by making LEFT JOIN we can achieve our goal.

Let me show you one sample example:

DECLARE @month AS INT = 5
DECLARE @Year AS INT = 2016
CREATE TABLE #Temp ( Dates Date)

;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a)

INSERT INTO #Temp
SELECT DATEFROMPARTS(@year,@month,N) dates FROM tally 
WHERE N <= DAY(EOMONTH(datefromparts(@year,@month,1)))

SELECT Date, SUM(ISNULL(TotalCount,0)) NoOfItems FROM #Temp T
LEFT JOIN TableName S ON S.Date = T.Dates
GROUP BY Dates
DROP TABLE  #Temp

And this will return all dates with NoOfItems. Yes, you have to change above query as per your requirement. Thanks

Upvotes: 0

Hannover Fist
Hannover Fist

Reputation: 10860

There's not a way to do this in SSRS.

Usually when I have a similar situation, I would make a table of the dates needed and then LEFT JOIN my data to it so the dates would appear when the date wasn't in the data.

I use a CTE to create the table in SQL:

DECLARE @START_DATE DATE = '01/01/2016' 
DECLARE @END_DATE  DATE  = '05/31/2016'

;WITH GETDATES AS  
    (  
        SELECT @START_DATE AS THEDATE
        UNION ALL  
        SELECT DATEADD(DAY,1, THEDATE) FROM GETDATES  
        WHERE THEDATE < @END_DATE  
)

Then use the table with your data (maybe put your results from your current query in a #TEMP_TABLE).

SELECT * 
FROM GETDATES D 
LEFT JOIN #TEMP_TABLE T ON T.DATE_FIELD = D.THEDATE

Upvotes: 1

Related Questions