topic Re: PBRS March Update breaking existing RDL reports that use LOOKUP function in Report Server

PBRS March Update breaking existing RDL reports that use LOOKUP function

BenMackin — Mon, 26 Mar 2018 20:48:50 GMT

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.

Re: PBRS March Update breaking existing RDL reports that use LOOKUP function

mgmeyer — Mon, 26 Mar 2018 21:19:56 GMT

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.

Re: PBRS March Update breaking existing RDL reports that use LOOKUP function

BenMackin — Mon, 26 Mar 2018 22:22:20 GMT

@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.

Re: PBRS March Update breaking existing RDL reports that use LOOKUP function

BenMackin — Tue, 27 Mar 2018 23:33:19 GMT

@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.

Re: PBRS March Update breaking existing RDL reports that use LOOKUP function

mgmeyer — Tue, 27 Mar 2018 23:58:39 GMT

Thanks Ben for the update, we are actively looking into the issue and hope to have a fix soon. To your question around changes there we changes that do impact RDL reports however most of the them were infrastructure related.

Re: PBRS March Update breaking existing RDL reports that use LOOKUP function

BenMackin — Wed, 28 Mar 2018 00:48:30 GMT

Awesome.

If there are any log files, or stack trace files, that I can send let me know. More than willing to help out troubleshoot.

Re: PBRS March Update breaking existing RDL reports that use LOOKUP function

Anonymous — Wed, 28 Mar 2018 07:35:06 GMT

Is this a general issue. In other words: the "Lookup" functions don't work at all in this PBRIS March 2018 release.

My customer has some 15 reports using this kind of functionality.

Re: PBRS March Update breaking existing RDL reports that use LOOKUP function

BenMackin — Wed, 28 Mar 2018 15:55:11 GMT

Hard to say.

I have many reports that use lookup. Some of them have no issues. Some of them have issues. I can't exactly tell what causes the #Error, but it seems like if the lookup is used in conjunction with anything else, it fails.

Best thing I can say to do is test it out. Install the update on a test instance, and load up your RDL files. Initialy, our plan was to spot check reports, since it didn't seem like there was any real changes around RDL reports in this release. But seeing as how we have found these breaking issues, we are going through the process of evaluating all 180 reports we have on our system.

Re: PBRS March Update breaking existing RDL reports that use LOOKUP function

Anonymous — Wed, 28 Mar 2018 17:39:02 GMT

@BenMackin Thank you for your feedback. We used the "Lookup" function to display the data in conjuction with other data sources data, and used in the sorting of the tables/matrices as well. Both sorting and display caused issues in all our reports.

@mgmeyer We decided to rollback to the October 2017 version, which wasn't a simple uninstall of the March version, and a re-install of the October version. It seemed that the "ReportServer" database scheme was changed as well by the "March version" because the PBIRS/SSRS service couldn't connect to the "ReportServer" database anymore. After a restore of the "ReportServer" database with a back-up just before the upgrade everything was working again.

- Could you confirm that the "March version" indeed applied some changes to the "ReportServer" database scheme.

- When will a patch be available for the reported issues so far?

BR Frank

Re: PBRS March Update breaking existing RDL reports that use LOOKUP function

Anonymous — Fri, 30 Mar 2018 07:53:02 GMT

@mgmeyer I noticed that a new patch/build was released for the PBIRS "March Version".

However the changelog doesn't mention that this bug/issue has been fixed as well. Could you verify whether this bug/issue was also (unmentioned) fixed in the latest patch, or that it will be fixed in another patch?

Version 1.2.6660.39920 (Build 15.0.2.389), Released: March 28, 2018

Bug fixes
- For Power BI Reports (PBIX), fix for Export Data not working from Power BI Visuals
- For Power BI Reports (PBIX), fix for URL filters not working
- For Paginated Reports (RDL), fix for images not being displayed correctly in IE11 after upgrading to Power BI Report Server March release

Re: PBRS March Update breaking existing RDL reports that use LOOKUP function

BenMackin — Fri, 30 Mar 2018 18:36:20 GMT

@Anonymous

I just installed the Version 1.2.6660.39920 (Build 15.0.2.389), Released: March 28, 2018 and tried with some of my RDL reports, and they are still getting the same errors when using the LOOKUP functions. So this is not fixed yet.

@mgmeyer

Is there any time frame for when this will be resovled?

Re: PBRS March Update breaking existing RDL reports that use LOOKUP function

mgmeyer — Fri, 30 Mar 2018 19:34:26 GMT

Hey Ben, unfortuantely we didn't have a fix for this issue at the time we released the latest update but we are actively looking into this. We have a couple options we're looking at one of which is very promising, if it works out we should have an update very soon. As soon as I have some more information I will post it here.

Re: PBRS March Update breaking existing RDL reports that use LOOKUP function

BenMackin — Fri, 30 Mar 2018 19:59:49 GMT

Thank you for feedback.

If there is anything I can provide to assist (RDL files, log files, etc) please let me know.

Re: PBRS March Update breaking existing RDL reports that use LOOKUP function

messerjc — Sat, 31 Mar 2018 06:41:07 GMT

Thank you for calling this out, Ben. You may have just saved our team a significant amount of pain. I suspect a decent number of our paginated reports are using LOOKUP functions but I'll have to confirm next week.

Re: PBRS March Update breaking existing RDL reports that use LOOKUP function

Anonymous — Sat, 31 Mar 2018 09:22:39 GMT

@mgmeyer Thank you for your confirmation that, unfornunately, this bug/issue hasn't been solved in the latest patch.

I am convident that Microsoft will deliver a patch at a short term notice to solve this bug as well.

Anyway: Happy Eastern!

Re: PBRS March Update breaking existing RDL reports that use LOOKUP function

Anonymous — Wed, 04 Apr 2018 12:06:34 GMT

@mgmeyer Is there already a date known when this bug will be fixed in a new patch? Our Power BI-report-designers are getting a bit impatient...

Re: PBRS March Update breaking existing RDL reports that use LOOKUP function

tp8 — Mon, 09 Apr 2018 18:02:28 GMT

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.

Re: PBRS March Update breaking existing RDL reports that use LOOKUP function

bruce1981 — Wed, 11 Apr 2018 18:54:01 GMT

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,???

Re: PBRS March Update breaking existing RDL reports that use LOOKUP function

bruce1981 — Wed, 11 Apr 2018 18:55:26 GMT

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,???

Re: PBRS March Update breaking existing RDL reports that use LOOKUP function

Anonymous — Fri, 13 Apr 2018 08:16:53 GMT

Hi @mgmeyer

2 weeks ago you mentioned that a possible solution was found to fix this issue.

This issue prevents us to put the PBIRS March Version into production (again).

The March version includes some new PowerBI functionality that our Dashboard designers are waiting for a long time already.

Could you please communicate a time schedule when a new PBIRS March patch will be available that also fixes this issue.

Or is MS planning to deliver an April version that includes the latest PowerBI functionality such as "buttons to trigger actions" and "Combo chart line styles"?

Thank you in advance,