Prev: Dynamic grouping problem
Next: How to dynamically set the height of the report?
From: dba123 on 31 Jan 2006 16:06
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

From: Bruce L-C [MVP] on 31 Jan 2006 18:02
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(a)hotmail.com> wrote in message
news:1138741587.676817.251770(a)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
>


From: dba123 on 1 Feb 2006 20:45
Thanks Bruce, actually I ended up doing just that but forgot about the
fact that I could add more datasets...you were right on!

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(a)hotmail.com> wrote in message
> news:1138741587.676817.251770(a)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
> >

 | 
Pages: 1
Prev: Dynamic grouping problem
Next: How to dynamically set the height of the report?