Help creating simple VB Function

dba123

Member
Joined
Jan 31, 2006
Messages
8
Programming Experience
5-10
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 =CASEWHENDatePart(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 =(SELECTCOUNT(*)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 +
CASEWHENISDATE(CONVERT(CHAR(6), @date, 112)+'29')= 1 THEN
CASEWHENDATENAME(WEEKDAY,CONVERT(CHAR(6), @date, 112)+'01')IN('Saturday','Sunday')
THEN 1 ELSE 0 ENDELSE 0 END+
CASEWHENISDATE(CONVERT(CHAR(6), @date, 112)+'30')= 1 THEN
CASEWHENDATENAME(WEEKDAY,CONVERT(CHAR(6), @date, 112)+'02')IN('Saturday','Sunday')
THEN 1 ELSE 0 ENDELSE 0 END+
CASEWHENISDATE(CONVERT(CHAR(6), @date, 112)+'31')= 1 THEN
CASEWHENDATENAME(WEEKDAY,CONVERT(CHAR(6), @date, 112)+'03')IN('Saturday','Sunday')
THEN 1 ELSE 0 ENDELSE 0 END


SET @TotalPostingDays = @TotalDaysInMonth -(@TotalHolidaysThisMonth + @TotalWeekendDays)

RETURN @TotalPostingDays
 
Back
Top