SQL Server & BI Blog by Andreas Wolter

As a first technique in this series we will look at what I will call “Layout template”.

I chose the Term “Layout-Template” for two reasons:

first: the main thing that they can be used for is, to define general report sizing, footer and header elements – in other words, the overall layout of the report.

secondly: in the next part of this series we will see a different type of template which will actually be called “Style-Template”.

So, what is a Layout-Template?:

A Report Template can be as simple as a standard report with a certain page width and height, certain colors, a standard company logo etc., which can be copied over and over again.

Here the Problem is, how to avoid to overwrite the defined Report Template unintentionally, and, how to make it easily available.. even (report-)project-independent.

And this is where “Layout-Templates” come at hand.

Report Layout-Templates are shown in the Project-Dialogue “Add“ – “New Item“ .

There you can choose a Template and it will create a copy local to your project – and not overwrite the Rdl at its original location.

And this is how we create a Report Layout-Template:

1) Simply create a regular report, configure size, header and footer and so on. You could create a data region, but it would make little sense – just as a sample maybe.

2) Then you put the .rdl-file in the following location:

for Reporting Services 2005

• %ProgramFiles%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

for Reporting Services 2008 x64

• %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

for 32-bit Reporting Services 2008

• C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

3) and when you right-click at the reports-node in your project, click “Add“ and then chose “New Item“, your Report Templates will appear in the “Add New Item” box:

That’s it. Very simple.

How about “centralizing”?

• You can “xcopy deploy” this folder regularly to all your Developers machines.
• But: once the reports are created, and you make a change to the originating template, existing reports will not be touched.

How about “standardizing”?

• If you “xcopy deploy” this folder regularly to all your Developers machines, all your reports should have the same Layout in terms of sizing, header&footer.

In short: What Layout-Templates can be used for:

• Having predefined report size, (i.e. landscaped and portrait version)
• Include report-header and footer with corporate design and navigational elements
• Can even include sample data-regions with appropriate fonts and colors

In the next Part of the series, you will see how to create and use a Style-Template.

Andreas

Sarpedon Quality Lab

While SQL Server 2011 (Denali) is at horizon, bringing up many changes in the way reports will be developed, Developers using SQL Server 2005, 2008 or 2008 R2 are still faced with on problem from the very first report on: How can I make not only development easier by having a template for reports at hand, but also, how to manage it.

Whereas in the area of webdesign, it is absolutely common to have a (cascading) style sheet to be referred to in all website-documents, in reporting services this concept does not exist.
While this is very annoying and it seems that it should be easy to implement at first, one has to remember, that Reporting Services does not just render to html, but at the same time Reporting Services have to be equally prepared to render Reports to Excel-Files, pdf-Files, TIFF-Image-Files, among others. So using the same technique as websites do, would simply to narrow.
There is a case at Microsoft Connect, where you can vote on this feature to get implemented in an upcoming version: http://connect.microsoft.com/SQLServer/feedback/details/253976/add-style-or-stylesheet-or-template-to-reporting-services-reports

In short: In Reporting Services there is no such one thing as ONE and only style sheet that does all magic for you.
This blog-series is intended to show you the existing techniques, and how to combine them. In the end your effectiveness in creating an managing Reports style-wise can be improved by a magnitude. But: there is work to do beforehand.

I spend quite a while, reading other blogs, the rare documentation and doing my own tests. Finally I had the honor of presenting a whole session on this matter at the European PASS Conference 2009.

In the following posts I will concentrate on the existing techniques I identified as the most useful techniques and combination.

Those are the techniques I will focus on:

The Basic Techniques:

• Layout-Templates
• Style-Templates

Custom methods:

• Using Custom Code
• Using an Assembly
• Using plain T-SQL

Combining techniques:

• Style Template + Layout Template
• Style Template + Custom style + Layout Template

Reporting Services 2008 R2 techniques

• Shared DataSet
• Report Part

I also tested using XML-Files as well as making a webservice-call to get Style-data, but this turned out to be rather pathological and is therefore left out from further consideration.

In the next Part of the series, I will show how to create and use a Layout-Template.

Andreas

Sarpedon Quality Lab

SQL Server 2008 R2 Reporting Services supports the visualization of geographical data in 3 ways.

• Datasets, based on the built-in .Net System-datatypes “geography” and “geometry”, which have been around since SQL Server 2008
• via built-in maps – but only for the United States
• via ESRI-Shapefiles, which one has to provide on his own

Now, where can you get those Shape-files?

Other, than in the USA where gathered data belongs to the people, with no cost (Census Bureau Geography), in other countries, such as in Germany, it is by far not easy, to even get such datat all.

In the following, I am collecting internet-sites, where one can find geographical data. I'd be happy to include more URLs, if you found a good one, to share.

Miscellaneous:

• Map Projection
  www.anychart.com/products/anychart/docs/users-guide/map-projections.html
  An overview over map projection types

Other:

Geocoding

The other option to show geospatial data in Reports, is to just use plain geometry/geography data. Here are some resources that help in getting access to such and/or converting/”geocoding” data:

The Google Geocoding API

developers.google.com/maps/documentation/geocoding/

Rob Farley shows how you can geocode you address-data using bing-maps and powershell:

sqlblog.com/blogs/rob_farley/archive/2010/05/23/fetching-latitude-and-longitude-co-ordinates-for-addresses-using-powershell.aspx

A nice walk-through in geocoding using SSIS and Google Geocoding API by Jeffrey Verheul:

devjef.wordpress.com/2013/02/05/enriching-your-dataset-the-ssis-way/

