0

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

Prasanth
  • 3
  • 5
  • Use - Group on Date and Add Sum of Items in No Of Items Column expression. – Pedram May 04 '16 at 11:57
  • It only shows data to the dates which are on the list above as i mentioned , but i want to display all the dates in a month. ex: on 05/05/2015 no record is present on that day, and it has to be displayed on the table with blank on the no of item column – Prasanth May 04 '16 at 12:26
  • Join against a calendar table and group on month – NewGuy May 04 '16 at 12:52
  • im using fetchXML not sql, i dont have calender table... is there anyway by doing it in expression – Prasanth May 04 '16 at 17:20

2 Answers2

1

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
Hannover Fist
  • 10,393
  • 1
  • 18
  • 39
  • Ok But I'm using FetchXml data for reports not sql , i dont have any calender entity in my fetch. Is there any way that i can use sql query on fetch ? – Prasanth May 06 '16 at 11:35
0

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

Pedram
  • 6,256
  • 10
  • 65
  • 87
  • Ok But I'm using FetchXml data for reports not sql , i dont have any calender entity in my fetch. Is there any way that i can use sql query on fetch ? – Prasanth May 06 '16 at 11:35