2

I am creating an SSRS2012 report.

I have a matrix with static columns and nested row groups. I would like to add a top and bottom border on my rows based on the parent group. The matrix looks as follows:

[ProcessCell] | [Name]  | [Field]|

My groups are: ProcessCell1 (parent) - Name (child)

I am currently using standard formatting to get [ProcessCell]'s borders and it works. I am using an expression (found in another article) for [Name]'s borders and it works, however the expression does not work for the [Field] box. The expression is as follows:

=IIf(Fields!ProcessCell.Value = Previous(Fields!ProcessCell.Value, "Name") OR Not(Fields!ProcessCell.Value = First(Fields!ProcessCell.Value, "Name")), "Light Gray", "Black")

I have tried changing the scopes of previous and first but it did not work.

The error I am getting is "BorderColor expression for the text box 'Field' has a scope parameter that is not valid for an aggregate function"

ekad
  • 14,436
  • 26
  • 44
  • 46
lilGrim
  • 23
  • 2
  • 5

1 Answers1

2

I had exactly the same problem today (well with a Tablix, not a matrix, and in SSRS 2008 R2). I couldn't get the Previous() function to work properly and not throw the same "BorderStyle expression for the textbox..." error that you're seeing.

What I did instead was add a new field to my Dataset that uses the ROW_NUMBER() analytic SQL function to categorize my results. The relevant fields in my report were UserName, ReportName, and DatetimeRun (yes, this was a report on report usage). I wanted borders to appear between each unique combination of UserName and ReportName, so I added this field to my dataset:

ROW_NUMBER() over (partition by UserName,ReportName order by UserName,ReportName)
as RowNum

which for each unique combination of UserName and ReportName (the fields in the Partition by portion), starts at 1 and sequentially numbers the rows returned.

Back in my BorderStyle/Top expression, instead of using Previous() I used this:

=iif(Fields!RowNum.Value = 1,"Solid","None")

to display a border only at the beginning of a new UserName/ReportName combination.

Hope this helps!

Lucas
  • 78
  • 1
  • 10