Just sharing a download link:
Sample Databases for Microsoft SQL Server 2008R2 RTM (May 2010)
Click here
It contains the complete set of databases:
– AdventureWorks
– AdventureWorks2008R2
– AdventureWorksDW2008R2
– AdventureWorksLT
– AdventureWorksLT2008R2
One of the nice new features in SSRS 2008 R2 is the Map Tool, it provides a Bing Maps visualization of your dataset (uses spatial data).
It’s not that difficult to setup, but a drill down functionality would be nice and it requires just one stored procedure!
sp_GetAllPersonsLocations
Note: The filter on State/Province will only be used for the US, since it’s a big country.
In this example I used the new AdventureWorks2008R2 database and the first query is a big one (on my laptop).
So far for the SP and its data, let’s begin with creating the report.
Start with creating a new report dataset:
Press OK, BIDS will automatically create four parameters corresponding to the SP.
Make sure the ‘All null value’ option is selected and the default values are NULLs.
Now reopen the dataset and modify the parameter-settings:
Drag a Map component onto the report.
Within the Map Wizard, select SQL Server spatial query and press Next. Select the existing dataset (in my case dsSpatialPersonsAddresses) and press Next.
You’ll receive an error ‘Unable to determine the spatial data type due to connection error’, this is correct because the stored procedure need some parameter values which are not known by the wizard. Ignore the error and press OK. Notice that ‘SpatialLocation’ has already been selected for Spatial Field. Set Layer type to ‘Point’ and select ‘Add a Bing Maps layer’. The result:
Press Next, select ‘Basic Marker Map’ and press Next again.
Now press Finish and view the result:
The final step is to setup the Map layer – Point properties, in order to enable the drill down functionality.
Go to the Action tab and select the option ‘Go to report’. Now select the report your making and setup the parameter properties.
The same report will be started every time, but with different parameter values. These parameter values are added one by one after every drill down in the stored procedure. Publish the report and you’re done!
If you want to calculate the day-of-week for your DateTime values, say for a Service Window/Time, it’s easy to use the function:
But, it’s tricky since the DATEFIRST setting of your current SQL Session depends on the user-account your currently using. The worst thing is, you can’t use the SET DATEFIRST statement within a function…it’s the language setting of the SQL Account you wish to tackle.
An example:
During my last assignment, I wrote a SP that was using a function (UDF) called ‘HandledTime’. HandledTime was the duration (in minutes or hours) that it took to resolve an incident. To make it short, I used day-of-week to calculate two things:
– Number of full days between the datetime’s @Received and @Handled
– Check whether both datetime’s didn’t fell in the weekend (since the service window started from Monday untill Friday)
My calculations worked fine on the development environment, but when we deployed it to the production environment it all got messed up.
The cause:
The SQL Accounts for both environments had a different language setting (regional settings of the SQL Servers where the same).
The solution:
DATEFIRST sets the first day-of-week:
7 = Sunday (Default setting, US)
1 = Monday (European setting)
After a few hours, I finally resolved it:
IF (@@DATEFIRST != 1)
BEGIN
SET @StartDate = ((DATEPART(dw,@StartDate) + @@DATEFIRST) %7) -1
SET @EndDate = ((DATEPART(dw,@EndDate) + @@DATEFIRST) %7) -1
END
ELSE
BEGIN
SET @StartDate = DATEPART(dw,@StartDate)
SET @EndDate = DATEPART(dw,@EndDate)
END
This solution works every time if want to use the European standard (Monday is the first day of week).
A smart trick I learned regarding an ‘All’-entity as a parameter value, is using:
‘OR (@Parameter IS NULL)‘
Parameter values:
Query:
Parameter value list:
Report Dataset:
Let’s start with something simple, check the ‘About’ page!
Clint Huijbers' Blog 