December 13, 2009

SSRS Row and Group Background Formatting

For Visual Stuido 2005:

Select the whole row, open the "Properties" pane. In the Background property click the drop down box and select "Expression". In the pop up box, copy/paste the =IIF(...) statement. Click OK.

Alternate the row color for Detail Lines:
=IIF(RowNumber(Nothing) Mod 2, "#eeeeee", "#ffffff")

Group Rows Alter row color: ("PO_NUM.Value" is the field that is being grouped on)
=iif(RunningValue(Fields!PO_NUM.Value,CountDistinct,Nothing) Mod 2, "#eeeeee", "#ffffff")

Alter text color dynamically:
1. Left-click the row and column that you’d like to have dynamicly formatted.
2. Go to Properties Pane and select the drop-down box under Color in the Appearance group. Select the "Expression" option.
3. Replace the word Black (which is the default) with one of the following text and click OK.
=iif(Fields!JobStatus.Value = "Failed", "Red", "Black")

Same with dynamically making the text bold, except choose the "Font-style" under "Font".
=iif(Fields!JobStatus.Value = "Failed", "Bold", " Normal ")

Parameter to toggle all visible all hidden:
Create a 'Report Parameter' by going to the 'Report' menu and choosing "Report Parameters". You have to be in either "Layout" or Data tab to see the 'Report' menu in the top tool bar.

After choosing "Report Parameters" a pop-up box appears. Create a new parameter called "Expand" and give it default values of 0 and 1. So, say 0 is not expanded, and 1 is. Give your parameter a default value to either always initially open the report with the parameter set to not expand, or to be expanded. Then click OK to close the Report Parameters pop up.
Choose the details row that you want to toggle visibility on. Right click and choose "Edit Group". Go to the Visibility tab and select "Expression" radio button. In the textbox type in the expression below:

=Parameters!Expand.Value = 0

Select the Group or item that the detail is dependent on. For instance.. you have Customers as a group, which is grouped on customer name.  Select the Customer Name textbox as the Toggle item. Click OK and run the report.

If you have questions, just ask.

No comments:

Post a Comment