split().GetValue()

The GetValue() method of the Split() function is cool.
E.g.
If the Version number is 5.4.6. I can get the release number by using:
Split(Fields!Version.Value, ".").GetValue(0)
and iteration number by using:
Split(Fields!Version.Value, ".").GetValue(1)

This is very neat.

Adding Line Feed in A Text Box

If you need to have multiple lines in a SSRS text box, VbCrLf is what you need.

Example:
="adbc" + VbCrLf + "xyz"
Result:
adbc
xyz

More details at:
http://stackoverflow.com/questions/26567/how-do-i-set-a-textbox-to-multi-line-in-ssrs

Solving Blockings with Dirty Read

A very nice article about dirty read.
http://blogs.neudesic.com/blogs/phil_scott/archive/2005/12/05/11.aspx

Linked Server Name Trick

I created a report that pulls data from Oracle databases via OpenQuery() agained SQL Server linked server. I initially set the linked server name with periods (.), i.e. Environment.Schemaname.abc. Then my SQL query failed because OpenQuery() doesn't like period in the linked server name. After replacing the (.) with (_) everying worked just fine.

Strang Right() Function Behavior

I have a query that has been working for 6 months. All of a sudden it started giving me the error "Invalid length parameter passed to the RIGHT function". That query contains serveral select queries that are UNIONed together. A RIGHT() function is in one of those select queries. When I ran the select queries seperately, they all worked fine. But when I ran them together with the UNION, I got the error. This is very confusing.

The error went away after I put additional filtered in the query with Right() function. The additional filter is not necessary. But it got rid of the error.

My theory is that when SQL server parses the query, the Right() was verified against data that will be filtered out by the WHERE clause. I hope there is a way to verify that.

Getting Percentage Over Subtotal in Matrix

I worked on a matrix report that needs percentage of each row against the subtotal of the second row group. There are 3 row groups. I looked around and tried different things like InScope(), Sum() with scope, etc. Nothing worked.

The problem was solved by a work around. So instead of trying to get the subtotal from the dataset, I used the ReportItems! Expression to get the subtotal directly from the subtotal cell. This can only be done in SSRS 2008 because in SSRS 2005, the subtotal field is not exposed. Here is the expression used to get the percentage:

=Iif(reportitems!txtEnvironmentSubTotal.Value is nothing, nothing, Format(SUM(Fields!Test_Check_Count.Value)/reportitems!txtEnvironmentSubTotal.Value, "0.0%"))
-- Ke

Data Warehouse Resources

A good article about general Data Warehouse concepts:
http://www.gantthead.com/content/processes/9076.cfm