SQL Reporting Group By Week

I have a chart that shows number of search hits for various time frames (e.g. Last Week, Last Month, Last Year etc.). For short time intervals chart is displayed on day-by-day basis, but for long intervals doing it on day-by-day basis makes the chart almost unreadable (as there's a lot of data overlaping). Thus i needed a way of alternating the day-by-day with week-by-week representation on the same chart, depending on the "longitivity" of the time frame selected.
In order to solve the problem I have changed the grouping for chart (category fields) to vary depending on the time frame selected:
=IIf((Parameters!TimeFrame.Value = "L6" or Parameters!TimeFrame.Value = "LY"), DateAdd("d", -1 * DatePart("w", Fields!SearchDate.Value) + 1, Fields!SearchDate.Value), Fields!SearchDate.Value) - with the same expression in Label field. Where L6 and LY correspond to time frames - Last 6 Months and Last Year - respectively. This expression assures that in cases re L6 and LY are selected - grouping by week is applied, otherwise grouping by day is in place.

ASP.NET 2.0 SQL permission failed

I have a web application that heavily relies on SQL Server for data retrieval. It worked magic up until I actually had posted it on the website. I got the following error:

Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

The solution was rather easy - i had to set trust level to Full in the web.config file.
This, along with the allowed anonymous access resolves the problem.

SQL 2005 Business Days Difference Function

Easy way to calculate business days difference in SQL 2005. The script below can be wrapped in a function or used with stored procedure by itself:

DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @Day varchar(30)

SET @StartDay = '01/01/2001'
SET @EndDate = getdate()

DECLARE @BusDaysDifference int

SET @Day = DATENAME(dw, @EndDay)

IF (@Day = 'Saturday')
SET @EndDay = @EndDay - 1
ELSE IF (@Day = 'Sunday')
SET @EndDay = @EndDay - 2

SET @BusDaysDifference = CASE

WHEN DATENAME(dw, @StartDate) IN ('Saturday') THEN DATEDIFF(day, @StartDate, @EndDate) - (2*(DATEDIFF(wk, @StartDate, @EndDate)) + 2)

WHEN DATENAME(dw, @StartDate) IN ('Sunday') THEN DATEDIFF(day, @StartDate, @EndDate) - (2*(DATEDIFF(wk, @StartDate, @EndDate)) + 1)

WHEN DATENAME(dw, @StartDate) IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday') THEN DATEDIFF(day, @StartDate, @EndDate) - 2*(DATEDIFF(wk, @StartDay, @EndDate))

END

PRINT (@BusDaysDifference)

The assumption IS week days are: Monday, Tuesday, Wednesday, Thursday and Friday. Unfortunately this script doesn't consider holidays, but this can be easily done using the holidays table.

SQL Integer Padding

I've found a very easy way for integer padding in SQL 2005.

cast(replace(str(INT_N, 3),' ','0')as char(3)),
where INT_N is an integer number, and 3 - the size of the string required, e.g

DECLARE INT_N int
SET INT_N = 33

SELECT cast(replace(str(INT_N, 8), ' ', '0') as char(8)) as PADDED_INT

Will result in:


PADDED_INT

00000033.

Permissions for BizTalk 2006

BizTalk interface was failing on writing to the system event log - System.Diagnostics.EventLog.WriteEntry("BizTalk orchestration", Message, System.Diagnostics.EventLogEntryType.Error);

To solve - I set full permissions for the BtsInstanceHost account (or account which used for BizTalkServerApplication logon) on HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog registry key.

Sometimes FILE adapter fails to pool file from the directory; As well - giving full permissions on the folder to BtsInstanceHost solves the problem.

ASP.NET Access Denied to TEMP folder

I have experienced this problem for several times. Whenever I try to setup the new Web project in Visual Studio and then run it in the debug mode, I get error stating that Access is denied to the Temp .Net folder. The solution i had found:
- To give the full permissions to IWAM_COMPUTERNAME account on that folder!

Page breaks for SQL 2005 Reporting

Seems like there is a bug in 2005 SQL Reporting with the InteractiveSize page property setting. Whenever there is an object in the report that have visibility property controlled by the expression (e.g. Hidden set to =Iff(IsNothing(Fields!Result.Value), true, false)), report doesn't break into multiple pages during parsing.

Toggled myself crazy on that one

Setting up 'Initial Toggle State' property drove me crazy. If you look at that property - you'll see that by default it is set to 'Collapsed', so the logical thing for the expression would be to use the same kind of terminology, e.g. iff(Fields!Expand.Value = 1, 'Collapsed', 'Expanded').
If only... I spent couple of hours trying to figure out why neither Collapsed nor Expanded were working, i was always getting '+' on the report and it didn't want to change dinamicaly, based on the field value. Turned out that instead of using 'Collapsed' or 'Expanded' - true or false should be used in the expression, as follows -
Expanded = true and Collapsed = false

I ended up just using the report parameter Toggle of type boolean and then setting InitialToggleState to =Parameters!Toggle.Value
So far so good - works fine!

SQL 2005 Reporting

It took me a while to figure out - how to use NULL in the Expression Editor. I tried NULL and got the error stating that NULL is not supported anymore, thus System.DBNull should be used.
I tried to use System.DBNull and parser was returning - "DBNull is a type in System and cannot be used as an expression." So much for clearity!
It turned out that 'nothing' should be used instead of null, whenever NULL value needs to be assigned, like in my case:
=IIf(Parameters!FILTER_POA.Value = "NONE", 3, nothing)

Time to start bloggin

Finaly have decided to start "professional" blog, where i would be posting various findings, tips, tricks, bugs descriptions/walkarounds and anything related to my everyday professional activity.
This way it will be easy for me to reference the things that i have done and/or found.
I will start organazing and posting anything I already have within a couple of days...

Hope this will also be helpfull to public!