Browsed by
Category: Uncategorised

Dashboard example

Dashboard example

Yesterday I attended and also spoke at SQL Saturday in Columbus Georgia. My presentation was all about effective dashboard design, tips and tricks how to make your dashboard efficient and some other things that you should definitely not do when designing dashboards.

Not even a day later I encountered a dashboard where I can back up two of my arguments that I included in my presentation. One of the arguments was that maps as a display medium are often used to make things look pretty and not because the geography is an important meaning of the data that is displayed. The second argument was that you don’t always have to make it pretty with images because displaying the most important things with just numbers is sufficient when it has a clear meaning. The dashboard that I encountered was the one that Delta Airlines offers when you start your internet browser (a similar page to an introduction page when you connect to a public hotspot where you have to pay for internet):

This dashboard has five indicators that displays nothing else but a few numbers, but these single numbers give me ALL the information that is relevant at that point of time. Also the map on the dashboard sends a clear message of where we are during our flight, so the geography dimension actually has a meaning to the message of the dashboard element.

Despite the fact that I told in my presentation that images should not be used in dashboard unless you really have to, in this case I understand that they have the link in the left bottom corner to suggest that I buy an internet pass. Thankfully that link is in a very subtle location on the dashboard and not at a place that is highly emphasized.

Personally I don’t care about the weather widget but hey, it’s in the most de-emphasized spot on the dashboard.

Time to prepare for landing…

Not having to delete the data file every time you run a report

Not having to delete the data file every time you run a report

In SQL Server Reporting Services, every time you preview a report, a file of type .data is stored in your project folder that caches the data when the report is run. When you have your data sets all set up and you’re only working on the layout of the report, this is a very good functionality because every time you preview your report, SSRS doesn’t have to pull the data from the source every time.

However, when your data sets change as well, you can get a very nasty error message:

Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index

Or maybe the data in your report does not change according to the changes that you made in your dataset. In that last case, you have to delete the .data file in your BIDS project folder and preview the report again.

If you want to make your report development life easier but then again, if you are ok with pulling the data from the source every time you preview your report, open your SSRS config file located in this location for SQL Server 2008:

C:Program Files (x86)Microsoft Visual Studio 9.0Common7IDEPrivateAssembliesRSReportDesigner.config

In this file, near the beginning of the code, you have to set the option “CacheDataForPreview” on True when you want to keep the .data files and on False when you don’t want to use these files:


Spatial data across country borders in SSRS

Spatial data across country borders in SSRS

SQL Server 2008 R2 has really nice features in Reporting Services where you can work with maps and for example plot markers on a map to get a geographical overview of whatever you like. There are many tutorials out there how to get started on this, but one of the things that I haven’t found much information about is about the following:

I had the situation where I wanted to show customers per country on a map. So I created a report with a parameter @CountryCode, where the user was able to select the country and all the customers where plotted on a map. The query looked like this:

SELECT CustomerId, Address, City, Country, GeographyCol
FROM Customers
AND Country = @CountryCode

The end result looked like this:

It works like expected, but one slight thing came back from the users that they would like to see as well. For example on the map above, all the customers were plotted on there but as you can see, a big part (or at least the biggest populated part) of Canada is visible on the map as well, but since I filtered the query on the United States only, no data was plotted for Canada.

To solve this issue, I changed the report like this:

– First of all, I got rid of the filter that limits the customer data for the country. This is actually the major disadvantage of this method, that regardless on which country or zoom level you are viewing this report, all data have to be extracted when the report is loaded.

– I added two parameters to select the continent and country. You are completely flexible in how to do this, but to avoid having one big parameter with a list of countries, I created a cascading parameter for the continent and country. The country selection parameter is only used for which country the report will be zoomed in to.

– Coordinates in a country table. In a separate country table, a prerequisite is to have the country coordinates stored. I have used the coordinates from this site (S-latitudes and W-longitudes have to be stored as a negative number).

– I added a few parameters:
CountryLat (type internal): This will be automatically filled through the selected country
CountryLng (type internal): This will be automatically filled through the selected country
ZoomLevel: Since every country is not the same size, you might want to be flexible in the zoom level of the report.

– Extra dataset: The query to fetch the countries needs to include the latitude and longitude. My query looks like this:

SELECT     Latitude, Longitude
FROM         Country
WHERE     (CountryCode IN (@Country))

– In the default values for the CountryLat and CountryLng parameters, link to the dataset created in the previous point.

So a little recap now… at this point, I have three extra parameters and an extra dataset to fill these parameters. I have chosen to make the ZoomLevel parameter a regular parameter since I would like the users to select the zoom level themselves.

The next step is to set some values in the map object of the report. Right-click somewhere on the map and select “Viewport Properties”. Make sure you select the same values as the screenshot below:

For the minimum and maximum values, select the expression button and use the following expressions:

Minimum Longitude:
=Parameters!CountryLng.Value – Parameters!ZoomLevel.Value

