I want to create an expression field on my report that performs an sql statement that I specify to bring back a total instead of using the built-n functions for SSRS, how can I do this? Basically just like in Crystal where you can create a formula field that runs a sql statement then brings back a value from your sql statement. This value is not related to the other data in the report...so yes in theory you may be thinking subreport...is tha the only way? or can I create some sort of textbox that shows the total I want below?
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
SELECT @.TotalDaysInMonth
-- TOTAL HOLIDAYS THIS MONTH
SELECT @.TotalHolidaysThisMonth = (SELECT COUNT(*) FROM Apex_ReportingServer.dbo.Holidays
WHERE HolidayDate BETWEEN (DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today))
AND (DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))))
SELECT @.TotalHolidaysThisMonth
-- 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
SELECT @.TotalWeekendDays
SELECT @.TotalPostingDays = @.TotalDaysInMonth - (@.TotalHolidaysThisMonth + @.TotalWeekendDays)
SELECT @.TotalPostingDays
You could simply add the SQL Statement above to a new/different Dataset and reference that Dataset in the given field on your report...does that make sense, or am I missing something?
|||exactly thanks! I actually tried that and it worked before you posted...thanks you were right on.sql
No comments:
Post a Comment