G/L Entries Dimensions

Hello,

i would like to make a SQL View which shows me all 6 dimensions for every GL Entry. Which tables do I have to use for this and how are they related?

Maybe some documentation about this? I couldn’t find anything . . .

Version: Dynamics Nav 2013 R2.

best regards,

I had such TSQL script, even posted it here, but can’t find it on the spot - it was several years ago. I’ll try to find it…

In short, it’s not that easy, because of use of Dimension Sets and how they are implemented in DB tables structure.

Hello, thanks for the answer.
If you can find the script this would be very helpful.
If you can’t find it maybe some tips on how to approach the problem.

best regards,

Uffff, found anyway… but it was hard task digging thru terabytes of crap accumulated over years on my HDDs :slight_smile:

My approach was more universal - the following script creates a VIEW of all defined Dimension Sets in system:

--CREATE VIEW vDimSETS_Pivoted AS	--uncomment this to create a permanent VIEW
									--which can be linked to Ledgers

SELECT
	 [Dimension Set ID] AS SetID
-- replace the following with your actual Dim codes
	,[AREA] 
	,[BUSINESSGROUP]  
	,[CUSTOMERGROUP] 
	,[DEPARTMENT] 
	,[PROJECT] 
	,[PURCHASER] 
	,[SALESCAMPAIGN] 
	,[SALESPERSON] 
 
FROM
	(
	SELECT  
		 [Dimension Set ID]
		,[Dimension Value Code]
		,[Dimension Code]
		FROM [dbo].[CRONUS Canada, Inc_$Dimension Set Entry]
		WHERE [Dimension Set ID] < 20  -- comment WHERE clause out if VIEW is to be created, "<20 "is for testing the script
	) AS SourceTable

PIVOT

(
max([Dimension Value Code])		-- aggregate function here is a MUST, we can use MAX(), 
								-- it does no harm & complies to required syntax

FOR [Dimension Code] 
-- replace the following with your actual Dim codes
	IN ([AREA],
		[BUSINESSGROUP],
		[CUSTOMERGROUP],
		[DEPARTMENT],
		[PROJECT],
		[PURCHASER],
		[SALESCAMPAIGN],
		[SALESPERSON])
) AS PivotTable

examine the script, I tried to comment it at my best, but you know how it stands with those comments :slight_smile:

results look like

PIVOT is required to transpose set dimensions in ONE row, because physically they are stored as 1 to n rows where n = count of Dims in a Set. Next script is for demonstration purpose, your issue doesn’t require it.

USE [Demo Database NAV NA (7-1)]
GO

SELECT [Dimension Set ID]
      ,[Dimension Code]
      ,[Dimension Value Code]
	  ,[Dimension Value ID]			--is unique throughout the table, even for different DimCodes,  can be used to get Dim NAME as follows
      ,(SELECT [Name]
			FROM [dbo].[CRONUS Canada, Inc_$Dimension Value] DV
			WHERE DV.[Dimension Value ID] = DSE.[Dimension Value ID]) as [Dimension Name]
  FROM [dbo].[CRONUS Canada, Inc_$Dimension Set Entry] DSE

  WHERE [Dimension Set ID] = 11 -- debug (to eliminate the output while testing)
GO

pastedimage1485530618774v2.png

Then, link the VIEW vDimSETS_Pivoted in your scripts with any Ledger Table as needed, not only G/L Ledger. All Ledger tables (+some other, too, BTW) contain field [Dimension Set ID]

Hope this helps,

Hello,

thanks man. That’s exactly what i needed.
This information saved me alot of time.

best regards,