Help creating Embedded code Function

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.

I need to do this calculation by using a UDF, not stored procedure maybe. The problem is, I do still need to do a lookup to my Holiday table as part of my UDF though; the rest can proabably be done in Straight VB for the weekend and other calculations:
LTER 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


for the holiday lookup, I can probably do something like this then use the variable below to proceed or something:
Dim intTotalPostingDays As Integer
Dim objConn As New SqlConnection("Data Source=server;Initial Catalog=database; integrated security=SSPI;persist security info=False; Trusted_Connection=Yes")
Dim objComm As New SqlCommand("SSRS_Return_TotalHolidaysThisMonth", objConn)
objComm.CommandType = CommandType.StoredProcedure
Dim returnValueParam As New SqlClient.SqlParameter("@.RETURN_VALUE", SqlDbType.Int)
objComm.Parameters.Add(returnValueParam)
objComm.Connection.Open()
Dim objReader As SqlClient.SqlDataReader = objComm.ExecuteReader()
intTotalHolidays = returnValueParam.Value()

should I use executescalar instead of datareader? I am not sure where to go here for the entire function that I need so I can get this into my SSRS report.

I would do it a little more simply and call the stored procedure using the RS data source and query functionality. You can call the stored procedure and create a one row data set to use in your report.

Forgot to add you can have multiple datasets in your report, so you are not limited to this query.

|||actually, that's not a bad idea...thanks, will try it.|||

For some reason, completely forgot about datasets in my report! I had initially created one to run a Stored Proc as the DataSet...then I just added another to run this stored proc to return the field then added that field to a textbox and that was it!

thanks for refreshing my memory about datasource, which lead me to create a new dataset instead!

sql