Browsed by
Month: February 2011

Use of the field ListLevel in SSRS

Use of the field ListLevel in SSRS

In SQL Server Reporting Services, there is the property “ListLevel” that you can use in your tables to add some padding to the different groups that you have created in your tablix. It’s nice to have that feature available, but I use other methods because of these reasons:

  • You have no control over how much space is added when the text is indented.
  • If you have several list levels and also multiple levels in your tablix that you can expand and collapse, the plus and minus icon will stay in the far left of the tablix.
Because of these reasons, I use the padding option of every cell. It lets me control the padding exactly how much I want to have the text indented.
Avoid SSRS to change your customized MDX datasets

Avoid SSRS to change your customized MDX datasets

I’ve experienced many times that when I changed the main dataset in SSRS (that reads from an SSAS cube), that the datasets of the parameters are changed again automatically. This has usually been a very annoying thing because I change the MDX in the parameter datasets very frequently.
To avoid this automatic change, you can add SuppressAutoUpdate=true in the code of your report that suppresses the change of this MDX. Right click the report and choose View Code. Find the dataset, and in the relevant node pertaining to your dataset, modify it. Then Save.

<Query>

<DataSourceName>DataSource1DataSourceName>

<CommandText> …

<rd:SuppressAutoUpdate>truerd:SuppressAutoUpdate>

<rd:Hidden>falserd:Hidden>

Query>

Source:
Use colors in your reports consistently

Use colors in your reports consistently

I’m in the process of creating a financial report that in the meantime has blown out of its own proportions. Some changes that seems like easy changes have to be changed in so many places that it’s not so easy anymore.

One of the things was to set colors for percentage figures that represent things like turnover compared to budget, costs compared to last year, and so on. If it concerns income, everything under 90% should be colored red, everything above 110% should be green. For the costs it’s the other way around, red when higher than 110%, green when it’s 90% or less.
Instead of changing the “Color” property of every single cell, I created a function that has the type of value as the input, and the color is returned. The color that is returned depends on the input, this is just an example of how I needed to use it, but it can be applied to any coloring or formatting.
The function looks like this:

Public Function setMarginColor(ByVal FieldType As String, ByVal FieldValue As Single) As String

Dim ReturnString As String = ""

Dim GoodColor As String = "LimeGreen"

Dim BadColor As String = "Red"

Dim NeutralColor As String = "Black"

If FieldType = "Incoming" And FieldValue <> 0 Then

If FieldValue < 0.9 Then

ReturnString = BadColor

ElseIf FieldValue > 1.1 Then

ReturnString = GoodColor

Else

ReturnString = NeutralColor

End If

ElseIf FieldType = "Outgoing" And FieldValue <> 0 Then

If FieldValue < 0.9 Then

ReturnString = GoodColor

ElseIf FieldValue > 1.1 Then

ReturnString = BadColor

Else

ReturnString = NeutralColor

End If

Else

ReturnString = NeutralColor

End If

Return ReturnString

End Function

There are a few things that happen here. First of all I’m setting the variables used and give them a default value. The field types that I use is “Incoming” for things like turnover and profits, and “Outgoing” for costs and losses. Then I evaluate the value passed from the report and by using a couple of if-statements I determine the right color. In the end I return the color.
In the color properties of the textbox or table cell, the following statement needs to be used (in this case I determine the color for last year’s total costs):
=Code.setMarginColor(“Outgoing”, Sum(Fields!Total_costs_LY.Value))
That’s it! When something change in the coloring or you need to use different evaluation criteria, all you have to do is change it in the function. All the properties in the report don’t need to be changed.
You could even add a user input to this. Let’s say the user would be able to set the margins in which the colors need to be applied. For example, this time someone wants the colors to be applied when the percentage is higher than 105% or lower than 95% (so a 5% difference instead of 10%). Obviously the choice of this boundary would be put into a parameter, and the function has to be extended that the parameter is part of the input variables.
I hope this gives you an idea about how you can use a simple function to apply a consistent color scheme to your report!