Dynamics Community Forum Thread Details

GL Entries with dimensions

The GL Entries table stores the values for two dimensions in columns Global Dimension 1 Code and Global Dimension 2 Code

but we have instances where we have more than two dimensions associated with an account.

I am trying to build a query that will dynamically show multiple dimension values for a gl entry in their own columns in a single row.

older versions of Nav we were able to accomplish this with a matrix but I do not see where BC has the same functionality.

the GL table has the Dimension Set ID which gets me to the Dimension Set Entry table (so far one to one relationship)

the Dimension Set Entry table gets me to the Dimension Value table (this is where the multiple values come in to play)

instead of the query adding multiple rows to accommodate the Dimension Values I need the query to add columns to a single row to display the values.

in older Nav we could use a matrix or a flowfield. How would we accomplish this in Business Central?

thank you

We have achieved this using Power Query in Excel. Use the web services to pull Page 20 - General Ledger Entries (this contains the Global Dimension 1 & 2 columns and the link to the Dimension Set). We loaded that as a data query only. Then we brought in Query 260 for the Dimension Set Entries also as a data query only. On that data query, we filtered to only show Shortcut Dimension 3. Then we used the merge query to add that as a column. We pulled Query 260 a second time and filtered for Shortcut Dimension 4. Then we used Merge Query to merge that into the previous version. This final query we returned to Excel as a table.

We also within the Excel file included parameters for the GL codes and dates which we fed back into the web service so the database did the filtering.

Probably not the most beautiful solution, but works effectively, users understand it and they simply open the Excel file and update their parameters and the data is returned.

Hope that sends you in the right direction.

Cheers

Heather

Quick Links

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