Wednesday, March 28, 2012

How to create Parameter Field with embedded SQL

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