Alternating colors in groups in Microsoft SQL Server Reporting Services

In this post I will present you a one of the ways of defining alternating row colors in group details. Recently I had to apply such thing in one of the projects I am working on and as it turns out, there are quite a lot instructions, guides, advices, etc. So I will try to combine and evaluate them as there are some flaws in some of them. First of all, see the end result I am trying to achieve:

1First row is the text field which I am grouping on, the second filed is the ID from the source dataset and the third row is the color, that the row must be.

1. Applying alternating color on detail rows of group using RunningValue() and CountDistinct()

First method I tried was using RunningValue() with the following syntax:

=IIF(RunningValue((Fields!DatasetColumn.Value), CountDistinct, Nothing) Mod 2, "White", "WhiteSmoke")

This is a very good method but it has one major flaw – when grouping over a text field, which contains data differing only in letter case, then those values are in a single group, but the RunningCount in combination with CountDistinct returns values, that are messing up the coloring. What do I mean – in our example if I use this method for coloring the result is the following:

2 

As it turned out (and as I mentioned before), when Reporting Services are grouping text fields, it does not matter what the letter case is. I could not find any reference in MSDN on the algorithm of grouping so it is kind of a mystery to be revealed.

2. Applying alternating color on detail rows of group using RunningValue() and Sum

The next method I tried was using Sum and Running Value. The syntax is as follows:

=IIf(RunningValue(1, Sum, "Group_Name") Mod 2, "White", "WhiteSmoke")

Unfortunately the result was the same

3. Applying alternating color on detail rows of group using RowNumber()

This is most probably the most universal function that will help you on applying alternating row colors. Of course you must be very careful when specifying the scope over which the RowNumber() function will “act” The syntax is:

=IIF(RowNumber(GroupName) Mod 2, "White", "WhiteSmoke")

And this did the trick.

So if you ever need to apply alternating colors, have in mind that you can do it over group details just as easy as doing it on the whole dataset!

Data Warehousing 2.0 and SQL Server: Architecture and Vision

If you are interested in the subject of datawarehousing and BI, you can check Microsoft’s vision on Datawarehouse 2.0.
 
I have to say that I am impressed with what is to come with R2 of SQL Server 2008 and already started planning to perform the upgrade.
 

Transferring data between Oracle and Microsoft SQL Server 2008 x64

Yesterday I had a task to transfer some data from Microsoft SQL Server 2008 x64 running on MS Server 2008 and at first I thought it will be a simple job. After all I thought it will not bi that big of a deal – what could go wrong with transferring 100K rows from one server to another (despite there are two different database engines). Before I tell you the whole story let me clear my predefined requirements for getting the job done:

  1. The method I use should be able to be integrated to Integration services package
  2. The transfer must not take more than minute per 10K rows
  3. The transfer must be initiated from the SQL server side with SSIS package, which means on 64-bit platform

The list is as simple as that. So my struggle began. First I tried with the simple INSERT INTO <linked Oracle Destination>. As easy that method was it was awfully slow – 10K rows were transferred for approximately 4 minutes, which of course did not meet my second requirement.

