adding date columns in SSRS Report

0
votes

I have created one SSRS report.

In the report I am providing one parameter named startDate. From the startDate I have to display 10 columns like week of dd-mon-yy.

First column contains date of Monday in the week which the start date lies. then the upcoming 10 Mondays are used.

Example is shown in the image. How can I achieve this in SSRS reports. I need query and column value.

enter image description here

3
sqlsql-serversql-server-2008reporting-services
what's your main problem? to get data by weeks starting with monday from the week which contains startdate? or to pivot the data in SSRS? – CeOnSql

3 Answers

0
votes

This should be your column name alias for current week

select 'Week of '+ CONVERT(VARCHAR(11),dateadd(week, datediff(week, 0, getdate()), 0),106);

then alias for next weeks

select  'Week of '+ CONVERT(VARCHAR(11),dateadd(week, 1,dateadd(week, datediff(week, 0, getdate()), 0)),106);
select  'Week of '+ CONVERT(VARCHAR(11),dateadd(week, 2,dateadd(week, datediff(week, 0, getdate()), 0)),106);
select  'Week of '+ CONVERT(VARCHAR(11),dateadd(week, 3,dateadd(week, datediff(week, 0, getdate()), 0)),106);
select  'Week of '+ CONVERT(VARCHAR(11),dateadd(week, 4,dateadd(week, datediff(week, 0, getdate()), 0)),106);
select  'Week of '+ CONVERT(VARCHAR(11),dateadd(week, 5,dateadd(week, datediff(week, 0, getdate()), 0)),106);
select  'Week of '+ CONVERT(VARCHAR(11),dateadd(week, 6,dateadd(week, datediff(week, 0, getdate()), 0)),106);
select  'Week of '+ CONVERT(VARCHAR(11),dateadd(week, 7,dateadd(week, datediff(week, 0, getdate()), 0)),106);
select  'Week of '+ CONVERT(VARCHAR(11),dateadd(week, 8,dateadd(week, datediff(week, 0, getdate()), 0)),106);
select  'Week of '+ CONVERT(VARCHAR(11),dateadd(week, 9,dateadd(week, datediff(week, 0, getdate()), 0)),106);
0
votes

To get date of Monday in the week which the start date lies along with the dates of next 10 mondays you can write a query as:

DECLARE @input date

SET @input = '12/29/2013'

SELECT 'Week of '+
        REPLACE(CONVERT(VARCHAR(11), 
        DATEADD(wk, DATEDIFF(wk,0,@input), T.week*7)
        , 106), ' ', '-')
FROM 
(VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7),(8))AS T(week)

Demo

0
votes

You can use this expression to get Monday in the week which the start date lies for first week:

=Dateadd(DateInterval.Day, 
        (0 * 7) + 1 - DatePart(DateInterval.Weekday, Parameters!startDate.Value, FirstDayOfWeek.Monday), 
        Parameters!startDate.Value)

For second week:

=Dateadd(DateInterval.Day, 
        (1 * 7) + 1 - DatePart(DateInterval.Weekday, Parameters!startDate.Value, FirstDayOfWeek.Monday), 
        Parameters!startDate.Value)

And so on.