Maximum Longitude:
=Parameters!CountryLng.Value + Parameters!ZoomLevel.Value

Minimum Latitude:
=Parameters!CountryLat.Value – Parameters!ZoomLevel.Value

Maximum Latitude:
=Parameters!CountryLat.Value + Parameters!ZoomLevel.Value

Basically what you’re setting here is that you define your boundaries of how you view the map. Each value is one corner of the rectangle that you will display and the zoom level determines how many degrees from your center point is visible on the map.

The second important setting that you have to set is in the tab “Center and Zoom”. Set these values according to the screenshot below:

Click OK and run the report again (don’t forget to remove the @CountryCode parameter from your original query!). The report should be extended with all the customers around the US as well. The North-East region in my example looks like this now:

So this method is a great way to not limit the markers on a map to a specific country, but keep in mind that every time this report is executed, all data is retrieved and not filtered to the country that you select.

SQL Server 2012 RC0 installation

SQL Server 2012 RC0 installation

With the announcement that Microsoft has released SQL Server 2012 RC0, I thought at first I’d skip installing that, because right now I have a virtual machine running CTP3, and I can remember it took quite a while to set that up. I was very delighted to hear that RC0 does not need to be installed as a brand new fresh reinstall! When you have CTP3 installed, you can simply upgrade the version.

At the setup screen, click on the installation link and then choose the “Upgrade… ” link:

Simply huh? Well, with my CTP3 installation it went through the installation process just fine, there is one issue that I’ve read about to take care of, when you want to add a user to the Distributed Relay Controller. A solution for this is to leave this for later, you can read here how to add the user(s) afterwards.

.NET Framework 3.5 for Report Builder when you have 4 installed

.NET Framework 3.5 for Report Builder when you have 4 installed

I wanted to use the Report Builder of my SQL Server 2008 R2 installation, and when I clicked on the button “Report Builder” in my Report Manager, I got this message:

To use Report Builder, you must install .Net Framework 3.5 on this computer

So first of all, I checked which version of the .NET Framework I had installed, and it turned out to be version 4 already!
When you get this message, here’s what you need to do:
  1. In your IE browser, go to the little cogwheel on the right top corner and select F12 Developer Tools
  2. On the bottom of your screen or in a new screen, you should get a code window. Click on the menu Browser Mode: IE9 and change this to Internet Explorer 8.
  3. Click on the button Report Builder again. If you haven’t installed the Report Builder yet, it should start installing, otherwise it starts the Report Builder right away.
PASS Summit 2011 – A first timer’s experience

PASS Summit 2011 – A first timer’s experience

From October 11th to the 14th, the yearly PASS Summit took place for the second last time in Seattle (it’s going to be in Charlotte NC as of 2013). I had never attended this conference, and initially I planned to go to SQL Bits or SQL Pass Camp. I looked at the itinerary of SQL Bits, and I didn’t really see a lot of interesting sessions that could fill two days, but at the schedule for PASS Summit, there were tons of things that I could fill the week with. So I looked for someone who could answer me the question if it’s worth buying a plane ticket that’s about as expensive as the registration fee. It happened to be Denny Cherry’s blog that I founded first, and he definitely recommended me to go, and he also mentioned that if I was going anyway, I might wanted to go to SQL Saturday in Portland as well.

So on Friday October 7th I was on the plane to Portland. I was able to submit a session to speak at SQL Saturday, so I was very excited to do that! On Friday night there was a speaker’s dinner so I got to know a bunch of people already. The next day was SQL Saturday and the day was fully booked with sessions. Afterwards there was dinner and drinks at Duff’s Garage.

On Monday I took the train from Portland to Seattle which was only a 3,5 hour train ride. During the trip I got myself set up with Twitter on my tablet and phone, to have all the right hashtags and columns in my Tweetdeck ready for the week. Already it was a madhouse with the tweets that was going on, that was great. The rest of the Monday was basically getting myself sorted and the registration at the convention center.

Tuesday started late since I didn’t book the pre-conference, but I did have the networking session with Don Gabor. This was a great session about…yes…networking. After that there was the speed-networking session also by Don Gabor, and this was totally new for me but really great! The conference day ended with a very warm welcome for the first timers and a buffet and drinks.

Wednesday to Friday was pretty much all about the planned sessions. There were a few time slots that I picked out where I didn’t have a complete match with the schedule and my interests, and that was the perfect time to check out the exhibition hall, the hands-on lab, the expert pods, and above all, just talking to fellow PASS people who are walking around there.

At night, there was just too much to do it all. I went to a few organized events, but on other occasions it was just a bunch of people going from place to place.

In conclusion, I am really glad that I went, I had a fantastic time with learning a lot and getting to know a lot of nice people. If it was just up to me, I would have registered right away for the PASS Summit 2012! I think a lot of people share the sad feeling that it’s over, but blessed to be part of this community that you can call your #sqlfamily!