Discussion on Mapping From City Name to Latitude and Longitude & Mapping From Latitude and Longitude to City with further links: stackoverflow.com/questions/23572/latitude-longitude-database

Further good articles concerning geographical data and SQL Server:

Creating your own SSRS map using Visio

blog.oraylis.de/2010/07/creating-your-own-ssrs-map-using-visio/

Using Visio and SSRS Map Reports for Store Layouts etc.

blog.oraylis.de/2010/06/using-visio-and-ssrs-map-reports-for-store-layouts-etc/

Using ESRI ShapeFiles with SSI

ssis-components.net/post/2010/04/11/ESRI-ShapeFiles-verarbeiten.aspx

For importing shape-files into SQL Server, see this blog-post at MSDN: blogs.msdn.com/seanboon/archive/2009/11/17/sql-server-2008-r2-map-tips-how-to-import-shapefiles-into-sql-server-and-aggregate-spatial-data.aspx

Achieve Spatial Data Support in SSIS:
www.sql-server-performance.com/articles/biz/spatial_data_support_ssis_p1.aspx

Bridge The Gap between Bing and Google Maps using SSIS
www.sql-server-performance.com/articles/biz/bing_google_maps_p1.aspx 

Heat Maps as Reports

prologika.com/CS/blogs/blog/archive/2009/08/30/heat-maps-as-reports.aspx

Stacia Misner shows in her blog, how to include your maps in the Map-Gallery for the Map-Builder-Wizard:

blog.datainspirations.com/2010/08/31/do-it-yourself-map-gallery-in-sql-server-2008-r2-reporting-services/

If you are working with Polygons & Multpolygons, you should know how to check and correct an instance of geometry data for validity with .STValid() and .MakeValid() ad Jeffrey Verheul is showing is his post here:

devjef.wordpress.com/2012/06/29/strange-behavior-of-spatial-data/

Have fun with Reporting Services 2008 R2

Andreas

Sarpedon Quality Lab

Auf der diesjährigen SQLCon in Mainz hielt ich die Session Anspruchsvollere Berichte mit Reporting Services 2008.

Link: http://it-republik.de/dotnet/sqlcon09/sessions/?tid=1259#session-10624

Themen waren:

Tablix

–Mehrere Gruppen auf einer Achse
–Static Header – wie geht das jetzt?
–Listen (gruppieren)

Charts

–Dynamisches Wachstum
–Trendlines einbauen, Pareto-Charts
–Multiple Charts

Gauges

–Varianten und Customizing

Kombinationen

–Charts und Gauges innerhalb von Tablix (Microcharts)

Weitere „Angenehmlichkeiten“

–Felder in Page Header & Footer
–Placeholder

Hinweise auf Weitere interessante Neuerungen

Ausblick SQL Server 2008 R2

–Map Control
–Componentizing Reports

bis zum Nächsten mal,

Andreas Wolter

Sarpedon Quality Lab

Many still use SQL Server/Reporting Services 2005 although sometimes alread on Windows Server 2008 / Vista / Windows 7.

I absolutely do recommend upgrading to SQL Server 2008. There are many advantages in the combination - be it security or performance (Security, Performance, No need for IIS) - but if you can't uprade right now, you do need to install IIS for Reporting Services 2005 to run.

I have seen recommendations (even on a Microsoft blog) where they tell you to install EVERY Role Service for IIS - but that is untrue and against basic security principles.

I always always recommend "install as little as possible, but just as much as required".

You do not need an FTP-Server to run Reporting Services! - Natural to most. But when it comes to less known features like, CGI , SSI, Tracing..?.. most aren't so sure.

So here is the definite list of required role services for IIS. I tried to leave of everything I could, and this turned out:

I also attach a cmd-file. This file will install all the required packages by itself. You do not need to don any IIS Installation beforehand. It's using the new Package Manager available on Windows Server 2008. Just remove the .txt after you checked it out ;-

If You are using Windows Server 2008 R2/Windows 7 with IIS 7,5, Package Manager is deprecated. Instead use Deployment Image Servicing and Management as described here: http://blogs.msdn.com/b/habibh/archive/2009/08/14/how-to-install-iis-7-5-on-windows-7-using-the-command-line.aspx

I also prepared a file using DISM, which you can download here.

For Some reason though the Packetmanager installs "Directory Browsing, which is NOT required (bull***) - you should remove it manually. This seems to be a bug with Package Manager.

At the end it should look like this:

 IIS will be properly detected (and we know for sure, that "Directory Browsing" is not a requirement"):

There is still something however:

After you installed and navigate to http://YourServername/Reports

You will get an error: "unable to connect to remote server"

when checking the Logfile  "ReportServerWebApp" it says:

w3wp!ui!7!22.10.2008-10:24:47:: e ERROR: Unable to connect to the remote server
w3wp!ui!7!22.10.2008-10:24:47:: e ERROR: HTTP status code --> 500
-------Details--------
System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it 127.0.0.1:443
   at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
   at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
   at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
   --- End of inner exception stack trace ---

Solution:

In IIS 7 Manager, highlight the ReportServer application (Not "Reports")

go to Handler Mappings, click "Edit Feature Permissions" in the "Actions Pane", and enable "Script and Execute".

You are all set - it should be running just fine now.

Everything described here also applies for running Reporting Services 2005 on Windows Vista (SP1).

Download to IIS 7 Setup-File using Package Manager (rename to .cmd or .bat)

Download to IIS 7.5 Setup-File using DISM (rename to .cmd or .bat)

I also found a nice explaination of the IIS-Setup here: http://learn.iis.net/page.aspx/130/understanding-setup-in-iis-7/  

Andreas Wolter Training & Consulting