Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Lookup function error ...
Microsoft Dynamics GP (Archived)

Lookup function error in SSRS

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

I have the following lookup code:

=Lookup(Fields!Item_Number.Value,Fields!Item_Number.Value,Fields!Item_Short_Name.Value,"Inventory") 

I am getting this error:

The expression used for the calculated field '=Lookup(Fields!Item_Number.Value,Fields!Item_Number.Value,Fields!Item_Short_Name.Value,"Inventory")' includes an aggregate, RowNumber, RunningValue, Previous or lookup function. Aggregate, RowNumber, RunningValue, Previous and lookup functions cannot be used in calculated field

I have no computed or calculated expressions in any of the fields.  I have two datasets, Equipment which is bound by the report and Inventory. 

Any help where to look would be appreciated.

*This post is locked for comments

  • Suggested answer
    John Lowther Profile Picture
    John Lowther 5,122 on at
    RE: Lookup function error in SSRS

    Well, I do not think that "first" is exactly what you want to do. But you could try this.

    Put a text box somewhere on the report and instead of doing the lookup have it place the values of Fields!Item_Number.Value,Fields!Item_Number.Value,Fields!Item_Short_Name.Value,"Inventory". To see what the values actually are. It may be necessary to put some sort of delimiter between them so you can read the text box easier.

    My guess, and keep in mind that it is a guess is that the first two things in your text box the "Fields!Item_Number.Value,Fields!Item_Number.Value" parts will actually be the exact same thing when (from what I understand you are trying to do) they should be two different values.

    Let me know how it goes,

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Lookup function error in SSRS

    John,

    I double checked the datasets, and Item_Number is used in both of them.  Do I need to identify the second object with first(fields!Item_Number) to separate it from the other object?  Just a thought.

    Thanks

    Joel

  • John Lowther Profile Picture
    John Lowther 5,122 on at
    RE: Lookup function error in SSRS

    Without digging into your SSRS report I am not really sure; however, I did notice that the first two objects in your lookup were the same "Fields!Item_Number.value" usually, or at least in the cases that I have seen they are different like "=Lookup(Fields!SaleProdId.Value, Fields!ProductID.Value,  Fields!Name.Value, "Product")"

    But then I could be totally off base, please let me know how it goes,

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,253 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,188 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans