Get All date even there is no item for that Date in SharePointList dataset

Question

Get All date even there is no item for that Date in SharePointList dataset

Vivek Sapkale

Valued Member

Points: 51
More actions
March 19, 2012 at 7:53 am

#270383

I have the SharePoint List of Date wise Sales as
Product NameDate(MM/DD/YYYY) Sale
A 3/1/2012 10
A 3/2/2012 20
B 3/2/2012 21
B 3/3/2012 30
C 3/6/2012 40
C 3/7/2012 50
A 3/7/2012 51
.... ...... ......
I have design the Matrix report as
Product Name3/1/20123/2/20123/3/20123/6/20123/7/2012 ..... ...... ....
A 1020 - - 51
B -21 30 40 -
C -- - - 50
But I want the show all Dates of the Months That are not available in the the SharePoint List Data
Product Name3/1/20123/2/20123/3/2012 3/4/2012 3/5/20123/6/20123/7/2012 ....
A 1020 - - - - 51
B -21 30 - - 40-
C -- - - - -50

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

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

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 1

Create a query (temp table, cte, table variable) where you create all the dates of the month with a zero value for the quantity and UNION ALL it with the query that pulls your data.

Vivek Sapkale Valued Member Points: 51 More actions · Answer 2

I am using Share Point List as Data Source, and the query is like

<ListName>ProductSale</ListName>

</ViewFields>

</RSSharePointList>

How to use the temp table, cte, table variable in this case to achieve the requirement.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Actually, I would use a calendar table and do a left join from it to the dates in the list. The null values can be converted to 0 using ISNULL or COALESCE.