sql - SSRS 日历每个月的 6 周 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-02-27 返回:2 月 27 日 - 3 月 5 日这一周
• 测试 2015-11-29 返回:11 月 29 日 - 12 月 5 日这一周
• 测试 2015-11-22 返回:11 月 22 日至 22 日这一周

Note I am using the field Date of your dataset intead of 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

如果这对您有帮助，请告诉我。