Forum Discussion
PBRS March Update breaking existing RDL reports that use LOOKUP function
It looks like something in the March update for Power BI Report Server is causing a breaking issue on RDL/SSRS reports that utilize the Lookup function (or it's derivatives LookupSet or MultiLookup).
For example, I have a column defined as:
=SUM(Fields!Loan_Balance.Value) / Lookup(Fields!Year_and_Month_and_Group.Value,Fields!Year_and_Month_and_Group.Value,Fields!Customer_Count.Value,"ReportDetails_Summary")
In the October release of PowerBI Report server, this works just fine, and returns the values I would expect. Also in Visual Studio 2015 with SSDT 14.0.61712.050, the report will preview just fine.
However, when I deploy this report to an upgraded server running the March release of Power BI Report Server, the column just returns with an "#Error". I can't seem to find anything in the log file that shws why this is an error. If I remove the SUM component, and just use the lookup, I get back a value. But for some reason the combination of the two functions doesn't work on the latest version.
The changelog should be updated soon but to answer your question yes this build addresses the nested Lookup function expression errors as well as several other bug fixes.
I just wanted to confirm that the latest build:
Version 1.2.6690.34729 (Build 15.0.2.402)
Does indeed fix the issues we were having. At this point, we are going to be moving this to our produciton instances, as it has passed all of our internal regressions.
30 Replies
- Any news? Very disappointed about the non-communication from the power bi team.
At least we should get a rough timeframe till you can provide a fix: are we talking about 1 week, 1 month,??? Thanks for reporting the issue this is something we are looking into. One question for you, are you using the Lookup function in the Header/Footer of the report or is it on the main canvas? How many reports do you have that are impacted by this? We do have one possible work, you can create a report level variable that contains the Lookup function and then reference that in your nested expression.
mgmeyerwrote:Thanks for reporting the issue this is something we are looking into. One question for you, are you using the Lookup function in the Header/Footer of the report or is it on the main canvas? How many reports do you have that are impacted by this? We do have one possible work, you can create a report level variable that contains the Lookup function and then reference that in your nested expression.
The lookup function is in the tablix on the main canvas.In total, we have 10 reports that are impacted.
As for the variable, I don't think that solution will work. Right now, we have the lookup being using in a column on a tablix. So it is repeated for each row. If I try and use a variable, I get the following error:
The Variable(LookUpTest1) expression for the report 'body' refers diorectly to the field 'Year_and_Month_and_Group' without specifing a dataset aggregate. When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions which specify a dataset scope.
In my case I don't think I can perfrom any aggregates, as the data is row level.
mgmeyer, it looks like there are some more bugs we have identified with the RDL/Paginated reports in this latest March update of Power BI Report Server
When we use an aggregate function in a page footer, we get an #Error message on the new version. On the old October version this worked fine.
Code example:
="Market(s): " + IIF(Parameters!P_MA.Count = Sum(Fields!ParameterLevel.Value, "P_MA_Param"), "All", Join(Parameters!P_MA.Label, ", "))
We were able to work around using a variable:
="Market(s): " + IIF(Parameters!P_MA.Count = Variables!MarketAll.Value, "All", Join(Parameters!P_MA.Label, ", "))
But if we need to update all reports for this bug, it becomes an issue. Were there some regressions that got missed when building this new release? Looking at the change log, there isn't a lot of changes to the RDL/Paginated reports, so it is surprising to find so many breaking issues.
I'm also curious as to whether there is a timeframe on when the bug fix for this will be released. We desperately need the March release in production but cannot update until this bug is fixed.
- Any news? Very disappointed about the non-communication from the power bi team.
At least we should get a rough timeframe till you can provide a fix: are we talking about 1 week, 1 month,???
