SQL Reporting Services (SSRS) is a great tool offering lots of features and ability to customize the report appearance apart from addressing complex business reporting needs. Above all SSRS is one of the components which comes free when you buy SQL Server :-)

Often in Reporting and Dashboarding projects customization/look and feel becomes one of the important aspects of the project. SQL Server Reporting Services offers various options to customize the look and feel of the report to make the report more Jazzy and Visually Appealing.

One of the most powerful components of SSRS is the ability to control various elements/properties of a report with the use of expressions. One of the properties which is usually set in tabular reports especially in case of really long reports with hundreds or thousands of rows is to set different colors to alternate rows.

To set different color (two different colors) to alternate rows, follow these steps:

• Select the Data Row of the table in the report
• Go to Properties Window
• Under the BackgroundColor property, select expression from the drop down
• Enter the expression "=IIf(RowNumber(Nothing) Mod 2 = 0, "Color1", "Color2")" in the Expression Window.
• In the above expression replace Color1 & Color2 with either the Color Code/Color Name as appropriate.

Below is a snapshot of a sample SSRS report with two different colors for alternate rows.

 















This customization is very useful in case of long running reports with hundreds/thousands of records having many columns where in tracking the data in one single row accross all the fields becomes a little difficult/confusing if we dont use seperate colors for alternate rows.

I was working on one of the requirements for a client where in the application owned by the client had a set of values in report column. The requirement was to make each of the values in that column a URL, on clicking this URL the value gets passed as a parameter to another report and the report is rendered without having to manually select/enter the parameter.

I had implemented a similar requirement in SSRS 2005 for one of the previous clients. In this current requirement which I am talking about the implementation was in SSRS 2008, which from this requirement standpoint does not make any difference.

I knew that we need to concatenate the value of the parameter to the URL of the report and this will do the job. So I took the report URL from the Report Manager and tried to concatenate the parameter but ended up in failure.

To resolve this I tried searching the web and found one article (http://www.mssqltips.com/tip.asp?tip=1336) which explained about various options/features which can be implemented through the report URL in SSRS.

While going through this post I found one very interesting thing and that is we need to concatenate the parameter value to the Report Server URL of the Report and not the Report Manager URL of the Report.

Many times we end up implementing the things in a straight forward fashion and hence dont come accross this kind of peculiar situations. However sometimes when we try doing the same thing differently, we end up meeting with this kind of peculiar findings which are worth treasuring in ones knowledge base.

Trying to implement something in different ways is worth it since we either end up finding an alternate/better/worst option which can be used to justify the best among the solutions identified or end up finding this kind of scenarios which have only one way to implement.

Happy Reporting :-)

Fiddler is Web Debugging Proxy which logs all the HTTP(S) traffic between your computer and the internet/other computers.

In many scenarios many applications need to interact with each other by passing information back & forth between each other to ensure successful handshaking. In these scenarios debugging becomes a challenge and hence first isolating the issue to a particular application/server is very essential.

This is very useful tool especially while debugging such issues which involve identifying the exact query strings that are being passed to the remote computer/internet from your computer during the process of handshaking. This tool can help us in isolating the issue there by reducing the scope of analysis.

Visit this site to check out the features and to download the tool:

 






http://www.fiddler2.com/fiddler2/

In one of my previour projects for one of the reporting requirements I wanted to replicate the data from one database (where ETL process was carried out) to another database (reports can source the data from here).

To achieve this I had setup On Demand Transactional Replication. Initially it seemed to work fine for few tables, however all of a sudden I started getting an error saying:

"Length of LOB data (78862) to be replicated exceeds configured maximum 65536"

After searching on the web I came to know that SQL Server has a default value configured maximum length of text that can be replicated.

To get rid of this error run the following command in the SQL Server Management Studio (How to launch Management Studio from Run prompt):

EXEC sp_configure 'max text repl size', 2147483647
RECONFIGURE

Hope that helps!

We often find ourself searching the web for options to configure/customize the Connection Strings. I have found a very interesting and useful website which lists/explains various options for configuring/customizing the Connection Strings for various Data Sources.

No need to worry about which source database you are dealing with, almost all the popular data sources have been listed on this site. You name the data source and you will find it on this site. SQL Server, Oracle, DB2, Excel, Access, SharePoint and many more.

Snapshot of sample OLE DB Connection String for AdventureWorks database:

Here is the site which I am talking about:
http://www.connectionstrings.com/

Hope most of you will find this site useful.