Different T-SQL Date Variations for Date Dimensions

Just another handy code snippet for generating dates for date dimensions in your data warehouse.

I am providing just the select statement here, but to generate (massive) date records, just create and set your start and end date variables, and enclose your insert and the code below in your WHILE loop.

Continue reading

Your report is only as good as your requirements

Your report is only as good as your requirements.

When I first worked with reports and reporting services, I was excited and giddy. Beside my plain old text and T-SQL, I now get to work with some shapes and colors! And look ma, no hands, err, it’s drag and drop!

But the fascination with colors, drilldowns, drillthroughs, what-have-yous fade away as quickly as that drag and drop. You realize fast that – although managers typically like the pie charts, the drill downs, the colored legends – if any number, or any minor thing for that matter, is not “right”, the whole report is not right, and all your work really goes down the drain.

Sometimes, it’s not because the report is “completely wrong”.
Continue reading

Retrieve SSRS Report Definition, Role Assignments, Executions Using T-SQL

Just a few queries against the ReportServer database that might come in handy…
Of course, these are just some sample queries. Tune and modify according to your needs …
Continue reading

SQLSaturday #43 (Redmond) – SQL Server Reporting and SQLServer/PowerShell

I would like to say a huge and warm thank you to all who attended my sessions for SQLSaturday#43 in Redmond. It was such a great event, and I hope you enjoyed the sessions – and enjoyed the whole day with all the wonderful speakers.

sqlsat43 redmond sessions

As promised here are the files for my sessions:
Continue reading

SQL Server 2008 Reporting Services Book Shelf

These two books are great, and they almost never leave my desk. I’ve been reading them nonstop.

Prologika Press. Applied Microsoft SQL Server 2008 Reporting Services by Teo Lachev

Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports by Paul Turley and Robert M. Bruckner

Exploring SSRS 2008 R2 Report Manager

SQL Server Reporting Services 2008 R2 (based on November CTP) has received a makeover! It looks more SharePoint-ish, especially with its new blue theme, contextual dropdowns, and ajaxified operations. I quite like the changes, this definitely feels more “user friendly” to me.

Download document/screenshots: Exploring SQL Server 2008 Reporting Services R2 Report Manager

Report Manager – Home Page

The new theme is easy on the eyes. When you hover over the items, whether it’s a folder or a file, there will be a contextual dropdown. Feels very much like SharePoint (2010).
SSRS 2008 R2 Report Manager Home Page

Report Manager – Site Settings

On the Site Settings page, you have General Properties, Security and Schedule.
SSRS 2008 R2 Report Manager - Site Settings

Report Manager – Data Source Properties

Data Source Properties page looks familiar, with the exception of Dependent Items. Dependent Items page will list all items (reports, data sets) that use this particular Data Source.
SSRS 2008 R2 Report Manager - Data Source Properties

Report Manager – Search

Yes, you can now search in SSRS Report Manager! Type in your keyword, and away you go! It will find your report, your data set, your folder, or even your report part.
SSRS 2008 R2 Report Manager - Search

Report Manager – Report Contextual Dropdown

If you have worked with SSRS in Integrated Mode in SharePoint, the contextual dropdown in the revamped Report Manager will look familiar. When you hover over a report item and click on the down arrow, you will be able to move, delete, edit in Report Builder 3.0, and explore other properties, to name a few.
SSRS 2008 R2 Report Manager - Report Contextual Dropdown

Report Manager – Report Properties

If you clicked on “Manage” in the contextual dropdown of a report item, you will be taken to this page. Note that on the left column, you have links for : properties, data sources, shared data sets, subscriptions, processing options, cache refresh options, report history, snapshot, and security.
SSRS 2008 R2 Report Manager - Report Properties

Report Manager – Shared Data Set Properties

Finally there is a way to share data sets among reports!
SSRS 2008 R2 Report Manager - Shared Data Set Properties

Report Manager – Shared Data Set Caching Properties

And yes, you can also cache these shared data sets.
SSRS 2008 R2 Report Manager - Shared Data Set Caching Properties

To infinity and beyond

