6 SSRS日历每个月的第7个星期日期范围

Question

我有一个SSRS月度日历，可以吸引您指定月份的所有日期，并且还显示前一个月和下一个月的日期，以显示6,7天周。我试图现在要做的就是显示每个星期范围一个月，例如，在2015年11月我会想：

Oct 25-31 
Nov 1-7 
Nov 8-14 
Nov 15-21 
Nov 22-28 
Nov 29-Dec 5 

我试图建立杠杆看起来像表达以下内容：

="Week " & VBCRLF & Left(MonthName(Parameters!start_cymd.Value.Month,False),3) & " " & Fields!Day.Value & " - " & Fields!Day.Value + 6 

为了得到我想要的结果，我应该做些什么？如果您需要更多信息来回答我的问题，我很乐意提供。谢谢！

编辑：

电流输出

存储过程生成日历：

ALTER PROCEDURE [Event].[Report_Event_Calendar_Month_sp] 
@start_cymd DATE = NULL 
AS 
BEGIN 

DECLARE @StartDate DATE = @start_cymd , 
     @EndDate DATE = @start_cymd , 
     @SDate DATE = @start_cymd 

---First day of current month 
SET @StartDate = DATEADD(s, 0, DATEADD(mm, DATEDIFF(m, 0, @StartDate), 0)) 
SET @SDate = DATEADD(s, 0, DATEADD(mm, DATEDIFF(m, 0, @SDate), 0)) 
---First day to display on calendar 
SET @StartDate = DATEADD(DAY, -DATEPART(DAY, @StartDate) - 6, @StartDate) 
---Last day of month 
SET @EndDate = DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, @SDate) + 1, 0)) 
---Last day to display on calendar 
SET @EndDate = DATEADD(DAY, -DATEPART(DAY, @EndDate) + 35, @EndDate) 

;WITH Dates([Date]) 
     AS (
      SELECT @StartDate AS [Date] 
      UNION ALL 
      SELECT DATEADD(DAY, 1, [Date]) 
      FROM Dates 
      WHERE [Date] < @EndDate 
     ) , 
     Events 
     AS (
      SELECT EventDesc , 
        Notes , 
        start_cymd AS EventDate , 
        MemberName 
      FROM [Event].Event_Description ED 
        INNER JOIN [Event].SQL_Team_Events SE ON ED.EventDesc_ID = SE.EventDesc_ID 
        INNER JOIN [Event].SQL_Team_Member SM ON SE.Event_ID = SM.Event_ID 
        INNER JOIN [Event].Members M ON SM.Member_ID = M.Member_ID 
     ) 


---Number the records based on the date, if multiple records have 
    ---the same date then they will be numbered the same. Used in 
    ---calculation to determine row record is to display 
    SELECT [Order] = DENSE_RANK() OVER (ORDER BY d.[Date]) , 
    ---Date used in all calculations for date 
      d.[Date] , 
    ---Generates matrix columns 
      [WeekDay] = DATEPART(WEEKDAY, d.[Date]) , 
    ---Used to display day of month on calendar 
      [Day] = DATEPART(DAY, d.[Date]) , 
    ---Used in some calculations for display 
      [Month] = DATEPART(MONTH, d.[Date]) , 
      e.EventDesc , 
      e.Notes , 
      e.EventDate , 
      e.MemberName 
    FROM  Dates d 
      LEFT JOIN Events e ON d.[Date] = e.EventDate 
    ---Set the maximum number of times the CTE 'Dates' can recurse 
    OPTION (MAXRECURSION 100) 

END 
GO 

Answer 1

试试这个：

Reporting Services的表达

=Switch(
DatePart("D",Fields!Date.Value) + 6 > Datepart("D",Fields!Date.Value.AddDays(1-Fields!Date.Value.Day).AddMonths(1).AddDays(-1)), 
"Week" & VBCRLF & Left(MonthName(Fields!Date.Value.Month,False),3) & " " & DatePart("D",Fields!Date.Value) & " - " & 
Left(MonthName(Fields!Date.Value.Month + 1,False),3) & " " & DatePart("D",Fields!Date.Value.AddDays(6)), 
true, 
"Week" & VBCRLF & Left(MonthName(Fields!Date.Value.Month,False),3) & " " & DatePart("D",Fields!Date.Value) & " - " & 
DatePart("D",Fields!Date.Value.AddDays(6)) 
) 

• 与2015年2月27日返回测试：测试与2015年11月29日返回Week feb 27 - mar 5
• ：Week nov 29 - dec 5
• 测试了2015年11月22日返回：Week nov 22 - 22

说明我正在使用的数据集中的字段Date对于Day

T-SQL

在T-SQL级别，你可以用你需要的数据添加列，然后在报告中表达串联那些列。

SELECT [Order] = DENSE_RANK() OVER (ORDER BY d.[Date]) , 
---Date used in all calculations for date 
      d.[Date] , 
---Generates matrix columns 
      [WeekDay] = DATEPART(WEEKDAY, d.[Date]) , 
---Used to display day of month on calendar 
      [Day] = DATEPART(DAY, d.[Date]) , 
---Used in some calculations for display 
      [Month] = DATEPART(MONTH, d.[Date]) 
---New filds for rows expression in SSRS 
      ,LEFT(DATENAME(MONTH,d.[Date]),3) as [StartMonth] 
      ,DATEPART(DAY,d.[Date]) as [StartDay] 
      ,IIF(DATEADD(day,6,d.[Date]) > EOMONTH(d.[Date]) 
       ,LEFT(DateName(Month,DATEADD(day,6,d.[Date])),3) 
       ,'' 
       ) as [EndMonth] 
      ,Datepart(day,DATEADD(day,6,d.[Date])) as [EndMonth]    
      e.EventDesc , 
      e.Notes , 
      e.EventDate , 
      e.MemberName 
FROM  Dates d 
      LEFT JOIN Events e ON d.[Date] = e.EventDate 

表达：

= "Week" & VBCRLF & Fields!StartMonth.Value & " " & Fields!StartDay.Value & 
" - " & Fields!EndMonth.Value & " " & Fields!EndDay.Value 

让我知道如果这能帮助你。