none
Returning all the days in selected Month in a Year using Report Builder

    Question

  • I am working Report Builder with SharePoint List Data Source. Report builder has to parameters Month and Year. If i select month as March and year as 2014 the report have to display

     March 1 2014 Saturday
     March 2 2014 Sunday
     March 3 2014 Monday 
     ................
     ................
     March 31 2014 Monday

    Can any one help on this, do i have to write this in Dataset Property query, Report Builder Properties or calling any function.....!

    Appreciate if any example query. Thank you

    Saturday, June 14, 2014 7:54 PM

Answers

  • Hi SPBee,

    If the all fields are already returned by dataset with SharePoint List Data Source, we can directly add filters in your dataset to achieve your requirement.
    Expression: [Month]
    Operator: In
    Value: [@Month]
    Expression: [Year]
    Operator: In
    Value: [@Year]

    If you want to create a report that display the data as you post, we can create a function in SQL Server Management Studio as below:
    IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME ='CalendarTable1' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE='FUNCTION')
    DROP FUNCTION dbo.CalendarTable1
    GO
    CREATE FUNCTION dbo.CalendarTable1
    (
    @StartDate datetime,

    @EndDate datetime,

    @WeekDaysOnly bit = 0,

    @MonthStart bit=0
    )
    RETURNS @CALENDAR TABLE
    (
    Date datetime,

    [Monthname] varchar(20),

    [Dayname] varchar(20),

    [Day] int
    )
    AS
    BEGIN
    ;With Calendar_CTE (Date,[Monthname],[Dayname],[Day])

    AS
    (
    SELECT @StartDate,datename(Month,@StartDate),  DATENAME(dw,@StartDate), DATEPART(day,@StartDate)
    UNION ALL
    SELECT DATEADD(dd,1,Date),datename(month,DATEADD(dd,1,Date)),  DATENAME(dw,DATEADD(dd,1,Date)) ,
    DATEPART(day,DATEADD(dd,1,Date))
    FROM Calendar_CTE
    WHERE DATEADD(dd,1,Date) < = @EndDate
    )
    INSERT INTO @CALENDAR
    SELECT Date,[monthname],[Dayname],[Day]
    FROM Calendar_CTE
    RETURN
    END

    Create two parameters start and end with Date/Time type in the report, then use the query like below in the dataset:
    SELECT * from dbo.CalendarTable1 (@start,@end,0,0)

    If there are any other questions, please feel free to ask.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Monday, June 16, 2014 11:28 AM

All replies

  • Hi SPBee,

    If the all fields are already returned by dataset with SharePoint List Data Source, we can directly add filters in your dataset to achieve your requirement.
    Expression: [Month]
    Operator: In
    Value: [@Month]
    Expression: [Year]
    Operator: In
    Value: [@Year]

    If you want to create a report that display the data as you post, we can create a function in SQL Server Management Studio as below:
    IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME ='CalendarTable1' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE='FUNCTION')
    DROP FUNCTION dbo.CalendarTable1
    GO
    CREATE FUNCTION dbo.CalendarTable1
    (
    @StartDate datetime,

    @EndDate datetime,

    @WeekDaysOnly bit = 0,

    @MonthStart bit=0
    )
    RETURNS @CALENDAR TABLE
    (
    Date datetime,

    [Monthname] varchar(20),

    [Dayname] varchar(20),

    [Day] int
    )
    AS
    BEGIN
    ;With Calendar_CTE (Date,[Monthname],[Dayname],[Day])

    AS
    (
    SELECT @StartDate,datename(Month,@StartDate),  DATENAME(dw,@StartDate), DATEPART(day,@StartDate)
    UNION ALL
    SELECT DATEADD(dd,1,Date),datename(month,DATEADD(dd,1,Date)),  DATENAME(dw,DATEADD(dd,1,Date)) ,
    DATEPART(day,DATEADD(dd,1,Date))
    FROM Calendar_CTE
    WHERE DATEADD(dd,1,Date) < = @EndDate
    )
    INSERT INTO @CALENDAR
    SELECT Date,[monthname],[Dayname],[Day]
    FROM Calendar_CTE
    RETURN
    END

    Create two parameters start and end with Date/Time type in the report, then use the query like below in the dataset:
    SELECT * from dbo.CalendarTable1 (@start,@end,0,0)

    If there are any other questions, please feel free to ask.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Monday, June 16, 2014 11:28 AM
  • Hi SPBee,

    You can have a sharepoint list requivalent to date dimension table and you can map this list to your parameter, Based on your selection of month and year, It will give you all the days of the particular month.

    Thanks,

    Vishal

    Monday, June 16, 2014 12:57 PM