Skip to main content

Notifications

Announcements

No record found.

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

OData - Get Dimension Set ID for Posted General Journals?

(0) ShareShare
ReportReport
Posted on by 115

Some web services only seem to return the 2 global dimension values and don't include a dimension set ID to reference the additional values.

Customer Ledger Entries for instance includes a dimension_set_id field which I was able to expand via Power Query into as many separate columns as needed, but have not been able to figure out how the Posted General Journals correspond to the additional dimension values. They're clearly linked somehow, since you can see the full list of values in the Dimension summary section to the right when reviewing the entries within BC.

I'm hoping there's another query that I can use to cross reference the entries, but I'm struggling to figure out what that would be from the fields that are currently being exported.

Thank you in advance for any feedback.

  • Suggested answer
    CU11121527-0 Profile Picture
    CU11121527-0 154 on at
    RE: OData - Get Dimension Set ID for Posted General Journals?

    You either have to create a new web service against the Table object 181 and publish that object, or use an existing web service object (if there is one) and add those missing fields in my screenshot.

  • Mark Cosgrove Profile Picture
    Mark Cosgrove 115 on at
    RE: OData - Get Dimension Set ID for Posted General Journals?

    Weird, I don't seem to have that option! 

    181 is Additional Customer Terms for us.

    pastedimage1674606073502v1.png

    I tried adding a new Web Service page, but couldn't find "Posted Gen. Journal Line" as an option.

    pastedimage1674606146053v2.png

    Thanks so much for the response though. I'll do some additional searching to see if there's something that I'm missing in order to get that.

  • CU11121527-0 Profile Picture
    CU11121527-0 154 on at
    RE: OData - Get Dimension Set ID for Posted General Journals?

    Hi 

    I think you are looking for table 181 "Posted Gen. Journal Line" see the screenshot below - 

    posted-gen-journal-line.png

    Good Luck!

  • Mark Cosgrove Profile Picture
    Mark Cosgrove 115 on at
    RE: OData - Get Dimension Set ID for Posted General Journals?

    In the meantime I'll share the Power Query code that I use to expand the Dimension Sets, in case anybody finds it of value. I'm no expert with the M language, so I'd imagine that it could be done in fewer lines, but here you go anyway...

    let

    Source = OData.Feed("api.businesscentral.dynamics.com/.../[ID]/Production/ODataV4/Company('[COMPANY]')/DimensionSetEntries", null, [Implementation="2.0"]),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Dimension_Set_ID"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Add DEPT" = Table.AddColumn(#"Removed Duplicates", "DEPT", each "DEPT"),
    #"Merge DEPT" = Table.NestedJoin(#"Add DEPT", {"Dimension_Set_ID", "DEPT"}, DimensionSets, {"Dimension_Set_ID", "Dimension_Code"}, "DimensionSets", JoinKind.LeftOuter),
    #"Expand DEPT" = Table.ExpandTableColumn(#"Merge DEPT", "DimensionSets", {"Dimension_Value_Code", "Dimension_Value_Name"}, {"DEPT Code", "DEPT Name"}),
    #"Remove DEPT" = Table.RemoveColumns(#"Expand DEPT",{"DEPT"}),
    #"Add PROJECT" = Table.AddColumn(#"Remove DEPT", "PROJECT", each "PROJECT"),
    #"Merge PROJECT" = Table.NestedJoin(#"Add PROJECT", {"Dimension_Set_ID", "PROJECT"}, DimensionSets, {"Dimension_Set_ID", "Dimension_Code"}, "DimensionSets", JoinKind.LeftOuter),
    #"Expand PROJECT" = Table.ExpandTableColumn(#"Merge PROJECT", "DimensionSets", {"Dimension_Value_Code", "Dimension_Value_Name"}, {"PROJECT Code", "PROJECT Name"}),
    #"Remove PROJECT" = Table.RemoveColumns(#"Expand PROJECT",{"PROJECT"}),
    #"Add EVENT" = Table.AddColumn(#"Remove PROJECT", "EVENT", each "EVENT"),
    #"Merge EVENT" = Table.NestedJoin(#"Add EVENT", {"Dimension_Set_ID", "EVENT"}, DimensionSets, {"Dimension_Set_ID", "Dimension_Code"}, "DimensionSets", JoinKind.LeftOuter),
    #"Expand EVENT" = Table.ExpandTableColumn(#"Merge EVENT", "DimensionSets", {"Dimension_Value_Code", "Dimension_Value_Name"}, {"EVENT Code", "EVENT Name"}),
    #"Remove EVENT" = Table.RemoveColumns(#"Expand EVENT",{"EVENT"})

    in

    #"Remove EVENT"

    Just update the Source line at the beginning with your own OData feed, then adjust the dimension names accordingly.

    For example, if your dimension was named DEPARTMENT instead of DEPT or whatever else, just replace DEPT with DEPARTMENT, and so on.

    #"Add DEPT" = Table.AddColumn(#"Removed Duplicates", "DEPT", each "DEPT"),

    would become

    #"Add DEPARTMENT" = Table.AddColumn(#"Removed Duplicates", "DEPARTMENT", each "DEPARTMENT"),

    Note that the first line of code for the next dimension will reference the previous line, so DEPT gets listed once within the PROJECT lines, and then the "in" at the bottom will be whatever you end with.

    You can then of course keep copying those rows down and adding more dimensions, just remember to add a comma to the last line before you paste it in, or you'll have an error in your code.

    That's it. You can now merge that into whatever other source that you've got which has a department_set_id and have as many dimension columns as you'd like!


    Additional references:

    The examples below just pivot the dimension sets directly which is much simpler. I used the method above because we only wanted certain dimensions in certain reports. Although you could just merge and only expand the dimension columns you need in each case... so maybe it was that I wanted both the dimension codes and values that I did it this way.

    Data model in Power BI and Dimension Set ID - Dynamics 365 Business Central Forum Community Forum

    Dealing with Dimensions - Hooking Power BI up to Microsoft Dynamics NAV (and Dynamics 365 for Financials!) (linkedin.com)

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 Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,445 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans