CODEWITHSUNDEEP
reporting-servicesssrs-2012
Marc
Marc

Reputation: 107

SSRS - Expression with Lookup in child group causes repeating label values

I created a matrix with two column groups (year and quarter).

groups

The expression for the quarter label (the expression under [year]) is: 

"Q " & CStr(Fields!quarter.Value)

So in the report I get Q1, Q2, Q3 and Q4 as quarter labels.

We have a international team so I need the others labels to be in german or in english, depending on the user. So I created a dataset with all translations which is filled from a translation table in the database. To get the correct translation for the label I use the LOOKUP() function.

When I use LOOKUP() to get the correct translation for the "amount Rest" textbox (located in the year group) it works fine:

lookup-amountrest

=LOOKUP(11, Fields!ID.Value, Fields!description.Value, "ds_Labels")

The result:

result1

But when I try to use LOOKUP() to get the translation for the "amount" textbox, which is located in the quarter group, the correct translation appears but the year label also starts with 2014 instead of 2008 and the quarter and year labels repeat:

repeat

=LOOKUP(17, Fields!ID.Value, Fields!description.Value, "ds_Labels")

The result:

repeat2

I've no idea what kind of bug this is and how I can get rid of it. Maybe someone can help me :)

UPDATE

Like I mentioned in the comments, the LOOKUP() function works in the first three columns of the quarter group. The report goes crazy if I try to use LOOKUP() in the fourth textbox of the quarter group (see the second last screenshot). But I still don't understand why...

UPDATE 2

When I deploy the report on a SSRS 2008R2 the report works completely fine. But I need it to run on SSRS 2012. Maybe it is a SSRS2012 bug?

Upvotes: 2

Views: 1610

Answers (2)

torvin
torvin

Reputation: 7101

I believe it's a bug. Running Lookup() / LookupSet() queries against the same dataset that is being currently rendered in a tablix causes duplicates.

The only workaround I found is to pass a copy of the dataset and run Lookup() queries against that copy. This way everything works fine.

Upvotes: 1

tember
tember

Reputation: 1496

Have you made sure that only one record is being returned when you look up the record(s) associated with id 17? The function is designed to be used for 1 -to- 1 relationships

Upvotes: 1

Related Questions