Small and medium business | Business Central, N...

Report Builder - Calculated field. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate

(0) Share

Report

Posted on by Nick Webb

911

I'm trying to use a calculated field that I've added to my Dataset in Microsoft Report Builder launched from an AL extension built in Visual Studio Code.

I've added the Calculated field ("TimeInDays") OK, (Expression below):-

=iif(Fields!UnitOfMeasure.Value = "DAY", Fields!Quantity.Value, Fields!Quantity.Value * 7.5)

I've dragged it on to the body of the report, OK.

However, when I try and view the report in Business Central, I get the error below:

Rendering output for the report failed and the following error occurred: The Value expression for the text box ‘TimeInDays’ refers to the field ‘TimeInDays’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case.

Where am going wrong?

I have the same question (0)

All responses (4)

Answers (0)

Sort by

Suggested answer

Amit 2,563 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,

Pls check this expression in AL code "‘TimeInDays’". If it is there, then publish the extension in sandbox and then open the report editor and assign the variable "‘TimeInDays’" in the formula.

Regards

Amit Sharma

www.erpconsultors.com

Was this reply helpful? Yes No
Nick Webb 911 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for your response.

After I've created the calculated field, saved the report, exited MS Report Builder and returned to VSCode, if I then view the .rdl in VSCode I see the TimeInDays Field node in the XML (as below). As soon as I hit Ctrl-F5 to deploy and test it, this Field node disappears, and it errors as previously reported..

<DataSet Name="DataSet_Result">
<Query>
<DataSourceName>DataSource</DataSourceName>
<CommandText />
</Query>
<Fields>
<Field Name="FirstName">
<DataField>FirstName</DataField>
</Field>
....
<Field Name="TimeInDays">
<Value>=iif(Fields!UnitOfMeasure.Value = "DAY", Fields!Quantity.Value, Fields!Quantity.Value / 7.5)</Value>
</Field>

Any thoughts?

1 people found this reply helpful.

Was this reply helpful? Yes No
Nick Webb 911 on at

Like
a
(1)

Report
Copy link

Link copied!

I don't know if this is the text-book solution but, I got round this by adding a couple of dummy columns in the AL Report module as below:

column(TimeInDays; gdecTimeInDays) { }

column(TimeInHours; gdecTimeInHours) { }

var

gdecTimeInDays: Decimal;

gdecTimeInHours: Decimal;

Was this reply helpful? Yes No
Suggested answer

IB-29041624-0 1,191 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

The solution you picked is just the way you have to do it. So a pretty good text-book example in my eyes.

Keep up the good work!

Was this reply helpful? Yes No

Join the conversation

Community site session details

Report Builder - Calculated field. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate

Helpful resources

News and Announcements

Quick Links

Season of Sharing Community Challenge Winners!

Women in Power Builds Momentum

Congratulations to the June Top 10 Community Leaders

Leaderboard > Small and medium business | Business Central, NAV, RMS

Featured topics

Product updates

Community site session details

Report Builder - Calculated field. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate

Helpful resources

News and Announcements

Quick Links

Season of Sharing Community Challenge Winners!

Women in Power Builds Momentum

Congratulations to the June Top 10 Community Leaders

Subscribe to this forum!

Select categories

Leaderboard > Small and medium business | Business Central, NAV, RMS

Featured topics

Product updates