"dba123" <d_schinkel@hotmail.com> wrote in message
news:1138741587.676817.251770@g43g2000cwa.googlegroups.com...
> How can I create a VB.NET function to run my query below that my SSRS
> report can run to retreive TotalPostingDays so I can show that in a
> textbox in my report? I need help creating the function in the Report
> properties of my SSRS report and not sure how to call this stored
> procedure to return TotalPostingDays.
>
> ALTER PROCEDURE SSRS_Return_TotalPostingDays
>
> AS
>
> DECLARE @TotalDaysInMonth int,
> @today datetime,
> @TotalWeekendDays int,
> @TotalHolidaysThisMonth int,
> @TotalPostingDays int
>
> SET @today = GETDATE()
>
> -- TOTAL DAYS THIS MONTH
> SET @TotalDaysInMonth = CASE WHEN DatePart(mm,GetDate()) IN
> (1,3,5,7,8,10,12) THEN
> 31
> ELSE
> DateDiff(day,GetDate(),DateAdd(mm, 1, GetDate()))
> END
>
>
> -- TOTAL HOLIDAYS THIS MONTH
> SELECT @TotalHolidaysThisMonth = (SELECT COUNT(*) FROM
> ReportingServer.dbo.Holidays
> WHERE HolidayDate BETWEEN (DATEADD(DAY, -DATEPART(DAY, @today) + 1,
> @today))
> AND (DATEADD(DAY, -DATEPART(DAY, @today), DATEADD(MONTH, 1,
> @today))))
>
>
> -- TOTAL # WEEKEND DAYS THIS MONTH
>
> DECLARE @date DATETIME
> SET @date = '20060101'
>
> SELECT @TotalWeekendDays = 8 +
> CASE WHEN ISDATE(CONVERT(CHAR(6), @date, 112) + '29') =
> 1 THEN
> CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @date, 112) +
> '01') IN ('Saturday', 'Sunday')
> THEN 1 ELSE 0 END ELSE 0 END +
> CASE WHEN ISDATE(CONVERT(CHAR(6), @date, 112) + '30') = 1 THEN
> CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @date, 112) +
> '02') IN ('Saturday', 'Sunday')
> THEN 1 ELSE 0 END ELSE 0 END +
> CASE WHEN ISDATE(CONVERT(CHAR(6), @date, 112) + '31') = 1 THEN
> CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @date, 112) +
> '03') IN ('Saturday', 'Sunday')
> THEN 1 ELSE 0 END ELSE 0 END
>
>
> SET @TotalPostingDays = @TotalDaysInMonth - (@TotalHolidaysThisMonth +
> @TotalWeekendDays)
>
> RETURN @TotalPostingDays
>

Bruce L-C [MVP] wrote:
> You can have multiple datasets. Just have a dataset that calls the stored
> procedure. Then in your textbox use the First aggregate function (RS will
> object because of scope with setting a textbox to a value of a field from
> your dataset). If your dataset consists of a single row then you can use the
> First
>
> Use the expression builder and have it do a sum then replace the word Sum
> with the word First.
>
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "dba123" <d_schinkel@hotmail.com> wrote in message
> news:1138741587.676817.251770@g43g2000cwa.googlegroups.com...
> > How can I create a VB.NET function to run my query below that my SSRS
> > report can run to retreive TotalPostingDays so I can show that in a
> > textbox in my report? I need help creating the function in the Report
> > properties of my SSRS report and not sure how to call this stored
> > procedure to return TotalPostingDays.
> >
> > ALTER PROCEDURE SSRS_Return_TotalPostingDays
> >
> > AS
> >
> > DECLARE @TotalDaysInMonth int,
> > @today datetime,
> > @TotalWeekendDays int,
> > @TotalHolidaysThisMonth int,
> > @TotalPostingDays int
> >
> > SET @today = GETDATE()
> >
> > -- TOTAL DAYS THIS MONTH
> > SET @TotalDaysInMonth = CASE WHEN DatePart(mm,GetDate()) IN
> > (1,3,5,7,8,10,12) THEN
> > 31
> > ELSE
> > DateDiff(day,GetDate(),DateAdd(mm, 1, GetDate()))
> > END
> >
> >
> > -- TOTAL HOLIDAYS THIS MONTH
> > SELECT @TotalHolidaysThisMonth = (SELECT COUNT(*) FROM
> > ReportingServer.dbo.Holidays
> > WHERE HolidayDate BETWEEN (DATEADD(DAY, -DATEPART(DAY, @today) + 1,
> > @today))
> > AND (DATEADD(DAY, -DATEPART(DAY, @today), DATEADD(MONTH, 1,
> > @today))))
> >
> >
> > -- TOTAL # WEEKEND DAYS THIS MONTH
> >
> > DECLARE @date DATETIME
> > SET @date = '20060101'
> >
> > SELECT @TotalWeekendDays = 8 +
> > CASE WHEN ISDATE(CONVERT(CHAR(6), @date, 112) + '29') =
> > 1 THEN
> > CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @date, 112) +
> > '01') IN ('Saturday', 'Sunday')
> > THEN 1 ELSE 0 END ELSE 0 END +
> > CASE WHEN ISDATE(CONVERT(CHAR(6), @date, 112) + '30') = 1 THEN
> > CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @date, 112) +
> > '02') IN ('Saturday', 'Sunday')
> > THEN 1 ELSE 0 END ELSE 0 END +
> > CASE WHEN ISDATE(CONVERT(CHAR(6), @date, 112) + '31') = 1 THEN
> > CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @date, 112) +
> > '03') IN ('Saturday', 'Sunday')
> > THEN 1 ELSE 0 END ELSE 0 END
> >
> >
> > SET @TotalPostingDays = @TotalDaysInMonth - (@TotalHolidaysThisMonth +
> > @TotalWeekendDays)
> >
> > RETURN @TotalPostingDays
> >