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

891

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)

Suggested answer

Amit 2,557 on at

Like (0)

Report

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 891 on at

Like (0)

Report

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?

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

Like (1)

Report

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

Inge M. Bruvik 1,105 Moderator on at

Like (0)

Report

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

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November 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

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

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

Featured topics

Product updates