Area Chart - Need to Show Empty Area instead of Zero

It's been awhile since I created my last report. Recently, I had to pick this up and create a burn down area chart. The remaining work data is set to something like "=iif(fields!date <= today(), sum(fields!remainingwork.value), nothing). I was expecting to see a nice cut off line of the burn down chart on the current day, where future days is blank. However, the report zeros the data on the next date and the chart ended up with a sharp line from the remaining work of the current day down to zero on the next day. After some digging around and playing around, I found out that this issue can be fixed by simply setting the EmptyPoint\Color to "No Color". Here are the steps: 1. Select the data series on the chart design section. 2. On the properties panel, expand the EmptyPoint node and set the color to "No Color". Simple as that.

Alternating Background Color by Groups

=IIF(RunningValue(Fields!Biz_Story_ID.Value,COUNTDISTINCT,NOTHING) MOD 2 = 0,"PaleGreen","White")

How to Pass Multi-Valued Parameter to SSRS Sub Reports

I'm working on a report that contains about 10 subreports. Some of the subreports need multi-valued parameter passed from the main report. After struggling with it for awhile I came across a good post that explains three different ways to do that. All three worked from me.
http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/ae5ac615-1076-428d-9fcf-6c27e5fee1f0/

An Interesting Report Caching Issue

I was working on caching a report and encountered an issue. It simply didn’t work. All caching settings were tried. But it just doesn’t cache the damn report. I looked around on the Internet and no one seemed to have the same issue. One unique thing about this report is a big multi-valued parameter called Feature. The drop down has 150 – 200 entries depending on the selection of other parameters.

The next thing I did was looking into the [ExecutionLog2] view in the ReportServer database. I compared the parameters used in two different report runs. And I noticed that on the big list of features, one feature was converted to lower case (test feature) while the other report run has the same feature in regular cases (Test Feature). The data set that generates the feature list has the feature listed as Test Feature. And the lower case issue is on different feature everytime. I don’t know why SSRS converts it into all lower case. But because of that, it thinks these two report runs contain different parameter selections. Therefore it is not cached.

To fix that, I did a upper() on the query that generates the feature list and the query that generates the final report data. The report now takes a little longer to run. But the caching issue is fixed.

A good article I found when I troubleshoot the issue is at:
http://blogs.technet.com/b/rob/archive/2010/02/11/caching-ssrs-reports-for-performance.aspx

SSRS Report Caching

I came across a good article that talks about report caching.
http://blogs.technet.com/b/rob/archive/2010/02/11/caching-ssrs-reports-for-performance.aspx

To repeat rows with column headings for a table with row groups

To repeat rows with column headings for a table with row groups
________________________________________
1. In Design view, select the table. The Grouping pane displays the row groups.
2. On right side of the Grouping pane, click the down arrow, and then click Advanced. The Grouping pane displays static and dynamic tablix members for each group. You can only set properties on a static tablix member.
3. In the Row Groups pane, click the static tablix member for the row that you want to repeat. When you select a static tablix member, the corresponding cell on the design surface is selected, if there is one. The Properties pane displays the properties for the selected tablix member.
4. Set the KeepWithGroup property in the following way:
o For a static row that is above a group, click After.
o For a static row that is below a group, click Before.
5. Set the RepeatOnNewPage property to True.
6. Preview the report. If possible, the row repeats with the group on each vertical page that the row group spans.
The post is at:
http://msdn.microsoft.com/en-us/library/cc627566.aspx

Merged colums in Excel exporting

I ran into a situation today that when I export a report to Excel there are unwanted merged columnns. After some digging I came across this post. It was very helpful.

http://blogs.msdn.com/chrisbal/archive/2006/07/08/659545.aspx

Using Custom Assembly in the Report

I was working on a report that requires getting information from a txt file. To do that, I planed to use the custom assembly reference feature in SSRS 2008. And I found a realy simple but useful post that walks through how to get thing setup(http://geekswithblogs.net/shervin/archive/2008/04/28/121712.aspx).

--Update--
1. The rssvPolicy.config file is not located in the Bin folder but one level up. The above post was wrong.
2. Here is a better post.
http://www.c-sharpcorner.com/UploadFile/balajiintel/CustomAssemblyinRS06302005081435AM/CustomAssemblyinRS.aspx

So I followed the steps in the post and create a Class Library C# solution. The custom assembly worked fine within its own solution. But after I copied the dll to the Report Designer bin folder and granted FullTrust access in the rssvPolicy.config file, the report couldn't find the file. This was frastrating because there is no error or exception that provides more information for debugging. After hours of digging on the Internet with no progress, I created a VB code snippet that does the same thing as the C# custom assembly. This time I embed the code in the report, hoping to get some different results. And indeed, I got an error:
********************
Build complete -- 0 errors, 0 warnings
[rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox1.Paragraphs[0].TextRuns[0]’ contains an error: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
Preview complete -- 0 errors, 1 warnings
********************

The above error led me to another Microsoft post (http://support.microsoft.com/kb/842419), which solved my problem.

Solution:
Add the following code to the class
********************
FileIOPermission filePerm = new FileIOPermission(FileIOPermissionAccess.Read, "C:\TestFile[Put in the actual location of the file.]");
filePerm.Assert();
********************

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

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

Hide Duplicates Property

The Hide Duplicates property comes in really handy after I struggled on how to format the table to hide the duplicate cells. And its ability of formatting each column separately works perfectly.

Take Advantage of the Calcuated Field

Get most of the detail level calculation done using the Calcuated Field helps simplify the Layout design.

Fixing The Custom MDX Lost in Report Designer Problem

Recently I encountered a problem with the SSRS 2005 Report Designer. When I opened a report with custom MDX code and go the data tab. The dataset view screen automatically went to the design view and wiped out the custom MDX code. The problem started occuring when I uninstalled SQL 2005 Express edition and installed the Enterprise edition. I found a post on the Internet that talks about similar situation. (http://social.technet.microsoft.com/Forums/en-US/sqlanalysisservices/thread/fca63464-cffb-4208-99e4-c0a53c3686a0/)

Because of the issue mentioned in the post, the Report Designer couldn't parse the MDX code. It automatically went back to the design mode when that happends because that's the "Previouse State" of the data set.

Installing SQL Server 2005 SP2 fixed this problem.

TFS Reports

A good resource about SSRS and SSAS on TFS.
http://weblogs.asp.net/vblasberg/archive/2008/06/04/tfs-reporting-architecture-notes.aspx