Browsed by
Author: Erik Monchen

Using SSAS dimension data with different labels

Using SSAS dimension data with different labels

In relation to my article about using date pickers to filter SSRS cube data, I have been asked a few times how it would be possible to use one set of parameters to select the date range that points to dimension data from different cubes.
For example, I have a report that pulls data from two different cubes. In the one cube, the main time dimension is called “Date”, and in the other cube there is a time dimension that is calle “Value Date”.

Basically there are two ways to accomplish this. The first one is exactly how I described in my previous post about this topic. For each date dimension you would have to create two extra parameters and use the expression as I mentioned. In the example above, you would have to create four parameters:

Parameter: DateFrom
Default value: =”[Date].[Date].&[” & Format(Parameters!FromDatePicker.Value, “yyyy-MM-dd”) & “T00:00:00]”

Parameter: DateTo
Default value: =”[Date].[Date].&[” & Format(Parameters!ToDatePicker.Value, “yyyy-MM-dd”) & “T00:00:00]”

Parameter: ValueDateFrom
Default value: =”[Value Date].[Date].&[” & Format(Parameters!FromDatePicker.Value, “yyyy-MM-dd”) & “T00:00:00]”

Parameter: ValueDateTo
Default value: =”[Value Date].[Date].&[” & Format(Parameters!ToDatePicker.Value, “yyyy-MM-dd”) & “T00:00:00]”

The second way (I don’t particularly see on beneficial way) is to use the Replace-function. As you can see above, the difference between the “DateFrom” parameter and the “ValueDateFrom” parameter is only in the first couple of characters. As a default value for the “ValueDateFrom” parameter, I also could have put this:

=Replace(Parameters!DateFrom.Value, “[Date].[Date]”, “[Value Date].[Date]”)

One little side note: When you add the date dimensions from different cubes in your report and let SSRS just create the parameters for you, you don’t have to create those parameters additionally. Make sure though that in the above example, there would have been two datasets for the available values for the ValueDateFrom and ValueDateTo parameters. Since we don’t see these parameters in the report anyway, you can set the available values to None and delete the datasets.

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…

SSRS: Using date pickers to filter SSAS cube data

SSRS: Using date pickers to filter SSAS cube data

When you have a report that pulls data from an SSAS cube, you have most likely used a date range filter on which time series you would like to display. It is not so straightforward though, to create date-parameters in SSRS and in your MDX query you refer to these queries. Because MDX needs to interpret the date values in a completely different syntax, we have to format the data. This post shows you how to accomplish this. This post does not describe how you should format the MDX to make this work, I assume that you have your dataset ready to go to use in your report.

The first step is to create two dimensions for the date range. The types of these parameters is “Date/Time”. There are no specific other settings for these parameters, as these are the date pickers that the end user can use to select a date range. So when you start the report, you will only have these parameters:


The next thing to do is create parameters that contain the formatted values that the MDX query can read. To accomplish this, create two more parameters called “FromDate” and “ToDate”, of type “Text”, and set the visibility on “Internal” (this causes the parameter to be hidden, but the values will change when the selection of other parameters change). On the “Available Values” tab, select “None”. On the “Default Values”, the following expression should be used:

=”[Order Date].[Date].&[” & Format(Parameters!FromDate.Value, “yyyy-MM-dd”) & “T00:00:00]”

The second parameter should contain this expression as the default value:

=”[Order Date].[Date].&[” & Format(Parameters!ToDate.Value, “yyyy-MM-dd”) & “T00:00:00]”

When you run the report now, you will see that the selected dates will be applied to your dataset. If not, you can always set the last two parameters to “Visible” and run the report. That way you will see how these parameters are formatted. Most of the errors that I got is that the descriptions of the cube dimensions were not completely correct.

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!

Various SQL date formats to use with SSIS, SSAS and/or SSRS

Various SQL date formats to use with SSIS, SSAS and/or SSRS

While designing data warehouses, SSIS packages, etc., like myself you’ve probably ran into the issue of having to format different date and time values that ends up in a long string of SQL code or expressions in SSIS. The purpose of this post is to collect some links to various pages where these kind of date and time formatting can be found.

Getting only date part of a datetime field

First of all, the function that I used the most is when I’m trying to get only the date part of a datetime field type. For example, when I’m looking at a sales order fact table, an order could have been placed at August 15th 2011 at 9:15am. This date would be stored as 2011-08-15 09:15:00:000. The issue here, is that it’s difficult to design your SSAS cube and use this field as a link to your time dimension table, because the time dimension table usually holds the dates only. Therefore, the following function could be used:


(thanks to Ben Nadel)

When you’re using SQL Server 2008 and above, you can also use the following function that is a lot easier to use. Since the date and time fields were introduced in SQL Server 2008, this won’t work in 2005 and earlier versions:


Date formats

Some simple date formats can be found here. These are just some standard SQL date formats but these could help you tremendously to avoid having to concatenate several strings together.

Calculations: First and last day of the month

You might have ran into the issue of calculating dates in relation to a certain input date. This page shows you how to get the first or last day of the month in relation to a certain date.

Calculations: First and last day of the year

Just as calculating the first and last day of the month, you can see here how you can calculate the first and last day of the year.

Standard functions

Other than the ones above, if you are looking for a calculation in terms of number of days, weeks, months, years, etc., the standard DATEADD and DATEDIFF functions in SQL would do the trick.