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.
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
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
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.
As promised here are the files for my sessions:
Continue reading
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
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
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).
On the Site Settings page, you have General Properties, Security and Schedule.
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.
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.
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.
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.
Finally there is a way to share data sets among reports!
And yes, you can also cache these shared data sets.
(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
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.
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 |
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
)
GO
[/sql]
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
FROM ”C:CACA.txt”
WITH (ROWTERMINATOR = ”’+CHAR(10)+”’)’
EXEC(@bulk_cmd)
GO
[/sql]
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]
ADD [GeoLocation] GEOGRAPHY
GO
[/sql]
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)) + ‘)’)
GO
[/sql]
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!
sqlbelle 