AL Query Object to Lookup Dimension Code Values

(0) Share

Report

Posted on by DinB

3,808

Please give me example of AL Code to create Query Object that will give similar results as following simplified SQL Query. Basically need to lookup [Dimension Set Entry]Table to get Dimension values.

Select [No.], [Sell-to Customer No.],
(Select [Dimension Value Code] from [Dimension Set Entry] where [Dimension Set ID] = [Sales Invoice Header].[Dimension Set ID] and [Dimension Code] = 'CUSTOMERGROUP') as [Customer Group],
(Select [Dimension Value Code] from [Dimension Set Entry] where [Dimension Set ID] = [Sales Invoice Header].[Dimension Set ID] and [Dimension Code] = 'DEPARTMENT') as [Department]
From [Sales Invoice Header]

The expected results is like this:

Thank you

Categories:

Reports and Business Intelligence

All responses (1)

Answers (0)

Suggested answer

YUN ZHU 62,477 Moderator on at

Like (0)

Report

AL Query Object to Lookup Dimension Code Values

Hi, Unfortunately, as far as I know, it is not possible to use Query to do what you see in the Excel table.

Because you mentioned Customer Group, Department is not a column in the table.

You can do the following:

Hope this helps.

Thanks.

ZHU