(You can tell I’m waiting for Toy Story 3) .. I’m excited to work with SSRS 2008 R2. Besides changes in the Report Manager, there’s quite a few more exciting additions. In my next few blogs I will post some mini tutorials on creating spark lines, data bars, indicators and map integration.

Download document/screenshots: Exploring SQL Server 2008 Reporting Services R2 Report Manager

SQL Server Spatial Data for Canada

I needed to load spatial data for Canadian places into a SQL Server 2008 R2 database. Good thing there is geonames.org, which is a geographic database that contains millions of placenames and their corresponding geolocations (latitude and longitude).

Here are the steps I took to create this spatial database.

Download the geospatial data

So I first downloaded the Canadian geolocation database from http://download.geonames.org/export/dump/ called CA.zip (according to the geonames readme).

This export file contains the following fields (excerpt from the readme file)

geonameid integer id of record in geonames database
name name of geographical point (utf8) varchar(200)
asciiname name of geographical point in plain ascii characters, varchar(200)
alternatenames alternatenames, comma separated varchar(5000)
latitude latitude in decimal degrees (wgs84)
longitude longitude in decimal degrees (wgs84)
feature class see http://www.geonames.org/export/codes.html, char(1)
feature code see http://www.geonames.org/export/codes.html, varchar(10)
country code ISO-3166 2-letter country code, 2 characters
cc2 alternate country codes, comma separated, ISO-3166 2-letter country code, 60 characters
admin1 code fipscode (subject to change to iso code), see exceptions below, see file admin1Codes.txt for display names of this code; varchar(20)
admin2 code code for the second administrative division, a county in the US, see file admin2Codes.txt; varchar(80)
admin3 code code for third level administrative division, varchar(20)
admin4 code code for fourth level administrative division, varchar(20)
population bigint (4 byte int)
elevation in meters, integer
gtopo30 average elevation of 30’x30′ (ca 900mx900m) area in meters, integer
timezone the timezone id (see file timeZone.txt)
modification date date of last modification in yyyy-MM-dd format

Create a table in your SQL Server database that maps to all these columns

I then created the following table
[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

CREATE TABLE [dbo].[CAGeoNames]
[geonameid] INT PRIMARY KEY,
[name] VARCHAR(200),
[asciiname] VARCHAR(200),
[alternatenames] VARCHAR(5000),
[latitude] DECIMAL(38,10),
[longitude] DECIMAL(38,10),
[featureclass] VARCHAR(10),
[featurecode] VARCHAR(10),
[countrycode] VARCHAR(2),
[cc2] VARCHAR(60),
[admin1code] VARCHAR(20),
[admin2code] VARCHAR(80),
[admin3code] VARCHAR(20),
[admin4code] VARCHAR(20),
[population] bigINT,
[elevation] INT,
[gtopo30] INT,
[timezone] VARCHAR(100),
[modificationdate] DATETIME

Import the data

I initially used BULK IMPORT with a ROWTERMINATOR of n because I know each line is terminated by a linefeed. But for some reason, n didn’t work. Neither did rn.

Baffled, I know that n should have worked, but I conceded I should consult Books Online. Turns out Books Online has an exact sample for importing a file that was produced by UNIX .. and for some reason it uses dynamic SQL. Not wanting to waste any more time with the import, I just used the Books Online sample as a template, and true enough, it worked.

One of these days though I will come back to this issue and figure out why a bare n ROWTERMINATOR doesn’t work.

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = ‘BULK INSERT CAGeoNames


Add a GEOGRAPHY type column

Next up, we’ll use SQL Server 2008’s new GEOGRAPHY data type.
[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
ALTER TABLE [dbo].[CAGeoNames]


Create GEOGRAPHY data based on the existing longitude and latitude values

And last but not least, we are going to derive the GEOGRAPHY value based on the longitude and latitude values that we just imported. We are going to use the Parse function that comes with the GEOGRAPHY data type.
[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

UPDATE [dbo].[CAGeoNames]
SET [GeoLocation] = Geography::Parse(‘POINT(‘ +
CAST([longitude] AS VARCHAR(20)) + ‘ ‘ +
CAST([latitude] AS VARCHAR(20)) + ‘)’)


That’s it. Now I have my own Canadian geolocation database. I can start using this database now for my SSRS R2/Bing Maps presentations!