CODEWITHSUNDEEP
reporting-servicesssrs-tablix
Gibblet
Gibblet

Reputation: 21

Use of a Previous aggregate function in a TablixCell error in one tablix, not in another

I have an SSRS report that works just fine with this code for checking the previous cell in a tablix:

=IIF(SUM(Fields!Transactions.Value) >       
           Previous(SUM(Fields!Transactions.Value),"matrix1_Month"), "Green", "Red")

This works in the report in the first tablix, matrix1, just fine.

I copied that tablix, used a different Different dataset, called it xForeignCardholders, and used this code:

=IIF(Fields!Transactions.Value > 
           Previous(Fields!Transactions.Value),"xForeignCardholders_Month", "Green", "Red")

It is the same except for the name of the tablix.

Now the report gives me the error

"Use of a Previous aggregate function in a TablixCell ..."

Why would it work in the first instance, and not in the second? The report is just one datacell, with no aggregation. In a previous version of this report, three different tablixes (tablii?) all used the same kind of code without error.

I can fix this in SQL, but it would really add a lot of complexity to something that should be really straightforward.

Upvotes: 2

Views: 4184

Answers (2)

AlsoKnownAsJazz
AlsoKnownAsJazz

Reputation: 495

My situation while similar, is not quite identical - so may not be what you're running into, but I've gotten that exact error, and it was because my grouping had changed - that is, each grouping has a unique name, so if you're copying expressions from one table to another, you have to make sure you've updated the group name in your 'Previous' expression. I was using a field called "Year" in both tables, but it appears you can't repeat a group from one table in another. So when I copied my cell (and the expression therein), the destination grouping was "Year1" or "Year2" etc. The expression had to be updated accordingly.

In your case, make sure that the each expression has the right group name, in your example, "xForeignCardholders_Month". You may find that the group name is actually "xForeignCardholders_Month1" or something similar.

I also found that when I would copy/paste the <> from one cell to another, it drops most of the expression. You have to edit the original expression, copy it, and edit the expression in the new cell, and paste it. This only seems to happen when using a small report code script calculate YoY delta, and everything after "code.GetDeltaPercentage" was being dropped from my expression when I copied. Just something to look out for.

Update: I just discovered that sometimes, after a copy/paste of some cells, after verifying the expressions were exactly the same, I would still get this error. Delete the contents of the cells, choose the Sum(Field.value) from the selector icon in the cell, and then edit the expression on the adjacent cell and plug in my 'Previous' expression, and it would work. If there's a good reason for this behavior, it's beyond me. Seems like another VS Bug. :|

I'm self taught, so I may have some terminology wrong, but hopefully that gets the idea across anyway.

Upvotes: 2

Alexey
Alexey

Reputation: 1539

I think the problem is that in the first instance you use an agregate function, whien in the second you use the value itself, without the aggregation, compare:

IIF(SUM(Fields!Transactions.Value) > Previous(SUM(Fields!Transactions.Value),"matrix1_Month"), "Green", "Red")

and

=IIF(Fields!Transactions.Value > Previous(Fields!Transactions.Value), "xForeignCardholders_Month", "Green", "Red")

 

Best regards,

~Alexey

Upvotes: 0

Related Questions