My second try was with SSIS using OLE DB Destination with the Microsoft OLE DB Driver for Oracle. NEVER DO THAT on a x64 platform. Business Intelligence studio is a 32-bit application and when you try to start the package it says there is an error with initializing the OLE DB driver. After a ton of reading I discovered that there is a collision between the 32-bit BIDS and 64-bit driver (of course there will be, my surprise was that actually there is no way using Microsoft’s driver in developing packages on x64 platform. By the way the same applies and for .NET Driver for Oracle.

So it was time for my next approach – I installed the OLE DB driver from Oracle. Well my first try was unfortunate – I received an error – TNS:could not resolve the connect identifier specified. This error is quite misleading! It took me a great deal of time to find out that I have fallen in the x64 pit again :( Anyway, after a quick search, I found a quick workaround for that case – you must install both x86 and x64 Oracle Clients and then create a simple softlink, which to be added in the environment variable PATH. Detailed description on how to do that, you can find here. This was my first break-through in finding another solution, besides the one with the linked server. I did a simple test with BIDS and 10K rows transferred for over 7 minutes. Complete disaster!

Then I figured if BIDS is a 32-bit application, why I do not use 64-bit application to create the package for transfer, SSMS for example. After all when you use Export and Import Wizard, you can save the package directly and the just execute it (with dtexec utility, of course :-) ). It was that easy…but again very slow. I used Oracle OLE DB driver, Microsoft’s OLE DB for Oracle driver, Microsoft’s .NET provider for Oracle, etc. I could not get the data to be transferred in less than 6 and a half minutes.

After trying so many methods and all of them so exhausting till I get to the point of performance testing them, I was on the verge of quitting (and approach with the standard file transfer to ftp, as the only secure and working way).

My last try was after doing a lot of reading and searching the web a method I have not tried yet. I have to say there are not many articles on the subject with transferring data from and to Oracle from x64 platform running Microsoft OS and Microsoft SQL Server. However, there is one product that save the day – Microsoft Connectors for Oracle and Teradata by Attunity (download it from here). Instruction on how to use it and a description of the whole product – here. But be careful to install the correct package (x86,x64 or IA), because you will find yourself searching the web and asking in forums for misleading errors like This occurs when CPackage::LoadFromXML fails. Anyway – I build my package and it was time for testing – I was pleasantly surprised, because for 10K rows it took less than a second to insert!

In conclusion if you want to build SSIS package on a x64 platform and transfer data to Oracle DB without additional overhead and slow performance, one of the best ways is to use the Attunity driver!

Integrating maps in reporting services reports

 

As promised in my previous post, now I will describe the basic steps for integrating a map into a simple sales report. Again I am using Report Builder 3 as maps are available only there (unless you are not using some third party software or other functionalities). So before we start, there is a serious prep to be done. There are three options when it comes to choosing map source for the report. First you can take advantage of some preinstalled maps in a gallery, which provide the actual spatial information that to be matched against the report data (for example to match states by their names). The second option you have is to choose and ESRI shapefile which to integrate into your report (information on ESRI shapefile standard you can find here). The third option (which we will use in our case) is to use spatial information from SQL Server query and to lay it out on a cool map from Microsoft’s Bing.

So, as I mentioned, I will shortly describe option three, just to show you the cool stuff you can do with Report Builder. As we are using SQL Server as source for our spatial information, our query for the dataset must have a column, that contains spatial data. My query is a very simple one, containing two columns – one with aggregated value and one with geography data (how I got to geography data, you can check in my previous post here). After you got the query and sorted out the part with the spatial data, you can create a new report following next few easy steps:

1. Start a report with new Map Wizard

3_1

2. Select the third option for source of spatial data (of course after that step you should define the data source and the query for the report dataset)

3_2

3. The MAP – here are some interesting things worth mentioning. First of all there is the screen:

3_3

In the upper left part you will see the drop down box where you should choose the filed from your dataset, that contains spatial data. Usually the wizard finds it itself, so just make sure you have it :).

Then on the upper right part you will see the layer type. In our case we are using Point layer type and for the rest I will provide additional info in one of my future posts.

Now on the bottom part is the interesting stuff! There you can check the option for adding a Bing map which will automatically display a map from Microsoft Bing. On the top of that, you can choose from three types of map display – Road, Aerial or hybrid.

4. Choose a map visualization – I will use bubble map which allows me quickly to configure my map pointers to be shaped as circle and depending on the value of my aggregated field, to change its size (the bigger the aggregate, the bigger is the circle)

3_4

5. Configuring map pointers, theme and visualization

3_5

In the data field you should choose the field from the dataset that will be used for determining how big the bubble will be. In our case – the aggregated field from the dataset. Of course there are few other quick customizations possible, but for the purpose of my demonstration I will try not to get carried away :-).

When you are done, just click Finish and the map layer will be displayed in Report Builder 3 where you can play with some other customization (adding additional map layers, different pointers, remove some legends, change titles, etc.).

Now there is a screenshot of my final report (after playing with some properties myself)

3_6

I will continue exploring the world of map integration into reports and share my experience so stay tuned ;-)

My first struggle with geospatial data in MS SQL Server 2008

Couple of days ago I decided that it was time to finally introduce myself with this cool new feature of Report Builder 3.0 for integrating maps in reports. I figured that first I should get some spatial data available in my database so I can actually report on it, because the one that is in Microsoft’s sample databases is not doing much help to me (I am located in Bulgaria after all and wanted to produce a sample report that is over a “known territory”). So my struggle began.

Today was the third day I was looking for any geographical data regarding Bulgarian cities and it seemed that my search was doomed. However, just an hour ago, I found some data in the web site of USA’s National Geospatial-Intelligence Agency (http://earth-info.nga.mil/gns/html/cntry_files.html). It seemed that there is hope after all :-). I downloaded the file with data for Bulgaria and imported it into my MSSQL instance. Of course the data there was not directly converted into spatial data but contained the longitude and latitude of all cities in my country. So I had to create a new column with data type GEOGRAPHY and use a very simple function to convert longitude and latitude to POINT:

<em><font color="#0000ff">UPDATE [BulgarianCities]
SET [GeoSpatialLocation] = geography::STPointFromText('POINT(' + CAST([LONG] AS VARCHAR(20)) + ' ' + 
                    CAST([LAT] AS VARCHAR(20)) + ')', 4326)
</font></em>

For additional reference for STPointFromText you can go here.

So I finally had everything needed in order to proceed with my first report with integrated map. How it turned out, you can find in one of my next posts.

How to use Indicators and Data Bars in Report Builder 3

In the next few rows I will give a simple example of using two new elements in Microsoft’s Report Builder 3 – Indicators and Data Bars.

As it is still February and SQL Server 2008 R2 has not reached its release version, I am using the last CTP from November 2009. The database is AdventureWorksDW2008 (you can get it from here).

Let me first start with a screenshot of the final report:

clip_image001

Steps:

1. Start Report Builder 3 November CTP. Proceed with selecting a new report -> Table or Matrix Wizard

clip_image003

2. Continue with setting up the datasource. Just make sure to use AdventureWorksDW2008 or AdventureWorksDW.

3. After setting up the datasource, you will be redirected to the screen for adding new dataset. There you have two options – either using the graphical designer or using a simple text editor. You can use the following statement:

SELECT (de.LastName) + ‘, ‘ + (de.FirstName) as FullName,frs.SalesAmount

FROM FactResellerSales frs

JOIN DimEmployee de

ON de.EmployeeKey = frs.EmployeeKey

clip_image005

4. On the next screen we will define the row grouping and values to aggregate (grouping on FullName column, aggregations on SalesAmount):

clip_image007

5. Choose a layout and color scheme for your report and return to the design view of Report Builder with a raw version of the report. Then add two new columns on the right side of Sales Amount column – one for placing data bars, one for indicators:

clip_image009

6. Add a new data bar element by selecting it from the Data Visualizations ribbon and place it directly in the cell where the data bar to be displayed (in our case – just on the right of Sales Amount value column). When you place it there a small screen will appear where to select the type of data bar. I used a simple data bar.

clip_image011

7. Then you must choose the aggregation that will measure how “long” the data bar to be (by double clicking on the data bar cell a Chart Data window is opened and from the green plus sign you choose the aggregation field).

clip_image013

8. The same goes and with the indicator.

clip_image015

Well that was a simple demonstration on how to use some of the new features in MS SQL Server 2008 R2. Of course you can do a lot more with those elements, use different type of data bars, indicators, integrate labels, values, etc.